|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Referencing another SQL Server database tableeditions. 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. What does your query look like ?
Show quote "jwilson" <jwilson***@yahoo.com> wrote in message news:1152215656.150247.75630@p79g2000cwp.googlegroups.com... >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. > 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 wrote: Show quote > What does your query look like ? > "jwilson" <jwilson***@yahoo.com> wrote in message > news:1152215656.150247.75630@p79g2000cwp.googlegroups.com... > >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. > > 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. Show quote "jwilson" <jwilson***@yahoo.com> wrote in message news:1152222027.481403.274770@j8g2000cwa.googlegroups.com... > > 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 wrote: >> What does your query look like ? >> "jwilson" <jwilson***@yahoo.com> wrote in message >> news:1152215656.150247.75630@p79g2000cwp.googlegroups.com... >> >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. >> > > 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? W.G. Ryan eMVP wrote: Show quote > 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" <jwilson***@yahoo.com> wrote in message > news:1152222027.481403.274770@j8g2000cwa.googlegroups.com... > > > > 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 wrote: > >> What does your query look like ? > >> "jwilson" <jwilson***@yahoo.com> wrote in message > >> news:1152215656.150247.75630@p79g2000cwp.googlegroups.com... > >> >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. > >> > > > Hang on.
You're using SQL Server 2005 Express. What is your connection string? Show quote "jwilson" <jwilson***@yahoo.com> wrote in message news:1152225857.964983.281150@m73g2000cwd.googlegroups.com... >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? > > W.G. Ryan eMVP wrote: >> 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" <jwilson***@yahoo.com> wrote in message >> news:1152222027.481403.274770@j8g2000cwa.googlegroups.com... >> > >> > 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 wrote: >> >> What does your query look like ? >> >> "jwilson" <jwilson***@yahoo.com> wrote in message >> >> news:1152215656.150247.75630@p79g2000cwp.googlegroups.com... >> >> >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. >> >> > >> > > 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. Brendan Green wrote: Show quote > Hang on. > > You're using SQL Server 2005 Express. What is your connection string? > > "jwilson" <jwilson***@yahoo.com> wrote in message > news:1152225857.964983.281150@m73g2000cwd.googlegroups.com... > >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? > > > > W.G. Ryan eMVP wrote: > >> 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" <jwilson***@yahoo.com> wrote in message > >> news:1152222027.481403.274770@j8g2000cwa.googlegroups.com... > >> > > >> > 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 wrote: > >> >> What does your query look like ? > >> >> "jwilson" <jwilson***@yahoo.com> wrote in message > >> >> news:1152215656.150247.75630@p79g2000cwp.googlegroups.com... > >> >> >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. > >> >> > > >> > > >  |
|||||||||||||||||||||||
Other interesting topics