SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Dynamic SQL quoted strings problem		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pwl2706
Starting Member

United Kingdom
2 Posts
Posted - 01/29/2013 :  07:56:25  Show Profile  Reply with Quote
Hi all

(BTW, this has also been posted on Stack Overflow)

I want to build a paremtized SQL Query that retrieves data from an EXCEL file.

The name of the Excel file will change every year.

Here is the query that runs fine in T-SQL (on SQL Server 2008 R2 Express):

SELECT * INTO MobilityData FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=\\UNCPath\Annual_Mobility_Update\DAM2013-3.xls;Extended Properties=Excel  8.0')...[Sheet1$]


(UNC Path is a network UNC Path, works fine)

The string 'DAM2013-3.xls' will be changing year on year. What I'd like to do would be execute a parametized stored procedure and build the Dyanamic SQL on the fly...

Something like this:

SELECT @sQry='SELECT * INTO MobilityData FROM OPENDATASOURCE(''''Microsoft.Jet.OLEDB.4.0',
'Data Source=\\sgstore\Shared\operations\DM\Annual_Mobility_Update\''' 
+ @XLFileSource + ';Extended Properties=Excel 8.0'')...[Sheet1$]'


but I seem to be in quote hell and can't get it to compile.

What should I do to fix this?

I'd be grateful for any tips, fishing rods, or even whole a fish etc

Thanks Philip

James K
Flowing Fount of Yak Knowledge

1481 Posts
Posted - 01/29/2013 :  08:08:45  Show Profile  Reply with Quote
This is not a fish, it might be a bait at best:
SELECT @sqry = 'SELECT * INTO MobilityData FROM OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'',
''Data Source=\\UNCPath\Annual_Mobility_Update\' + @XLFileSource +  ';Extended Properties=Excel  8.0'')...[Sheet1$]';
Go to Top of Page

pwl2706
Starting Member

United Kingdom
2 Posts
Posted - 01/29/2013 :  09:16:21  Show Profile  Reply with Quote
thanks for that help...

Now whey would I get an error

quote:
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.


when I run this:
EXECUTE sp_executesql @sqry
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1481 Posts
Posted - 01/29/2013 :  09:27:29  Show Profile  Reply with Quote
@sqry needs to be of type nvarchar - something like this:
DECLARE @sqry NVARCHAR(4000);
DECLARE @XLFileSource nvarchar(32) = 'DAM2013-3.xls';

SELECT @sqry = 'SELECT * INTO MobilityData FROM OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'',
''Data Source=\\UNCPath\Annual_Mobility_Update\' + @XLFileSource +  ';Extended Properties=Excel  8.0'')...[Sheet1$]';
OPENDATASOURCE does not accept variables for its arguments, but as far as I can see, you are not doing that, so the @sqry not being of type NVARCHAR may be the problem.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000