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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Dynamic SQL quoted strings problem

Author  Topic 

pwl2706
Starting Member

2 Posts

Posted - 2013-01-29 : 07:56:25
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-29 : 08:08:45
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

2 Posts

Posted - 2013-01-29 : 09:16:21
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-29 : 09:27:29
@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
   

- Advertisement -