I encountered a problem. I used to import data from Excel to sql2005 many times. This time I encountered some new situations.
The statement used is as follows:
Select * From OpenRowSet
('Microsoft. Jet. oledb.4.0 ',
'Excel 8.0; HDR = yes; IMEX = 1; database = E: \ bb.xls ',
[Sheet1 $]
)
Error: The ole db access interface 'Microsoft. Jet. oledb.4.0 'is configured to run in single-threaded unit mode. Therefore, this access interface cannot be used for Distributed queries.
I found a lot of information, usually when both the client and server are 64-bit systems.
In my case, the client uses a 32-bit XP system, and the server sql2005 is a 64-bit system, which is different from many of the situations found.
Later, I installed the ace2010 driver on the server, which is 64-bit.
Http://www.microsoft.com/downloads/zh-cn/details.aspx? Familyid = c06b8369-60dd-4b64-a44b-84b371ede16d
ModifiedCode
Select * From OpenRowSet
('Microsoft.Ace. Oledb.12. 0 ',
'Excel 8.0; HDR = yes; IMEX = 1; database = E: \ bb.xls ',
[Sheet1 $]
)
However, an error occurs. The prompt is as follows:
You cannot obtain column information from the ole db access interface "Microsoft. Ace. oledb.12.0" of the linked server "(null.
Later I sawArticleDescriptionThe database that executes the preceding SQL statement must be a local database. If it is a remote database, the preceding error is reported.
I tried database operations on a local 32-bit system. Everything works.
Then I uploaded an Excel file on the server and executed the Statement on the server. Everything is normal.
Then execute the same statement locally. Note that the Excel of the edisk BB refers to the edisk on the server, which is normal.
From: http://www.cnblogs.com/tippoint/archive/2012/05/09/2491496.html
Personal Learning favorites