1

Consider the following command, which I've wrapped for readability:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'DIFF', 
@Verify = 'Y', @CleanupTime = NULL, @CheckSum = 'Y', @LogToTable = 'Y'" -b

It returns the following error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'E'.
Msg 103, Level 15, State 4, Line 3
The identifier that starts with 'EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'USER_DATABASES', @Directory = N'C:\Backup', 
@BackupType = 'DIFF', @Verify = 'Y', @C' is too long. 
Maximum length is 128.

When I remove the sqlcmd it works. I know I'm doing something wrong, however I don't understand what the problem is.

  • You have tagged this question with olla-hallengren - but you failed to mention anything about that in your question. Presumably the command you're using is in a SQL Server Agent job step? Add those details to your question, please. Use the edit functionality. Oh, and take the tour while you're at it! – Max Vernon Mar 19 '18 at 15:47
  • 1
    Incorrect syntax near 'E' indicates that the "Type" for the job step is not "operating system (CmdExec)" but is instead set to "Transact-SQL script (T-SQL)". Change the type to "Operating System (CmdExec)" and give it a try. – MillhouseD Mar 19 '18 at 16:58
1

I just put your command into a SQL Server Agent Jobstep configured to use CmdExec; it ran the command perfectly.

USE [msdb]
GO

/****** Object:  Job [CmdExecTest]    Script Date: 2018-03-19 10:55:07 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2018-03-19 10:55:07 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'CmdExecTest', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'This job provides a way to test SQL Server Agent Jobs', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'<me>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step1]    Script Date: 2018-03-19 10:55:07 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'CmdExec', 
        @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''USER_DATABASES'', @Directory = N''C:\Backup'', @BackupType = ''DIFF'', @Verify = ''Y'', @CleanupTime = NULL, @CheckSum = ''Y'', @LogToTable = ''Y''" -b', 
        @flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

The result was a job failure for me because I don't have the dbo.DatabaseBackup stored procedure:

Msg 2812, Level 16, State 62, Server MyServer, Line 1
Could not find stored procedure 'dbo.DatabaseBackup'.

However, this indicates that sqlcmd was successfully executed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.