Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
The Enigma
Posting Yak Master
179 Posts |
 Posted - 2004-01-23 : 03:17:43
|
| [code]begin select @datasource = 'Data Source="c:/' +@FOLDERNAME +'/' +@FILENAME +'";User ID=;Password=;Extended properties=Excel 5.0' select * into #excel_table from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', @DATASOURCE)...sheet1$ end[/code]need to do this ... but using variables in opendatasource not allowed.so how do we go about itWhat 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 structure.So i tried this[code]select @query ='create procedure sp_excel_upload as select * into excel_table from OpenDataSource( '+''''+'Microsoft.Jet.OLEDB.4.0'+''''+','+''''+'Data Source="' +@FOLDERNAME +@FILENAME +'";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[/code]How much performance overhead would this approach add on say about 40 files daily ???Any ideas ??Enigma |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-24 : 16:27:01
|
| IMHO, overhead of the dynamic approach will be near to NOTHING.A bit surprising your doubts are. Sorry if I am wrong in that. |
 |
|
|
|
|
|
OpenDataSource Problem