23 January,2019 by Tom Collins
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)
SQL Server - SQLCMD with screen output and output file
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |