Row(s) Affected different in sqlcmd to UI?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gerry Hickman - 30 May 2008 18:05 GMT

Hi,

When I run my T-SQL query in SQL 2005 mamagement studio, I get four output
messages (as expected)

10,000 rows affected
10,000 rows affected
10,000 rows affected
10,000 rows affected

but when I run the same Query in sqlcmd, I only get

10,000 rows affected

All other messages are identical and all operations are carried out in both
cases.

Signature

Gerry Hickman
London (UK)

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 30 May 2008 18:23 GMT

Yes I think sqlcmd will only echo the last line of output from the row(s)
affected message. A workaround would be to set nocount on at the beginning
of the .sql file, and then print your own messages after each statement,
e.g.

PRINT RTRIM(@@ROWCOUNT + ' row(s) affected.';

Yes, this is more work. But the nice thing about this is, if you want to,
you can customize each of these messages instead of trying to count them in
the output to figure out which one is which.

On 5/30/08 1:05 PM, in article uWv4QdnwIHA.576@TK2MSFTNGP05.phx.gbl, "Gerry
Hickman" <gerry666uk@newsgroup.nospam> wrote:

> Hi,
>
[quoted text clipped - 12 lines]
> All other messages are identical and all operations are carried out in both
> cases.

Reply to this Message

Gerry Hickman - 30 May 2008 20:39 GMT

> Yes I think sqlcmd will only echo the last line of output from the row(s)
> affected message.

Hmmmmm, OK so it's a known issue, but pretty strange...

> A workaround would be to set nocount on at the beginning
> of the .sql file, and then print your own messages after each statement,
> e.g.
>
> PRINT RTRIM(@@ROWCOUNT + ' row(s) affected.';

OK, thanks.

Signature

Gerry Hickman (London UK)

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 30 May 2008 19:08 GMT

FYI, see
http://connect.microsoft.com/sql/ViewFeedback.aspx?FeedbackID=309251

On 5/30/08 1:05 PM, in article uWv4QdnwIHA.576@TK2MSFTNGP05.phx.gbl, "Gerry
Hickman" <gerry666uk@newsgroup.nospam> wrote:

> Hi,
>
[quoted text clipped - 12 lines]
> All other messages are identical and all operations are carried out in both
> cases.

Reply to this Message

Gerry Hickman - 30 May 2008 20:37 GMT

Hi Aaron,

> FYI, see
> http://connect.microsoft.com/sql/ViewFeedback.aspx?FeedbackID=309251

It says "The system has encountered an unexpected error."

Signature

Gerry Hickman (London UK)

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 30 May 2008 20:41 GMT

Sorry should be
connect.microsoft.com/sql/Feedback/ViewFeedback.aspx?FeedbackID=309251

On 5/30/08 3:37 PM, in article uyZMqyowIHA.4376@TK2MSFTNGP06.phx.gbl, "Gerry
Hickman" <gerry666uk@newsgroup.nospam> wrote:

> Hi Aaron,
>
>> FYI, see
>> http://connect.microsoft.com/sql/ViewFeedback.aspx?FeedbackID=309251
>
> It says "The system has encountered an unexpected error."

Reply to this Message

Gerry Hickman - 30 May 2008 21:29 GMT

> Sorry should be
> connect.microsoft.com/sql/Feedback/ViewFeedback.aspx?FeedbackID=309251

Right, so it's a confirmed bug and is "too complicated" to fix for
SQL2008, brilliant!

Signature

Gerry Hickman (London UK)

Reply to this Message