Boot.ini file: running

Showing posts with label running. Show all posts

Thursday, March 22, 2012

bugs, Information_schema appeared as a user in master database

Hi, All
Recently, I was running a script in sql 2000 on window 2000 server like:
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME('db_executor')
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
exec master.dbo.xp_execresultset @.sql,@.db
to grant stored procedures execution permition to role, after running the
script, the privilige is grant it to that role, but I got very strange
things happened on sql server.
1. Master database get Information_Shema as a user. dbaccess "via group
membership"
2.Master database got system_function_schema as a user , dbaccess "via group
membership"
3.guest user show in every database in the instance, dbaccess "via group
membership"
if I delete guest account, show me message "the user is not in
database", sp_helpuser did not show guest user, enterprice manager show it
as a user.
4. run sp_grantdbaccess guest. the guest get permitted to use database. I
delete it successful, however, a couple minutes later, it appears in the db
as dbaccess via group membership.
it is really painful for me. could anyone give me a hint to fix the problem?
any help is appreciated.
Thanks
YifeiHi
You may want to try
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
'.INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
Although if that gives you thre right view you may get nothing back as the
OBJECT_ID function could cause problems, you may therefore want to resort to
using sysobjects.
John
"Yifei Jiang" wrote:
> Hi, All
> Recently, I was running a script in sql 2000 on window 2000 server like:
> declare @.sql nvarchar(4000)
> declare @.db sysname ; set @.db = DB_NAME()
> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> exec master.dbo.xp_execresultset @.sql,@.db
> to grant stored procedures execution permition to role, after running the
> script, the privilige is grant it to that role, but I got very strange
> things happened on sql server.
> 1. Master database get Information_Shema as a user. dbaccess "via group
> membership"
> 2.Master database got system_function_schema as a user , dbaccess "via group
> membership"
> 3.guest user show in every database in the instance, dbaccess "via group
> membership"
> if I delete guest account, show me message "the user is not in
> database", sp_helpuser did not show guest user, enterprice manager show it
> as a user.
> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> delete it successful, however, a couple minutes later, it appears in the db
> as dbaccess via group membership.
> it is really painful for me. could anyone give me a hint to fix the problem?
> any help is appreciated.
> Thanks
> Yifei
>
>
>|||Hi, John,
Thank you for replay,
I think the issue relate to sql 2005, because from em on the server, the
information_shema does not show as user, from em installed sql2005,
information_shema shows as user. Same as another machine that installed sql
2005 and does not run the script.
The reason because I did not check the user until I run the script and have
to check it and find the problem.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
> Hi
> You may want to try
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> '.INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> Although if that gives you thre right view you may get nothing back as the
> OBJECT_ID function could cause problems, you may therefore want to resort
> to
> using sysobjects.
> John
>
>
> "Yifei Jiang" wrote:
>> Hi, All
>> Recently, I was running a script in sql 2000 on window 2000 server like:
>> declare @.sql nvarchar(4000)
>> declare @.db sysname ; set @.db = DB_NAME()
>> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
>> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.''
>> +
>> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
>> INFORMATION_SCHEMA.ROUTINES ' +
>> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
>> exec master.dbo.xp_execresultset @.sql,@.db
>> to grant stored procedures execution permition to role, after running the
>> script, the privilige is grant it to that role, but I got very strange
>> things happened on sql server.
>> 1. Master database get Information_Shema as a user. dbaccess "via group
>> membership"
>> 2.Master database got system_function_schema as a user , dbaccess "via
>> group
>> membership"
>> 3.guest user show in every database in the instance, dbaccess "via group
>> membership"
>> if I delete guest account, show me message "the user is not in
>> database", sp_helpuser did not show guest user, enterprice manager show
>> it
>> as a user.
>> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
>> delete it successful, however, a couple minutes later, it appears in the
>> db
>> as dbaccess via group membership.
>> it is really painful for me. could anyone give me a hint to fix the
>> problem?
>> any help is appreciated.
>> Thanks
>> Yifei
>>
>>
>>|||The original post has no mention of SQL 2005. If you use Management
Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
under system views in the master database. If you use Enterprise
manager to look at a SQL 2000 database there is no division between
system and user views. If you used a cursor instead of using the
undocumented xp_execresultset this should work.
John
Scott wrote:
> Hi, John,
> Thank you for replay,
> I think the issue relate to sql 2005, because from em on the server, the
> information_shema does not show as user, from em installed sql2005,
> information_shema shows as user. Same as another machine that installed sql
> 2005 and does not run the script.
> The reason because I did not check the user until I run the script and have
> to check it and find the problem.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
> > Hi
> >
> > You may want to try
> >
> > set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> > QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> > '.INFORMATION_SCHEMA.ROUTINES ' +
> > 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >
> > Although if that gives you thre right view you may get nothing back as the
> > OBJECT_ID function could cause problems, you may therefore want to resort
> > to
> > using sysobjects.
> >
> > John
> >
> >
> >
> >
> > "Yifei Jiang" wrote:
> >
> >> Hi, All
> >>
> >> Recently, I was running a script in sql 2000 on window 2000 server like:
> >>
> >> declare @.sql nvarchar(4000)
> >> declare @.db sysname ; set @.db = DB_NAME()
> >> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> >>
> >> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.''
> >> +
> >> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> >> INFORMATION_SCHEMA.ROUTINES ' +
> >> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >>
> >> exec master.dbo.xp_execresultset @.sql,@.db
> >>
> >> to grant stored procedures execution permition to role, after running the
> >> script, the privilige is grant it to that role, but I got very strange
> >> things happened on sql server.
> >>
> >> 1. Master database get Information_Shema as a user. dbaccess "via group
> >> membership"
> >> 2.Master database got system_function_schema as a user , dbaccess "via
> >> group
> >> membership"
> >> 3.guest user show in every database in the instance, dbaccess "via group
> >> membership"
> >> if I delete guest account, show me message "the user is not in
> >> database", sp_helpuser did not show guest user, enterprice manager show
> >> it
> >> as a user.
> >> 4. run sp_grantdbaccess guest. the guest get permitted to use database. I
> >> delete it successful, however, a couple minutes later, it appears in the
> >> db
> >> as dbaccess via group membership.
> >>
> >> it is really painful for me. could anyone give me a hint to fix the
> >> problem?
> >>
> >> any help is appreciated.
> >>
> >> Thanks
> >>
> >> Yifei
> >>
> >>
> >>
> >>
> >>
> >>|||Hi, John,
You are right, what I want to say that sql 2005 caused the information_shema
as a user in master db. it is nothing wrong with running script.
Thanks
Yifei
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1134379113.719483.236400@.o13g2000cwo.googlegroups.com...
> The original post has no mention of SQL 2005. If you use Management
> Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
> under system views in the master database. If you use Enterprise
> manager to look at a SQL 2000 database there is no division between
> system and user views. If you used a cursor instead of using the
> undocumented xp_execresultset this should work.
> John
> Scott wrote:
>> Hi, John,
>> Thank you for replay,
>> I think the issue relate to sql 2005, because from em on the server, the
>> information_shema does not show as user, from em installed sql2005,
>> information_shema shows as user. Same as another machine that installed
>> sql
>> 2005 and does not run the script.
>> The reason because I did not check the user until I run the script and
>> have
>> to check it and find the problem.
>> Thanks
>> Yifei
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
>> > Hi
>> >
>> > You may want to try
>> >
>> > set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
>> > ''.'' +
>> > QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
>> > '.INFORMATION_SCHEMA.ROUTINES ' +
>> > 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
>> >
>> > Although if that gives you thre right view you may get nothing back as
>> > the
>> > OBJECT_ID function could cause problems, you may therefore want to
>> > resort
>> > to
>> > using sysobjects.
>> >
>> > John
>> >
>> >
>> >
>> >
>> > "Yifei Jiang" wrote:
>> >
>> >> Hi, All
>> >>
>> >> Recently, I was running a script in sql 2000 on window 2000 server
>> >> like:
>> >>
>> >> declare @.sql nvarchar(4000)
>> >> declare @.db sysname ; set @.db = DB_NAME()
>> >> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
>> >>
>> >> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
>> >> ''.''
>> >> +
>> >> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
>> >> INFORMATION_SCHEMA.ROUTINES ' +
>> >> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
>> >>
>> >> exec master.dbo.xp_execresultset @.sql,@.db
>> >>
>> >> to grant stored procedures execution permition to role, after running
>> >> the
>> >> script, the privilige is grant it to that role, but I got very strange
>> >> things happened on sql server.
>> >>
>> >> 1. Master database get Information_Shema as a user. dbaccess "via
>> >> group
>> >> membership"
>> >> 2.Master database got system_function_schema as a user , dbaccess "via
>> >> group
>> >> membership"
>> >> 3.guest user show in every database in the instance, dbaccess "via
>> >> group
>> >> membership"
>> >> if I delete guest account, show me message "the user is not in
>> >> database", sp_helpuser did not show guest user, enterprice manager
>> >> show
>> >> it
>> >> as a user.
>> >> 4. run sp_grantdbaccess guest. the guest get permitted to use
>> >> database. I
>> >> delete it successful, however, a couple minutes later, it appears in
>> >> the
>> >> db
>> >> as dbaccess via group membership.
>> >>
>> >> it is really painful for me. could anyone give me a hint to fix the
>> >> problem?
>> >>
>> >> any help is appreciated.
>> >>
>> >> Thanks
>> >>
>> >> Yifei
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>|||Hi
In SQL 2005 you need to be more aware of the difference between a schema and
a user. I think Kalen wrote about this some time ago in SQL Server magazine,
but I can't put my hands on this, but you may want to read
http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx
John
"Yifei" wrote:
> Hi, John,
> You are right, what I want to say that sql 2005 caused the information_shema
> as a user in master db. it is nothing wrong with running script.
> Thanks
> Yifei
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1134379113.719483.236400@.o13g2000cwo.googlegroups.com...
> > The original post has no mention of SQL 2005. If you use Management
> > Studio for a SQL 2005 database the INFORMATION_SCHEMA views appear
> > under system views in the master database. If you use Enterprise
> > manager to look at a SQL 2000 database there is no division between
> > system and user views. If you used a cursor instead of using the
> > undocumented xp_execresultset this should work.
> >
> > John
> >
> > Scott wrote:
> >> Hi, John,
> >>
> >> Thank you for replay,
> >>
> >> I think the issue relate to sql 2005, because from em on the server, the
> >> information_shema does not show as user, from em installed sql2005,
> >> information_shema shows as user. Same as another machine that installed
> >> sql
> >> 2005 and does not run the script.
> >>
> >> The reason because I did not check the user until I run the script and
> >> have
> >> to check it and find the problem.
> >>
> >> Thanks
> >>
> >> Yifei
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:7CFEFF81-1EAD-456A-A5FC-247E9D0B7418@.microsoft.com...
> >> > Hi
> >> >
> >> > You may want to try
> >> >
> >> > set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
> >> > ''.'' +
> >> > QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM ' + @.db +
> >> > '.INFORMATION_SCHEMA.ROUTINES ' +
> >> > 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >> >
> >> > Although if that gives you thre right view you may get nothing back as
> >> > the
> >> > OBJECT_ID function could cause problems, you may therefore want to
> >> > resort
> >> > to
> >> > using sysobjects.
> >> >
> >> > John
> >> >
> >> >
> >> >
> >> >
> >> > "Yifei Jiang" wrote:
> >> >
> >> >> Hi, All
> >> >>
> >> >> Recently, I was running a script in sql 2000 on window 2000 server
> >> >> like:
> >> >>
> >> >> declare @.sql nvarchar(4000)
> >> >> declare @.db sysname ; set @.db = DB_NAME()
> >> >> declare @.u sysname ; set @.u = QUOTENAME('db_executor')
> >> >>
> >> >> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) +
> >> >> ''.''
> >> >> +
> >> >> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> >> >> INFORMATION_SCHEMA.ROUTINES ' +
> >> >> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
> >> >>
> >> >> exec master.dbo.xp_execresultset @.sql,@.db
> >> >>
> >> >> to grant stored procedures execution permition to role, after running
> >> >> the
> >> >> script, the privilige is grant it to that role, but I got very strange
> >> >> things happened on sql server.
> >> >>
> >> >> 1. Master database get Information_Shema as a user. dbaccess "via
> >> >> group
> >> >> membership"
> >> >> 2.Master database got system_function_schema as a user , dbaccess "via
> >> >> group
> >> >> membership"
> >> >> 3.guest user show in every database in the instance, dbaccess "via
> >> >> group
> >> >> membership"
> >> >> if I delete guest account, show me message "the user is not in
> >> >> database", sp_helpuser did not show guest user, enterprice manager
> >> >> show
> >> >> it
> >> >> as a user.
> >> >> 4. run sp_grantdbaccess guest. the guest get permitted to use
> >> >> database. I
> >> >> delete it successful, however, a couple minutes later, it appears in
> >> >> the
> >> >> db
> >> >> as dbaccess via group membership.
> >> >>
> >> >> it is really painful for me. could anyone give me a hint to fix the
> >> >> problem?
> >> >>
> >> >> any help is appreciated.
> >> >>
> >> >> Thanks
> >> >>
> >> >> Yifei
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >
>
>

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

Tuesday, March 20, 2012

BUG: Internal Server Error

Hi all!
MSSQL 7.0 and MSSQL2000 Server.
When running query:
create table t (id int primary key identity(1,1), f int, u varchar not null
default USER)
GO
create view v
as
select id, f
from t
where u = USER
GO
insert v (f)
select f
from v
group by f
GO
--
then get error:
Server: Msg 8624, Level 16, State 9, Line 1
Internal SQL Server error.
It's a bug?hi
just try this way
create table t
(id int primary key identity(1,1),
f int, u varchar not null
default 'USER')
GO
create view v
as
select id, f
from t
where u = 'USER'
GO
insert v (f) select f from t group by f
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Roman S. Golubin" wrote:

> Hi all!
> MSSQL 7.0 and MSSQL2000 Server.
> When running query:
> --
> create table t (id int primary key identity(1,1), f int, u varchar not nul
l
> default USER)
> GO
> create view v
> as
> select id, f
> from t
> where u = USER
> GO
> insert v (f)
> select f
> from v
> group by f
> GO
> --
> then get error:
> --
> Server: Msg 8624, Level 16, State 9, Line 1
> Internal SQL Server error.
>
> It's a bug?
>
>|||Hi, Chandra!

> just try this way
> create table t
> (id int primary key identity(1,1),
> f int, u varchar not null
> default 'USER')
> GO
BOL. USER.
-> Use USER to return the current user's database username

BUG: Internal Server Error

Hi all!
MSSQL 7.0 and MSSQL2000 Server.
When running query:
create table t (id int primary key identity(1,1), f int, u varchar not null
default USER)
GO
create view v
as
select id, f
from t
where u = USER
GO
insert v (f)
select f
from v
group by f
GO
then get error:
Server: Msg 8624, Level 16, State 9, Line 1
Internal SQL Server error.
It's a bug?
hi
just try this way
create table t
(id int primary key identity(1,1),
f int, u varchar not null
default 'USER')
GO
create view v
as
select id, f
from t
where u = 'USER'
GO
insert v (f) select f from t group by f
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Roman S. Golubin" wrote:

> Hi all!
> MSSQL 7.0 and MSSQL2000 Server.
> When running query:
> --
> create table t (id int primary key identity(1,1), f int, u varchar not null
> default USER)
> GO
> create view v
> as
> select id, f
> from t
> where u = USER
> GO
> insert v (f)
> select f
> from v
> group by f
> GO
> --
> then get error:
> --
> Server: Msg 8624, Level 16, State 9, Line 1
> Internal SQL Server error.
>
> It's a bug?
>
>
|||Hi, Chandra!

> just try this way
> create table t
> (id int primary key identity(1,1),
> f int, u varchar not null
> default 'USER')
> GO
BOL. USER.
-> Use USER to return the current user's database username

Bug: "ALTER TABLE *** WITH NOCHECK" does not work for SQL Server 2005

I wanted to turn off 'Enforce Foreign Key Constrain' using SQL. However, it did not work on the SQL Server 2005 database.

For example, running following SQL in Management Studio, and the result showed that 'Enforce Foreign Key Constrain' property for the newly created constraint was still enabled.

ALTER TABLE [dbo].[Tags] WITH NOCHECK ADD CONSTRAINT [FK_Tags_ChannelID] FOREIGN KEY([ChannelID]) REFERENCES [dbo].[Channels] ([ID])

Does anybody know any way to get around with this problem. It is quite important for my software.

Ps. with the SP1, problem remains the same.

ADD CONSTRAINT WITH NOCHECK is for not to fire the constraint against existing data in the table, when the constraint is created. If you want to disable the constraint you will need to run this statement as well:

ALTER TABLE dbo.Tags NOCHECK CONSTRAINT FK_Tags_ChannelID

"WITH NOCHECK" and "NOCHECK" have different meanings here even though they look similar.

|||

Thanks, That works very well.

What surprised me is that when I tried to generate the SQL script for a table with the disabled constraint using Management Tools, it did not give the other SQL statement. That is why I thought it is a bug of SQL Server 2005. Well, it IS a bug for the management tool then.

Thanks for the help

Monday, March 19, 2012

Bug with InScope() and some custom code

Hi guys,

i was developing some custom code to do a running total in a matrix, and i have noticed some odd behaviour with the InScope function. I am doing year on year reporting, so i have two row groups on my matrix: the first is on month (matrix2_Calendar_Month), the second on year (matrix2_Calendar_Year).

I needed to total the number of days covered by the months i was reporting on, so i wrote some very standard code to do this, along with an expression in that column of the matrix:

=IIf(
InScope("matrix2_Calendar_Year"),
CStr( Round( Sum(Fields!Sales.Value / (24 * Code.AddDays( CStr(Fields!Calendar_Month.Value), CInt(Fields!Calendar_Year.Value))), 2)) ,
Code.getBounds()
)

Code.AddDays() calculates and returns the number of days in the month of that year on that row. Code.getBounds simply returns the lower and upper bounds of the array, plus its contents (so i can inspect them). This is what is returned in the report:


Month / Year		Sales	Capacity	% Capacity	Avg $/h
February	2006	3842	7706	49.86%	2.86
February	2007	0	0	0.00%	0
March	2006	4949	8692	56.94%	3.33
March	2007	0	0	0.00%	0
April	2006	5160	8154	63.28%	3.58
April	2007	0	0	0.00%	0
May	2006	3309	8348	39.64%	2.22
May	2007	0	0	0.00%	0
Total		17259	32900	52.46%	0-8*28,28,31,31,30,30,31,31,28

If you look at the output in the total row, you will see that Code.AddDays() has been called one extra time at the end, with Feb 2006 as its parameters, thus adding an extra 28 days to the running total. Why is Code.AddDays called on the total row, when i should be out of the scope of both the row groups? (Note: this happens for whichever row group i use in the InScope check in the expression).

Here is the custom code used for all this:

Dim numDays()

Public Function AddDays(ByVal month As String, ByVal year As Integer) As Integer
Dim thisMonth As String

Dim upper As Integer
upper = 0
On Error Resume Next
upper = UBound(numDays) + 1
ReDim Preserve numDays(upper)

thisMonth = CStr(year) & "-" & month & "-01"
numDays(upper) = DateDiff("d", CDate(thisMonth), DateAdd("m", 1, CDate(thisMonth)))
AddDays = numDays(upper)
End Function

Public Function TotalDays() As Integer
Dim lower As Integer
Dim upper As Integer

lower = 0
upper = 0
On Error Resume Next
lower = LBound(numDays)
upper = UBound(numDays)

TotalDays = 0
Dim ii As Integer
For ii = lower To upper
TotalDays = TotalDays + CInt(numDays(ii))
Next
End Function

public function getBounds() as string
getBounds = Cstr(LBound(numDays)) & "-" & CStr(UBound(numDays)) & "*" & Join(numDays, ",")
end function

sluggy

This has nothing to do with InScope().

The AddDays function is used within a IIF function call. IIF (like any other VB function call) evaluates all arguments before the function is invoked. Hence, the AddDays function is invoked in all cases.

Suggestion:
1. change the expression in the matrix cell to:
=Code.MyCalculation(InScope("matrix2_Calendar_Year"))

2. add a custom code function MyCalculation which uses IF - ELSE blocks to call the other custom code functions. Only in the case of using the conditional IF statement (instead of the IIF function) you will achieve the desired effect.

-- Robert

|||

Doh, thanks Robert, i should have known that I last did VB a few years ago, i've obviously forgotten a bit :)

sluggy

|||

No problem. I'm glad I could help resolving your issue and there is no bug in InScope :)

-- Robert

bug when running sp at package

Dear all

i have a stored procedure which run in package. I find out that the stored

procedure stop at the running after 1 seconds, but the pacakage reports the

success.

this stored procedure can run at query analyzer normally.

I tried to adjust the command time-out at the execute sql task properties

object to 9999 or 0 or 1800. However, I find out it is useless to extend the

executive time.

The case happens in two sql server 2000 which is installed in win 2k server

and nt server 5.0. So, I guess it is a sql server bug.

Please give me some suggestion. whether a sql server has this bug? how can i

fix it?

thx
alex

my code is as follow.

CREATE PROCEDURE usp_UpdateRGNUPC
AS
declare @.upcVar varchar(13), @.skuVar varchar(9), @.strStore varchar(7), @.tempSql varchar(1000)

declare varTempShop cursor
local
static
FOR select distinct Shop from strmst
OPEN varTempShop
fetch next from varTempShop into @.strStore
while @.@.FETCH_STATUS = 0
begin
declare varTemp cursor
FOR SELECT r.upc, r.sku
FROM rgnupc r
INNER JOIN strmst s ON r.rgnid = s.rgnid
WHERE s.Shop = @.strStore
OPEN varTemp
fetch next from varTemp into @.upcVar, @.skuVar
while @.@.FETCH_STATUS = 0
begin
set @.tempSql = 'select * from ' + @.strStore + '.dbo.invupc where upc = ''' + @.upcVar + ''''
exec(@.tempSql)

if (@.@.rowcount>0)
begin
set @.tempSql = 'Update ' + @.strStore + '.dbo.invupc set sku = ''' + @.skuVar + ''' where upc = ''' + @.upcVar + ''''
exec(@.tempSql)
end
else
begin
set @.tempSql = 'Insert into ' + @.strStore + '.dbo.invupc (upc, sku) values (''' + @.upcVar + ''',''' + @.skuVar + ''')'
exec(@.tempSql)
end

fetch next from varTemp into @.upcVar, @.skuVar
end
close varTemp
deallocate varTemp

fetch next from varTempShop into @.strStore
end
close varTempShop
deallocate varTempShop
GO

one sql server 2000 is service pack 3. another is service pack 4.

Alex

Thursday, March 8, 2012

Bug in Report Server?

I am running a report using RS2005. It connects to a web service using the XML data source. It works correctly in my development environment. When I put it up on the server and run the report, I get the error message:

An error occurred during rendering of the report.

Item has already been added. Key in dictionary: '1' Key being added: '1'

If I run the web service from the server, it returns correct data, so I know error is not coming from my web service. The only answer I can think of is that there is a bug in the Report Server's or XML Data Source's code.

Has anyone else seen this problem?

Thank you,

Jessica

Hi Jessica, could you please repro the bug and send me most recent RS logs to almineev. At microsoft. Com.

RS logs are located at location similar to this one: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles

|||

Jessica, did you ever find out the solution for your problem? I am experiencing a similar issue now.

Thanks,

James

|||It hapened to me, more or less the same.

For what I know it can be a problem with the default values of the

Report Parameters... they should not be originated by a query.

I had this problem and puting this as static or calculated values solved the issue...

Not the best way as by default the user's are not getting the data shown, but that's a solution.

Hope Microsoft solves the issue.. as this is a serious bug.

Regards,

Jose|||

Our problem was two report items on top of each other. Once we moved one of them over by just a little bit, the error went away.

I'm assuming it was something in the server code that wasn't handling the scenario of the same positional coordinates being inserted into a hash table, but that's just a guess.

We solved it by recreating the report one item at a time, uploading after each item. When we layered the second item on top of the first, that's when we received that message.

Good luck with everyone else trying to get this to work!

-Jessica

Bug in Report Server?

An error occurred during rendering of the report.

Item has already been added. Key in dictionary: '1' Key being added: '1'

Has anyone else seen this problem?

Thank you,

Jessica

Hi Jessica, could you please repro the bug and send me most recent RS logs to almineev. At microsoft. Com.

RS logs are located at location similar to this one: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles

|||

Jessica, did you ever find out the solution for your problem? I am experiencing a similar issue now.

Thanks,

James

|||It hapened to me, more or less the same.

For what I know it can be a problem with the default values of the

Report Parameters... they should not be originated by a query.

I had this problem and puting this as static or calculated values solved the issue...

Not the best way as by default the user's are not getting the data shown, but that's a solution.

Hope Microsoft solves the issue.. as this is a serious bug.

Regards,

Jose|||

Our problem was two report items on top of each other. Once we moved one of them over by just a little bit, the error went away.

I'm assuming it was something in the server code that wasn't handling the scenario of the same positional coordinates being inserted into a hash table, but that's just a guess.

We solved it by recreating the report one item at a time, uploading after each item. When we layered the second item on top of the first, that's when we received that message.

Good luck with everyone else trying to get this to work!

-Jessica

Bug in Report Server?

An error occurred during rendering of the report.

Item has already been added. Key in dictionary: '1' Key being added: '1'

Has anyone else seen this problem?

Thank you,

Jessica

Hi Jessica, could you please repro the bug and send me most recent RS logs to almineev. At microsoft. Com.

RS logs are located at location similar to this one: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles

|||

Jessica, did you ever find out the solution for your problem? I am experiencing a similar issue now.

Thanks,

James

|||It hapened to me, more or less the same.
For what I know it can be a problem with the default values of the Report Parameters... they should not be originated by a query.
I had this problem and puting this as static or calculated values solved the issue...
Not the best way as by default the user's are not getting the data shown, but that's a solution.
Hope Microsoft solves the issue.. as this is a serious bug.
Regards,
Jose|||

Our problem was two report items on top of each other. Once we moved one of them over by just a little bit, the error went away.

I'm assuming it was something in the server code that wasn't handling the scenario of the same positional coordinates being inserted into a hash table, but that's just a guess.

We solved it by recreating the report one item at a time, uploading after each item. When we layered the second item on top of the first, that's when we received that message.

Good luck with everyone else trying to get this to work!

-Jessica

Bug in Report Server?

An error occurred during rendering of the report.

Item has already been added. Key in dictionary: '1' Key being added: '1'

Has anyone else seen this problem?

Thank you,

Jessica

Hi Jessica, could you please repro the bug and send me most recent RS logs to almineev. At microsoft. Com.

RS logs are located at location similar to this one: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles

|||

Jessica, did you ever find out the solution for your problem? I am experiencing a similar issue now.

Thanks,

James

|||It hapened to me, more or less the same.

For what I know it can be a problem with the default values of the

Report Parameters... they should not be originated by a query.

I had this problem and puting this as static or calculated values solved the issue...

Not the best way as by default the user's are not getting the data shown, but that's a solution.

Hope Microsoft solves the issue.. as this is a serious bug.

Regards,

Jose|||

Our problem was two report items on top of each other. Once we moved one of them over by just a little bit, the error went away.

I'm assuming it was something in the server code that wasn't handling the scenario of the same positional coordinates being inserted into a hash table, but that's just a guess.

We solved it by recreating the report one item at a time, uploading after each item. When we layered the second item on top of the first, that's when we received that message.

Good luck with everyone else trying to get this to work!

-Jessica

Bug in DBCC SQLPerf ?

I've noticed that running DBCC SQLPERF(Threads) shows the login name
truncated to 10 characters, which is a tiny problem if you use Windows
authentication, as all you seem to get is the first 10 characters of the
domain name. This leaves you guessing as to which pesky user is using all
that resource. Anybody know of any useful workarounds?
Thanks.Having thought about it a little harder, I can get the desired result by
loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
joining them on their respective SPIDs. I suppose the only danger is that
the SPIDS change user between the two calls.
"BarryC" wrote:
> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> truncated to 10 characters, which is a tiny problem if you use Windows
> authentication, as all you seem to get is the first 10 characters of the
> domain name. This leaves you guessing as to which pesky user is using all
> that resource. Anybody know of any useful workarounds?
> Thanks.|||Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
have all information in a documented table (sysprocesses)?
select spid as [Spid],
kpid as [Thread ID],
loginame as [LoginName],
status as [Status],
physical_io as [IO],
cpu as [CPU],
memusage as [MemUsage]
from master.dbo.sysprocesses
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
> Having thought about it a little harder, I can get the desired result by
> loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> joining them on their respective SPIDs. I suppose the only danger is that
> the SPIDS change user between the two calls.
> "BarryC" wrote:
>> I've noticed that running DBCC SQLPERF(Threads) shows the login name
>> truncated to 10 characters, which is a tiny problem if you use Windows
>> authentication, as all you seem to get is the first 10 characters of the
>> domain name. This leaves you guessing as to which pesky user is using
>> all
>> that resource. Anybody know of any useful workarounds?
>> Thanks.|||A perfectly reasonable question to which I'm sure there is an excellent
answer. Just makes me feel like a man, I guess.
Thanks, though - you're quite right of course...
"Gert E.R. Drapers" wrote:
> Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
> have all information in a documented table (sysprocesses)?
> select spid as [Spid],
> kpid as [Thread ID],
> loginame as [LoginName],
> status as [Status],
> physical_io as [IO],
> cpu as [CPU],
> memusage as [MemUsage]
> from master.dbo.sysprocesses
>
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright Â© SQLDev.Net 1991-2005 All rights reserved.
> "BarryC" <BarryC@.discussions.microsoft.com> wrote in message
> news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
> > Having thought about it a little harder, I can get the desired result by
> > loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> > joining them on their respective SPIDs. I suppose the only danger is that
> > the SPIDS change user between the two calls.
> >
> > "BarryC" wrote:
> >
> >> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> >> truncated to 10 characters, which is a tiny problem if you use Windows
> >> authentication, as all you seem to get is the first 10 characters of the
> >> domain name. This leaves you guessing as to which pesky user is using
> >> all
> >> that resource. Anybody know of any useful workarounds?
> >>
> >> Thanks.
>
>

Bug in DBCC SQLPerf ?

I've noticed that running DBCC SQLPERF(Threads) shows the login name
truncated to 10 characters, which is a tiny problem if you use Windows
authentication, as all you seem to get is the first 10 characters of the
domain name. This leaves you guessing as to which pesky user is using all
that resource. Anybody know of any useful workarounds?
Thanks.
Having thought about it a little harder, I can get the desired result by
loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
joining them on their respective SPIDs. I suppose the only danger is that
the SPIDS change user between the two calls.
"BarryC" wrote:

> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> truncated to 10 characters, which is a tiny problem if you use Windows
> authentication, as all you seem to get is the first 10 characters of the
> domain name. This leaves you guessing as to which pesky user is using all
> that resource. Anybody know of any useful workarounds?
> Thanks.
|||Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
have all information in a documented table (sysprocesses)?
select spid as [Spid],
kpid as [Thread ID],
loginame as [LoginName],
status as [Status],
physical_io as [IO],
cpu as [CPU],
memusage as [MemUsage]
from master.dbo.sysprocesses
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...[vbcol=seagreen]
> Having thought about it a little harder, I can get the desired result by
> loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> joining them on their respective SPIDs. I suppose the only danger is that
> the SPIDS change user between the two calls.
> "BarryC" wrote:
|||A perfectly reasonable question to which I'm sure there is an excellent
answer. Just makes me feel like a man, I guess.
Thanks, though - you're quite right of course...
"Gert E.R. Drapers" wrote:

> Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
> have all information in a documented table (sysprocesses)?
> select spid as [Spid],
> kpid as [Thread ID],
> loginame as [LoginName],
> status as [Status],
> physical_io as [IO],
> cpu as [CPU],
> memusage as [MemUsage]
> from master.dbo.sysprocesses
>
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "BarryC" <BarryC@.discussions.microsoft.com> wrote in message
> news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
>
>

Bug in DBCC SQLPerf ?

I've noticed that running DBCC SQLPERF(Threads) shows the login name
truncated to 10 characters, which is a tiny problem if you use Windows
authentication, as all you seem to get is the first 10 characters of the
domain name. This leaves you guessing as to which pesky user is using all
that resource. Anybody know of any useful workarounds?
Thanks.Having thought about it a little harder, I can get the desired result by
loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
joining them on their respective SPIDs. I suppose the only danger is that
the SPIDS change user between the two calls.
"BarryC" wrote:

> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> truncated to 10 characters, which is a tiny problem if you use Windows
> authentication, as all you seem to get is the first 10 characters of the
> domain name. This leaves you guessing as to which pesky user is using all
> that resource. Anybody know of any useful workarounds?
> Thanks.|||Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
have all information in a documented table (sysprocesses)?
select spid as [Spid],
kpid as [Thread ID],
loginame as [LoginName],
status as [Status],
physical_io as [IO],
cpu as [CPU],
memusage as [MemUsage]
from master.dbo.sysprocesses
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...[vbcol=seagreen]
> Having thought about it a little harder, I can get the desired result by
> loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> joining them on their respective SPIDs. I suppose the only danger is that
> the SPIDS change user between the two calls.
> "BarryC" wrote:
>|||A perfectly reasonable question to which I'm sure there is an excellent
answer. Just makes me feel like a man, I guess.
Thanks, though - you're quite right of course...
"Gert E.R. Drapers" wrote:

> Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
> have all information in a documented table (sysprocesses)?
> select spid as [Spid],
> kpid as [Thread ID],
> loginame as [LoginName],
> status as [Status],
> physical_io as [IO],
> cpu as [CPU],
> memusage as [MemUsage]
> from master.dbo.sysprocesses
>
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "BarryC" <BarryC@.discussions.microsoft.com> wrote in message
> news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
>
>

Bug in DATEDIFF?

Running on Sql Server 2000.
I was curious if there is a bug in DATEDIFF when dealing with years. The
months seem to work correctly, but the Years are all over the place.
Here is some examples:
select
DATEDIFF(YEAR,'11/01/04','05/01/05'),DATEDIFF(MONTH,'11/01/04','05/01/05')
select
DATEDIFF(YEAR,'08/01/98','11/01/99'),DATEDIFF(MONTH,'08/01/98','11/01/99')
select
DATEDIFF(YEAR,'11/01/99','05/01/01'),DATEDIFF(MONTH,'11/01/99','05/01/01')
-- --
1 6 Should be 0 and 6
(1 row(s) affected)
-- --
1 15 Should be 0 and 15
(1 row(s) affected)
-- --
2 18 Should be 1 and 18
(1 row(s) affected)
Am I missing something?
Thanks,
Tom(a) never use such ambiguous date formats. I have no idea whether that
first date is November 1 2004, January 11 2004, January 4th 1911, or January
4th 2011. The server you are on is maybe guessing right, but what if you
run this code on a different server with different SET DATEFORMAT or regionl
settings?
(b) DATEDIFF(YEAR, measures the number of years that have passed. So,
DATEDIFF(YEAR, '20051231','20060101') = 1
DATEDIFF(YEAR, '20050101','20061231') = 1
This result is expected, and is not a bug. Basically, you can pretend that
it strips the month/day parts from the date (and time if that is supplied as
well), and just subtracts the integer derived from DATEPART(YEAR,
<dateValue> ).
If you're trying to calculate age based on birthdays, it's a little more
complex than a simple DATEDIFF.
http://www.aspfaq.com/2233
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e5bgY%23UFGHA.2204@.TK2MSFTNGP10.phx.gbl...
> Running on Sql Server 2000.
> I was curious if there is a bug in DATEDIFF when dealing with years. The
> months seem to work correctly, but the Years are all over the place.
> Here is some examples:
> select
> DATEDIFF(YEAR,'11/01/04','05/01/05'),DATEDIFF(MONTH,'11/01/04','05/01/05')
> select
> DATEDIFF(YEAR,'08/01/98','11/01/99'),DATEDIFF(MONTH,'08/01/98','11/01/99')
> select
> DATEDIFF(YEAR,'11/01/99','05/01/01'),DATEDIFF(MONTH,'11/01/99','05/01/01')
> -- --
> 1 6 Should be 0 and 6
> (1 row(s) affected)
>
> -- --
> 1 15 Should be 0 and 15
> (1 row(s) affected)
>
> -- --
> 2 18 Should be 1 and 18
> (1 row(s) affected)
> Am I missing something?
> Thanks,
> Tom
>|||There is no bug, it is working as it was meant to. All it does, I believe,
is take the year into account, does not do anything clever with the
respective position within each year.
"tshad" wrote:

> Running on Sql Server 2000.
> I was curious if there is a bug in DATEDIFF when dealing with years. The
> months seem to work correctly, but the Years are all over the place.
> Here is some examples:
> select
> DATEDIFF(YEAR,'11/01/04','05/01/05'),DATEDIFF(MONTH,'11/01/04','05/01/05')
> select
> DATEDIFF(YEAR,'08/01/98','11/01/99'),DATEDIFF(MONTH,'08/01/98','11/01/99')
> select
> DATEDIFF(YEAR,'11/01/99','05/01/01'),DATEDIFF(MONTH,'11/01/99','05/01/01')
> -- --
> 1 6 Should be 0 and 6
> (1 row(s) affected)
>
> -- --
> 1 15 Should be 0 and 15
> (1 row(s) affected)
>
> -- --
> 2 18 Should be 1 and 18
> (1 row(s) affected)
> Am I missing something?
> Thanks,
> Tom
>
>|||>> Am I missing something?
Yes, you are assuming DATEDIFF function on two dates returns the completed
year, month etc, which is not accurate.
DATEDIFF simply returns the number of date and time boundaries crossed
between two specified dates. And the results you got are consistent.
Anith|||select DATEDIFF(YEAR,'20051231','20060101'),
DATEDIFF(YEAR,'20050101','20051231')
Returns 1 and 0 even though in days, the first one is 1 day and the second
364 days.
DateDiff with years returns in reality the number of times January 1 is
"crossed".
This is what BOL has to say:
"Returns the number of date and time boundaries crossed between two
specified dates."
This is true for Datediff with years, months, w

s, days...
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e5bgY%23UFGHA.2204@.TK2MSFTNGP10.phx.gbl...
> Running on Sql Server 2000.
> I was curious if there is a bug in DATEDIFF when dealing with years. The
> months seem to work correctly, but the Years are all over the place.
> Here is some examples:
> select
> DATEDIFF(YEAR,'11/01/04','05/01/05'),DATEDIFF(MONTH,'11/01/04','05/01/05')
> select
> DATEDIFF(YEAR,'08/01/98','11/01/99'),DATEDIFF(MONTH,'08/01/98','11/01/99')
> select
> DATEDIFF(YEAR,'11/01/99','05/01/01'),DATEDIFF(MONTH,'11/01/99','05/01/01')
> -- --
> 1 6 Should be 0 and 6
> (1 row(s) affected)
>
> -- --
> 1 15 Should be 0 and 15
> (1 row(s) affected)
>
> -- --
> 2 18 Should be 1 and 18
> (1 row(s) affected)
> Am I missing something?
> Thanks,
> Tom
>|||"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:B4D02445-41D7-43CC-B570-DF88B181FF3C@.microsoft.com...
> There is no bug, it is working as it was meant to. All it does, I
> believe,
> is take the year into account, does not do anything clever with the
> respective position within each year.
If that is the case, it is relatively useless.
I am not actually passing the literal date as shown, but 2 smalldatetime
fields from my table.
But 6 months does not equal a year and 18 months doesn't equal a 2 years.
If what you are saying is true about just looking at the year, why then does
not the MONTH calculation look only at the months. It does calculate
correctly.
Here is a calculation that is 7 days apart, so months should be 0 and it is:
select
DATEDIFF(YEAR,'11/01/99','11/05/99'),DATEDIFF(MONTH,'11/01/99','11/08/99')
-- --
0 0
(1 row(s) affected)
> "tshad" wrote:
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uLu6kFVFGHA.3120@.TK2MSFTNGP10.phx.gbl...
> (a) never use such ambiguous date formats. I have no idea whether that
> first date is November 1 2004, January 11 2004, January 4th 1911, or
> January 4th 2011. The server you are on is maybe guessing right, but what
> if you run this code on a different server with different SET DATEFORMAT
> or regionl settings?
>
I am not using ambiguous formats. This was just for demonstration. I am
actually using fields from my table:
DATEDIFF(YEAR,FromDate,ToDate) (FromDate and ToDate are DateTime
fields)

> (b) DATEDIFF(YEAR, measures the number of years that have passed. So,
> DATEDIFF(YEAR, '20051231','20060101') = 1
> DATEDIFF(YEAR, '20050101','20061231') = 1
> This result is expected, and is not a bug. Basically, you can pretend
> that it strips the month/day parts from the date (and time if that is
> supplied as well), and just subtracts the integer derived from
> DATEPART(YEAR, <dateValue> ).
>
If not a bug, definately makes no sense to me as to what usefulness it would
be.

> If you're trying to calculate age based on birthdays, it's a little more
> complex than a simple DATEDIFF.
> http://www.aspfaq.com/2233
>
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e5bgY%23UFGHA.2204@.TK2MSFTNGP10.phx.gbl...
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OWHP%23HVFGHA.3892@.TK2MSFTNGP12.phx.gbl...
> Yes, you are assuming DATEDIFF function on two dates returns the completed
> year, month etc, which is not accurate.
> DATEDIFF simply returns the number of date and time boundaries crossed
> between two specified dates. And the results you got are consistent.
No.
I would agree if MONTH worked the same way, but as I mentioned in another
response, but MONTH does work correctly.
What you should get is 0 years for 0-11 months and 1 for 1-12 months.
Subtracting the years makes no sense - at least as far as I can fathom.
Thanks,
Tom
> --
> Anith
>|||> But 6 months does not equal a year and 18 months doesn't equal a 2 years.
If you're interested in the number of months that have passed, then use
MONTH instead of YEAR as the argument to DATEDIFF.

> Here is a calculation that is 7 days apart, so months should be 0 and it
> is:
> select
> DATEDIFF(YEAR,'11/01/99','11/05/99'),DATEDIFF(MONTH,'11/01/99','11/08/99')
I'm not sure I understand your argument. Of course both return 0 -- you
have not crossed into a new month, nor have you crossed into a new year.
How about :
SELECT DATEDIFF(MONTH, '19991031', '19991101');
SELECT DATEDIFF(MONTH, '19991001', '19991130');
SELECT DATEDIFF(MONTH, '19991231', '20000101');
Gee, all of those return 1. In the first case, only 1 day has passed; in
the second case, almost 2 months have passed; and, in the third case, we've
crossed into a new year.
Should SQL Server guess what each one of us would consider a threshold for
the difference in those dates being anything other than 1 month? Do you
round up starting at 15 days, 28 days, 30 days, 31 days? What if it's
February and you're crossing a leap year? What about Daylight Savings Time?
What if the groundhog does't see his shadow? DATEDIFF is based on the
simple parts of a date, it is not a mind reader.|||(2005 - 2004) = 1
(2004-2005) = -1
(2005 - 2005) = 0
It is just working with the integer year numbers, no fancy date
calculations. What are you actually needing?
William Stacey [MVP]
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23pD85lVFGHA.216@.TK2MSFTNGP15.phx.gbl...
> My mistake on the MONTH ( I was actually using Days in my example on
> Months)
> But I see no use for the YEAR DATEDIFF ( I am sure there is some use, but
> to say 1 month and 15 months are both 1 year makes no sense)
> Tom
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:%232DO9JVFGHA.1288@.TK2MSFTNGP09.phx.gbl...
>

Thursday, March 22, 2012

bugs, Information_schema appeared as a user in master database

bugs, Information_schema appeared as a user in master database

bugs, Information_schema appeared as a user in master database

BugCheck in SQL Server 2000 SP3a?

BugCheck in SQL Server 2000 SP3a?

Tuesday, March 20, 2012

BUG: Internal Server Error

BUG: Internal Server Error

Bug: "ALTER TABLE *** WITH NOCHECK" does not work for SQL Server 2005

Monday, March 19, 2012

Bug with InScope() and some custom code

bug when running sp at package

Thursday, March 8, 2012

Bug in Report Server?

Bug in Report Server?

Bug in Report Server?

Bug in Report Server?

Bug in DBCC SQLPerf ?

Bug in DBCC SQLPerf ?

Bug in DBCC SQLPerf ?

Bug in DATEDIFF?

Boot.ini file

Blog Archive

About Me