1

I am working in Visual Studio and using the SQL manager built into the studio. Now I am connecting to several databases and I would very much like to be able to save and open my SQL queries and still have them access the correct database and table.

So:

Database servers:
db.company.com
    databasenumber1
    databasenumber2
    databasenumber3
db2.company.com
    databasenumber1
    databasenumber2
    databasenumber3
db3.company.com
    databasenumber1
    databasenumber2
    databasenumber3

Now I wish to write an sql query that does something simple, lets say:

select * from users where userid = '12';

However I want to select this from database server db2 and from database databasenumber3.

How do I write that in a use statement? Or is there something other than "use"??

Bhavesh Odedra
  • 10,990
  • 12
  • 33
  • 58
Mr.Glaurung
  • 516
  • 6
  • 17

1 Answers1

2

Working among several databases in once script file requires USE followed by GO statement. 

USE db1;
GO

SQL statements ...
...


USE db2;
GO

SQL statements ...
...

Another option is to use server.dbname.tablename format but that strictly requires that all of your databases are hosted on same server.

SELECT * FROM server.db1.table1  
SELECT * FROM server.db2.table2
...
gofr1
  • 15,741
  • 11
  • 42
  • 52
Umesh
  • 2,704
  • 19
  • 21
  • I don't get this to work. db1.company.com is one computer which has a database engine running on it. db2.company.com is a completely different computer with a database engine running on that, even on a different subnet. Under SQL Servers in Visual Studio I have them connected, can browse them and work with them (in the SQL Server Object Explorer). If I write "use db1; go" I get this error: Msg 911, Level 16, State 1, Line 1 Database 'db1' does not exist. Make sure that the name is entered correctly. (Sorry for lousy formatting. I don't understand the formatting guide) – Mr.Glaurung Apr 14 '14 at 10:43