Openquery q - 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 2000 Forums

Transact-SQL (2000)

Openquery q

Author

Topic

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-07-25 : 12:52:32

Hi,

INSERT INTO my table (c1, c2, …cn)
SELECT * FROM OPENQUERY (SS, ‘SELECT c1, c2, …cn FROM “mytable2”’)

How can be retrieved only values From my table2( in the linked server) that are NOT IN mytable ( or values that are > max(values of my table )?

(The linked server is an Access, only openquery statements are working)

Thanks for any suggestions.

Dobby

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 13:56:50

quote:

How can be retrieved only values From my table2( in the linked server) that are NOT IN mytable

Well here's one way

SELECT *
FROM myTable2 l LEFT JOIN myTable r ON l.myKey = r.myKey
WHERE r.myKey IS NULL

Brett

8-)

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-07-25 : 14:38:18

Hi Brett,

I'm not able to address the linked server directly (MS Access). I'm using
domain account and whatever I did security is an issue and prevents me direct addressing. I followed MS Knowledge base articles that deal with Access linked servers (814398, 296711, 2858330 - in vain.
The only addressing is trough OPENQUERY().
(Linked server is created as OLE DB not as Jet)

I'll try to create and use a table in Access for reference purposes.

Thx,

Dobby

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-25 : 15:34:49

I'm confused.

Are you trying to create a linked server from SQL Server or not?

I set this up (just have a problem with my workgroup file)


EXEC sp_addlinkedserver 
   'lnkNorthwind', 
   'OLE DB Provider for Jet',
   'Microsoft.Jet.OLEDB.4.0', 
   'C:\MSOffice\Access\Samples97\Northwind.mdb'
GO

And the following should. Make sure your path is correct, and you're using the correct dbname

SELECT * FROM lnkNorthwind...Orders

It would then be something like
SELECT *
FROM myTable2 l LEFT JOIN Northwind...myTable r ON l.myKey = r.myKey
WHERE r.myKey IS NULL

Or don't you have access to the server?

But it is talking to it

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources