Dynamic SQL not executing

Question

Post reply

Dynamic SQL not executing

Jeffery Williams

SSCertifiable

Points: 6441
More actions
December 19, 2017 at 1:45 pm

#341400

Wow I can not believe the number of questions I have had. I am way over tired but that only goes so far.
Ok I have a statement that builds a dynamic query that returns a syntax error. The code is broken into chunks right now so that I could better visualize the problem so don't laugh.
The Code:
       SET @SQL = ''''
        SET @SQL = @SQL + 'SELECT * into ' + coalesce(@ClientName, 'TestClient') + ' FROM OPENROWSET('
        SET @SQL = @SQL + ''''
        SET @SQL = @SQL + '' + 'Microsoft.ACE.OLEDB.12.0' + '' --+ ', '
-- Excel 12.0;HDR=NO;Database=g:\UnZip\ImportSampleXLSX.xlsx;' + ''
        SET @SQL = @SQL + '''' + ', '
        SET @SQL = @SQL + '''' + 'Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;' + '''' + ', ' + @Sheetname + ')'
        SET @SQL = @SQL + ''''
The result statement printed to screen. Works if I past the code from MESSAGES to ssms and execute. (removing leading and trailing single quote of course. I need to execute as a variable : exec sp_executesql @SQL
'SELECT * into TestClient FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;', [Sheet1$])'
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442467

December 19, 2017 at 1:55 pm

#1972794

Would you believe me if I said your ticks were off?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Lynn Pettis - Tuesday, December 19, 2017 1:54 PM
Would you believe me if I said your ticks were off?

Put this into a variable try running it using sp_executesql:
'SELECT * into TestClient FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;'', [Sheet1$])'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

December 19, 2017 at 1:58 pm

#1972796

FYI, I am working it still as well.

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 4

Lynn Pettis - Tuesday, December 19, 2017 1:54 PM
Would you believe me if I said your ticks were off?

lol That was funny.

I know they are. I got closer. I just have been up two days and nothing is making sense and I need to fix it. ugh

It's getting me TICKED off.. lmao

SET @SQL = ''''
SET @SQL = @SQL + 'SELECT * into ' + coalesce(@ClientName, 'TestClient') + ' FROM OPENROWSET('

SET @SQL = @SQL + ''''

SET @SQL = @SQL + '''' + 'Microsoft.ACE.OLEDB.12.0' + '''' --+ ', '
-- Excel 12.0;HDR=NO;Database=g:\UnZip\ImportSampleXLSX.xlsx;' + ''

SET @SQL = @SQL + '''' + ', '

SET @SQL = @SQL + '''' + '''Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;''' + '''' + ', ' + @Sheetname + ')'

SET @SQL = @SQL + ''''

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 5

Lynn Pettis - Tuesday, December 19, 2017 1:58 PM
FYI, I am working it still as well.

I just posted a copy that is much closer. I hate dynamic sql with mixed quotes 🙁

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

How about something like this:
DECLARE @SQLCmd NVARCHAR(MAX), @Sheetname NVARCHAR(128) = N'Sheet1$', @SQL NVARCHAR(MAX) = N' SELECT * into TestClient FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;'', [@Sheetname@]); ';

SELECT @SQLCmd = REPLACE(@SQL,'@Sheetname@',@Sheetname)
EXEC sp_executesql @SQLCmd;

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 7

Lynn Pettis - Tuesday, December 19, 2017 2:06 PM
How about something like this:
DECLARE @SQLCmd NVARCHAR(MAX), @Sheetname NVARCHAR(128) = N'Sheet1$', @SQL NVARCHAR(MAX) = N' SELECT * into TestClient FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;'', [@Sheetname@]); ';
SELECT @SQLCmd = REPLACE(@SQL,'@Sheetname@',@Sheetname)
EXEC sp_executesql @SQLCmd;

Thank you let me play with it. Some of the stuff at the top of the proc is coded

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 8

Jeffery Williams - Tuesday, December 19, 2017 2:12 PM
Lynn Pettis - Tuesday, December 19, 2017 2:06 PM
How about something like this:
DECLARE @SQLCmd NVARCHAR(MAX), @Sheetname NVARCHAR(128) = N'Sheet1$', @SQL NVARCHAR(MAX) = N' SELECT * into TestClient FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;'', [@Sheetname@]); ';
SELECT @SQLCmd = REPLACE(@SQL,'@Sheetname@',@Sheetname)
EXEC sp_executesql @SQLCmd;
Thank you let me play with it. Some of the stuff at the top of the proc is coded

Same issue I am afraid;

(2 row(s) affected)

'SELECT * into TestClient FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;', [Sheet1$])'
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'SELECT * into TestClient FROM OPENROWSET('.

(2 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
'SELECT * into TestClient FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;', [Sheet1$])'
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'SELECT * into TestClient FROM OPENROWSET('.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Jeffery Williams - Tuesday, December 19, 2017 2:12 PM
Jeffery Williams - Tuesday, December 19, 2017 2:12 PM
Lynn Pettis - Tuesday, December 19, 2017 2:06 PM
How about something like this:
DECLARE @SQLCmd NVARCHAR(MAX), @Sheetname NVARCHAR(128) = N'Sheet1$', @SQL NVARCHAR(MAX) = N' SELECT * into TestClient FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;'', [@Sheetname@]); ';
SELECT @SQLCmd = REPLACE(@SQL,'@Sheetname@',@Sheetname)
EXEC sp_executesql @SQLCmd;
Thank you let me play with it. Some of the stuff at the top of the proc is coded
Same issue I am afraid;
(2 row(s) affected)
'SELECT * into TestClient FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;', [Sheet1$])'
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'SELECT * into TestClient FROM OPENROWSET('.
(2 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
'SELECT * into TestClient FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;', [Sheet1$])'
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'SELECT * into TestClient FROM OPENROWSET('.

I am thinking it has something to do with the OPENROWSET itself. I don't use it so I can't really provide much more guidance. I assume if you print and then copy/paste the string it works.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

I really don't like importing data from Excel spreadsheets.

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 11

Lynn Pettis - Tuesday, December 19, 2017 2:24 PM
I really don't like importing data from Excel spreadsheets.

OH I love it LMAO..

Back in the day it did not have to be associated with a LinkedServer. This is B.S.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Sue_H SSC Guru Points: 90860 More actions · Answer 12

It could be something else is going on. I just took Lynn's code, change the path to the Excel file and that was all. It ran without any problems.

Sue

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Sue_H - Tuesday, December 19, 2017 2:40 PM
It could be something else is going on. I just took Lynn's code, change the path to the Excel file and that was all. It ran without any problems.
Sue

Could you post your code?

Sue_H SSC Guru Points: 90860 More actions · Answer 14

Lynn Pettis - Tuesday, December 19, 2017 2:47 PM
Sue_H - Tuesday, December 19, 2017 2:40 PM
It could be something else is going on. I just took Lynn's code, change the path to the Excel file and that was all. It ran without any problems.
Sue
Could you post your code?

This is what I used:
DECLARE @SQLCmd NVARCHAR(MAX), @Sheetname NVARCHAR(128) = N'Sheet1$', @SQL NVARCHAR(MAX) = N' SELECT * into TestClient FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;Database=C:\IChangedThisTo\Something.xls;'', [@Sheetname@]); ';

SELECT @SQLCmd = REPLACE(@SQL,'@Sheetname@',@Sheetname) EXEC sp_executesql @SQLCmd;

Just used your code and all I did was just change that path to the Excel file.

But I did go back and look at Jeffrey's error. It looks to me like it has a quote before the word Select. If I put a quote there before the select, I can get the same error.

Sue