|
Hi, I am using sqlcmd commandline utility to deploy my objects and seed data to site. Well, its runnign perfectly in normal cases. I have gone through a critical condition now. one of the insert script contains a value like the one given below 'Value:$COUNT$(Account)_Control' This fails my insert statement saying, 'Account' scripting variable not defined. I understand, as sqlcmd takes $(Account) as a variable it is not allowing to insert the above string. I tried many options, like replace and searched for escape character but nothing worked out. Can anybody help me in this regard?
(comments are locked)
|
|
Oh I understand now. You don't want it to be seen as a variable The insert should be working OK, it's just that you get an error message as output. You can silence the output with Alternatively change the sql script to have explicit concatenation No Kev, The value is not getting inserted. I checked by silencing the output as u suggested. And what do you mean by change the sql script to have explicit concatenation? Can you give an example, better with my string itself??
Jul 27 '12 at 01:46 PM
paps
Hmm worked for me? As for the concatenation, I gave you an example in the answer - I break the value up at the dollar sign and concatenate them back together so that sqlcmd doesn't think it is a variable
Jul 27 '12 at 01:55 PM
Kev Riley ♦♦
Okay..Thank you Kev.. The answer was some how not fully visible to me.. By the way, The Splitting I have tried previously, But was thinking it would be more helpful if i get any other option like any escape character or some thing else which didnt come in my mind. Please do post if you come across any other ideas. Thanks for the help.
Jul 27 '12 at 02:15 PM
paps
In a query window in SSMS does work ok?
Jul 27 '12 at 02:36 PM
Kev Riley ♦♦
Yes it will work But if you select SSMS-->Query-->SQLCMDMode and again run the same script, it will fail.
Jul 27 '12 at 02:40 PM
paps
(comments are locked)
|
|
Kev, Can u pls suggest any idea on my below post? http://ask.sqlservercentral.com/questions/92047/sqlcmd-error-when-saving-xml-data-from-cmd-file.html
(comments are locked)
|
Can you add more detail to the question: what exact command line are you using to invoke sqlcmd with variables, and can you post the snippet of script that is erroring.
sqlcmd -I -E -S %DBServerInstance% -i "01_DBInsert.sql" This is the command line i am using to invoke sqlcmd And the only command inside 01_DBInsert.sql is an insert statement given below INSERT MessageProcessing ([StateValue]) VALUES ('Value:$COUNT$(Account)_Control')
and how are you setting the $(Account) variable?
That is not a variable. It is just a string which is stored in one of my setting table. I want to Send the that exact value to client site. But SQLCMD treats it as a variable. I want to overcome that hurdle. That is my requirement.