October 26, 2007 at 7:14 pm
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.
October 27, 2007 at 6:36 am
Try using the below command, it will work.
SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=servername\instancename;UID=username;PWD=password;',
'select * from databasename..objectname');
Regards..Vidhya Sagar
SQL-Articles
October 27, 2007 at 7:57 am
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?
October 27, 2007 at 8:11 am
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.
Regards..Vidhya Sagar
SQL-Articles
October 27, 2007 at 10:06 am
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.
October 27, 2007 at 10:24 am
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
October 28, 2007 at 12:39 pm
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
October 28, 2007 at 10:31 pm
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
October 28, 2007 at 11:23 pm
Try this site....
msdn2.microsoft.com/en-us/library/ms190312.aspx
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply