September 14, 2008 at 11:44 am
I posted an earlier question to the forum under "openrowset with excel". Here is the query and the errors I'm receiving, when a similar query is run against Adventure Works it runs fine, but not against this DB (ScottyGifts). The spreadsheet has 3 columns, saved as an an xls 97-2003, the path is correct and columns have headings and data in each cell. As stated in the earlier post, this DB was "attached" as opposed to "added". Not sure if it would make a difference. Any help is greatly appreciated!
Query: UPDATE SP
SET SP.ProductDescription = T.NewProductDescription,
SP.QuantityOnHand = T.ActualCounted
FROM [ScottyGifts].[dbo].[SGProductTBL] SP
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\MSSQLTIPS\DDTEST2.xls;',
'SELECT ProductId, NewProductDescription, ActualCounted
FROM [Sheet1$]') T
ON SP.ProductId = T.ProductId
GO
This is the error: Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
October 14, 2008 at 2:43 am
Is the workbook already open? Also, check Task Manager for excel.exe if you've closed the application but you still get the error.
Does the SQL Server service account have appropriate permissions on the source folder/file?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
October 14, 2008 at 6:41 am
The workbook is not open and I have admin rights to the computer. It is the strangest thing! I finally got it to work on my XP machine, but still cannot get the code to work on the clients machine. Made sure all settings on the database are exactly the same as on my XP machine, code is identical, and client has admin rights on their machine, still get errors about "null" server. It works like a charm on my Vista machine, go figure. I may never figure this one out, without having a DBA look at the clients machine, and that is too costly at the moment.
I'm still wondering if there is a difference between "adding" a database and "attaching" one?
Thanks for the help!
Don
October 14, 2008 at 6:54 am
If you are using Windows authentication (an I suppose you do), then the Windows account you are logged in with should have permissions on the folder where Excel file is located.
October 14, 2008 at 6:56 am
What has changed on your machine? What account is the SQL Server service running under?
I'm not sure what you mean with "adding" a database. Attaching, on the other hand, is a database creation method where database files (thet have previosuly been detached from the source server instance) are attached to the destination server instance.
You can find more information regarding attaching/detaching databases in Books Online.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply