syntax for querying to another sql server

Question

syntax for querying to another sql server

Matt Miskinnis

SSC-Addicted

Points: 461
More actions
January 16, 2004 at 9:30 am

#107293

Hello all, I need to query a database that is on another server, and fill a table from the recordset that is brought back. I’m unsure of the syntax to query another server from the query analyzer? Is it also possible to create a table on another db server this way using the ‘create table’ syntax? Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

You either have to use openrowset or create a linked server to the other server. I prefer creating a linked server because with openrowset, you have to create the connection to teh server with every query.

Once you have the linked server setup, just refer to the database objects by their full names: ServerName.DatabaseName.OwnerName.ObjectName

for example:

Select *

From SQL021.SomeDB.dbo.MyTableOrView

Another thing to bear in mind is that in some instances you can't use a 4 part name, like in an update query. So you have to use the four part name in the From clause and alias it and use the alias in the Update clause. Like so:

Update MyObject

Set MyField = 'Some data'

From SQL021.SomeDB.dbo.MyTableOrView As MyObject

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Matt Miskinnis SSC-Addicted Points: 461 More actions · Answer 2

Awsome, that should help a lot. Do you know if there is a way run a stored procedudre on the other server this way?

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

Yep, just do it the same way. As long as your login has permissions to do it.

Exec SQL021.SomeDB.dbo.MyProc @Param1 = 'Somedata'

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Matt Miskinnis SSC-Addicted Points: 461 More actions · Answer 4

Thanks, for the help! I do have one final question though - after adding the link do I need to drop the link or anything like that?

ThomasH SSCertifiable Points: 7437 More actions · Answer 5

Just steppin' in for "offline" Rawhide....

The link server entry remains in existance until DROPed.

Any subsequent calls to the server from other sources

will have access to this Linked server while it exists. Most of

the time, the Linked server entry is part of the, let's call it

"configuration / setup", and is used indefinitly.

In your code, you could ADD the entry, process, DROP entry every time

the code executes, but you have the overhead of SQL doing this work,

and since the Link Server entry is a "named" entry, you would have problems

when two processes try do that work at the same time.

FYI you can also call a remote SP using OPENROWSET type syntax.

Once you understand the BITs, all the pieces come together

ThomasH SSCertifiable Points: 7437 More actions · Answer 6

Just another thought Matt...

Your code could 1st check for the existance of the Link Server entry, then if it's

not yet there, create it and leave it.

Once you understand the BITs, all the pieces come together

Matt Miskinnis SSC-Addicted Points: 461 More actions · Answer 7

That is something I'd like better. I know the syntax for doing that with stored proc's : IF EXISTS (SELECT name FROM sysobjects WHERE name = N'' AND type = 'P') DROP PROCEDURE But can you tell me what the syntax would be for linked servers?

ThomasH SSCertifiable Points: 7437 More actions · Answer 8

Try...

SELECT SRVName, IsRemote, * FROM Master.dbo.sysServers

to get started.

BOL about SysServers table has notes on IsRemote column I do not think are quite right...

Looking deeper... will post more

Once you understand the BITs, all the pieces come together

ThomasH SSCertifiable Points: 7437 More actions · Answer 9

BOL - SysServer - IsRemote = "1 if server is a remote server, else 0 if server is a linked server."

hmmmm

I added a Linked Server using sp_addlinkedserver, sp_serveroption, sp_addlinkedsrvlogin syntax and IsRemote returned 1, and my "local" server entry has IsRemote = 0... so I do not know if IsRemote column will do you any good...

You could add a WHERE to prior posted SELECT ...WHERE SRVName <> @@Servername

or SELECT ...WHERE SRVName = "the linked server name you are dealing with"

There may be some other master...system tables that have the specific Linked Srv entries, but this is what I query against.

Once you understand the BITs, all the pieces come together

Robert Davis One Orange Chip Points: 28027 More actions · Answer 10

Mine too. And all of my remote servers have an IsRemote of 0.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]