SQL Server Performance

CROSS SERVER QUERY

Discussion in 'General DBA Questions' started by akothand, Jan 7, 2004.

  1. akothand New Member

    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

  2. Luis Martin Moderator

    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
  3. Twan New Member

    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
  4. akothand New Member

    Thanks a lot guyz !
    Anand

Share This Page