import data from excel to SqlServer

Hello,

I want to import data from an excel sheet to SqlServer...
I use a linked server...
I execute the following code:

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\MyExcel.xls',NULL,
'Excel 5.0'
GO

sp_addlinkedsrvlogin N'ExcelSource', false, sa, N'ADMIN', NULL
GO

SELECT * FROM ExcelSource...Sheet1$
GO

and I get the error:

Server: Msg 7314, Level 16, State 1, Line 2
OLE DB provider 'ExcelSource' does not contain table 'Sheet1$'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='ExcelSource', TableName='Sheet1$'].

When I execute the command:

select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\book1.xls',Sheet1$)

I get the same error...

Can anyone help me?

Thanks
KorinaTry using OPENQUERY.

SELECT * FROM OPENQUERY('ExcelSource','SELECT * FROM SHEET1$')|||I get the error:

Server: Msg 7403, Level 16, State 1, Line 2
Could not locate registry entry for OLE DB provider 'c:\book1.xls'.
OLE DB error trace [Non-interface error: Provider not registered.].

What I am doing wrong?|||Make the following changes.

sp_addlinkedserver 'ExcelSource6',
'Excel',
'Microsoft.Jet.OLEDB.4.0',
'c:\MyExcel.xls',
NULL,
'Excel 8.0'

SELECT * FROM OPENQUERY(ExcelSource6,'SELECT * FROM [Sheet1$]')|||and now I get the error:

Server: Msg 7399, Level 16, State 1, Line 8
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].|||Mine works. A couple of questions for you.

Do you or someone else or another program have the file open?

Do you realize the path you define (c:\MyExcel.xls) is relative to the server and not to your client machine? This the servers C:\ drive.|||The file is close and it is placed on the specified server drive...
Do you have any other idea?

I would be gratefull because I need it as soon as possible.

Thanks|||Take a look at this:

http://support.microsoft.com/default.aspx?scid=314530

Ahhh Google.... Who needs to know anything anymore? programming before high speed internet access was such a pain.

Implicit transaction for a page request?

If I execute multiple SqlCommands in a single .aspx, using a single SqlConnection, are all the commands part of the same transaction?

That is, if I say:

cmd1.CommandText = "UPDATE foo SET myVal = myVal - 1"
cmd2.CommandText = "SELECT myVal FROM foo"
cmd1.Connection = myConn
cmd2.Connection = myConn
myConn.Open()
cmd1.ExecuteNonQuery() ' decrement myVal
intResult = CInt(cmd2.ExecuteScalar()) ' select value of myVal
myConn.Close()

Do the UPDATE and the SELECT happen in the same transaction, or is it possible that someone else would have changed the value of myVal between these two calls?Nope. Each statement has an implicit transaction but to put them both under a single transaction you have to use an explicit transaction.

So yes, data could have changed between the statements.

Don|||Thanks for the clarification. So if I understand you right, this:

cmd.CommandText = "UPDATE ... ; SELECT @.myResult = ... "
cmd.ExecuteNonQuery()

would put them in the same transaction, which is what I want.|||Now I'm on shakier ground. I'm pretty sure that SQL Server still considers them to be separate statements, simply batched together. But not absolutely positive.

Any reason you don't want to just use an explicit transaction?

Don|||No -- I'd like to use an explicit transaction, but I'm not sure how. Can you give me an example of how to do it w/o moving the queries to a stored procedure?

Thanks!|||Well, you're opening yourself up to all kinds of security holes by using dynamic SQL like this. Stored procedures are much better way to go.

But you can either use transactions via dynamic SQL and batch statements, or using the SQLTransaction class in ADO.NET. The example in the .NET docs is pretty clear. For the latter, just new up a SQLTransaction object and use the connection object to begin the transaction. Then call either the Commit or Rollback methods of the transaction object.

But remember that your app won't be very secure unless you take all the steps to close the holes.

Don|||Thanks, Don. What kind of hacks am I risking? I'm using parameters to avoid sql-insertion attacks; are there other risks that come with using SqlCommands?|||Ah, you're using ADO.NET parameters? That wasn't clear from the snippets you've posted, although you had one @.Result showing. You're using parameters to provide the changeable values in the SQL, right?

If that's the case, you're probaly reasonably well protected from SQL injection, since ADO.NET passes the SQL and parameters separately to SQL Server, where they are inserted into the SQL.

Don