BugCheck in SQL Server 2000 SP3a?

When running a particular really badly written query on SQL Server 2000 SP3a,
I'm seeing a dump file generated about every 4 minutes. The top of the dump
is:
* BEGIN STACK DUMP:
* 09/19/04 12:40:38 spid 0
*
* Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
Line
0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000190
Anyone from Microsoft interested in the rest of the dump?
Regards,
Jonathan
http://kerblog.com/earlyedition/archive/2004/09/22/176.aspxJonathan,
Whenever a T-SQL query causes a stack dump, it's indicative of a bug. You
can check the SQL Server 2000 hotfixes released by Microsoft since then to
see if your case is covered. If not, you can report this to MS by phoning in
an incident to Microsoft PSS.
Ron
--
Ron Talmage
SQL Server MVP
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:F194838F-B59F-4518-B430-22A4BF9D899C@.microsoft.com...
> When running a particular really badly written query on SQL Server 2000
SP3a,
> I'm seeing a dump file generated about every 4 minutes. The top of the
dump
> is:
> * BEGIN STACK DUMP:
> * 09/19/04 12:40:38 spid 0
> *
> * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> Line
> 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000190
>
> Anyone from Microsoft interested in the rest of the dump?
> Regards,
> Jonathan
> http://kerblog.com/earlyedition/archive/2004/09/22/176.aspx|||Thanks, Ron.
Am I going to be charged for reporting this bug?
Regards,
Jonathan
"Ron Talmage" wrote:
> Jonathan,
> Whenever a T-SQL query causes a stack dump, it's indicative of a bug. You
> can check the SQL Server 2000 hotfixes released by Microsoft since then to
> see if your case is covered. If not, you can report this to MS by phoning in
> an incident to Microsoft PSS.
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:F194838F-B59F-4518-B430-22A4BF9D899C@.microsoft.com...
> > When running a particular really badly written query on SQL Server 2000
> SP3a,
> > I'm seeing a dump file generated about every 4 minutes. The top of the
> dump
> > is:
> >
> > * BEGIN STACK DUMP:
> >
> > * 09/19/04 12:40:38 spid 0
> >
> > *
> >
> > * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> > Line
> > 0+00000000)
> >
> > * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> >
> > * Access Violation occurred reading address 00000190
> >
> >
> > Anyone from Microsoft interested in the rest of the dump?
> >
> > Regards,
> >
> > Jonathan
> >
> > http://kerblog.com/earlyedition/archive/2004/09/22/176.aspx
>
>|||Hi, Jonathan
Thanks for your posting!
From your descriptions, I understood that you wonder whether it will be a
free case if you raise this your dump files to us checking it is an known
issue for us. Correct me if I was wrong.
Based on my scope, if this is confirmed to be an known issue and no hotfix
or workaround is available, it will be definitely a free case. If there is
an hotfix or workaround found to resolved this, I am afraid it will not be
a refund. Moreover, just asking for hotfix from an KB will also be free
case.
FYI, if you want to know more detailed information about this, you can
contact Microsoft Product Support directly to discuss additional support
options you may have available, by contacting us at 1-(800)936-5800 or by
choosing one of the options listed at
http://support.microsoft.com/default.aspx?scid=sz;en-us;top
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Jonathan,
I am sorry to say that checking dumps file is not supported in the
newsgroup and this kind of issues can be caused by various factors, it is
difficult to locate the root cause in a newsgroup thread. If you want to
find the root cause of that, you will have to turn to PSS for help.
Thank you for your patience and understanding.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Mingqing,
Thanks for your reply! You understand my question well. I didn't see any
matching hotfix, but is it possible/advisable for me to post my dump file
here and have it checked?
Thank you,
Jonathan
""Mingqing Cheng [MSFT]"" wrote:
> Hi, Jonathan
> Thanks for your posting!
> From your descriptions, I understood that you wonder whether it will be a
> free case if you raise this your dump files to us checking it is an known
> issue for us. Correct me if I was wrong.
> Based on my scope, if this is confirmed to be an known issue and no hotfix
> or workaround is available, it will be definitely a free case. If there is
> an hotfix or workaround found to resolved this, I am afraid it will not be
> a refund. Moreover, just asking for hotfix from an KB will also be free
> case.
> FYI, if you want to know more detailed information about this, you can
> contact Microsoft Product Support directly to discuss additional support
> options you may have available, by contacting us at 1-(800)936-5800 or by
> choosing one of the options listed at
> http://support.microsoft.com/default.aspx?scid=sz;en-us;top
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Well, I just found KB article 875445, which is a hotfix that seems to fix my
core dump. Let's see...
-- J
http://kerblog.com/earlyedition/archive/2004/09/27/184.aspx
"Jonathan Levine" wrote:
> When running a particular really badly written query on SQL Server 2000 SP3a,
> I'm seeing a dump file generated about every 4 minutes. The top of the dump
> is:
> * BEGIN STACK DUMP:
> * 09/19/04 12:40:38 spid 0
> *
> * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> Line
> 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000190
>
> Anyone from Microsoft interested in the rest of the dump?
> Regards,
> Jonathan
> http://kerblog.com/earlyedition/archive/2004/09/22/176.aspx|||Hi Jonathan,
I would strongly recommend you open a new case with PSS asking for that
htofix. PSS will make sure that hotfix is suitable for your issue. You can
contact Microsoft Product Support directly to discuss additional support
options you may have available, by contacting us at 1-(800)936-5800 or by
choosing one of the options listed at
http://support.microsoft.com/default.aspx?scid=sz;en-us;top.
Anyway, asking a hotfix will be a free case. You are welcome to post the
result here let us know whether that hotfix resolved your issue. If not,
you could continue discussing it with PSS to find the root cause.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Mingqing,
""Mingqing Cheng [MSFT]"" wrote:
> I would strongly recommend you open a new case with PSS asking for that
> htofix. PSS will make sure that hotfix is suitable for your issue.
Thank you, I already did so and got the hotfix.
Regards,
Jonathan

BugCheck in SQL Server 2000 SP3a?

When running a particular really badly written query on SQL Server 2000 SP3a,
I'm seeing a dump file generated about every 4 minutes. The top of the dump
is:
* BEGIN STACK DUMP:
* 09/19/04 12:40:38 spid 0
*
* Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
Line
0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000190
Anyone from Microsoft interested in the rest of the dump?
Regards,
Jonathan
http://kerblog.com/earlyedition/arch...09/22/176.aspx
Jonathan,
Whenever a T-SQL query causes a stack dump, it's indicative of a bug. You
can check the SQL Server 2000 hotfixes released by Microsoft since then to
see if your case is covered. If not, you can report this to MS by phoning in
an incident to Microsoft PSS.
Ron
Ron Talmage
SQL Server MVP
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:F194838F-B59F-4518-B430-22A4BF9D899C@.microsoft.com...
> When running a particular really badly written query on SQL Server 2000
SP3a,
> I'm seeing a dump file generated about every 4 minutes. The top of the
dump
> is:
> * BEGIN STACK DUMP:
> * 09/19/04 12:40:38 spid 0
> *
> * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> Line
> 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000190
>
> Anyone from Microsoft interested in the rest of the dump?
> Regards,
> Jonathan
> http://kerblog.com/earlyedition/arch...09/22/176.aspx
|||Thanks, Ron.
Am I going to be charged for reporting this bug?
Regards,
Jonathan
"Ron Talmage" wrote:
> Jonathan,
> Whenever a T-SQL query causes a stack dump, it's indicative of a bug. You
> can check the SQL Server 2000 hotfixes released by Microsoft since then to
> see if your case is covered. If not, you can report this to MS by phoning in
> an incident to Microsoft PSS.
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:F194838F-B59F-4518-B430-22A4BF9D899C@.microsoft.com...
> SP3a,
> dump
>
>
|||Hi, Jonathan
Thanks for your posting!
From your descriptions, I understood that you wonder whether it will be a
free case if you raise this your dump files to us checking it is an known
issue for us. Correct me if I was wrong.
Based on my scope, if this is confirmed to be an known issue and no hotfix
or workaround is available, it will be definitely a free case. If there is
an hotfix or workaround found to resolved this, I am afraid it will not be
a refund. Moreover, just asking for hotfix from an KB will also be free
case.
FYI, if you want to know more detailed information about this, you can
contact Microsoft Product Support directly to discuss additional support
options you may have available, by contacting us at 1-(800)936-5800 or by
choosing one of the options listed at
http://support.microsoft.com/default...d=sz;en-us;top
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Mingqing,
Thanks for your reply! You understand my question well. I didn't see any
matching hotfix, but is it possible/advisable for me to post my dump file
here and have it checked?
Thank you,
Jonathan
""Mingqing Cheng [MSFT]"" wrote:
> Hi, Jonathan
> Thanks for your posting!
> From your descriptions, I understood that you wonder whether it will be a
> free case if you raise this your dump files to us checking it is an known
> issue for us. Correct me if I was wrong.
> Based on my scope, if this is confirmed to be an known issue and no hotfix
> or workaround is available, it will be definitely a free case. If there is
> an hotfix or workaround found to resolved this, I am afraid it will not be
> a refund. Moreover, just asking for hotfix from an KB will also be free
> case.
> FYI, if you want to know more detailed information about this, you can
> contact Microsoft Product Support directly to discuss additional support
> options you may have available, by contacting us at 1-(800)936-5800 or by
> choosing one of the options listed at
> http://support.microsoft.com/default...d=sz;en-us;top
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Jonathan,
I am sorry to say that checking dumps file is not supported in the
newsgroup and this kind of issues can be caused by various factors, it is
difficult to locate the root cause in a newsgroup thread. If you want to
find the root cause of that, you will have to turn to PSS for help.
Thank you for your patience and understanding.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Well, I just found KB article 875445, which is a hotfix that seems to fix my
core dump. Let's see...
-- J
http://kerblog.com/earlyedition/arch...09/27/184.aspx
"Jonathan Levine" wrote:

> When running a particular really badly written query on SQL Server 2000 SP3a,
> I'm seeing a dump file generated about every 4 minutes. The top of the dump
> is:
> * BEGIN STACK DUMP:
> * 09/19/04 12:40:38 spid 0
> *
> * Exception Address = 00444449 (ExecutionContext::SmartYield + 00000007
> Line
> 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000190
>
> Anyone from Microsoft interested in the rest of the dump?
> Regards,
> Jonathan
> http://kerblog.com/earlyedition/arch...09/22/176.aspx
|||Hi Jonathan,
I would strongly recommend you open a new case with PSS asking for that
htofix. PSS will make sure that hotfix is suitable for your issue. You can
contact Microsoft Product Support directly to discuss additional support
options you may have available, by contacting us at 1-(800)936-5800 or by
choosing one of the options listed at
http://support.microsoft.com/default...=sz;en-us;top.
Anyway, asking a hotfix will be a free case. You are welcome to post the
result here let us know whether that hotfix resolved your issue. If not,
you could continue discussing it with PSS to find the root cause.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
""Mingqing Cheng [MSFT]"" wrote:
> I would strongly recommend you open a new case with PSS asking for that
> htofix. PSS will make sure that hotfix is suitable for your issue.
Thank you, I already did so and got the hotfix.
Regards,
Jonathan

Bug in INFORMATION_SCHEMA.SCHEMATA?

This is SQL Server 2000 with SP3a.
When I do
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
I get only a subset of databases listed. I did check the permission to the
non-listed databases, and without knowing exactly what permission "chain"
the above view uses, the user definitely "has access" to the missing
databases. I tried both with an account with very limited permissions (SQL
Server login), "sa" (SQL Server login) and Integrated Security login from a
Windows account in the (Local) Administrators group on the SQL Server
machine. The login "BUILTIN\Administrators" has SA access, and has
permissions to all the databases.
If I connect as the same user to a different ("unlisted") database, the
current database shows up in the above query, and the one that I was
previously connected to (and therefore showed up) is gone from the results.
I also tried
SELECT HAS_DBACCESS('<unlisted db>')
and received 1 as a result (in QA, while connected as a limited user to
another database - also unlisted).
Most (or all?) of the "unlisted" databases have been created by a script run
in QA and/or with osql.exe (command line parameters). Is there something I
am missing? My script assignes specific privilleges to a role that I
create, as well as to the "public" role.
The assignment of privilleges are done per the following script excerpt:
if not exists (select * from master.dbo.syslogins where loginname =
N'SomeUser')
BEGIN
declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
N'master', @.loginlang = N'us_english'
if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)
select @.logindb = N'master'
if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'us_english')
select @.loginlang = @.@.language
exec sp_addlogin N'SomeUser', null, @.logindb, @.loginlang
END
GO
if not exists (select * from dbo.sysusers where name = N'SomeUser' and uid <
16382)
EXEC sp_grantdbaccess N'SomeUser', N'SomeUser'
GO
exec sp_addrolemember N'db_datareader', N'SomeUser'
GO
exec sp_addrolemember N'db_datawriter', N'SomeUser'
GO
exec sp_addrolemember N'my_app_role', N'SomeUser'
GO
(You may recognize this as (based on) output from EM Generate SQL Script...)
I know I can use master..sysdatabases, but I am really trying to follow MS'
advice not to access the system tables directly...
While I found some similar questions through Google, I couldn't find any
that addressed this as an actual issue - apparently the "don't have access"
was more or less accepted without much protest...
Help, please...
Tore.Is the 'auto close' database option turned on? Apparently, closed database
are not included in this view.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tore" <pointzero@.vbdreamteam.net> wrote in message
news:u1n7AXrRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> This is SQL Server 2000 with SP3a.
> When I do
> SELECT CATALOG_NAME
> FROM INFORMATION_SCHEMA.SCHEMATA
> I get only a subset of databases listed. I did check the permission to
> the non-listed databases, and without knowing exactly what permission
> "chain" the above view uses, the user definitely "has access" to the
> missing databases. I tried both with an account with very limited
> permissions (SQL Server login), "sa" (SQL Server login) and Integrated
> Security login from a Windows account in the (Local) Administrators group
> on the SQL Server machine. The login "BUILTIN\Administrators" has SA
> access, and has permissions to all the databases.
> If I connect as the same user to a different ("unlisted") database, the
> current database shows up in the above query, and the one that I was
> previously connected to (and therefore showed up) is gone from the
> results.
> I also tried
> SELECT HAS_DBACCESS('<unlisted db>')
> and received 1 as a result (in QA, while connected as a limited user to
> another database - also unlisted).
> Most (or all?) of the "unlisted" databases have been created by a script
> run in QA and/or with osql.exe (command line parameters). Is there
> something I am missing? My script assignes specific privilleges to a role
> that I create, as well as to the "public" role.
> The assignment of privilleges are done per the following script excerpt:
> if not exists (select * from master.dbo.syslogins where loginname =
> N'SomeUser')
> BEGIN
> declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
> N'master', @.loginlang = N'us_english'
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
> master.dbo.syslanguages where name = @.loginlang) and @.loginlang <>
> N'us_english')
> select @.loginlang = @.@.language
> exec sp_addlogin N'SomeUser', null, @.logindb, @.loginlang
> END
> GO
> if not exists (select * from dbo.sysusers where name = N'SomeUser' and uid
> < 16382)
> EXEC sp_grantdbaccess N'SomeUser', N'SomeUser'
> GO
> exec sp_addrolemember N'db_datareader', N'SomeUser'
> GO
> exec sp_addrolemember N'db_datawriter', N'SomeUser'
> GO
> exec sp_addrolemember N'my_app_role', N'SomeUser'
> GO
> (You may recognize this as (based on) output from EM Generate SQL
> Script...)
> I know I can use master..sysdatabases, but I am really trying to follow
> MS' advice not to access the system tables directly...
> While I found some similar questions through Google, I couldn't find any
> that addressed this as an actual issue - apparently the "don't have
> access" was more or less accepted without much protest...
> Help, please...
> Tore.
>|||Thanks,
Yes, autoclose is on (which is a reasonable option for these databases).
I guess it's back to the system tables... :-<
Regards,
Tore.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OoS3%23ftRFHA.580@.TK2MSFTNGP15.phx.gbl...
> Is the 'auto close' database option turned on? Apparently, closed
> database are not included in this view.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tore" <pointzero@.vbdreamteam.net> wrote in message
> news:u1n7AXrRFHA.2784@.TK2MSFTNGP12.phx.gbl...
>|||Hi,
The root cause of this is that the DATABASEPROPERTY() and
DATABASEPROPERTYEX() functions return NULL in cases where the database is
off-line or auto-closed. The INFORMATION_SCHEMA.SCHEMATA includes a JOIN
expression on one of these functions; it returns null, and the so the row
is not seen in the result set.
This "Returns NULL" behavior of those functions is documented in the SQL
2005 BOL.
In any case, the INFORMATION_SCHEMA.SCHEMATA view has undergone a breaking
change in SQL 2005. It now returns all the schemas in the current catalog
(database) rather than all catalogs (databases) in the server. This
changes brings our implementation into compliance with the SQL standard.
Sadly, our SQL 2000 implementation of that view was in error. This
breaking change is documented in SQL 2005 BOL and our "Upgrade Analyzer"
will detect it for you.
Unforutnately, the SQL standard does not provide as way to discover all
catalogs (databases) in the server. From the standard's perspective, that
is an "implementation detail." I can tell you that it is OK to use
master.dbo.sysdatabases in your SQL2K application and it will upgrade to
SQL 2005. NB: We have modified the security rules around who can "see" rows
in catalog views in SQL 2005. In this particular case, we've introduced a
new server-level permission called VIEW ANY DATABASE, and it is GRANT-ed to
PUBLIC by default. The net result is that your application should continue
to work after you upgrade to SQL 2005.
IN general, you are OK to use system tables as long as you STAY AWAY FROM
UNDOCUMENTED OR INTERNAL OR RESERVED tables and columns!! We make no
guarantees to support them in SQL 2005.
So, for example, avoid things like:
* sysproperties - undocumented
* sysxlogins - undocumented
* sysdatabases.mode - internal only
* sysdatabases.reserved - reserved
The rule of thumb is this:
If the system table has a topic page devoted to it in the BOL, it is OK.
If the column is documented and it does not say "internal" or "reserved",
it is OK.
Regards,
Cliff Dibble
Program Manager, SQL Server Engine
MSFT
Message posted via http://www.webservertalk.com|||Thanks for the detailed response. I find your hint about how to decide what
to stay away from especially useful.
However, the way I read the SQL Standard definition of
INFORMATION_SCHEMA.SCHEMATA is (quote):
"Identify the schemata that are owned by a given user."
Inspection of the underlying query also indicates that it refers to the
catalog that contains the information schema.
Since the SQL standard allows for adding objects to the INFORMATION_SCHEMA,
it seems that INFORMATION_SCHEMA.DATABASES (or INFORMATION_SCHEMA.CATALOGS?)
would be a prime candidate... :->
Thanks and Regards,
Tore.
"Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:eb54e4cc92aa4ec7bd82c20143fb12f2@.SQ
webservertalk.com...
> Hi,
> The root cause of this is that the DATABASEPROPERTY() and
> DATABASEPROPERTYEX() functions return NULL in cases where the database is
> off-line or auto-closed. The INFORMATION_SCHEMA.SCHEMATA includes a JOIN
> expression on one of these functions; it returns null, and the so the row
> is not seen in the result set.
> This "Returns NULL" behavior of those functions is documented in the SQL
> 2005 BOL.
> In any case, the INFORMATION_SCHEMA.SCHEMATA view has undergone a breaking
> change in SQL 2005. It now returns all the schemas in the current catalog
> (database) rather than all catalogs (databases) in the server. This
> changes brings our implementation into compliance with the SQL standard.
> Sadly, our SQL 2000 implementation of that view was in error. This
> breaking change is documented in SQL 2005 BOL and our "Upgrade Analyzer"
> will detect it for you.
> Unforutnately, the SQL standard does not provide as way to discover all
> catalogs (databases) in the server. From the standard's perspective, that
> is an "implementation detail." I can tell you that it is OK to use
> master.dbo.sysdatabases in your SQL2K application and it will upgrade to
> SQL 2005. NB: We have modified the security rules around who can "see"
> rows
> in catalog views in SQL 2005. In this particular case, we've introduced a
> new server-level permission called VIEW ANY DATABASE, and it is GRANT-ed
> to
> PUBLIC by default. The net result is that your application should
> continue
> to work after you upgrade to SQL 2005.
> IN general, you are OK to use system tables as long as you STAY AWAY FROM
> UNDOCUMENTED OR INTERNAL OR RESERVED tables and columns!! We make no
> guarantees to support them in SQL 2005.
> So, for example, avoid things like:
> * sysproperties - undocumented
> * sysxlogins - undocumented
> * sysdatabases.mode - internal only
> * sysdatabases.reserved - reserved
> The rule of thumb is this:
> If the system table has a topic page devoted to it in the BOL, it is OK.
> If the column is documented and it does not say "internal" or "reserved",
> it is OK.
> Regards,
> Cliff Dibble
> Program Manager, SQL Server Engine
> MSFT
> --
> Message posted via http://www.webservertalk.com|||> Since the SQL standard allows for adding objects to the INFORMATION_SCHEMA, it seems that

> INFORMATION_SCHEMA.DATABASES (or INFORMATION_SCHEMA.CATALOGS?) would be a prime ca
ndidate... :->
But that would make it proprietary, just the same as sysdatabases and the fu
ture sys.databases
are...?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tore" <pointzero at vbdreamteam dot net> wrote in message
news:Oo5FacUSFHA.248@.TK2MSFTNGP15.phx.gbl...
> Thanks for the detailed response. I find your hint about how to decide wh
at to stay away from
> especially useful.
> However, the way I read the SQL Standard definition of INFORMATION_SCHEMA.
SCHEMATA is (quote):
> "Identify the schemata that are owned by a given user."
> Inspection of the underlying query also indicates that it refers to the ca
talog that contains the
> information schema.
> Since the SQL standard allows for adding objects to the INFORMATION_SCHEMA
, it seems that
> INFORMATION_SCHEMA.DATABASES (or INFORMATION_SCHEMA.CATALOGS?) would be a
prime candidate... :->
> Thanks and Regards,
> Tore.
>
> "Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in messa
ge
> news:eb54e4cc92aa4ec7bd82c20143fb12f2@.SQ
webservertalk.com...
>|||Initially we were against adding anything new that was not explicit in the
standard. However, there does appear to be an escape clause in the
standard. See section 4.2, p.8 of Part 11.
"An SQL-implementation may define objects that are associated with
INFORMATION_SCHEMA that are not defined in this Clause. An SQL-
implementation or any future version of ISO/IEC 9075 may also add columns
to tables that are defined in this Clause."
It's not entirely clear how we'd do that without risking some future
incompatibility with the standard. We'd probably have to define some MSFT-
specific naming convention.
In any case, we did not get around to adding I/S customizations in Yukon.
We will consider adding more extensions doing a fuller implementation of
the I/S views in the next product release. The I/S views encapsulate a lot
of JOINs required in the underlying catalog.
Regards,
Cliff Dibble
PM, SQL Engine
Message posted via http://www.webservertalk.com|||Just to close on the topic of system table compatibility in Yukon, here is
the complete list of previously documented columns that are no longer
compatible in Yukon, along with an explanation why. You should avoid
these, too.
view column_name value rationale
========================================
====================================
==
syscomments compressed 0 ctext is always uncompressed in Yukon.
sysdevices size 0 This has been deprecated since SQL2K
sysindexes statblob NULL Security reasons.
sysindexes keys NULL Severe perf hit. The format of the
binary
data was not documented.
sysobjects schema_ver 0 Due to architectural change, can not
guarantee strict monotonicity due to
round
off errors when converting/mapping from
8-byte datetime to 4-byte integer.
Use sys.objects.modify_date instead.
sysoledbusers rmtpassword NULL Security reasons.
sysremotelogins status 0 The format of the options was not
documented.
sysservers topologyy 0 The UI no longer uses this.
There are also a few incompatibilities in sysindexes (which are doc'ed in
the most recent SQL 2005 CTP builds):
a) No more indid=255 due to storage engine changes.
b) The page counters like "dpages" don't row-overflow.
c) The behavior of rowmodctr is not exactly the same due to storage engine
changes (we now carry per-column modification counters).
d) maxirow values are not identical due to storage engine changes.
e) keys column is NULL, as previously mentioned
f) The name column is NULL for heaps because we now have a unique index on
that column and we need to avoid possible name conflicts.
Finally - note that if you use NEW features in Yukon (e.g. partitioning or
any of the new user/schema DDL like CREATE USER), you will absolutely need
to migrate to the new catalog views. For example, sysindexes.root is NULL
for partitioned indexes because each partition has its own B-tree. Another
example:
select * from sysobjects where uid=1 and name = 'X'
might return >1 row due to user/schema separation (suppose dbo owned two
schemas S1 and S2, each of which contained a table named X).
Regards,
Cliff Dibble
PM SQL Engine
Message posted via http://www.webservertalk.com|||That's a good list, Clifford. Any plans to document it? (Or did I miss it in
April CTP BOL?)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:3d4abaedc650452998ebec042f092996@.SQ
webservertalk.com...
> Just to close on the topic of system table compatibility in Yukon, here is
> the complete list of previously documented columns that are no longer
> compatible in Yukon, along with an explanation why. You should avoid
> these, too.
> view column_name value rationale
> ========================================
==================================
====
> syscomments compressed 0 ctext is always uncompressed in Yukon.
> sysdevices size 0 This has been deprecated since SQL2K
> sysindexes statblob NULL Security reasons.
> sysindexes keys NULL Severe perf hit. The format of the
> binary
> data was not documented.
> sysobjects schema_ver 0 Due to architectural change, can not
> guarantee strict monotonicity due to
> round
> off errors when converting/mapping from
> 8-byte datetime to 4-byte integer.
> Use sys.objects.modify_date instead.
> sysoledbusers rmtpassword NULL Security reasons.
> sysremotelogins status 0 The format of the options was not
> documented.
> sysservers topologyy 0 The UI no longer uses this.
> There are also a few incompatibilities in sysindexes (which are doc'ed in
> the most recent SQL 2005 CTP builds):
> a) No more indid=255 due to storage engine changes.
> b) The page counters like "dpages" don't row-overflow.
> c) The behavior of rowmodctr is not exactly the same due to storage engine
> changes (we now carry per-column modification counters).
> d) maxirow values are not identical due to storage engine changes.
> e) keys column is NULL, as previously mentioned
> f) The name column is NULL for heaps because we now have a unique index on
> that column and we need to avoid possible name conflicts.
>
> Finally - note that if you use NEW features in Yukon (e.g. partitioning or
> any of the new user/schema DDL like CREATE USER), you will absolutely need
> to migrate to the new catalog views. For example, sysindexes.root is NUL
L
> for partitioned indexes because each partition has its own B-tree. Anothe
r
> example:
> select * from sysobjects where uid=1 and name = 'X'
> might return >1 row due to user/schema separation (suppose dbo owned two
> schemas S1 and S2, each of which contained a table named X).
> Regards,
> Cliff Dibble
> PM SQL Engine
> --
> Message posted via http://www.webservertalk.com|||> It's not entirely clear how we'd do that without risking some future
> incompatibility with the standard. We'd probably have to define some MSF
T-
> specific naming convention.
My .02: Don't.
I think can see the temptation: "By just adding this column, the view will b
e usable in a lot more
scenarios."
To me, the purpose of these views are that you know you are within the stand
ard. If I would need
something outside these views, I prefer diving into the catalog views or sys
tem tables, as I would
still need a re-write in the unlikely case I need to port the app. Also, the
catalog views have a
much better design than the old style system tables, so ease of use (info sc
hema vs. system tables)
isn't an argument either.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:8ee5d4d5a35947ec98ee43006ed152cd@.SQ
webservertalk.com...
> Initially we were against adding anything new that was not explicit in the
> standard. However, there does appear to be an escape clause in the
> standard. See section 4.2, p.8 of Part 11.
> "An SQL-implementation may define objects that are associated with
> INFORMATION_SCHEMA that are not defined in this Clause. An SQL-
> implementation or any future version of ISO/IEC 9075 may also add columns
> to tables that are defined in this Clause."
> It's not entirely clear how we'd do that without risking some future
> incompatibility with the standard. We'd probably have to define some MSF
T-
> specific naming convention.
> In any case, we did not get around to adding I/S customizations in Yukon.
> We will consider adding more extensions doing a fuller implementation of
> the I/S views in the next product release. The I/S views encapsulate a lo
t
> of JOINs required in the underlying catalog.
> Regards,
> Cliff Dibble
> PM, SQL Engine
> --
> Message posted via http://www.webservertalk.com

Bug in CASE statement?

(SQL Server 2000, SP3a)
Hello all!
I'm trying to "seed" some random data in a table, and am having some trouble with the CASE
statement that I don't fully understand.
Consider the following:
select id,
[Rating] = case (abs(convert(int, convert(varbinary(4), newid())) % 5))
when 0 then NULL
when 1 then 'Poor'
when 2 then 'Fair'
when 3 then 'Good'
when 4 then 'Excellent'
else '?'
end
from sysobjects
I would expect this to return values in the range of 0-4, which would map to the various
strings that you see. However, I keep getting a high incidence of '?' strings returned.
When I use the exact same expression without the CASE statement:
select distinct Rating
from (
select Rating = abs(convert(int, convert(varbinary(4), newid())) % 5)
from sysobjects
) as q
I show that, indeed, the only values that are returned are 0-4.
This sure *seems* like a bug to me, but I suspect the problem lies in my understanding of
the CASE statement.
Any help would be much appreciated! :-)
John PetersonHi John
The only problem you are having with CASE is your terminology. CASE is an
expression, not a statement. But since you are using it as an expression,
within the SELECT statement, you do seem to understand it.
This is very strange behavior indeed. I think the problem is with NEWID().
You can establish that your understanding of the CASE expression by changing
NEWID() to something else, like id from the sysobjects table (and I am also
returning the value computed by the case expression for comparison
purposes):
select id,abs(convert(int, convert(varbinary(4), id)) % 5),
[Rating] = case (abs(convert(int, convert(varbinary(4), id)) % 5))
when 0 then NULL
when 1 then 'Poor'
when 2 then 'Fair'
when 3 then 'Good'
when 4 then 'Excellent'
else '?'
end
from sysobjects
The above seems to work as expected.
My guess is that NEWID() is being regenerated for EVERY comparison, so the
value is always changing. So your expression abs(convert(int,
convert(varbinary(4), id)) % 5) is evaluated and compared to 0, and if so,
NULL is returned, otherwise the expression is evaluted AGAIN (with a new
NEWID() ) and that is compared to 1, and if that one wasn't equal to one,
another NEWID() expression is compared to 2, etc. So it's hardly surprising
that few of the expressions will match the constant you're comparing too
(although a few will) and many will have no match and fall through to '?'
Why don't you tell us exactly what you're trying to do, and someone may have
another suggestion for you.
In the meantime, I'll try to find out what's happening with NEWID() here.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eaQO0ZzkDHA.976@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I'm trying to "seed" some random data in a table, and am having some
trouble with the CASE
> statement that I don't fully understand.
> Consider the following:
>
> select id,
> [Rating] = case (abs(convert(int, convert(varbinary(4), newid())) %
5))
> when 0 then NULL
> when 1 then 'Poor'
> when 2 then 'Fair'
> when 3 then 'Good'
> when 4 then 'Excellent'
> else '?'
> end
> from sysobjects
>
> I would expect this to return values in the range of 0-4, which would map
to the various
> strings that you see. However, I keep getting a high incidence of '?'
strings returned.
>
> When I use the exact same expression without the CASE statement:
>
> select distinct Rating
> from (
> select Rating = abs(convert(int, convert(varbinary(4), newid())) %
5)
> from sysobjects
> ) as q
>
> I show that, indeed, the only values that are returned are 0-4.
> This sure *seems* like a bug to me, but I suspect the problem lies in my
understanding of
> the CASE statement.
> Any help would be much appreciated! :-)
> John Peterson
>|||Hello, Kalen! Please see inline:
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ubXkerzkDHA.3312@.tk2msftngp13.phx.gbl...
> Hi John
> The only problem you are having with CASE is your terminology. CASE is an
> expression, not a statement. But since you are using it as an expression,
> within the SELECT statement, you do seem to understand it.
Thanks -- I always want to use the correct nomenclature. I bet if I would have looked in
BOL, I might have used the right term. :-)
> This is very strange behavior indeed. I think the problem is with NEWID().
> You can establish that your understanding of the CASE expression by changing
> NEWID() to something else, like id from the sysobjects table (and I am also
> returning the value computed by the case expression for comparison
> purposes):
> select id,abs(convert(int, convert(varbinary(4), id)) % 5),
> [Rating] = case (abs(convert(int, convert(varbinary(4), id)) % 5))
> when 0 then NULL
> when 1 then 'Poor'
> when 2 then 'Fair'
> when 3 then 'Good'
> when 4 then 'Excellent'
> else '?'
> end
> from sysobjects
> The above seems to work as expected.
Yeah, that's exactly what I wound up doing in my table, since the IDs were GUIDs, I didn't
necessarily need to use newid().
> My guess is that NEWID() is being regenerated for EVERY comparison, so the
> value is always changing. So your expression abs(convert(int,
> convert(varbinary(4), id)) % 5) is evaluated and compared to 0, and if so,
> NULL is returned, otherwise the expression is evaluted AGAIN (with a new
> NEWID() ) and that is compared to 1, and if that one wasn't equal to one,
> another NEWID() expression is compared to 2, etc. So it's hardly surprising
> that few of the expressions will match the constant you're comparing too
> (although a few will) and many will have no match and fall through to '?'
Oh, interesting! I guess that sort of makes sense if the "internals" of the execution are
always re-evaluating NEWID() instead of just having one persistent value.
> Why don't you tell us exactly what you're trying to do, and someone may have
> another suggestion for you.
> In the meantime, I'll try to find out what's happening with NEWID() here.
All I really wanted to do was generate some random data -- and I tried using the RAND()
function, but it would only evaluate RAND() *once* for every row in the result set (just
like GETDATE() does, I believe); as such, every value was the same. So, I switched to use
NEWID(), since that appears to generate a new value for each row in the result set.
At this point, I was able to use a variant of your suggestion (to use a static value
instead of NEWID()) to generate my random data. But, I thought it would be worth posting
this issue to try and find out more about it. :-)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:eaQO0ZzkDHA.976@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I'm trying to "seed" some random data in a table, and am having some
> trouble with the CASE
> > statement that I don't fully understand.
> >
> > Consider the following:
> >
> >
> > select id,
> > [Rating] = case (abs(convert(int, convert(varbinary(4), newid())) %
> 5))
> > when 0 then NULL
> > when 1 then 'Poor'
> > when 2 then 'Fair'
> > when 3 then 'Good'
> > when 4 then 'Excellent'
> > else '?'
> > end
> > from sysobjects
> >
> >
> > I would expect this to return values in the range of 0-4, which would map
> to the various
> > strings that you see. However, I keep getting a high incidence of '?'
> strings returned.
> >
> >
> > When I use the exact same expression without the CASE statement:
> >
> >
> > select distinct Rating
> > from (
> > select Rating = abs(convert(int, convert(varbinary(4), newid())) %
> 5)
> > from sysobjects
> > ) as q
> >
> >
> > I show that, indeed, the only values that are returned are 0-4.
> >
> > This sure *seems* like a bug to me, but I suspect the problem lies in my
> understanding of
> > the CASE statement.
> >
> > Any help would be much appreciated! :-)
> >
> > John Peterson
> >
> >
>|||Yes, this is an useful distinction between RAND() and NEWID(). I knew
NEWID() regenerated for each row, but it sure seems strange that it would
reevaluate for every comparison within a CASE.
But it's probably good to know this!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uBnY#xzkDHA.2312@.TK2MSFTNGP12.phx.gbl...
> Hello, Kalen! Please see inline:
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:ubXkerzkDHA.3312@.tk2msftngp13.phx.gbl...
> > Hi John
> > The only problem you are having with CASE is your terminology. CASE is
an
> > expression, not a statement. But since you are using it as an
expression,
> > within the SELECT statement, you do seem to understand it.
> Thanks -- I always want to use the correct nomenclature. I bet if I would
have looked in
> BOL, I might have used the right term. :-)
>
> > This is very strange behavior indeed. I think the problem is with
NEWID().
> > You can establish that your understanding of the CASE expression by
changing
> > NEWID() to something else, like id from the sysobjects table (and I am
also
> > returning the value computed by the case expression for comparison
> > purposes):
> >
> > select id,abs(convert(int, convert(varbinary(4), id)) % 5),
> > [Rating] = case (abs(convert(int, convert(varbinary(4), id)) %
5))
> > when 0 then NULL
> > when 1 then 'Poor'
> > when 2 then 'Fair'
> > when 3 then 'Good'
> > when 4 then 'Excellent'
> > else '?'
> > end
> > from sysobjects
> >
> > The above seems to work as expected.
> Yeah, that's exactly what I wound up doing in my table, since the IDs were
GUIDs, I didn't
> necessarily need to use newid().
>
> > My guess is that NEWID() is being regenerated for EVERY comparison, so
the
> > value is always changing. So your expression abs(convert(int,
> > convert(varbinary(4), id)) % 5) is evaluated and compared to 0, and if
so,
> > NULL is returned, otherwise the expression is evaluted AGAIN (with a new
> > NEWID() ) and that is compared to 1, and if that one wasn't equal to
one,
> > another NEWID() expression is compared to 2, etc. So it's hardly
surprising
> > that few of the expressions will match the constant you're comparing too
> > (although a few will) and many will have no match and fall through to
'?'
> Oh, interesting! I guess that sort of makes sense if the "internals" of
the execution are
> always re-evaluating NEWID() instead of just having one persistent value.
>
> > Why don't you tell us exactly what you're trying to do, and someone may
have
> > another suggestion for you.
> > In the meantime, I'll try to find out what's happening with NEWID()
here.
> All I really wanted to do was generate some random data -- and I tried
using the RAND()
> function, but it would only evaluate RAND() *once* for every row in the
result set (just
> like GETDATE() does, I believe); as such, every value was the same. So, I
switched to use
> NEWID(), since that appears to generate a new value for each row in the
result set.
> At this point, I was able to use a variant of your suggestion (to use a
static value
> instead of NEWID()) to generate my random data. But, I thought it would
be worth posting
> this issue to try and find out more about it. :-)
>
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:eaQO0ZzkDHA.976@.tk2msftngp13.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > I'm trying to "seed" some random data in a table, and am having some
> > trouble with the CASE
> > > statement that I don't fully understand.
> > >
> > > Consider the following:
> > >
> > >
> > > select id,
> > > [Rating] = case (abs(convert(int, convert(varbinary(4),
newid())) %
> > 5))
> > > when 0 then NULL
> > > when 1 then 'Poor'
> > > when 2 then 'Fair'
> > > when 3 then 'Good'
> > > when 4 then 'Excellent'
> > > else '?'
> > > end
> > > from sysobjects
> > >
> > >
> > > I would expect this to return values in the range of 0-4, which would
map
> > to the various
> > > strings that you see. However, I keep getting a high incidence of '?'
> > strings returned.
> > >
> > >
> > > When I use the exact same expression without the CASE statement:
> > >
> > >
> > > select distinct Rating
> > > from (
> > > select Rating = abs(convert(int, convert(varbinary(4),
newid())) %
> > 5)
> > > from sysobjects
> > > ) as q
> > >
> > >
> > > I show that, indeed, the only values that are returned are 0-4.
> > >
> > > This sure *seems* like a bug to me, but I suspect the problem lies in
my
> > understanding of
> > > the CASE statement.
> > >
> > > Any help would be much appreciated! :-)
> > >
> > > John Peterson
> > >
> > >
> >
> >
>|||Hello
> select id,
> [Rating] = case (abs(convert(int, convert(varbinary(4), newid())) %
5))
> when 0 then NULL
> when 1 then 'Poor'
> when 2 then 'Fair'
> when 3 then 'Good'
> when 4 then 'Excellent'
> else '?'
> end
> from sysobjects
>
> I would expect this to return values in the range of 0-4, which would map
to the various
> strings that you see. However, I keep getting a high incidence of '?'
strings returned.
What version of SQL server are you using?
I've tested your query on my MSSQL 2000 EE + SP3 and it works fine.
Number of '?' is near the number of other variants.
Serge Shakhov|||That's just it -- there should be *no* '?' results. (I'm using SQL 2000 EE SP3a.)
"Serge Shakhov" <REMOVETHIS_ACETYLENE@.mail.ru> wrote in message
news:q0ilmb.039.ln@.proxyserver.ctd.mmk.chel.su...
> Hello
> > select id,
> > [Rating] = case (abs(convert(int, convert(varbinary(4), newid())) %
> 5))
> > when 0 then NULL
> > when 1 then 'Poor'
> > when 2 then 'Fair'
> > when 3 then 'Good'
> > when 4 then 'Excellent'
> > else '?'
> > end
> > from sysobjects
> >
> >
> > I would expect this to return values in the range of 0-4, which would map
> to the various
> > strings that you see. However, I keep getting a high incidence of '?'
> strings returned.
> What version of SQL server are you using?
> I've tested your query on my MSSQL 2000 EE + SP3 and it works fine.
> Number of '?' is near the number of other variants.
>
> Serge Shakhov
>