Re: ADO.net Connection - Database Discussion Boards

Re: moving database in sql (question again)

PlayByTheRules3-Jul-06 5:17

PlayByTheRules

3-Jul-06 5:17

shaz jazz wrote:
2

You mean "to", "too" or "two"?

shaz jazz wrote:
8

Are you really that lazy that you cannot type "at"? (It is the only word that fits, but it took me quite a few seconds to figure that out - Do you really expect a prompt reply when it takes people an unnecessary amount time to figure out what you are writing?!

shaz jazz wrote:
r

"are" - You know how difficult it is to read this crap?!

shaz jazz wrote:
u

Another weird abbreviation - Or are you now speaking Dutch?

shaz jazz wrote:
ur

"your" or "you're"?? C'mon if you want to be taken seriously don't write this SMS style crap.

shaz jazz wrote:
thx

THX is a quality standard for sound repoduction in cinemas and home cinema surround sound systems.

Re: moving database in sql (question again)

Colin Angus Mackay3-Jul-06 5:32

Colin Angus Mackay

3-Jul-06 5:32

shaz jazz wrote:
is owner of database and login same??

I don't know - that depends on your database.

If the user in the database has db_owner rights then the user will be the owner of the database, and that user must be associated to a login.

shaz jazz wrote:
there is no owner of this database

Find out which user or users are marked as the database owners (they have db_owner rights on the database, or sysadmin rights over the whole server) and apply them on your database at home.

shaz jazz wrote:
but it doesn't accepts it and gives error of unregistered owner

I've never seen this error before, So I'm guessing that you have to use the same user as the owner as where the database was created. You need to use ensure that user has a valid login and that user is granted the db_owner rights on your server at home. As it is a lab experiment, you might want to elevate that to sysadmin rights (which will give that user the right to do anything on the server, not just the database).

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: moving database in sql (question again)

Colin Angus Mackay3-Jul-06 5:34

Colin Angus Mackay

3-Jul-06 5:34

Wait a moment: Are you moving a SQL Server 2000 database onto a SQL Server 2005 system? If so then the diagrams won't work - It is a known limitation, SQL Server 2005 does not read SQL Server 2000 diagrams and the tools for SQL Server 2005 won't create diagrams on a SQL Server 2000 database.

Re: moving database in sql (question again)

Frank Kerrigan3-Jul-06 22:18

Frank Kerrigan

3-Jul-06 22:18

"(You'll then have to wire up the logins again as the login ids will be different because they are stored in the master db)" i just cant get it?
"

If you restore a db with its security context (It has users). You have to delete them from the db then add then again. If the users don't exist on the SQL box you have to create them.

Look where you want to go not where you don't want to crash.

Bikers Bible

Convert filtered dataview to datatable

Rickey_Me1-Jul-06 2:05

Rickey_Me

1-Jul-06 2:05

I have a dataview then i use rowfilter on htis dataview.
now i want to convert this filtered dataview to datatable or to dataset.Is there any method or i have to write my own logic.

Rickey

Re: Convert filtered dataview to datatable

Frank Kerrigan3-Jul-06 22:21

Frank Kerrigan

3-Jul-06 22:21

OK your slightly unclear here.

You can create a new datatable in your current dataset and "copy" the data from the dataview in there.

or

Create a new dataset, create a new datatable in the new dataset and you guessed it "copy" the data from the dataview in there.

DataSets are collecton holders of Tables, Views etc.

Look where you want to go not where you don't want to crash.

Bikers Bible

ADO.net Connection

Maira K1-Jul-06 0:51

Maira K

1-Jul-06 0:51

Hi Everybody,

I have a problem with the ADO.NET Connection which i use in my coding in VB.net 2005, to connect to the SQL Server 2000.

This connection works properly on a local machine , when i set the servername as localhost. but when i set the servename as the name of the server on which the SQLServer is installed, it doesn't work. whereas the ADO Connection does.(Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Test;Data Source=" & ServerName)

The ConnectionString I'm using is "Data Source=" & ServerName & ";Initial Catalog=DatabaseName;Integrated Security=true"

Thanks, in advance
Regards,

Myra

Re: ADO.net Connection

Colin Angus Mackay1-Jul-06 1:18

Colin Angus Mackay

1-Jul-06 1:18

Myra EL KHOURY wrote:
I have a problem with the ADO.NET Connection which i use in my coding in VB.net 2005, to connect to the SQL Server 2000.

And the error is???

Myra EL KHOURY wrote:
This connection works properly on a local machine , when i set the servername as localhost. but when i set the servename as the name of the server on which the SQLServer is installed, it doesn't work. whereas the ADO Connection does

Your ADO connection is passing a user id and password. Your ADO.NET connection is using a trusted connection. Could that be the cause of the problem?

Re: ADO.net Connection

Maira K1-Jul-06 20:06

Maira K

1-Jul-06 20:06

hi,

the error is :
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

Regards,

Re: ADO.net Connection

Colin Angus Mackay1-Jul-06 23:10

Colin Angus Mackay

1-Jul-06 23:10

Myra EL KHOURY wrote:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

This has nothing to do with the connection. This error message can only be generated after .NET has successfully connected to the database. The error message is telling you that the command you sent the SQL Server is taking longer to return the results than the timout allows (by default the timeout is set to 30 seconds).

Re: ADO.net Connection

Maira K1-Jul-06 23:23

Maira K

1-Jul-06 23:23

the connection timeout is equal to 15.
but how can I manage this error?

thanks,

Re: ADO.net Connection

Colin Angus Mackay1-Jul-06 23:31

Colin Angus Mackay

1-Jul-06 23:31

There are two timeouts. Since you are allready connected changing the connection timeout will not help you. The timeout is occurring on the command - it is the length of time that the command is taking to execute. SqlCommand.CommandTimeout is 30 (seconds) by default.

You can, of course, just increase the timeout. Or you can examine the SQL you are sending to the database to try and determine why it is taking too long. However, since you have not given any information on the SQL Command you are sending to the database I cannot help you with that.

Re: ADO.net Connection

Maira K2-Jul-06 7:32

Maira K

2-Jul-06 7:32

thanks a lot for you.

I tried to add to the connection string : "Connect Timeout=30", and it worked.

get into big problem-need help

aligatojohn1-Jul-06 0:10

aligatojohn

1-Jul-06 0:10

I have two table with one-many relationship
table1 has 2 fields:type(int,PK),name
table2 has 4 fields:id(PK),type(int,FK reference field type of table1),content,shortDesc

How to write a SELECT statement that return rows from table1 join table2(via type field) but each row has a different value of type.
Result example:
--------------------------------------------------
| table1.type | name | content | ShortDesc | <--- FIELDS NAME
--------------------------------------------------
| type1 | name1 | content1| shortDesc1 | <---VALUE( ROW )
--------------------------------------------------
| type2 | name2 content2| shortDesc2|

Can anyone help me.Thanks in advance

Re: get into big problem-need help

Colin Angus Mackay1-Jul-06 1:21

Colin Angus Mackay

1-Jul-06 1:21

SELECT table1.type, table1.name, table2.content, table2.shortDesc
FROM table1
INNER JOIN table2 ON table1.type = table2.type

Re: get into big problem-need help

aligatojohn1-Jul-06 18:51

aligatojohn

1-Jul-06 18:51

Sorry but the statement don't return what i expected
Example:
type1 |Id1 | name1...
type1 |Id2 |name2...
because there's a one-many relationship between 2 table
Thanks anyway

Re: get into big problem-need help

Colin Angus Mackay1-Jul-06 23:05

Colin Angus Mackay

1-Jul-06 23:05

ali33 wrote:
Sorry but the statement don't return what i expected

However, it does return what you asked for.

You asked for: | table1.type | name | content | ShortDesc |

The SELECT statement returns: table1.type, table1.name, table2.content, table2.shortDesc
name only exists on table1, content and shortDesc only exist on table2

You are now asking for something with an example output of:
type1 |Id1 | name1...
type1 |Id2 |name2...

This is not possible because name is on table1. Therefore any output row that contains type1 must also contain name1. An output row containing type1 cannot contain name2

ali33 wrote:
because there's a one-many relationship between 2 table

Yes, I know that - That is what I gave you. I suspect that either you did not give the full information when you asked the question or you do not understand the implications of the join scenario.

Re: get into big problem-need help

aligatojohn2-Jul-06 1:32

aligatojohn

2-Jul-06 1:32

oh!sorry . I have a mistake

When I run your SQL statement the result return is:

type1 |Id1 | name1...
type1 |Id2 |name1... <---(sorry not name2)
But the result I expected is:

type1| Id1 |name1...
type2| id2 |name2...
type3| Id3 |name3....
(each type return only one ID of table 2,for all type)
Sorry for the mistake and can you help me.Thanks

Re: get into big problem-need help

Colin Angus Mackay2-Jul-06 1:42

Colin Angus Mackay

2-Jul-06 1:42

ali33 wrote:
each type return only one ID of table 2,for all type

Sorry, but I'm still convinced my SQL is correct.

Show me an example of a few rows from each table that should match and tell me for each row in table1 what rows in table2 match to it.

Re: get into big problem-need help

aligatojohn3-Jul-06 0:16

aligatojohn

3-Jul-06 0:16

Colin Angus Mackay wrote:
Sorry, but I'm still convinced my SQL is correct.

Show me an example of a few rows from each table that should match and tell me for each row in table1 what rows in table2 match to it.

here is an example of some rows from each table:
table1:
|type |name |
----------------------------
| 1 | type1 |
----------------------------
| 2 | type2 |

table2:
|id |type | content | shortDesc |
---------------------------------------------
|1 | 1 | content1 | desc 1 |
|2 | 1 | content2 | desc 2 |
|3 | 2 | content3 | desc3 |
|4 | 2 | content4 | desc4 |
|5 | 1 | content5 | desc5 |

The expected result :
|id | table1.type | name |content | shortDesc |
-------------------------------------------------
| 1 | 1 | type1|content1 | desc1 |
| 3 | 2 | type3|content3 |desc3 |

each type returns only one row.
So now can you help me.If you need,i 'll send you my database and the description more clearly.
Thanks.

Re: get into big problem-need help

Colin Angus Mackay3-Jul-06 1:51

Colin Angus Mackay

3-Jul-06 1:51

I see now. You need to aggregate the rows in table2 somehow then join it. However there is no natural aggregation that you can use. From your example result set you appear to be taking the lowest id for each type - so that is the colum that needs to be aggregated.

This is the aggregation component:

SELECT MIN(id) AS id, type
FROM table2
GROUP BY type

It basically takes the first row for each type and discards the rest.

Now it has to be joined up. The result of the aggregation is added to the previous join to act as a filter.

SELECT table1.type, table1.name, table2.content, table2.shortDesc
FROM table1
INNER JOIN table2 ON table1.type = table2.type
INNER JOIN (SELECT MIN(id) AS id, type
            FROM table2
            GROUP BY type) AS filterQuery ON filterQuery.id = table2.id

This should now produce the results that you want.

Re: get into big problem-need help

aligatojohn3-Jul-06 20:49

aligatojohn

3-Jul-06 20:49

wonderfull!It run perfectly.Thanks Colin Angus Mackay
Best wishes!

How 2 find 4th working day of any month using PL/SQL?

Jazzy_J30-Jun-06 23:55

Jazzy_J

30-Jun-06 23:55

Hi ALL,

How to find 4th working day(any for tht matter) of
any month...If for ex. For June 2006, the 4th working day is
6th June. So, i need to write a PL/SQL(or even SQL Query is also fine) to obtain tht particular day.

Plz do the needful.

Regs,
Jazzy

Re: How 2 find 4th working day of any month using PL/SQL?

Paul Conrad1-Jul-06 17:53

Paul Conrad

1-Jul-06 17:53

Getting Data from Excel to SQL Server..

GBal30-Jun-06 23:33

GBal

30-Jun-06 23:33

Hi,
Im trying to get the records from excel sheet to sql server table.
pls help me..getting some errors..

--------------------------------------------
@file_name varchar(255)

as

begin
--select @file_name = DOCUMENT_PATH from DOCUMENT
SELECT @file_name =(' \\ZSW125\AON\BatchProcessing;HDR=YES ')

--Get the excel filename & path to be imported from the reference table

declare @strsql varchar(500)

set @strsql = 'select * from openrowset(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=' + @file_name + ';HDR=YES'', ''" Smile | :)

select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database='\\ZSW125\AON\BatchProcessing;HDR=YES','')

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.