CODEWITHSUNDEEP
sql-serversql-server-2008
mikebmassey
mikebmassey

Reputation: 8584

Function like USE to point to a SQL database on a different server?

In SQL Server, you can apply the use function to point a query to another database. For example:

USE databasename GO;

Is there a function that allows you to point to a different database server and use a database on that server? I would expect this to work, but no luck:

USE [servername].databasename GO;

I know I could just point the query to the database on the other server, but when I am dealing with production versus staging environments, it's more efficient to declare the server and database in the beginning of the query.

Thanks

Upvotes: 8

Views: 16793

Answers (2)

Keith Walton
Keith Walton

Reputation: 5364

I use Linked Servers to accomplish this. I don't know if this will meet your needs, however.

http://msdn.microsoft.com/en-us/library/ms188279.aspx

In Management Studio, this is available under Database/Server Objects/Linked Servers.

You can refer to objects on this server like this:

[Server].database.schema.object

I just realized this isn't what you want. JonH has it right, you can't specify a dabase on another server at the beginning of your query.

Upvotes: 6

JonH
JonH

Reputation: 33143

USE does not span across to another server, you need to define a linked server on your local instance and then you can access data from that server.

Upvotes: 7

Related Questions