February 22, 2011 at 12:35 pm
I would like to find a simple way to insert into an existing table in SQL 2005 from an existing Excel 2003 table. I have seen posting in these forum that suggest using OPENROWSET or OPENDATASOURCE to do this. I have tried variations of the two following scripts:
INSERT INTO dbo.myTable
SELECT * FROM OPENDATASOURCE
('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\myExcel.xls;
Extended Properties=Excel 8.0')...[sheet1$]
SELECT * INTO dbo.myTable
FROM OPENROWSET
('Microsoft.jet.OLEDB.4.0','Excel 8.0;
database=C:\myExcel.xls',
'SELECT * FROM [Sheet1$]')
In both cases, I get the following error message:
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
I have run these queries both from my local machine and from the server, but still get these errors.
Do I need to set some values to make the imports work correctly? If so, please provide me with the specific code needed. Thank you for your help.
gmrose
February 23, 2011 at 8:35 am
Have you tried the import wizard? There are several methods to get to it, but the one I use is to right click on the database name, tasks -> Import Data...
This is going to generate a throw away SSIS package (although you can save it to look at it later) to do your import. Now of course you could just open up BIDS and create your own SSIS package but the wizard is nice and easy.
Fair warning though, everything I have ever read (and my own experience) says that importing data from excel is a pain in the rear. Watch out for lost leading 0's on things like account numbers '00001235' tends to become the number 1235 in excel. And you also get numbers converted into scientific notation and dates into integers. The solution to all of that is to make sure that the columns in your excel spreadsheet accurately match your data (and to cross fingers, toes, your friends fingers and toes and pray really really hard).
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 23, 2011 at 12:17 pm
Are you using a 32-bit OLEDB provider on a 64-bit server?
If the above is not the problem, have you set the Ad Hoc Distributed to true?
sp_configure
GO
sp_configure ‘show advanced options’, 1
GO
reconfigure
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
reconfigure
GO
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply