OpenRowSet Without linking servers

  • Hi all, I have seen some posts on this but still haven't been able to get this working.

    I need to query server B from Server A without linking the servers. I have read Microsoft's article on this (http://msdn2.microsoft.com/en-us/library/ms190312.aspx) and I think i have everything enabled on both servers. I have also ran this sql

    EXEC sp_configure 'show advanced options', 1

    GO

    Reconfigure

    GO

    EXEC sp_Configure 'Ad Hoc Distributed Queries', 1

    GO

    Reconfigure

    My Sql is this

    Select * From OPENROWSET('SQLNCLI', 'DRIVER={SQL Server}; SERVER=Name\instance; UID=username;PWD=password', Database.dbo.Table)

    I return these errors.

    -OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired"

    -OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under default settings SQL Server does not allow remote connections.

    -Msg 65535, Level 16, State 1, Line 0

    Sql Network interfaces: Error Locating Server/Instances Specified

    -OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute"

    Obviously these errors makes sense to me but i don't know what is wrong exactly.

    Any help would be appreciated.

  • Try using the below command, it will work.

    SELECT *

    FROM OPENROWSET('SQLNCLI', 'Server=servername\instancename;UID=username;PWD=password;',

    'select * from databasename..objectname');

  • Thanks for the reply but i get the same error message with the syntax like that. Is there some other setting i need to change or something. Or is there a problem that im connecting to an instance?

  • 1.) Username and password should be Remote server's username and password. If you are going to use windows authentication then remove username and password option and then use "Trusted_Connection=yes;" option.

    2.) Try using port number in the server name

    3.) Try connecting the remote server from the source servers SSMS and check connection is establishing correctly.

  • alright i was able to get it working.

    One more question. What im trying to do is compare a column from serer B to Server error and return the rows that are different in that column.

    Select A.* From OPENROWSET('MSDASQL', 'DRIVER={SQL SERVER};SERVER=name\instance;UID=user;PWD=password',

    'SELECT * FROM pubs.dbo.authors') AS A Where A.MaintenanceDate <> MaintenanceDate

    There are differences in the maintenancedates between the servers but im not returning any rows. Is there something im missing??

    Thank you guys in advance.

  • pricejt (10/27/2007)


    alright i was able to get it working.

    One more question. What im trying to do is compare a column from serer B to Server error and return the rows that are different in that column.

    Select A.* From OPENROWSET('MSDASQL', 'DRIVER={SQL SERVER};SERVER=name\instance;UID=user;PWD=password',

    'SELECT * FROM pubs.dbo.authors') AS A Where A.MaintenanceDate <> MaintenanceDate

    There are differences in the maintenancedates between the servers but im not returning any rows. Is there something im missing??

    Thank you guys in advance.

    You are comparing the column MaintenanceDate to itself....I guess it should be like...

    Select A.* From pubs.dbo.authors S1, OPENROWSET('MSDASQL', 'DRIVER={SQL SERVER};SERVER=name\instance;UID=user;PWD=password',

    'SELECT * FROM pubs.dbo.authors') S2 Where S1.KeyID = S2.KeyID AND S1.MaintenanceDate <> S2.MaintenanceDate

    Meanwhile have you checked what vidya sagar has suggested?

    --Ramesh


  • Here is what im trying to do. Server A Table A has a table that has primary keys (PlantID, RuleType, RulePrefix, RuleSuffix) Same with Server B Table B. The only differences is that on Server A Table A the maintenanceDate Will change.

    Users will make there Changes on Server A Table A which will then in turn make Server A Table A have newer data then on Server B. I want to find these changes. Keep in mind that Server A Table A will also get new records that wont even exist in Server B Table B.

    My Sql now looks like this.

    Select A.* From OPENROWSET('MSDASQL',

    'Driver={SQL SERVER};SERVER=server\instance;UID=user;PWD=pass',

    'SELECT * FROM database.dbo.table') as A

    Right Outer Join database.dbo.table As B on A.PlantID = B.PlantID

    AND A.RuleType = B.RuleType AND A.RulePrefix = B.RulePrefix and

    A.RuleSuffix = B.RuleSuffix

    Where A.MaintenanceDate <> B.MaintenanceDate

    This works however its not returning the records in B that dont exist in A like I thought it would.

    Thanks for your help guys

  • Try,

    SELECT A.*

    FROM database.dbo.table AS B

    LEFT JOIN OPENROWSET('MSDASQL', 'Driver={SQL SERVER};SERVER=server\instance;UID=user;PWD=pass'

    ,'SELECT * FROM database.dbo.table') AS A

    ON B.PlantID = A.PlantID AND B.RuleType = A.RuleType AND B.RulePrefix = A.RulePrefix

    AND B.RuleSuffix = A.RuleSuffix AND B.MaintenanceDate <> A.MaintenanceDate

    Andy

  • Try this site....

    msdn2.microsoft.com/en-us/library/ms190312.aspx

Viewing 9 posts - 1 through 9 (of 9 total)

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