December 21, 2017 at 5:47 am
Sue_H - Tuesday, December 19, 2017 2:40 PMIt 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
It is the ticks. They are just a pain in the ass when encapsulating in dynamic sql. I was off sick yesterday so I am back to it.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 21, 2017 at 5:49 am
Sue_H - Tuesday, December 19, 2017 2:53 PMLynn Pettis - Tuesday, December 19, 2017 2:47 PMSue_H - Tuesday, December 19, 2017 2:40 PMIt 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
Here is the entire SPROC:
I have a table that is loaded by another stored procedure that lists the files in a directory waiting to be processed. you can just eliminate that and define a single file in the variable @ETLFileName.
USE [ETL]
GO
/****** Object: StoredProcedure [dbo].[ImportExcelSheetForCustomerEmployeeUpdate2] Script Date: 12/19/2017 4:03:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ImportExcelSheetForCustomerEmployeeUpdate2]
(@BatchID int)
as
-- EXEC ImportExcelSheetForCustomerEmployeeUpdate 1
-- Jeffery Williams
-- 12/18/2017
DECLARE @FileID int
,@ETLFilename varchar(250)
,@ClientName varchar(100)
,@FileType varchar(5)
,@ColumnCount int
,@RowsToETL int
,@StatusID int
,@Processed bit = 0
,@Count int
,@SQL nvarchar(4000)
,@Sheetname varchar(50) = '[Sheet1$]'
,@CMDSQL as varchar(4000)
,@SQLCmd NVARCHAR(MAX)
SELECT *
FROM FilesToImport
BEGIN
SELECT @Count = count(*)
FROM FilesToImport
WHERE BatchID = @BatchID
AND StatusID = 1
END
PRINT 'Count of records to process: ' + cast(@Count as varchar)
WHILE @Count > 0
BEGIN
BEGIN
SELECT TOP 1 @FileID = FileID, @ETLFilename = ETLFilename, @ClientName = ClientName
,@FileType = FileType, @ColumnCount = ColumnCount, @RowsToETL = RowsToETL
FROM FilesToImport
WHERE StatusID = 1
AND BatchID = @BatchID
END
-- Rename the file
set @CMDSQL = 'rename G:\CustomerETL\Employee\PendingETL\' + @ETLFilename + ' ETLEmployeexls.xls'
exec master..xp_cmdshell @CMDSQL
--PRINT cast(@cmdsql as varchar(4000))
-- Ciode below generates our select. Need to add an INTO clause and create a staging table for each import. Prior to this step we need to rename the file.
SET @sql = ''''
SET @sql = @sql + 'SELECT * into ' + coalesce(@ClientName, 'TestClient') + ' FROM OPENROWSET('
SET @sql = @sql + '''' + 'Microsoft.ACE.OLEDB.12.0' + '''' --+ ', '
-- Excel 12.0;HDR=NO;Database=g:\UnZip\ImportSampleXLSX.xlsx;' + ''
SET @sql = @sql + '''' + '''Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;''' + '''' + ', ' + @Sheetname + ')'
PRINT cast(@SQL as varchar(8000))
EXEC sp_executesql @sql
set @CMDSQL = 'rename G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls ' + @ETLFilename
exec master..xp_cmdshell @CMDSQL
UPDATE FilesToImport
SET StatusID = 2
WHERE FileID = @FileID
/* -- TRUNCATE TABLE FilesToImport
UPDATE FilesToImport
SET StatusID = 1
*/
SET @Count = (@Count - 1)
CONTINUE
END
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 21, 2017 at 6:30 am
Jeffery Williams - Thursday, December 21, 2017 5:49 AMSue_H - Tuesday, December 19, 2017 2:53 PMLynn Pettis - Tuesday, December 19, 2017 2:47 PMSue_H - Tuesday, December 19, 2017 2:40 PMIt 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
Here is the entire SPROC:
I have a table that is loaded by another stored procedure that lists the files in a directory waiting to be processed. you can just eliminate that and define a single file in the variable @ETLFileName.
USE [ETL]
GO
/****** Object: StoredProcedure [dbo].[ImportExcelSheetForCustomerEmployeeUpdate2] Script Date: 12/19/2017 4:03:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ImportExcelSheetForCustomerEmployeeUpdate2]
(@BatchID int)as
-- EXEC ImportExcelSheetForCustomerEmployeeUpdate 1
-- Jeffery Williams
-- 12/18/2017DECLARE @FileID int
,@ETLFilename varchar(250)
,@ClientName varchar(100)
,@FileType varchar(5)
,@ColumnCount int
,@RowsToETL int
,@StatusID int
,@Processed bit = 0
,@Count int
,@SQL nvarchar(4000)
,@Sheetname varchar(50) = '[Sheet1$]'
,@CMDSQL as varchar(4000)
,@SQLCmd NVARCHAR(MAX)SELECT *
FROM FilesToImportBEGIN
SELECT @Count = count(*)
FROM FilesToImport
WHERE BatchID = @BatchID
AND StatusID = 1END
PRINT 'Count of records to process: ' + cast(@Count as varchar)
WHILE @Count > 0
BEGINBEGIN
SELECT TOP 1 @FileID = FileID, @ETLFilename = ETLFilename, @ClientName = ClientName
,@FileType = FileType, @ColumnCount = ColumnCount, @RowsToETL = RowsToETL
FROM FilesToImport
WHERE StatusID = 1
AND BatchID = @BatchIDEND
-- Rename the file
set @CMDSQL = 'rename G:\CustomerETL\Employee\PendingETL\' + @ETLFilename + ' ETLEmployeexls.xls'
exec master..xp_cmdshell @CMDSQL--PRINT cast(@cmdsql as varchar(4000))
-- Ciode below generates our select. Need to add an INTO clause and create a staging table for each import. Prior to this step we need to rename the file.
SET @sql = ''''
SET @sql = @sql + 'SELECT * into ' + coalesce(@ClientName, 'TestClient') + ' FROM OPENROWSET('SET @sql = @sql + '''' + 'Microsoft.ACE.OLEDB.12.0' + '''' --+ ', '
-- Excel 12.0;HDR=NO;Database=g:\UnZip\ImportSampleXLSX.xlsx;' + ''SET @sql = @sql + '''' + '''Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;''' + '''' + ', ' + @Sheetname + ')'
PRINT cast(@SQL as varchar(8000))
EXEC sp_executesql @sql
set @CMDSQL = 'rename G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls ' + @ETLFilename
exec master..xp_cmdshell @CMDSQLUPDATE FilesToImport
SET StatusID = 2
WHERE FileID = @FileID/* -- TRUNCATE TABLE FilesToImport
UPDATE FilesToImport
SET StatusID = 1
*/SET @Count = (@Count - 1)
CONTINUE
END
If I copy / past from my message window it does run. It does not execute as sp_execute sql. I get a syntax error. Something with the ticks.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 21, 2017 at 6:42 am
Jeffery Williams - Thursday, December 21, 2017 6:30 AMIf I copy / past from my message window it does run. It does not execute as sp_execute sql. I get a syntax error. Something with the ticks.
Yeah...it's the ticks - too many in a few places. Not the cleanest but if I take out some of the ticks from your code, it worked for me. For the part where you generate the openrowset command, try something like: -- Ciode below generates our select. Need to add an INTO clause and create a staging table for each import. Prior to this step we need to rename the file.
SET @sql = ''
SET @sql = @sql + 'SELECT * into ' + coalesce(@ClientName, 'TestClient') + ' FROM OPENROWSET('
SET @sql = @sql + 'Microsoft.ACE.OLEDB.12.0''' --+ ', '
-- Excel 12.0;HDR=NO;Database=g:\UnZip\ImportSampleXLSX.xlsx;' + ''
SET @sql = @sql + '' + '''Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;''' + ', ' + @Sheetname + ')'
print @sql
EXEC sp_executesql @sql
Sue
December 21, 2017 at 6:47 am
Sue_H - Thursday, December 21, 2017 6:42 AMJeffery Williams - Thursday, December 21, 2017 6:30 AMIf I copy / past from my message window it does run. It does not execute as sp_execute sql. I get a syntax error. Something with the ticks.Yeah...it's the ticks - too many in a few places. Not the cleanest but if I take out some of the ticks from your code, it worked for me. For the part where you generate the openrowset command, try something like:
-- Ciode below generates our select. Need to add an INTO clause and create a staging table for each import. Prior to this step we need to rename the file.SET @sql = ''
SET @sql = @sql + 'SELECT * into ' + coalesce(@ClientName, 'TestClient') + ' FROM OPENROWSET('SET @sql = @sql + 'Microsoft.ACE.OLEDB.12.0''' --+ ', '
-- Excel 12.0;HDR=NO;Database=g:\UnZip\ImportSampleXLSX.xlsx;' + ''SET @sql = @sql + '' + '''Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;''' + ', ' + @Sheetname + ')'
print @sql
EXEC sp_executesql @sql
Sue
This is strange. I pasted your code of my same code block.
The copy in messages will execute just like mine would but running through the stored procedure I get the same exact syntax error.
(3 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
December 21, 2017 at 6:48 am
Sue_H - Thursday, December 21, 2017 6:42 AMJeffery Williams - Thursday, December 21, 2017 6:30 AMIf I copy / past from my message window it does run. It does not execute as sp_execute sql. I get a syntax error. Something with the ticks.Yeah...it's the ticks - too many in a few places. Not the cleanest but if I take out some of the ticks from your code, it worked for me. For the part where you generate the openrowset command, try something like:
-- Ciode below generates our select. Need to add an INTO clause and create a staging table for each import. Prior to this step we need to rename the file.SET @sql = ''
SET @sql = @sql + 'SELECT * into ' + coalesce(@ClientName, 'TestClient') + ' FROM OPENROWSET('SET @sql = @sql + 'Microsoft.ACE.OLEDB.12.0''' --+ ', '
-- Excel 12.0;HDR=NO;Database=g:\UnZip\ImportSampleXLSX.xlsx;' + ''SET @sql = @sql + '' + '''Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;''' + ', ' + @Sheetname + ')'
print @sql
EXEC sp_executesql @sql
Sue
And yeah code not that clean, this is for sure :).. It was on a single line concatenated however when I was having problems AI started breaking it out into logical chunks to better visualize what was going on
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 21, 2017 at 8:06 am
Jeffery Williams - Thursday, December 21, 2017 6:47 AMSue_H - Thursday, December 21, 2017 6:42 AMJeffery Williams - Thursday, December 21, 2017 6:30 AMIf I copy / past from my message window it does run. It does not execute as sp_execute sql. I get a syntax error. Something with the ticks.Yeah...it's the ticks - too many in a few places. Not the cleanest but if I take out some of the ticks from your code, it worked for me. For the part where you generate the openrowset command, try something like:
-- Ciode below generates our select. Need to add an INTO clause and create a staging table for each import. Prior to this step we need to rename the file.SET @sql = ''
SET @sql = @sql + 'SELECT * into ' + coalesce(@ClientName, 'TestClient') + ' FROM OPENROWSET('SET @sql = @sql + 'Microsoft.ACE.OLEDB.12.0''' --+ ', '
-- Excel 12.0;HDR=NO;Database=g:\UnZip\ImportSampleXLSX.xlsx;' + ''SET @sql = @sql + '' + '''Excel 12.0;HDR=YES;Database=G:\CustomerETL\Employee\PendingETL\ETLEmployeexls.xls;''' + ', ' + @Sheetname + ')'
print @sql
EXEC sp_executesql @sql
Sue
This is strange. I pasted your code of my same code block.
The copy in messages will execute just like mine would but running through the stored procedure I get the same exact syntax error.
(3 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('.
Just FYI. Here is the block I started with before I broke it out and started messing with it. The net result is exactly the same between this and the mess above 🙂
BEGIN
Set @sql='SELECT * INTO ' + @ETLFilename + '
FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;HDR=YES;Database=' + @Route + ''',
''SELECT * FROM [Sheet1$]'')'
END
Print @sql
--Exec(@sql)
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 21, 2017 at 8:32 am
Jeffery Williams - Thursday, December 21, 2017 8:06 AMJeffery Williams - Thursday, December 21, 2017 6:47 AMThis is strange. I pasted your code of my same code block.The copy in messages will execute just like mine would but running through the stored procedure I get the same exact syntax error.
(3 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('.Just FYI. Here is the block I started with before I broke it out and started messing with it. The net result is exactly the same between this and the mess above 🙂
BEGIN
Set @sql='SELECT * INTO ' + @ETLFilename + '
FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;HDR=YES;Database=' + @Route + ''',
''SELECT * FROM [Sheet1$]'')'END
Print @sql
--Exec(@sql)
On one of your earlier ones, the issue was started by how you initialized the @sql variable so you need to look at that. You would not have made the change I had made on the previous one to get the error:
Incorrect syntax near 'SELECT * into TestClient FROM OPENROWSET('.
You had initialized @sql to '''' and I had changed it to ''
The two are fairly different but I could get that one to work as well by changing the ticks to: Set @sql='SELECT * INTO ' + @ETLFilename + '
FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;HDR=YES;Database=' + @Route + ''',
[Sheet1$]);'
Sue
December 21, 2017 at 8:42 am
Sue_H - Thursday, December 21, 2017 8:32 AMJeffery Williams - Thursday, December 21, 2017 8:06 AMJeffery Williams - Thursday, December 21, 2017 6:47 AMThis is strange. I pasted your code of my same code block.The copy in messages will execute just like mine would but running through the stored procedure I get the same exact syntax error.
(3 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('.Just FYI. Here is the block I started with before I broke it out and started messing with it. The net result is exactly the same between this and the mess above 🙂
BEGIN
Set @sql='SELECT * INTO ' + @ETLFilename + '
FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;HDR=YES;Database=' + @Route + ''',
''SELECT * FROM [Sheet1$]'')'END
Print @sql
--Exec(@sql)On one of your earlier ones, the issue was started by how you initialized the @sql variable so you need to look at that. You would not have made the change I had made on the previous one to get the error:
Incorrect syntax near 'SELECT * into TestClient FROM OPENROWSET('.
You had initialized @sql to '''' and I had changed it to ''The two are fairly different but I could get that one to work as well by changing the ticks to:
Set @sql='SELECT * INTO ' + @ETLFilename + '
FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;HDR=YES;Database=' + @Route + ''',
[Sheet1$]);'Sue
Thank you Sue for offering your assistance. Now I pasted what you gave me here and same error. So the only thing I could think is the reference you are making to how I initialize the variable @sql? I do not see a problem here? What are you referring to?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 21, 2017 at 8:46 am
Jeffery Williams - Thursday, December 21, 2017 8:42 AMSue_H - Thursday, December 21, 2017 8:32 AMJeffery Williams - Thursday, December 21, 2017 8:06 AMJeffery Williams - Thursday, December 21, 2017 6:47 AMThis is strange. I pasted your code of my same code block.The copy in messages will execute just like mine would but running through the stored procedure I get the same exact syntax error.
(3 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('.Just FYI. Here is the block I started with before I broke it out and started messing with it. The net result is exactly the same between this and the mess above 🙂
BEGIN
Set @sql='SELECT * INTO ' + @ETLFilename + '
FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;HDR=YES;Database=' + @Route + ''',
''SELECT * FROM [Sheet1$]'')'END
Print @sql
--Exec(@sql)On one of your earlier ones, the issue was started by how you initialized the @sql variable so you need to look at that. You would not have made the change I had made on the previous one to get the error:
Incorrect syntax near 'SELECT * into TestClient FROM OPENROWSET('.
You had initialized @sql to '''' and I had changed it to ''The two are fairly different but I could get that one to work as well by changing the ticks to:
Set @sql='SELECT * INTO ' + @ETLFilename + '
FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;HDR=YES;Database=' + @Route + ''',
[Sheet1$]);'Sue
Thank you Sue for offering your assistance. Now I pasted what you gave me here and same error. So the only thing I could think is the reference you are making to how I initialize the variable @sql? I do not see a problem here? What are you referring to?
Change it to what I changed it to in my earlier post - SET SQL = ' '
You keep using SET SQL = ' ' ' '
Sue
December 21, 2017 at 8:49 am
Jeffery Williams - Thursday, December 21, 2017 8:42 AMSue_H - Thursday, December 21, 2017 8:32 AMJeffery Williams - Thursday, December 21, 2017 8:06 AMJeffery Williams - Thursday, December 21, 2017 6:47 AMThis is strange. I pasted your code of my same code block.The copy in messages will execute just like mine would but running through the stored procedure I get the same exact syntax error.
(3 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('.Just FYI. Here is the block I started with before I broke it out and started messing with it. The net result is exactly the same between this and the mess above 🙂
BEGIN
Set @sql='SELECT * INTO ' + @ETLFilename + '
FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;HDR=YES;Database=' + @Route + ''',
''SELECT * FROM [Sheet1$]'')'END
Print @sql
--Exec(@sql)On one of your earlier ones, the issue was started by how you initialized the @sql variable so you need to look at that. You would not have made the change I had made on the previous one to get the error:
Incorrect syntax near 'SELECT * into TestClient FROM OPENROWSET('.
You had initialized @sql to '''' and I had changed it to ''The two are fairly different but I could get that one to work as well by changing the ticks to:
Set @sql='SELECT * INTO ' + @ETLFilename + '
FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;HDR=YES;Database=' + @Route + ''',
[Sheet1$]);'Sue
Thank you Sue for offering your assistance. Now I pasted what you gave me here and same error. So the only thing I could think is the reference you are making to how I initialize the variable @sql? I do not see a problem here? What are you referring to?
Disregard. I missed where you specified the quotes, sorry.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply