Friday, March 23, 2012

OpenDataSource Problem

select @.datasource = 'Data Source="c:/'
+'";User ID=;Password=;Extended properties=Excel 5.0'
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', @.DATASOURCE)...sheet1$

need to do this ... but using variables in opendatasource not allowed.

so how do we go about it

What i need to do is allow upload of any excel file ... may contain errors and then perform some checks on the data before putting it into another table with a fixed @.query ='create procedure sp_excel_upload as select
from OpenDataSource( '+''''+'Microsoft.Jet.OLEDB.4.0'+''''+','+''''+'Da ta Source="'
+'";User ID=;Password=;Extended properties=Excel 5.0'+'''' + ')...sheet1$'
exec (@.query)
exec sp_excel_upload
/* put checks here */
select * into final_table from excel_table
drop procedure sp_excel_upload
drop table excel_table

How much performance overhead would this approach add on say about 40 files daily ?

Any ideas ??|||Guys .. Gurus ...

Nobody has any thoughts about this ??sql

No comments:

Post a Comment