Scripting variable not defined error during batch job

23 January,2019 by Jack Vamvas

Question: I'm trying to execute a batch script which includes a SQLCMD

 

SQLCMD -E -d master -S %1 -i "SQL_AGENT_JOBS_INSTALL.sql"

The sql script includes a reference to a SQL AGENT token SQLLOGDIR. 

When I run the same script from SSMS it all work OK, but executing it through the batch\ SQLCMD route throws this error. 

How can I fix this issue?

Answer:   To pass a variable into SQLCMD use the -v switch. 

the format is : -v var = "value"

Extending the example used above : 

SQLCMD -E -d master -S %1 -v SQLLOGDIR="" JOBNAME="" STEPID="" STRTDT="" STRTTM="" -i "SQL_AGENT_JOBS_INSTALL.sql"
PAUSE

 

This is a workaround - and the problem suggests the sql script is attempting to capture values from within the SQL Server environment - but as this is running as a batch job the values are set prior to the interaction with SQL Server.

 

For more SQLCMD related reading 

How to capture errors from SQLCMD (SQL Server DBA)

SQLCMD and blank password

SQL Server - SQLCMD with screen output and output file


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Scripting variable not defined error during batch job


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer