CROSS SERVER QUERY | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

CROSS SERVER QUERY

Hi All, I have two SQL 2000 servers . One is a Named instance LMR20BHRCI01 .
Other is a default instance LMR20A Please let me know how can we write a Cross Database server Query to a named instance of SQL Server (I am able to create linked servers with these two) I am unable to use the usual named instance notation LMR20BHRCI01 , as is not allowed in queries.
Thanks Anand
If I follow you, the following is from BOL:<br /><br />Distributed Queries on Multiple Instances <br />of SQL Server<br />Specifying an instance of Microsoft® SQL Serverâ„¢ 2000 on a server running multiple instances of SQL Server requires no syntax changes to the Transact-SQL elements used in distributed queries. Instances can be specified in distributed queries using one of these methods: <br /><br />Specify a server name using the syntax ‘server_name/instance_name’ in the @datasrc parameter of sp_addlinkedserver.<br /><br /><br />Specify Server=server_name; INSTANCENAME=instance_name in a connection string. <br />If an instance is not specified, the distributed query connects to the default instance of SQL Server 2000 on the specified server.<br /><br />Examples of specifying a specific instance named Payroll on a server named London are:<br /><br />– Define a linked server on an instance of SQL Server using @datasrc.<br />sp_addlinkedserver<br /> @server = ‘LondonPayroll1’,<br /> @provider = ‘SQLOLEDB’,<br /> @datasource = ‘London/Payroll'<br /><br />– Define a linked server on an instance of SQL Server using<br />– INSTANCENAME in a provider string.<br />sp_addlinkedserver<br /> @server = ‘LondonPayroll2’,<br /> @provider = ‘SQLOLEDB’,<br /> @provstr = ‘Server=London;INSTANCENAME=Payroll'<br /><br />– Specify an instance of SQL Server in OPENDATASOURCE<br />– using Data Source.<br />SELECT *<br />FROM OPENDATASOURCE(<br /> ‘SQLOLEDB’,<br /> ‘Data Source=London/Payroll;User ID=MyUID<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword=MyPass'<br /> ).Northwind.dbo.Categories<br /><br />– Specify an instance of SQL Server in OPENROWSET<br />– using a provider string.<br />SELECT a.*<br />FROM OPENROWSET(<br /> ‘SQLOLEDB’,<br /> ‘Data Source=London;INSTANCENAME=Payroll;<br /> User ID=MyUID<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword=MyPass’,<br /> Northwind.dbo.Categories<br /> ) AS a<br /><br />– Specify an instance of SQL Server in OPENROWSET<br />– using a the datasource parameter.<br />SELECT a.*<br />FROM OPENROWSET(<br /> ‘SQLOLEDB’,’London/Payroll’,’MyUID’,’MyPass’,<br /> ‘SELECT * FROM Northwind.dbo.Categories'<br /> ) AS a<br /><br />HTH<br /><br /><br />Luis Martin<br /><br />…Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. <br />Bertrand Russell
You can put the server name in square brackets to allow you to use the backslash/forwardslash. So select * from [LMR20BHRCI01].master.dbo.syslogins would work when run from the default instance.
Cheers
Twan
Thanks a lot guyz !
Anand
]]>