Import excel data into sql table

Hi!

I have to develop an application for transfering data from an excel file into a sql table.The excel file is uploaded to a server.The database(and the table) is on another server.At first,I used openrowset for transferring data to the table.My sql command looked like this(in my asp page):

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

I kept getting this 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.]

After reading a few articles,I think the cause of my error is that the excel file is uploaded into the folder where the asp script is located.I have 2 servers : one running the asp scripts and one containing the database.

Is my error generated by the fact that the excel file is on a different server than the sql server?How could I make this work?

Have you had a look at SSIS (Integration Services) if you're using SQL 2005 or DTS if you're using SQL2000. You can use the Excel Connection Manager. Can your Excel file be accessed by the SQL Server through a share?

|||I'm using SQL Server 2000.I want to automatize the process(so that the users don't have to install SQL server).After reading a few articles,I think I have a problem with the access rights on the SQL Server.Can u confirm me that?sql