OpenRecordset - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

OpenRecordset

Author

Topic

pricejt
Starting Member

9 Posts

Posted - 2007-10-26 : 22:35:20

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.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-27 : 00:30:25

Try this:

Select * From OPENROWSET('SQLNCLI', 'SERVER=Name\instance; UID=username;PWD=password;', 'select * from Database.dbo.Table') as a

pricejt
Starting Member

9 Posts

Posted - 2007-10-27 : 07:22:13

quote:
Try this:

Select * From OPENROWSET('SQLNCLI', 'SERVER=Name\instance; UID=username;PWD=password;', 'select * from Database.dbo.Table') as a

I think you ment this Select A.* From OpenRowSet('SQLNCLI', 'SERVER=Name\Instance;UID=username;PWD=password;','database.dbo.table') as A

I tried this and got the same errors.

Thanks for the reply though.

Kristen
Test

22859 Posts

Posted - 2007-10-27 : 08:10:12

This is what I do:


--(1)
SELECT	A.*
FROM	OPENROWSET('MSDASQL',
	'DRIVER={SQL Server};SERVER=MyOtherServer; UID=MyUserID;PWD=MyPassword;',
	pubs.dbo.authors) AS A
ORDER BY A.au_lname, A.au_fname

--(2)
SELECT	A.*
FROM OPENROWSET('MSDASQL',
	'DRIVER={SQL Server};SERVER=MyOtherServer; UID=MyUserID;PWD=MyPassword;',
	'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS A

Note that in (1) the database/table is specified WITHOUT quotes, and in the second a Query is specified WITH quotes

Kristen

pricejt
Starting Member

9 Posts

Posted - 2007-10-27 : 09:54:59

Kristen

Thanks for the response.

I did try that MSDASQL user before and i get this error. I dont know if i have to set anything up special for them.

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)"

Kristen
Test

22859 Posts

Posted - 2007-10-27 : 10:17:43

What's the exact SQL you are using?

pricejt
Starting Member

9 Posts

Posted - 2007-10-27 : 10:55:22

Never mind Kristen thank you. Your code did work. when i tested it i misspelled the server name.

Thanks a lot

pricejt
Starting Member

9 Posts

Posted - 2007-10-27 : 11:51:24

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.

Kristen
Test

22859 Posts

Posted - 2007-10-27 : 14:11:49

You need to JOIN the second table


Select A.*
From OPENROWSET('MSDASQL', 'DRIVER={SQL SERVER};SERVER=name\instance;UID=user;PWD=password','SELECT * FROM pubs.dbo.authors') AS A 
    JOIN MyLocalTable AS B
        ON A.MyID = B.MyID
Where A.MaintenanceDate <> MaintenanceDate

This route is probably going to be very inefficient ...

You need to post the exact query you are using if you want us to help you with it, otherwise we are just guessing what you have.

Kristen

pricejt
Starting Member

9 Posts

Posted - 2007-10-28 : 11:09:18

quote:

Select A.*
From OPENROWSET('MSDASQL', 'DRIVER={SQL SERVER};SERVER=name\instance;UID=user;PWD=password','SELECT * FROM pubs.dbo.authors') AS A
JOIN MyLocalTable AS B
ON A.MyID = B.MyID
Where A.MaintenanceDate <> MaintenanceDate

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

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 05:21:57

I think you will be much better off to use the OPENROWSET to pull all the relevant data into a temporary table, and the process the UPDATEs and INSERTs from there. My experience is that a JOIN across servers is very slow.

Even better would be to have some sort of UpdateDate or Incrementing EditNumber column on the remote database, so that you only query items that you KNOW have changed since last time.

Also don't use SELECT * - just list the columns that you ACTUALLY need. Otherwise you are wasting bandwidth getting all the columns across - including goodness knows what columns may be added in the future - Image, Text, Blob ...

"This works however its not returning the records in B that don't exist in A like I thought it would. "

You need a LEFT outer join ... the table you want "everything" from is on the Left of the JOIN - i.e. TableA.

If you ever have a genuine Right out join I recommend that you change the order of the tables and JOINs to use LEFT outer join instead. The Right outer join flummoxes most people because the tables are more awkward to comprehend when that way around

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources