Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

3873 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

3873 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  
 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.06 seconds. Powered By: Snitz Forums 2000