Join Excel with Table in SQLSERVER

[erickwidya]

can we do something like that without using OPENROWSET or OPENDATASOURCE?

thanks,
erick

[erickwidya]

yes it can be done..after trying a couple of hours

using sp_addlinkserver to do that
after use it like usual query

read BOL for further information

[edit]: found another issue, i got this error when try to connect to Server DB

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource".

any idea?

regards,
erick

[Mark Gambo]

How about creating a temporary table and then import the data in to the table and then join as normal

[erickwidya]

not quite understand

it need to be execute against Network Server not local Server..

not sure how to work with this one

[Mark Gambo]

not quite understand

it need to be execute against Network Server not local Server..

not sure how to work with this one

What I am trying to say is first create a temporary table in your SQL Server Database and then import the excel spreadsheet, via DTS, into that temporary table. Now join the temporary table with the other tables as you would normally do. I think you would be better off creating a Stored Procedure (SPROC) to accomplish this.

[erickwidya]

thanks Mark Gambo
DTS need to be run manually right? or do that via Job Schedule ?
it can be done but it can't be used since this is end user that involved..and this one needs to do it automatically..

i have this i create linked server then do that via OPENQUERY..

i'm not sure about the performance though

thanks,
erick