none
Cannot initialize the data source object of OLE DB provider

    Question

  • When I try to run this SQL "Select * from  [GGSyncCSV]...[MerchoMasterInfo#CSV]",  I am getting the following error.

     

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "GGSyncCSV" 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 "GGSyncCSV".

     

    The [GGSyncCSV] link server was created by following SQL statement a couple months ago.

     

    EXEC master.dbo.sp_addlinkedserver @server = N'GGSyncCSV', @srvproduct=N'Jet 4.0', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'D:\Integration\GordonGotch', @provstr=N'Text'

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'GGSyncCSV',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

     

    GO

    EXEC master.dbo.sp_serveroption @server=N'GGSyncCSV', @optname=N'collation compatible', @optvalue=N'false'

    GO

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

    GO

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

    GO

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

    GO

    EXEC master.dbo.sp_serveroption @server=N'GGSyncCSV', @optname=N'rpc', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'GGSyncCSV', @optname=N'rpc out', @optvalue=N'false'

    GO

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

    GO

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

    GO

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

    GO

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

    GO

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

    GO

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

     

    The SQL statement "Select * from  [GGSyncCSV]...[MerchoMasterInfo#CSV]" always returns the content of the CSV file until 2 days ago.  

     

    I like to ask: is there anything that causes OLE DB provider "Microsoft.Jet.OLEDB.4.0" not working properly?

     

    Any possible solution for this issue?

     

    Thanks.

    Wednesday, November 21, 2007 12:01 AM

All replies

  • I forgot to say that the server is running SQL SERVER 2005 sp2 + Windows 2003.

    Wednesday, November 21, 2007 12:24 AM
  • The first things to check are that:

     

    the file exists, and SQL Server has permissions to open it

    the dll for the provider is registered

     

    Then, let's see what changed 2 days ago on your machine...

     

     

     

    Wednesday, November 21, 2007 6:16 PM
  • Could you give me more details on how to check whether SQL Server has permissions to open it

    the dll for the provider is registered?

    Wednesday, November 21, 2007 10:16 PM
  • Check the account SQL Server is running under to make sure that that user has (read ? maybe read/write?) permissions to the file.  When you installed SQL Server, you chose the account to run under, so that user may or may not have permission to open the file.

     

    regsvr32 registers dll files.  You would just type regsvr32 <dllname> to register it.

     

    Hope that helps,

     

    John

    Thursday, November 22, 2007 4:01 AM
  • I am having this same problem.  Except that it works for users who are in the windows admin group of the server, but not for other users.  I have tried other groups such as powerusers, but they did not work either. 

     

    I can't seem to isolate what local policy the admin group has that allows it to query the linked server when other users can't. 

     

    I have checked the linked server setup, and the file permissions, but nothing had any impact until I put the user in the admin group.

     

     

     

    Tuesday, February 12, 2008 7:59 PM
  • In this case, are you talking about the user in SQL Server or the user that SQL Server is running under?  Assuming that you are talking about the user that SQL Server is running under, perhaps you could check to see if it is a file permissions issue directly by sharing the directory out to "everyone" temporarily and using the shared directory path to the file when adding the linked server.  If that works, then it is definitely a file permission issue.

    Tuesday, February 12, 2008 8:16 PM