By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Use variable for datasource in Opendatasource

P: n/a
I would like to create a stored procedure that imports an Excel file to
a sql server table. I need to be able to pass the path to the
spreadsheet to the stored procedure like so:

CREATE PROCEDURE dbo.ImportSpreadsheet

(
@Path nvarchar(120)

)

AS

SELECT * INTO Audit
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source = ' + @Path + ';Extended Properties=Excel 8.0')...Audit$
I will call the stored procedure from within my .NET application and
pass it the path as @Path. The path will be chosen by the user at run
time and will be something similar to
C:\Spreadsheets\Audits.xls.

I can make this run in the Query Analyzer:
SELECT * INTO Audit
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source = ''C:\Spreadsheets\Audits.xls\Audit_TC.xls'';Extend ed
Properties=Excel 8.0')...Audit$

I could run the SQL directly from my app, but I like to use stored
procs whenever I can for the speed and flexibility. I can change the
stored proc and not have to recompile/reinstall the app.

Any help would be greatly appreciated.

May 3 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
(lo*****************@gmail.com) writes:
I can make this run in the Query Analyzer:
SELECT * INTO Audit
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source = ''C:\Spreadsheets\Audits.xls\Audit_TC.xls'';Extend ed
Properties=Excel 8.0')...Audit$

I could run the SQL directly from my app, but I like to use stored
procs whenever I can for the speed and flexibility. I can change the
stored proc and not have to recompile/reinstall the app.


To do this in a stored procedure, you would have to use dynamic SQL.
See http://www.sommarskog.se/dynamic_sql.html for details.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 4 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.