Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rubendn
Starting Member
3 Posts |
 Posted - 2003-06-26 : 22:53:26
|
| I am trying to connect via ASP with the following connection string:Provider=SQLOLEDB.1;Password=XXXX;Persist Security Info=True;User ID=TFBO;Initial Catalog=TFBODATA;Data Source=SERVERIf I use the following query:"SELECT * FROM Inventory WHERE SKUCode = 25235"I get the following error:Invalid object name 'Inventory'If I use the following query:"SELECT * FROM TFBODATA.dbo.Inventory WHERE SKUCode = 25235"it works fine.Anybody know what is the reason for this? I though that if I put the database name in the connection string that I wouldn't have to put it in the query.The same thing happens using a DSN.Below is the full code: dim conn, rs set conn = CreateObject("ADODB.Connection") conn.Open "Provider=SQLOLEDB.1;Password=XXXXX;Persist Security Info=True;User ID=TFBO;Initial Catalog=TFBODATA;Data Source=SERVER" sql="SELECT * FROM Inventory WHERE SKUCode = 25235" Set rs = conn.execute(sql)Any help greatly appreciated.Ruben |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-26 : 23:02:16
|
| The connection creation does not guarantee that you will be set to that database. You should check it after the connect.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rubendn
Starting Member
3 Posts |
Posted - 2003-06-26 : 23:35:06
|
| If the connection string does not guarantee that I am set to that database then how do I tell it to use that database besides having to include the database name in every table name?Doesn't the connection string, by using "Initial Catalog", set which database to use?Thanks for your help. |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-06-27 : 07:42:28
|
| Try this connection string:"Provider=SQLOLEDB;Server=SERVER;Database=TFBOData;User Id=TFBO;Password=XXXX"Owais |
|
|
|
rubendn
Starting Member
3 Posts |
Posted - 2003-06-27 : 10:06:10
|
| Owais,Tried the new connection string and no luck. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-06-27 : 10:10:18
|
| Hmm. I would have expected it to work without the database name, but, if it doesn't, then just supply the database name. It's probably better practice in any case.Either that, or have you tried changing the default database for the user?-------Moo. |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-06-27 : 13:27:00
|
quote:
Owais, Tried the new connection string and no luck.
Hmmm...this is wierd. If you run "SELECT @@DBNAME" from this connection, what do you get?I have a feeling that it's not the database that is the problem, it's the user. Can you assign TFBO the dbo role for a minute (gasp!) and see if you still get this problem?Owais |
|
|
|
|
|
|
|
|
SQL & ASP Error