I'm doing a series of imports of Excel sheets into a SQLServer database via OleDbConnection --> SqlBulkCopy. When I run my app on my local desktop (XP, Visual Studio 2010, .NET framework 4.0), it's working fine, but when I push it up to a server (Windows
Server 2008 64 bit, IIS7, 64-bit Jet download from MS installed), the OleDBDataReader I'm using to bring in the excel data doesn't read the entire data set.
The relevant code looks like this:
OleDbCommand oCmd = new OleDbCommand(queryString, _oConn);
oRdr = oCmd.ExecuteReader();
SqlBulkCopy dest = new SqlBulkCopy(_dbConn);
dest.BatchSize = 1000;
dest.DestinationTableName = destinationTable;
dest.WriteToServer(oRdr);
Running the exact same code against the exact same database using the exact same Excel document, a ~54,000-row Excel worksheet imports correctly when run from my desktop. When run on the server, it consistently stops at 2931 rows. Another document, this one with ~28000 rows, brings in ~7000 (a different spreadsheet layout with a different querystring, which also works locally).
Just to see whether the problem was in the read or the bulkcopy, I inserted some loop & count code after the reader and took a look at the record count:
oRdr = oCmd.ExecuteReader();
int x = 0;
while (oRdr.Read())
{
x++;
}
And, in fact, the reader is only reading 2931 rows.
My suspicion is that there's some obscure IIS7 setting that needs to be tweaked. I've played with various timeout values with no success - and I don't think it's a timeout issue, because it would be less likely to consistently fail on the same row. I've
bumped up _oConn.ConnectionTimeOut and oCmd.CommandTimeout to obscene levels, with no change. The rows in the second sheet I mentioned are narrower than in the first - so possibly the 2931 rows in sheet 1 have the same memory footprint as the 7k+ in sheet
2... some sort of buffer overrun error happening, maybe?
This code is wrapped in a try-catch block that's not picking up any errors. It's running inside its own thread - I don't think that would effect error trapping but I figured it might be worth mentioning. Could the OleDB pieces be erroring out without bubbling
the error back to .NET? Is there someplace I might find a log of what's happening - something to look for in the Event Viewer on the server, perhaps?
Any suggestions as to what might be going wrong and how to fix it?
The interop assemblies are simply the .dll's associated with the version of MS Office. Older versions are somewhat forward compatible until it finds code it doesn't know how to read. Then it just stops.
You can simply download the PIA's and install them on the server and see if the problem goes away. No harm no foul.
How can I tell which versions of which DLLs are being opened by this process? There are multiple copies of assorted OLEDB and Jet dlls on both machines in question, and before I try to get approval to install anything new, I'd like to check out the in-use versions
of the DLLs in question on each box.
The closest I thing to an OLEDB-related reference in the project is to System.Data. I'm trying to figure out specifically what DLLs are in use when the OleDBReader is running so I can compare the working machine against the nonworking and see where there
are version mismatches.
I should add that doing a SELECT COUNT(*) [etc] querystring against the Excel files also returns the correct row count only on the local machine and not the server. I've tried removing the lines on and around where the imports stop, but they still end on
the exact same row number even when the data there is different. I've saved out copies of the Excel sheets with columns removed and tried to load those. They also work on the local machine, but fail on the server - but at different line numbers than the
standard-width sheet. The more columns I remove, the bigger OleDB thinks the result set is. I tried adding a few dummy text columns instead, and, sure enough, the row count before failure shrunk.
Looks like the problem was with my connectionstring. The one posted above worked on my local box, but on the server I had to switch it up by file type and add IMEX:
DonL
Member
1 Points
69 Posts
OleDbDataReader stopping prematurely with no error
Apr 20, 2012 01:43 PM|LINK
I'm doing a series of imports of Excel sheets into a SQLServer database via OleDbConnection --> SqlBulkCopy. When I run my app on my local desktop (XP, Visual Studio 2010, .NET framework 4.0), it's working fine, but when I push it up to a server (Windows Server 2008 64 bit, IIS7, 64-bit Jet download from MS installed), the OleDBDataReader I'm using to bring in the excel data doesn't read the entire data set.
The relevant code looks like this:
The connectionString I'm using looks like this:
Running the exact same code against the exact same database using the exact same Excel document, a ~54,000-row Excel worksheet imports correctly when run from my desktop. When run on the server, it consistently stops at 2931 rows. Another document, this one with ~28000 rows, brings in ~7000 (a different spreadsheet layout with a different querystring, which also works locally).
Just to see whether the problem was in the read or the bulkcopy, I inserted some loop & count code after the reader and took a look at the record count:
oRdr = oCmd.ExecuteReader(); int x = 0; while (oRdr.Read()) { x++; }And, in fact, the reader is only reading 2931 rows.
My suspicion is that there's some obscure IIS7 setting that needs to be tweaked. I've played with various timeout values with no success - and I don't think it's a timeout issue, because it would be less likely to consistently fail on the same row. I've bumped up _oConn.ConnectionTimeOut and oCmd.CommandTimeout to obscene levels, with no change. The rows in the second sheet I mentioned are narrower than in the first - so possibly the 2931 rows in sheet 1 have the same memory footprint as the 7k+ in sheet 2... some sort of buffer overrun error happening, maybe?
This code is wrapped in a try-catch block that's not picking up any errors. It's running inside its own thread - I don't think that would effect error trapping but I figured it might be worth mentioning. Could the OleDB pieces be erroring out without bubbling the error back to .NET? Is there someplace I might find a log of what's happening - something to look for in the Event Viewer on the server, perhaps?
Any suggestions as to what might be going wrong and how to fix it?
adamturner34
Contributor
3890 Points
971 Posts
Re: OleDbDataReader stopping prematurely with no error
Apr 20, 2012 04:12 PM|LINK
This rings a very faint bell. It might have something to do with the Excel PIA's (primary interop assemblies) on the server.
Did you try downloading and installing them?
DonL
Member
1 Points
69 Posts
Re: OleDbDataReader stopping prematurely with no error
Apr 20, 2012 04:58 PM|LINK
I'm not familiar with the PIAs. Would their absence cause something to only partially work rather than failing right at the outset?
adamturner34
Contributor
3890 Points
971 Posts
Re: OleDbDataReader stopping prematurely with no error
Apr 20, 2012 05:21 PM|LINK
The interop assemblies are simply the .dll's associated with the version of MS Office. Older versions are somewhat forward compatible until it finds code it doesn't know how to read. Then it just stops.
You can simply download the PIA's and install them on the server and see if the problem goes away. No harm no foul.
DonL
Member
1 Points
69 Posts
Re: OleDbDataReader stopping prematurely with no error
Apr 20, 2012 06:51 PM|LINK
I'm not explicitly using anything related to Interop - does OleDB or the Jet provider use these behind the scenes?
DonL
Member
1 Points
69 Posts
Re: OleDbDataReader stopping prematurely with no error
Apr 23, 2012 04:13 PM|LINK
How can I tell which versions of which DLLs are being opened by this process? There are multiple copies of assorted OLEDB and Jet dlls on both machines in question, and before I try to get approval to install anything new, I'd like to check out the in-use versions of the DLLs in question on each box.
adamturner34
Contributor
3890 Points
971 Posts
Re: OleDbDataReader stopping prematurely with no error
Apr 23, 2012 04:41 PM|LINK
Look at the properties of them in your project. Right-Click | Properties | Version tab
DonL
Member
1 Points
69 Posts
Re: OleDbDataReader stopping prematurely with no error
Apr 23, 2012 04:47 PM|LINK
The closest I thing to an OLEDB-related reference in the project is to System.Data. I'm trying to figure out specifically what DLLs are in use when the OleDBReader is running so I can compare the working machine against the nonworking and see where there are version mismatches.
DonL
Member
1 Points
69 Posts
Re: OleDbDataReader stopping prematurely with no error
Apr 23, 2012 05:26 PM|LINK
I should add that doing a SELECT COUNT(*) [etc] querystring against the Excel files also returns the correct row count only on the local machine and not the server. I've tried removing the lines on and around where the imports stop, but they still end on the exact same row number even when the data there is different. I've saved out copies of the Excel sheets with columns removed and tried to load those. They also work on the local machine, but fail on the server - but at different line numbers than the standard-width sheet. The more columns I remove, the bigger OleDB thinks the result set is. I tried adding a few dummy text columns instead, and, sure enough, the row count before failure shrunk.
DonL
Member
1 Points
69 Posts
Re: OleDbDataReader stopping prematurely with no error
Apr 30, 2012 05:43 PM|LINK
Looks like the problem was with my connectionstring. The one posted above worked on my local box, but on the server I had to switch it up by file type and add IMEX:
<add name="xls" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 8.0;IMEX=1';" /> <add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0 Xml;IMEX=1';" /> <add name="xlsm" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0 Macro;IMEX=1';" />Switching to these and appending the file location worked to get the full import running.
Now on to the next problem!