Linked server issue with JET 4.0

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 2005 Forums

SQL Server Administration (2005)

Linked server issue with JET 4.0

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-22 : 12:28:20

Scenario
We have a linked server setup that points to a local folder on the SQL Server Box. All permissions are correct, etc. The files in the local folder are DBF files and the linked server is configured as follows:

EXEC master.dbo.sp_addlinkedserver @server = N'DBFFOLDER', @srvproduct=N'Jet 4.0', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'\\SQLSERVER\DBFFOLDER', @provstr=N'dBASE IV'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DBFFOLDER',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_serveroption @server=N'DBFFOLDER', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DBFFOLDER', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DBFFOLDER', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DBFFOLDER', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DBFFOLDER', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DBFFOLDER', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DBFFOLDER', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DBFFOLDER', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'DBFFOLDER', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'DBFFOLDER', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DBFFOLDER', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'DBFFOLDER', @optname=N'use remote collation', @optvalue=N'true'

As far as the provider this linked server is using, the only option checked is Allow Inprocess - all other options are unchecked.

Problem Statement
The issue I'm experiencing is that our code runs fine for days, weeks and sometimes several months then we all the sudden start receiving errors like this:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DBFFOLDER" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DBFFOLDER".

The same error is generated regardless of how the linked server is accessed (i.e. OPENQUERY, SELECT * FROM [LinkedServer]...[DBFFILE.DBF], etc.).

If I restart SQL Server that resolves the issue temporarily. But this is not always ideal in our production environment and is not a good long term solution. I've tried running this provider out of process but then I start encountering file system permission issues (Access Denied) errors so I'm back to running in process so it inherits all the permissions of the SQL service.

Questions
I'm looking for a few things on this one:

First, any suggestions on how to unload the provider without restarting the SQL Server service?

Second, I have a feeling the root cause of this issue is simply internal to the JET dll(s) where it's possibly not releasing internal resources and then resulting in this error. Any suggestions on how to confirm this? I've never had to work with any counters or anything before like this so I'm green in this area.

Third, any suggestions on different options to either configure this linked server or possibly a different provider? Keeping in mind the only reason this linked server exists is to allow a scheduled process written in .NET to import the data into SQL Server for further processing. Most of the work is done through bulk INSERT statements into SQL Server tables.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-22 : 13:31:59

A bit of interesting news... when I configure a linked server using an ODBC entry (to the same location), I get this error:

OLE DB provider "MSDASQL" for linked server "SAMPLE" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "SAMPLE" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "SAMPLE" returned message "[Microsoft][ODBC dBase Driver] System resource exceeded.".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SAMPLE".

So I think this confirms my resource issue. Perhaps it's a connection pooling related issue? Dunno yet. Does anyone know how to configure additional connection string attributes in a linked server?

B0g
Starting Member

19 Posts

Posted - 2008-07-28 : 04:10:41

Hello ... it seems that I have the same error. I have a linked server to MS Acceess and it works fine for a while and then it throws this error:
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "linked server name".

If I restart the SQL Server it works.

Did you manage in solving this problem?

Thanks,
B0g

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-28 : 08:51:33

Is the database file you are linking to a DBase file? What about trying a DBase ODBC: http://www.dbase.com/ODBCSummary.asp#Features

B0g
Starting Member

19 Posts

Posted - 2008-07-28 : 09:37:27

@dexter.knudson: it's MS Access 2003

s_mc
Starting Member

1 Post

Posted - 2009-02-24 : 11:39:39

Hi: I was just wondering if you were able to reach resolution on this issue. Specifically:
Second, I have a feeling the root cause of this issue is simply internal to the JET dll(s) where it's possibly not releasing internal resources and then resulting in this error. Any suggestions on how to confirm this? I've never had to work with any counters or anything before like this so I'm green in this area.
I am experiencing the same issue and have researched this to the nth degree without luck.
Please le me know.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2009-02-24 : 12:33:52

quote:
Originally posted by s_mc

Hi: I was just wondering if you were able to reach resolution on this issue. Specifically:
Second, I have a feeling the root cause of this issue is simply internal to the JET dll(s) where it's possibly not releasing internal resources and then resulting in this error. Any suggestions on how to confirm this? I've never had to work with any counters or anything before like this so I'm green in this area.
I am experiencing the same issue and have researched this to the nth degree without luck.
Please le me know.

Actually, I did find a solution to this issue due to a suggestion from a friend. We did this by increasing the amount of memory available to SQL Server for memory allocations within the SQL Server process, but outside of the SQL Server memory pool. The memory outside of the memory pool is the area used by SQL Server for loading items such as extended procedure .dll files, the OLE DB providers referenced by distributed queries, and automation objects referenced in Transact-SQL statements. The default is 256 MB. Pretty much straight from the documentation - http://msdn.microsoft.com/en-us/library/ms190737(SQL.90).aspx.

I don't know the exact problem we had but concluded one of two things - (a) the amount of memory required exceeeded 256 MB or the memory within this region became fragmented enough and was not able to obtain a contiguous section of memory to load another resource. To resolve this, we increased this reserved memory region to 1 GB by specifying the -g startup option.

Martin Smith
Starting Member

2 Posts

Posted - 2009-06-25 : 11:13:09

I'm having a similar issue with in-process OLEDB providers not working when trying to import a CSV file and am trying to figure out how I can determine whether the -g switch will resolve the issue or whether I will need to rewrite my import code.

My Client's Server has 4GB of RAM and does not use the boot.ini 3GB switch.

I have tried accessing the text file using OPENDATASOURCE/OPENROWSET queries via the Microsoft.Jet.OLEDB.4.0, MSDASQL and Microsoft.ACE.OLEDB.12.0 providers.

All fail.

On my development machine it also failed but started working again after I restarted the SqlServer service. I am unable to restart SQL on my client's machine quite as easily as they have various critical applications running on it!

I have tested accessing the file using ADO and the offending OLEDB providers and this works.

I have tried running procmon whilst executing the query and can see no permission denied errors on the file system or registry.

ERROR LOGS:

I do not see either of the messages referred to in the article in the Error log

"Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>"

"Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>"

I do see some of the following though which may be correlated with at least some of my attempts to run a distributed query.

AppDomain [xyz] is marked for unload due to memory pressure. These messages all immediately follow another message "A significant part of sql server process memory has been paged out. This may result in a performance degradation."

From my reading so far the CLR app domains are competing for the same MemToLeave as distributed queries but the presence of the other message seems to indicates a more general memory issue?

I've tried running various permutations of the query both inprocess and out of process and the exact error messages I am getting are below (Note that the out of process does return the correct column headers of the result set but no actual results. If anyone knows how to avoid the IID_IDBCreateCommand error that would be useful!

SELECT * FROM OPENDATASOURCE
('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\Feeds\20090624\50;Extended Properties="text;HDR=No"')...[062402#csv]

/*

InProcess:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" 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 "(null)".

Out of Process:
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 3
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

*/

SELECT *
FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\Feeds\20090624\50;',
'SELECT * from 062402.csv');

/*
InProcess:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] System resource exceeded.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Out of Process:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] System resource exceeded.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

*/

SELECT * FROM OPENDATASOURCE
('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\Feeds\20090624\50;Extended Properties="text;HDR=No"')...[062402#csv]
/*
InProcess:

Now hangs on an OLEDB wait. If I try and kill the spid the connection remains in a killed/rollback state. This wasn't happening earlier so may well be as I

tried repairing the office 2007 data access components and left it with a pending reboot requirement.

Out of Process:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

*/

Martin Smith
Starting Member

2 Posts

Posted - 2009-06-26 : 06:07:12

I'm now coming to the conclusion that this may not a memory issue. Using the query here http://www.johnsansom.com/index.php/2009/03/sql-server-memory-configuration-determining-memtoleave-settings/ seems to indicate that there is plenty of free memory.

Total avail mem, KB = 47432
Max free size, KB = 13196

Also when I run the command DBCC FREESYSTEMCACHE('ALL') it does not suddenly start working again.

I would really like to be able to find a way to unload the OLE DB provider without restarting SQL. Anyone any ideas?

Edit: Actually comparing the values on the live box they do look a lot lower than on my development box which has

Total avail mem, KB = 289,008 KB
Max free size, KB = 157,412 KB

But still I don't see that the amount of memory stated on the production server ought to be insufficient. The OLEDB dlls are loaded in process already and the query aborts before even looking at the csv file (which is only 400KB in any case)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources