bnrtech
asked on
Insert Into Excel File From 64 Bit SQL 2008
Hello,
I have a stored procedure that uses the following code to insert into a pre existing excel file.
SET @mysql = 'insert into OPENROWSET(' + char(39) + 'Microsoft.Jet.OLEDB.4.0' + char(39) + ','
+ char(39) + 'Excel 8.0;Database=' + @myexcelfile + ';' + char(39) + ','
+ char(39) + '
SELECT *
FROM [' + @mytable + ']' + char(39) + ')
SELECT * FROM ' + @mytable
But am getting an error
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
I understand that this is because there is no 64 bit OLEDB driver available.
I have read that I can install SQL 2005 Express 32 bit along side of SQL 2008 to work around this. But I would like to avoid doing that.
I have also read that the download available @
http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en
Does not remedy this problem as it only bridges OLE DB to ODBC. It does not bridge 64-bit to 32-bit. Which means that 32-bit OLE DB Providers, such as Jet, and 32-bit ODBC are still inaccessible to SQL Server 64-bit.
Are there any alternatives to get my code working without having to install another SQL server version etc?
I have a stored procedure that uses the following code to insert into a pre existing excel file.
SET @mysql = 'insert into OPENROWSET(' + char(39) + 'Microsoft.Jet.OLEDB.4.0' + char(39) + ','
+ char(39) + 'Excel 8.0;Database=' + @myexcelfile + ';' + char(39) + ','
+ char(39) + '
SELECT *
FROM [' + @mytable + ']' + char(39) + ')
SELECT * FROM ' + @mytable
But am getting an error
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
I understand that this is because there is no 64 bit OLEDB driver available.
I have read that I can install SQL 2005 Express 32 bit along side of SQL 2008 to work around this. But I would like to avoid doing that.
I have also read that the download available @
http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en
Does not remedy this problem as it only bridges OLE DB to ODBC. It does not bridge 64-bit to 32-bit. Which means that 32-bit OLE DB Providers, such as Jet, and 32-bit ODBC are still inaccessible to SQL Server 64-bit.
Are there any alternatives to get my code working without having to install another SQL server version etc?
SET @mysql = 'insert into OPENROWSET(' + char(39) + 'Microsoft.Jet.OLEDB.4.0' + char(39) + ','
+ char(39) + 'Excel 8.0;Database=' + @myexcelfile + ';' + char(39) + ','
+ char(39) + '
SELECT *
FROM [' + @mytable + ']' + char(39) + ')
SELECT * FROM ' + @mytable
You could run an SSIS package in 32 bit mode to do that.
What you are doing with your code is connecting to Excel and trying to use it directly as a datasource .. this will not be possible as Excel is a 32bit application and you are running 64bit sql .... "there is no fix to use 32 bit data source for 64 sql"
so your options
1, install sql2008 x64 - 32 bit .. then you can setup you Excel datasource
or
2, create an SSIS package to import the data from you .xls into a holding table on your sql server then you can query the data... you could run you SSIS package every hour for example to update you SQL Table
so your options
1, install sql2008 x64 - 32 bit .. then you can setup you Excel datasource
or
2, create an SSIS package to import the data from you .xls into a holding table on your sql server then you can query the data... you could run you SSIS package every hour for example to update you SQL Table
Actually I think they are trying to insert into the worksheet.
Actually I think they are trying to insert into the worksheet.
would still have to use SSIS.. you cant directy connect from x64 sql to x32 excel
Yes,as I already stated in the very first post.
ASKER
Hi nmcdermaid,
I had previously tried placing my command inside an SQL Exec Task within my SSIS package, but still get the same error. Perhaps I am following the wrong series of steps. Can you explain?
I had previously tried placing my command inside an SQL Exec Task within my SSIS package, but still get the same error. Perhaps I am following the wrong series of steps. Can you explain?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
rename
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.
then copy
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.
to the first mentioned folder. It will run everything as 32-bit, but it will work
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.
then copy
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.
to the first mentioned folder. It will run everything as 32-bit, but it will work
As you are using SQL 2008, see bottom of this page for an alternative method:
http://msdn.microsoft.com/en-us/library/ms141766.aspx
http://msdn.microsoft.com/en-us/library/ms141766.aspx
ASKER
Hi TAB8,
Thank you for suggestion but I cannot follow through with it as there are existing packages that need to run in 64 bit mode.
nmcdermaid,
You are correct that I need to run the package as a 32bit process. I have been doing so by running dtexec via cmdshell with a full path to the 32 bit version. I'll try building from scratch and will let you know how it goes.
Thank you for suggestion but I cannot follow through with it as there are existing packages that need to run in 64 bit mode.
nmcdermaid,
You are correct that I need to run the package as a 32bit process. I have been doing so by running dtexec via cmdshell with a full path to the 32 bit version. I'll try building from scratch and will let you know how it goes.
check out the link - there is no need to run as a cmdexec process anymore. You can run a standard SSIS step and just tick 32 bit.
ASKER
Hello again,
The SSIS step is not an option for me as many users need to be able to run this process at once. However the dtexec 32bit is working well so far. If I run into any issues ill start another thread. Thanks for all the help.
The SSIS step is not an option for me as many users need to be able to run this process at once. However the dtexec 32bit is working well so far. If I run into any issues ill start another thread. Thanks for all the help.