By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

SQL 2005 Management Studio Bugs

P: n/a
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?

Apr 6 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
(ji**********@countrywide.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 6 '06 #2

P: n/a
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

Apr 6 '06 #3

P: n/a
ji**********@countrywide.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/m...S,SQL.90).aspx
--

Apr 6 '06 #4

P: n/a
(ji**********@countrywide.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 6 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.