Referencing another SQL Server database table

[jwilson]

I am a new user of Visual Basic Express / SQL Server 2005 Express
editions. I am trying to write sql statements in an application
database that reference tables in another database (same SQL server).
Based on the prior posts that I've read, it would seem this is pretty
straight forward by specifying a full database path prior to the table
name. However, when I execute this SQL through ADO, I get an error
message indicating an "invalid object name".

Eaxmple:

queryString = _
"SELECT * FROM
BelvedereMasterApplicationDB.dbo.LOAN_DATA WHERE LOAN_NUM_BLVD<=2;"
Dim command2 As New
System.Data.SqlClient.SqlCommand(queryString, m_ConnString)
Dim reader2 As System.Data.SqlClient.SqlDataReader =
command2.ExecuteReader()


Is there something that I'm missing? Thanks.

 

[W.G. Ryan eMVP]

What does your query look like ?

 

[jwilson]

Following is the query string I'm passing to a command object using a
connection to the same SQL server but different database (the database
is embedded in the visual basic application):

queryString = _
"SELECT * FROM
BelvedereMasterApplicationDB.dbo.LOAN_DATA WHERE LOAN_NUM_BLVD<=2;"

Is this what you mean? Thanks.

 

[W.G. Ryan eMVP]

I think you can use the .. in between the db and table name, but you can
also use the ChangeDatabase method on the connection or just open it
directly to the db.

 

[jwilson]

I tried the .. and get the same error. I hear what you're saying as
far as changing the connection if my query were ultimately going to be
this simple. However, I need to join the table referenced below with
other tables in the application DB. further, I would like to avoid
temporarily pulling the data down in ADO datasets to avoid the time and
memory consumption as some tables are fairly large. Any further ideas?

 

[Brendan Green]

Hang on.

You're using SQL Server 2005 Express. What is your connection string?

 

[jwilson]

The string I was using is:

connectionString="Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\BelvedereFinancialApplicationDB.mdf;Integrated
Security=True;User Instance=True"

I was able to get it to work by changing the database to a fixed
loaction on my local drive and changing the user instance=false. I
cannot claim to fully understand this yet, but I believe some sort of
temporary sql server instance is created when using a user instance
(thus the inability to reference databases in the regular instance).
Curious how this might be different with a full version of SQL Server?
Thanks for the help.