can we do something like that without using OPENROWSET or OPENDATASOURCE?
thanks,
erick
Printable View
can we do something like that without using OPENROWSET or OPENDATASOURCE?
thanks,
erick
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
any idea?Quote:
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".
regards,
erick
How about creating a temporary table and then import the data in to the table and then join as normal
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.Quote:
Originally Posted by 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