| Author |
Topic |
|
LisaS
Starting Member
12 Posts |
 Posted - 2007-08-16 : 13:49:22
|
| I'm trying to use the techinque from this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 and I'm getting errors: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].This sheet is saved as "Excel 97 - Excel 2003"I'm running Enterprise Manager/SQL Query Analyzer from my desktop connected to a db on a server. Is this a message about not finding the sheet? If so: a) can I reference a spreadsheet on "my" local c: drive or on my common network share (not mapped on the server)? b) if I put the spreadsheet on a network share mapped on the server, how do I reference it? if there is another cause for the error, any hints on what to look for? Currently, I'm pointing to a share that I have mapped locally and is also referenced directly by the server - the actual SQL I'm running is: SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\hqp1psfile01\DWExtracts\Lisa\dts_to_jobs.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-16 : 14:24:07
|
The path to the file is relative to the SQL Server.Also see the SQL Server 2005 clarification.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
LisaS
Starting Member
12 Posts |
Posted - 2007-08-16 : 14:51:14
|
| Thanks. I modified it to reference a share the same way that successfull DTS on same server access that share. Still got an error. Then, I tried the MSDASQL "version" to see more of the error message and got: OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.]the file I'm referencing is not open and should not have any permissions issues. But the message calls the file "unknown" so I'm not sure if I'm doing something else wrong or if that is a message quirk. forgive my ignorance - but what is "see the SQL Server 2005 clarification" referring to? |
|
|
|
LisaS
Starting Member
12 Posts |
Posted - 2007-08-16 : 15:39:42
|
| OK. I was finally able to remote desktop to the server and drop my spreadsheet on the c: drive. then run the query from my desktop - and it was fine. So it seems that my issues are in having the file on a share rather than on a local drive - or not referencing it properly. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-16 : 15:45:14
|
Seems to me you had the Excel file opened by someother application, or hasn't enough permission (the account for which sql server is running) on the share to view the file.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
LisaS
Starting Member
12 Posts |
Posted - 2007-08-16 : 15:51:20
|
| that's what it seems like - but that doesn't seem to be true. It definitely isn't open by another user as from my desktop I can open, update & close it fine. Another interesting thing is that we don't have excel itself installed on the server so I can't attempt to natively open it from that share signed on to the server - but that server has access to that share for reading & writing from DTS packages - I'm going to bump the question "up the line" to the DBA to see if he has any ideas. |
|
|
|
LisaS
Starting Member
12 Posts |
Posted - 2007-08-16 : 16:19:30
|
| I've now tried it on 2 local hard drives on the server (c: & d:) and it was fine. Tried it on two mapped network shares - and no go. Notice that signed on to the server I was moving the file around from drive to drive fine - so no permissions issues at that level. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-08-17 : 02:30:57
|
| Try putting the EXCEL file in C drive of the server and then querySELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\dts_to_jobs.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')MadhivananFailing to plan is Planning to fail |
|
|
|
minalj
Starting Member
1 Post |
Posted - 2008-08-08 : 11:37:33
|
| Can any one know how to select only two colums from Excel using Query analyzer.like ....SELECT col1,col2FROMOPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\dts_to_jobs.xls;HDR=YES','SELECT col1,col2 FROM [Sheet1$]')I m trying above query but its not working. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-08 : 13:29:01
|
quote:
Originally posted by minalj
Can any one know how to select only two colums from Excel using Query analyzer.like ....SELECT col1,col2FROMOPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\dts_to_jobs.xls;HDR=YES','SELECT col1,col2 FROM [Sheet1$]')I m trying above query but its not working.
whats the error obtained? |
|
|
|
|
openrowset reading Excel