Post reply

Linked Server

  • August 19, 2003 at 11:16 am

    #159762

    I have added a linked server on my sql 2000 db server, pointing to MS Access 2000 database.

    when I fire a select statement :

    SELECT * FROM OPENQUERY(NewVerification, 'SELECT id,verifier FROM newverification...accounts')

    I get the following error.

    Server: Msg 7357, Level 16, State 2, Line 1

    Could not process object 'SELECT id,verifier FROM newverification...accounts'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.

    OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=SELECT id,verifier FROM newverification...accounts'].

    I am able to see all the tables thru EM which to means I am successfully able to connect to that DB. But then why is not letting me query that linked server ?

    Any help is appreciated.

    Thanks

  • August 19, 2003 at 12:07 pm

    #470454

    try the following code:

    select *

    from [LikedServerName]...[AccessTableName]

  • August 20, 2003 at 6:58 am

    #470588

    I still get the following error :

    Server: Msg 7313, Level 16, State 1, Line 1

    Invalid schema or catalog specified for provider 'MSDASQL'.

    OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].

  • August 20, 2003 at 8:19 am

    #470602

    The linked server gave me a lot problem before. It is sensitive to any update you applied to the server. At the end we choice to use ADO to replace all linked server connections.

    Here is list you need to check when you have a linked server:

    1.make sure you have the latest MDAC on the SQL server side

    2.create the linked server on the server side:

    USE master

    GO

    -- To use named parameters:

    EXEC sp_addlinkedserver

    @server = 'test',

    @provider = 'Microsoft.Jet.OLEDB.4.0',

    @srvproduct = 'OLE DB Provider for Jet',

    @datasrc = '\\testpc\test.mdb'

    GO

    exec sp_addlinkedsrvlogin 'test', false, null, 'Admin', NULL

    go

    3.test the linked server on the server side and the client side with Query Analyze – select count(*) from [test]…

    good luck.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply