Is there a way to get a script file that will be executing via SQLCMD to accept the parms passed in the SQLCMD call..
ie..
SQLCMD (assume the connection is OK..) -V "1/25/2012" -i "ScriptFile.sql"
What would the structure of ScriptFile.sql Look like? Would it start with the same as any other procedure..?
Basically I'm trying to call a text file script with parameters as an SQL command. So far I haven't been able to find any MSDN articles on this one.
OK.. If anyone cares, it Goes Like This...
The SQL Script file which is using the variable uses the variable like so:
Update TableName Set ColumnValue=$(Param)
--Notice there's no declare, and the format of the parameter uses
$(Param) instead of @Param as a variable...
The Command line looks almost the same. For some reason, the passed value needs to be in single quotes within double quotes, like so:
SQLCMD -S ServerName -d DatabaseName -v Param="'Param'" -i"ScriptFile.sql"
Use just double quotes around the ScriptFile name, Single within Double for the Parameters.
Set ColumnValue='$(Param)' in your script and then pass the value without the single quotes?
USE:
sqlcmd -E -S <Your Server> -v test="Test String" -i file.sql
file.sql:
SELECT @test
According to http://msdn.microsoft.com/en-us/library/ms188714.aspx you can use -v to set a scripting variable. This worked for me, and the other solutions here didnt;
prune.sql: delete from Table where Column=$(Param)
and in dos cmd or in powershell: sqlcmd -S "My Server" -v Param=100 -i prune.sql
Quotation marks seem only to be needed when the value being set has spaces. Hope this helps.
