problem with part-name on a link server

[lupien1]

Hi,

The following query on a link db server is working

select * from OPENQUERY(db_access, 'select * from tab1')

if i tried the following query with part-name instead of OPENQUERY, i have an error message

select * from db_access...tab1

error message #7313 (problem with MSDASQL)

What i'm doing wrong?

Thank you

 

[mrdenny]

You should include the database and owner name instead of relying on the default to kick in.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[lupien1]

With MS access what is the database name and the owner name?

 

[mrdenny]

Ah, when linking to an Access database you shold use the ... as there is no database or owner name.

When you created the linked server what provider did you use? There is a bunch of into in BOL under "linked servers, access an Access database"

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[lupien1]

In SQL Server Enterprise Manager, the provider i use to create my db link for my access database is:

Microsoft OLE DB Provider for ODBC Drivers

Thank you

 

[lupien1]

I try the "Microsoft Jet 4.0 OLE DB Provider" (suggestion from another forum) and it work.

The only thing, is the result of this simple query "select * from db_access...table1" return the column in alphabatical order instead of the creating order. Using the same db link with openquery return the column in the creating order.

 

[mrdenny]

Yep, you'll need to use the Jet driver for access.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief