x

SQLCMD Error when insert string value with special character

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?
more ▼

asked Jul 27, 2012 at 08:58 AM in Default

paps gravatar image

paps
20 4 4 4

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.
Jul 27, 2012 at 12:58 PM Kev Riley ♦♦
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')
Jul 27, 2012 at 01:06 PM paps
and how are you setting the $(Account) variable?
Jul 27, 2012 at 01:10 PM Kev Riley ♦♦
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.
Jul 27, 2012 at 01:15 PM paps
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

sqlcmd -I -E -S %DBServerInstance% -i "01_DBInsert.sql" 2>nul

Alternatively change the sql script to have explicit concatenation

 INSERT MessageProcessing ([StateValue]) VALUES ('Value:$COUNT$'+'(Account)_Control')
more ▼

answered Jul 27, 2012 at 01:33 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

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, 2012 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, 2012 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, 2012 at 02:15 PM paps

In a query window in SSMS does

INSERT MessageProcessing ([StateValue]) VALUES ('Value:$COUNT$(Account)_Control')
work ok?
Jul 27, 2012 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, 2012 at 02:40 PM paps
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Jul 31, 2012 at 09:40 AM

paps gravatar image

paps
20 4 4 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x9

asked: Jul 27, 2012 at 08:58 AM

Seen: 1665 times

Last Updated: Jul 31, 2012 at 09:40 AM