SQL 2005 Management Studio Bugs

There are four limitations or apparent bugs I have experienced in 2005
MS.  Two I know are for real.

Two real limitations/bugs --

1.  Can't edit table-valued function.
2.  Can't get nulls to be missing (blank) in text output pane

Two more I just encountered, and am wondering about --

3.  DB->All Tasks->Import Data->Load CSV file (exported from Excel) ->
garbled table as some rows end up being extra columns.  The same
spreadsheets load okay from XLS format.

Table should have shape A, instead has shape B:

A     B
|...|  |...|
|...|  |...|
|...|  |......|
|...|  |......|
|...|  |...|
|...|
|...|

4.  SET NOCOUNT OFF has no effect after SET NOCOUNT ON

Is there a way to work around these, or are they real bugs?

(jim_geissman@countr
ywide.com)  writes:
> Two real limitations/bugs --
>
> 1.  Can't edit table-valued function.

If it's on SQL 2000. Actually, Mgmt Studio is partly innocent. There is a
bug in SQL 2000, so that objectproperty incorrectly reports status for
QuotedIdentifier and AnsiNulls for multi-statement functions.

> 2.  Can't get nulls to be missing (blank) in text output pane

Personally, I would not call that neither bug nor limitation. Just
different.


> 3.  DB->All Tasks->Import Data->Load CSV file (exported from Excel) ->
> garbled table as some rows end up being extra columns.  The same
> spreadsheets load okay from XLS format.

I've stayed away from the Import/Export wizard, so I cannot comment on
that one.

> 4.  SET NOCOUNT OFF has no effect after SET NOCOUNT ON

Do you have an example?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Re SET NOCOUNT

My experience yesterday:

a)  default state is row count echo, e.g.  "(1257343 row(s) affected)"
b)  set nocount on stops those -- e.g., during a script that does a lot
of inserts
c)  while nocount is on, the echo is:  "Command(s) completed
successfully."  Once for each block of commands rather than
individually for each insert/update/etc query
d)  set nocount off should cause row counts to reappear -- a way to
confirm queries are working
e)  "Command(s) completed successfully." persisted after set nocount
off -- have to use other means to confirm what happened

jim_geissman@country
wide.com  wrote:
> Re SET NOCOUNT
>
> My experience yesterday:
>
> a)  default state is row count echo, e.g.  "(1257343 row(s) affected)"
> b)  set nocount on stops those -- e.g., during a script that does a lot
> of inserts
> c)  while nocount is on, the echo is:  "Command(s) completed
> successfully."  Once for each block of commands rather than
> individually for each insert/update/etc query
> d)  set nocount off should cause row counts to reappear -- a way to
> confirm queries are working
> e)  "Command(s) completed successfully." persisted after set nocount
> off -- have to use other means to confirm what happened

That sounds strange. When I run the following script in SSMS:

SET NOCOUNT ON
SELECT 1
SET NOCOUNT OFF
SELECT 2

GO

I get:


-----------
1


-----------
2

(1 row(s) affected)


Exactly what I expected. I get the same result if I put that code in a
proc. Do you get the same output?

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

(jim_geissman@countr
ywide.com)  writes:
> Re SET NOCOUNT
>
> My experience yesterday:
>
> a)  default state is row count echo, e.g.  "(1257343 row(s) affected)"
> b)  set nocount on stops those -- e.g., during a script that does a lot
> of inserts
> c)  while nocount is on, the echo is:  "Command(s) completed
> successfully."  Once for each block of commands rather than
> individually for each insert/update/etc query

So you mean that if you had:

insert #h (a) values (12)
go
insert #h (a) values (12)
go

You got "Command(s) completed successfully." twice? I have never seen that.

> d)  set nocount off should cause row counts to reappear -- a way to
> confirm queries are working
> e)  "Command(s) completed successfully." persisted after set nocount
> off -- have to use other means to confirm what happened

Maybe there is some way to get Mgmt Studio to lose control, so you
could this behaviour, but I think you need to find a reproducible
scenario, for a bug report to be meaningful.

Those (1257343 row(s) affected), were they just instered, or were they
from a SELECT that was sent to Mgmt Studio?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx