Exporting data to Excel hangs when using Open Rowset with Microsoft.ACE.OLEDB.12.0
Hello, I am not sure if this forum is the right place to ask my question, but I believe there are more experts here than anywhere else. So I will post my questions here anyway. We used to use "Microsoft.Jet.OLEDB.4.0" to handle exporting data to Excel under MS SQL Server 2000, and everything worked fine for years. Last month, we migrated to SQL Server 2005 (64-bit) running on Windows Server 2003 (64-bit). Because "Microsoft.Jet.OLEDB.4.0" doesn't have 64-bit version, we switched to "Microsoft.ACE.OLEDB.12.0" engine to handle the export. We have daily reports, weekly reports, and monthly reports. Some of them are in text format, the rest of them in Excel format. The total number of reports is 15-30 depending on the days. After migration, everything worked fine except some small issues like sorting problem, etc. But after two weeks, the process of exporting to Excel was hanging and I even couldn't kill the process. The only way is to restart the SQL Server service. I have searched on the web, somebody suggested that we should reserve more memory by using -g parameter in the service startup settings. I have tried that, but after two weeks, same thing happened again. Could anybody provide any suggestions? I will be very appreciated. David
March 28th, 2011 7:49pm

Are you sure you have a memory starvation issue? If so and you have SQL server on the same machine where you are processing the reports, it could be the cause.It is a good practice to leave some memory to other processes. Try limiting how much memory the SQL Server can use. Right-click on the SQL Server instance and go to the memory page, in there examine the setting, if it is at the default value, find out how much memory you got on the box and decrease the MaxMemory setting to be at around 80% of the total memory.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
March 28th, 2011 8:16pm

I have total 12GB memory on the box, and I reserve 8GB for SQL Server because I have another program running on the box every night. I am pretty sure I don't have memory starvation issue. The reason I use "-g" startup parameter is that out of 8GB memory pool, I wanted to reserve more (I think the default value is 256 MB) for SQL Server to handle external operations. This was from one article I read online. After the issue happened, I have checked and everything seems normal, the VAS is normal too.
March 28th, 2011 8:44pm

At what stage the package gets stuck? It seems to me you could continue running the package with the old driver, you only needed to set this package to execute in 32 bit mode vs 64. Do you have IMEX=1 enabled in your Excel connection?Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
March 28th, 2011 9:11pm

I am not exactly using SSIS packages. In fact I am using Open Rowset from a stored procedure. The code is like: ... SET @Provider = 'Microsoft.ACE.OLEDB.12.0' SET @ExcelString = 'Excel 12.0;Database=' + @ReportFile + ';Extended Properties=Excel 12.0 XML; HDR=YES' SET @query = 'SELECT ... FROM TableA' SET @sqlstmt = 'insert into OpenRowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$A1:N1]'') ' + @query EXEC(@sqlstmt) ... It hangs when it's reaching "EXEC(@sqlstmt)", which is the exporting to Excel part. I have tested the procedure step by step, everything else works fine.
March 28th, 2011 9:19pm

Add IMEX=1 after HDR=YESArthur My Blog
Free Windows Admin Tool Kit Click here and download it now
March 28th, 2011 10:38pm

But this IMEX=1 setting is for handling numeric/string mixture. Are you sure it will fix my problem. My problem here is the code works for two weeks then stops working. I don't want to restart my SQL Server service every two weeks. It's supposed to be running 24/7.
March 28th, 2011 10:45pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics