April 8, 2015 at 7:44 am
Hi
When I use the following code in a sql agent job I get the error for quotations but I don't have any unclosed quotes. What am I missing?
Message
Executed as user: NT Service\SQLSERVERAGENT. Msg 105, Level 15, State 1, Server MyServer, Line 1 Unclosed quotation mark after the character string 'EXECUTE [dbo].[IndexOptimize] @databases = 'MyDatabase', '. Process Exit Code 0. The step succeeded
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d msdb -Q "EXECUTE dbo.IndexOptimize @databases = 'MyDatabase',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = 'Y',
@MaxDOP = 8,
@LogToTable = 'Y'" -b
April 8, 2015 at 7:58 am
Are the parameters really defined over multiple lines in your job step? If yes, it should all be in one line.
April 8, 2015 at 8:16 am
Yes they are on multiple lines. I will put it on one line and give it a try, thanks.
April 8, 2015 at 8:54 am
Pretty sure it's because sqlcmd expects it all to be on a single line. Since you're calling this through Agent, couldn't you skip using sqlcmd? Just asking.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2015 at 9:05 am
OP's job calls a procedure part of Ola Hallengren's SQL Server maintenance plan.
Using sqlcmd.exe is recommanded for proper error handling (From https://ola.hallengren.com/frequently-asked-questions.html):
Do the stored procedures need to be executed in a CmdExec job step with sqlcmd? Or can I use a T-SQL job step?
The stored procedures must be executed in a CmdExec job step with sqlcmd and the -b option, if error handling and logging are to work as designed. The problem with the T-SQL job step is that it stops executing after the first error.
April 8, 2015 at 9:09 am
Eric Prévost (4/8/2015)
OP's job calls a procedure part of Ola Hallengren's SQL Server maintenance plan.Using sqlcmd.exe is recommanded for proper error handling (From https://ola.hallengren.com/frequently-asked-questions.html):
Do the stored procedures need to be executed in a CmdExec job step with sqlcmd? Or can I use a T-SQL job step?
The stored procedures must be executed in a CmdExec job step with sqlcmd and the -b option, if error handling and logging are to work as designed. The problem with the T-SQL job step is that it stops executing after the first error.
Ah, cool. Good to know.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2015 at 1:18 pm
Yes it is Ola's
April 8, 2015 at 1:42 pm
A CmdExec job step uses the command interpreter, just like a Command Prompt window or a .bat file. It doesn't do multi-line string literals.
It should work if it's all one line. Another option would be to put the T-SQL commands into a separate .sql file and reference it as the input file in the SQLCMD command line. Or put the whole command into a stored procedure, then the SQLCMD line only needs a simple EXEC command with no parameters.
April 9, 2015 at 9:30 am
Thanks everyone! Putting the command on one line worked but now I have another problem.
When the job runs it fails with the error: 'Procedure IndexOptimize, Line 505 The value for the parameter @MaxDOP is not supported' and according to Ola's code there are a few conditions which raise the error and my instance doesn't meet any of the conditions. Any suggestions?
IF @MaxDOP < 0 OR @MaxDOP > 64 OR @MaxDOP > (SELECT cpu_count FROM sys.dm_os_sys_info) OR (@MaxDOP > 1 AND SERVERPROPERTY('EngineEdition') NOT IN (3,5))
BEGIN
SET @ErrorMessage = 'The value for the parameter @MaxDOP is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
My instance:
maxdop set to 0, SELECT cpu_count FROM sys.dm_os_sys_info = 16 and EngineEdition = 2
April 9, 2015 at 9:49 am
If the string is within SSMS, you could try adding a backslash to the end of each line to continue the string. For example:
declare @varchar varchar(30)
set @varchar = 'abcdef'
print @varchar
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2015 at 11:22 am
jdbrown239 (4/9/2015)
Thanks everyone! Putting the command on one line worked but now I have another problem.When the job runs it fails with the error: 'Procedure IndexOptimize, Line 505 The value for the parameter @MaxDOP is not supported' and according to Ola's code there are a few conditions which raise the error and my instance doesn't meet any of the conditions. Any suggestions?
IF @MaxDOP < 0 OR @MaxDOP > 64 OR @MaxDOP > (SELECT cpu_count FROM sys.dm_os_sys_info) OR (@MaxDOP > 1 AND SERVERPROPERTY('EngineEdition') NOT IN (3,5))BEGIN
SET @ErrorMessage = 'The value for the parameter @MaxDOP is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
My instance:
maxdop set to 0, SELECT cpu_count FROM sys.dm_os_sys_info = 16 and EngineEdition = 2
Hi, based on EngineEdition value you provided, your SQL instance is Standard edition. Parallel index operations are available only in evaluation, developer and enterprise edition. So to run successfully IndexOptimize procedure, just dont provide the @MaxDOP parameter.
April 9, 2015 at 11:56 am
Eric Prévost (4/9/2015)
jdbrown239 (4/9/2015)
Thanks everyone! Putting the command on one line worked but now I have another problem.When the job runs it fails with the error: 'Procedure IndexOptimize, Line 505 The value for the parameter @MaxDOP is not supported' and according to Ola's code there are a few conditions which raise the error and my instance doesn't meet any of the conditions. Any suggestions?
IF @MaxDOP < 0 OR @MaxDOP > 64 OR @MaxDOP > (SELECT cpu_count FROM sys.dm_os_sys_info) OR (@MaxDOP > 1 AND SERVERPROPERTY('EngineEdition') NOT IN (3,5))BEGIN
SET @ErrorMessage = 'The value for the parameter @MaxDOP is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
My instance:
maxdop set to 0, SELECT cpu_count FROM sys.dm_os_sys_info = 16 and EngineEdition = 2
Hi, based on EngineEdition value you provided, your SQL instance is Standard edition. Parallel index operations are available only in evaluation, developer and enterprise edition. So to run successfully IndexOptimize procedure, just dont provide the @MaxDOP parameter.
I believe you can just comment out that check. If you provide too high a value to MAXDOP, I think SQL will just ignore it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2015 at 2:53 pm
Hi, based on EngineEdition value you provided, your SQL instance is Standard edition. Parallel index operations are available only in evaluation, developer and enterprise edition. So to run successfully IndexOptimize procedure, just dont provide the @MaxDOP parameter.
Thanks I was unaware of that limitation.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply