August 6, 2008 at 11:44 am
Insert into tblemp Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\statedc1\Finance\loading.xls;HDR=YES',
'SELECT * FROM [Upload$]')
when i run the above query i get the following 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)".
August 6, 2008 at 11:47 am
not too sure about the parameters passed to OPENROWSET, but assuming they're valid, are you running the query logged in as a sql user or a windows user? i'd check both the sql service user and/or the windows user to make sure either one's got rights to that share.
August 6, 2008 at 11:58 am
i am runnig with windows user.
August 6, 2008 at 12:01 pm
what happens when you do:
start
run
type "\\statedc1\Finance\loading.xls" without the quotes
hit enter
does it bring up the xl sheet?
August 6, 2008 at 12:04 pm
yes, when i type in link in the run cmd my excel sheet shows up..so?
August 6, 2008 at 12:10 pm
does this work in a query window?
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\statedc1\Finance\loading.xls', 'SELECT * FROM [Upload$]')
August 6, 2008 at 12:54 pm
I tried that in query window and get this error
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'Upload$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".
August 6, 2008 at 12:59 pm
dumb question... is there a tab in the xl sheet called 'upload'?
also, have you enabled openrowset and opendatasource in the surface area configuration tool for that server?
August 6, 2008 at 1:09 pm
yes, OPENROWSET is enabled.
August 6, 2008 at 1:36 pm
i finally found a machine here that generates the errors you're seeing, so i'm doing some research. in the meantime, wouldn't it be faster to just create an import task and run it once?
August 6, 2008 at 1:59 pm
just for fun, i tried this and it worked...
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\statedc1\Finance\loading.xls', 'SELECT * FROM [Upload$]')
August 8, 2008 at 10:46 am
That didnt work for me still i get 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)".
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply