MS SQL problem

[b00gieman]

Hi!
Can somebody tell me what's wrong in this SQL command:

SELECT * INTO dbo.shopping_TSR FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database='c:\inetpub\[URL unfurl="true"]wwwroot\fileuploader\upload\tmb2.xls'',[/URL] 'SELECT * FROM [Sheet1$]')

because I'm getting this error:
Incorrect syntax near 'c:'

 

[unclejimbob]

Try

SELECT * INTO dbo.shopping_TSR FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\inetpub\

http://wwwroot\fileuploader\upload\tmb2.xls',

'SELECT * FROM [Sheet1$]')

 

[b00gieman]

That will give the following error:

Initialize returned 0x80004005: The provider did not give any information about the error.]

I'm developing an application in asp for importing data from excel to ms sql.
My SQL command(in the ASP page) looks like this:

SQLstr = "SELECT * INTO dbo.shopping_TSR FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database='"+Server.MapPath("/fileuploader/upload/tmb2.xls")+"'', 'SELECT * FROM [Sheet1$]')"

Am I doing something wrong in the SQL statement?
I used Server.mapPath because the excel file is stored on a server.

 

[ESquared]

Get rid of the single quote after Database=' and change the two single quotes later to one single quote. If that doesn't work, use two each in place of the ones you removed.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[b00gieman]

I did that.Now I'm getting the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]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.]

Any idea on what could be the cause?

Thanks in advance!

 

[PWise]

do you have read write access to the temp directory on the server?

 

[b00gieman]

On which server?The asp files and the .xls file are on a server,and the database is on another server.

 

[PWise]

not sure try both

 

[ESquared]

I've always had security problems when trying to do stuff like this. Test it with a file on the server first, then if you get that working you know what the problem if it doesn't work somewhere else.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[b00gieman]

Well...the excel file and the asp script are on the same server.Should I try with a file stored on the sql server or....?

 

[ESquared]

Yes put the file on the sql server to try it, since you are accessing it through the server.


[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]