Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vitoco
Starting Member
22 Posts |
 Posted - 2008-02-19 : 17:38:38
|
I need the rowcount of each statement processed by SQLCMD command line tool called from a batch script using -i and -o options, but it seems that it shows the "(XXX rows affected)" message whenever it wants  For example, the following code:quote:
insert into MyTrace select * from ::fn_trace_gettable('C:\trace.trc',1)
returns nothing, but if I change this to:quote:
select 'Loading...'insert into MyTrace select * from ::fn_trace_gettable('C:\trace.trc',1)
SQLCMD returns:quote:
-----------Loading...(70832 rows affected)
Please note that there is no rowcount for the "Loading..." message, which will apperar if the select is the only statement in the SQL file.What's going on?Thanks... ++Vitoco |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-20 : 03:57:14
|
start each "batch" withset nocount on
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
vitoco
Starting Member
22 Posts |
Posted - 2008-02-20 : 07:44:42
|
| Eeeerh... I need the row count for every SQL statement in the input file as feedback. That will be used by my batch process.++V |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-20 : 07:59:52
|
And end each "batch" with "RETURN @@ROWCOUNT" ?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
vitoco
Starting Member
22 Posts |
Posted - 2008-02-20 : 10:10:03
|
That returns an error message that says "A RETURN statement with a return value cannot be used in this context."...My "batch" process generates a SQL file on-the-fly and calls SQLCMD passing it this file (with -i option) to run against a SQL server, and then parses the resulting log (specified by -o option) to get feedback, which sometimes is missing.I found the following remark for SQLCMD utility at TechNet:quote:
[url]http://technet.microsoft.com/en-us/library/ms162773.aspx[/url]:
When multiple results are returned, sqlcmd prints a blank line between each result set in a batch. In addition, the "<x> rows affected" message does not appear when it does not apply to the statement executed.
 I wish I know when id does not apply, but my little experiment concludes that I found a bug!!!++V |
|
|
|
pbannist
Starting Member
3 Posts |
Posted - 2011-03-08 : 12:18:26
|
| use SELECT @@ROWCOUNT rather than RETURN @@ROWCOUNT |
|
|
|
|
|
|
XXX rows affected