Post reply

Error while transferring SQL 2005 data to MS ACCESS

  • December 26, 2008 at 4:41 am

    #199401

    I have two servers suppose A and B. I am transferring data from SQL 2005 to MS ACCESS table by table through a linked server.

    I am using the following query to select data from access "select * from [linked_server]...tablename"

    The same query runs successfully on server A but when I run the same on server B it throws me the following error.

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "linked_server" returned message "Unspecified error".

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "linked_server".

    I am creating identical linked servers on both the A & B, even then the issue is'nt resolved.

    Please help me with the same.

  • December 26, 2008 at 8:28 am

    #917798

    Have you checked permissions? the server might not have the rights to get to the Access file.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • December 30, 2008 at 10:13 pm

    #919435

    Thnx...Its working...

  • March 4, 2009 at 2:55 pm

    #953628

    Can you post your findings?

    What was the issue?

  • March 4, 2009 at 9:54 pm

    #953757

    I wasn't able to find the exact cause. But what I did was:

    1) I reconfigured the MSDTC.

    2) I created a new linked server and used the linked server script generated for the one that was working.

    Sorry Guys for replyin late...

  • March 5, 2009 at 10:08 am

    #954173

    Would it be possible post the script you used?

  • March 5, 2009 at 9:47 pm

    #954634

    EXEC master.dbo.sp_addlinkedserver @server = N'Linked_Server_Name', @srvproduct=N'server_to_link_name', @provider=N'SQLNCLI', @datasrc=N'server_to_link_name', @catalog=N'DatabaseName'

    GO

    EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'collation compatible', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'data access', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'dist', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'pub', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'rpc', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'rpc out', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'sub', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'connect timeout', @optvalue=N'0'

    GO

    EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'collation name', @optvalue=null

    GO

    EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'lazy schema validation', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'query timeout', @optvalue=N'0'

    GO

    EXEC master.dbo.sp_serveroption @server=N'Linked_Server_Name', @optname=N'use remote collation', @optvalue=N'true'

Viewing 7 posts - 1 through 6 (of 6 total)

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