July 29, 2008 at 1:24 am
I am using SQLCMD utility provided by dearest Microsoft 😉 to execute sql scripts from command shell. I have written a VB Script which finds all the files in given folder and execute all the files (sql scripts).
The problem is, some of the scripts contain mulitple sql statements which is a common thing. So when a script is executed, it doesn't return messages (number of rows effected) for all the sql statements given in particular script.
in order to demonstrate my problem, sample script contains simple few sql statements given below and script name is test.txt
create table test1
(col1 int)
go
insert into test1
select 1
go
insert into test1
select 2
go
insert into test1
select 3
go
insert into test1
select 4
insert into test1
select 5
go
delete from test1
insert into test1
select 4
insert into test1
select 5
go
drop table test1
when i execute this script from command shell using sqlcmd, i get the following output.
C:\temp>sqlcmd -E -s sqlcon -d testanam -r 1 -m-1 -i test.txt
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(5 rows affected)
C:\>
But when i run the same script in Management Studio, i get following correct and desired output
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(5 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
My questions:
1. Has anyone faced similar problem before? If yes, what was the solution?
2. Why does sqlcmd behave differently from Management Studion running same code?
3. Is it some bug in sqlcmd utility?
4. Any other way you recommend?
Note: I haven't tested osql utility as it is deprecated now.
July 29, 2008 at 2:14 am
You can get around this by adding go statements between the inserts. Also have a look at the remarks section of http://technet.microsoft.com/en-us/library/ms162773.aspx
What you experience may have to do something with:
... In addition, the " rows affected" message does not appear when it does not apply to the statement executed. ...
Regards,
Andras
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply