Ignoring Scripting variables in sqlcmd (Importing database)

Question

I am having a database import being done form a SQL file and i see my content have some data that is conflicting with sqlcmd import. The following queries are the 2 scenarios i am encountering in my import.

INSERT INTO [users] ([id],[email],[title],[desc]) VALUES (1096,'[email protected]',N'Shami Doc', N'This is profile of Shami who sends $(mid) message');

In this query am getting the following error...

'mid' scripting variable not defined.

Now in the second case...

INSERT INTO [users] ([id],[email],[title],[desc]) VALUES (1091,'[email protected]',N'Raj Doc', N'This is profile of Raj who sends $(m message');

Am getting Syntax Error. In actual i just want both data to be imported to users table with the same data defined... i mean the $(mid) should be imported just as text into that column and also $(m should be imported as same.

I tried using escape character backslash and it did not help. Can someone help me in this ? How can i have both above queries run without loosing that data ?

Dan Guzman · Accepted Answer · 2023-01-24 10:23:56Z

1

Specify the -x SQLCMD command-line option to ignore scripting variables when executing scripts where scripting variables need to be retained as data. For example:

SQLCMD -S YourServer -x -E -I -i YourScript.sql

Note this example also includes the -I option to set QUOTED_IDENTIFIER ON, which is required for filtered indexes, indexed views and other features. SQLCMD initially sets QUOTED_IDENTIFIER OFF off by default for backwards compatibility.

edited Jan 24, 2023 at 10:23

answered Jan 24, 2023 at 3:51

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Ignoring Scripting variables in sqlcmd (Importing database)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related