Creating a record set from 2 databases

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

SQL Server Development (2000)

Creating a record set from 2 databases

Author

Topic

kathunter
Starting Member

35 Posts

Posted - 2002-02-25 : 13:31:42

I have an access database, and a SQL database. These two generally never interact, but I recently had a case where I needed data from both of them in a "JOIN" fashion.

In Access, the packageID and pubisher from a
and in SQL the packageID and LabelNote from s
Where a.rank >= '20'

Is this possible? The packageID is the common element

I should have taken the blue pill

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-02-25 : 13:55:44

If you are running the query in SQL you should be able to set up a linked server to your Access DB.

In enterprise manager go to Security | Linked Servers.
Create a Linked Server for a Microsoft Jet Provider and fill in the details.

for example if you created a linked server called ACCESS_DB your query would be the following. You might want to double check on the ACCESS_DB.a syntax. I know in SQL Server it would be LINKED_SERVER.DATABASE.USER.TABE


SELECT adb.publisher, s.packageID, s.LabelNote FROM ACCESSDB.a adb INNER JOIN s ON (a.packageID = s.packageID) WHERE adb.rank >= '20'

If you are going to run your query in Access you might want to look into Linked Tables. I havent done this myself in Access but you should be able to link Access to the s table in your SQL DB.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-02-25 : 14:04:08

Without actually creating a linked server you could use OPENROWSET.

Look in BOL for more details but you should be able to do the following:


SELECT a.publisher, s.packageID, s.LabelNote 
FROM s INNER JOIN 
     OPENROWSET('Microsoft.Jet.OLEDB.4.0',
     'c:\myAccessDB.mdb';'admin';'mypwd', a)
     AS a
     ON (s.packageID = a.packageID)
WHERE a.rank >= '20'

kathunter
Starting Member

35 Posts

Posted - 2002-02-25 : 22:40:11

ACK! The below code is giving me an error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Microsoft.Jet.OLEDB.4.0'.

Is there another method for accessing access db's in OPENROWSET? BOL just gae this example.

/* code follows */

SELECT a.featured, a.gamerank, a.gamename, a.package_no, a.gamepublisher, a.platform, a.game_new, a.downloadsize, a.genre
, s.labelnote
FROM dlproperties as s

INNER JOIN
OPENROWSET ('Microsoft.Jet.OLEDB.4.0','d:\games_database.mdb';'';'',qurTop50) as a

ON s.packageID=a.packageID

set oRS=objConnection.execute(SQL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources