Run SQL script with SQLCMD

Question

Run SQL script with SQLCMD

Edvard Korsbæk

SSCertifiable

Points: 5994
More actions
August 17, 2010 at 3:49 am

#226168

I have tried to use the SQLCMD from a DOS window.
Used this command line:
SQLCMD -U SA -P [Strong password] -S EDVARDDELL-PC\EASYPLAN
When I click 'Enter', I get a:
1>
Telling me, that I have connection.
Next line is
-i C:\CreateEasyplan.sql
When I click 'Enter' I get
>2
Telling me, that there is no syntax errors.
As the name suggests, It should create a DB.
If open the script in the manager, it first wants me to give the user/password, and after that when I click 'Execute', the DB is created.
What Am I doing wrong?
Edvard Korsbæk

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 1

edvard 19773 (8/17/2010)
I have tried to use the SQLCMD from a DOS window.
Used this command line:
SQLCMD -U SA -P [Strong password] -S EDVARDDELL-PC\EASYPLAN
When I click 'Enter', I get a:
1>
Telling me, that I have connection.
Next line is
-i C:\CreateEasyplan.sql
When I click 'Enter' I get
>2
Telling me, that there is no syntax errors.
As the name suggests, It should create a DB.
If open the script in the manager, it first wants me to give the user/password, and after that when I click 'Execute', the DB is created.
What Am I doing wrong?
Edvard Korsbæk

First thing doesn't matter how strong your password is for SA, if you are passing that in with your SQLCMD it is still viewable b/c it is in plain text. Instead use a trusted connection.

Second, to load a script and execute it with SQL Server use the "-i" parameter when you start SQLCMD. And not after SQLCMD has started.

Reference SQLCMD manual at http://msdn.microsoft.com/en-us/library/ms162773.aspx.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

you will also need to issue a GO statement to signal the end of the batch and execute the statements 😎

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Edvard Korsbæk SSCertifiable Points: 5994 More actions · Answer 3

so:

SQLCMD -U SA -P [Strong password] -S EDVARDDELL-PC\EASYPLAN -i c:\CreateEasyplan.sql

Should do the trick, if the last line in C:\CreateEasyplan.sql is GO

I will give it a try tomorrow - Need to clean up my PC first.

Thanks!

Edvard Korsbæk

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

no, if enetering T-SQL statements into the SQLCMD console you need to signal the end of the batch using the GO statement.

The attached screenshot shows this!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 5

As well thought out as many of the answers are, I believe none of them will actually work. The reason is because of the backslash in the server instance name. I believe you'll need to put the server instance name in double quotes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 6

Jeff actually that does work without double quotes (just did it today :P); no issues :D. But I usually put -SServerName\InstanceName and avoid using "-" in my server names heh.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

The backslash is not an issue, open a sqlcmd window and try it.

My screenshot shows, you can enter a statement on line 1 and you push return expecting the statement to execute but it jumps to line 2. Push return as many times as you like, until you type GO SQL server will not execute the batch!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 8

Mohit K. Gupta (8/17/2010)
Jeff actually that does work without double quotes (just did it today :P); no issues :D. But I usually put -SServerName\InstanceName and avoid using "-" in my server names heh.

Thanks for the correction, Mohit. Maybe it was a "dash" that I was thinking of. I don't remember anymore because, like you, I encapsulate things like server names and disk paths in double quotes just so I never have to worry about it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 9

Maybe i am coming in on this a bit late but this thread which i worked on gives you a good example:

Adam Zacks-------------------------------------------Be Nice, Or Leave