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: SQLXML 3.0 attempts to update Timestamp column

When submitting an update gram generated from a dataset containing a table
having a timestamp column, SQLXML 3.0 will generate an UPDATE statement
attempting to set the timestamp column even after the timestamp column is
marked in the XSD with the attribute sql:datatype="timestamp". This of
course results in an error from SQL Server.
If the /diffgr:diffgram/me:DataSet/me:TABLE_NAME/me:TimeStamp node is
present in the diffgram and the
/diffgr:diffgram/diffgr:before/me:TABLE_NAME/me:TimeStamp node is removed.
Then SQLXML attempts to set the value of TimeStamp to the value from the
/diffgr:diffgram/me:DataSet block.
If TimeStamp node is not present in either block, then SQLXML attempts to
set the value of the TimeStamp column to NULL.
I am attempting to remove all but the primary key columns from the
/diffgr:diffgram/diffgr:before block as suggested as a work around by
Microsoft to other bugs and this is clearly not going to work with tables
having TimeStamp columns.
This bug is a showstopper for me and I am going to have to switch to a
different update mechanism.
AND ANOTHER BUG:
SQLXML 3.0 also applies exact constraints to floating point numbers when
generating update statements. This does not work all the time due to the
vagaries of string to floating point representation conversions.Hello Kevin,
Thank you for your feedback, and this has been routed to the proper
channel. In the meantime, I also encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
To be able to choose the updating logic, you may choose to use updategrams
instead of diffgrams:
Updating Data by Using XML Updategrams
<http://msdn.microsoft.com/library/d...n-us/sqlxml3/ht
m/updategram_0i5v.asp>
This lets you choose the updating logic to be sent to SQL server:
Handling Database Concurrency Issues in Updategrams
<http://msdn.microsoft.com/library/d...n-us/sqlxml3/ht
m/updategram_7tir.asp>
You will see that there is a way to use Primary Key and Timestamp (Specify
the timestamp column (if available) in the <before> block.).
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||I am getting the DiffGram from a changed DataSet. UpdateGram is not an
option given by DataSet.GetChanges.WriteXml.
Does MSoft have a DiffGram to UpdateGram stylesheet laying around somewhere?
My DataSet has over 30 tables having over 1000 columns. At first glance
this DiffGram approach seemed like a miracle: 5 lines of code to perform
the updates using a stylesheet generate by 100 lines of code. Wiring up
data adapters and getting all of that correct is at least another order of
magnitude and possibly 3 orders of magnitude more code. (The generated
dataset code is 86,000 lines).
It looks like I am going to have to see how I can minimally tweak the
generated Adapters that I am not using at this moment to leverage the
generated update code. I hate to do this because all future DB changes are
going to result in a very error-prone code management process.
""privatenews"" <petery@.online.microsoft.com> wrote in message
news:gHQJ9w0bGHA.2240@.TK2MSFTNGXA01.phx.gbl...
> Hello Kevin,
> Thank you for your feedback, and this has been routed to the proper
> channel. In the meantime, I also encourage you submit via the link below
> http://lab.msdn.microsoft.com/produ...ck/default.aspx
> To be able to choose the updating logic, you may choose to use updategrams
> instead of diffgrams:
> Updating Data by Using XML Updategrams
> <[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/ht[/ur
l]
> m/updategram_0i5v.asp>
> This lets you choose the updating logic to be sent to SQL server:
> Handling Database Concurrency Issues in Updategrams
> <[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/ht[/ur
l]
> m/updategram_7tir.asp>
> You will see that there is a way to use Primary Key and Timestamp
> ("Specify
> the timestamp column (if available) in the <before> block.").
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Kevin,
Thank you for your comments on this feature. To the best of my knowledge,
there is currently no stylesheet to transform DiffGram to UpdateGram. You
may need to develop your own transform if it is necessary. I think it shall
be future product enhancement and this has been reported to the proper
channel.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

BUG or not?

I have a case when the same query returns two different results, depending
on the generated exec plan. I managed to reproduce it on different servers
and databases.
Heres the code, the trigger should generate new number max(Broj)+1 but the
result here is 1 because join in the selec max() part returns empty result
set.
If you substitute part in the join on n.prefix = i.prefix with n.prefix =
convert(varchar(10), i.prefix) then the trigger works.
Notice that the convert function doesnt actually do anything, its there as
an example, feel free to use any method to force different exec plan.
if exists(select * from sysobjects where xtype='U' and name='tbl')
drop table tbl
go
create table tbl (
[id] int identity(1,1) not null primary key clustered,
prefix varchar(10) not null,
broj int null
)
go
insert tbl (prefix, broj) values( '2006/01-', 54)
insert tbl (prefix, broj) values( '2006/01-', 53)
insert tbl (prefix, broj) values( '2006/01-', 52)
insert tbl (prefix, broj) values( '2006/01-', 51)
insert tbl (prefix, broj) values( '2006/01-', 50)
insert tbl (prefix, broj) values( '2006/01-', 49)
insert tbl (prefix, broj) values( '2006/01-', 48)
insert tbl (prefix, broj) values( '2006/01-', 47)
insert tbl (prefix, broj) values( '2006/01-', 46)
insert tbl (prefix, broj) values( '2006/01-', 45)
insert tbl (prefix, broj) values( '2006/01-', 44)
insert tbl (prefix, broj) values( '2006/01-', 43)
insert tbl (prefix, broj) values( '2006/01-', 42)
insert tbl (prefix, broj) values( '2006/01-', 41)
insert tbl (prefix, broj) values( '2006/01-', 40)
insert tbl (prefix, broj) values( '2006/01-', 39)
insert tbl (prefix, broj) values( '2006/01-', 38)
insert tbl (prefix, broj) values( '2006/01-', 37)
insert tbl (prefix, broj) values( '2006/01-', 36)
go
if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
drop trigger tr_tbl_ins
go
create trigger tr_tbl_ins on tbl
for insert
as
update tbl
set
broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
n.prefix = i.prefix),0)+1
where
[id] = (select [id] from inserted)
go
insert into tbl (prefix)
values ('2006/01-')
select * from tbl order by broj
-- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
returns NULL valueForgot to add, its for SQL Server 2005, SQL Server 2000 works as
expected...
Marko
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:uqJ6IuJVGHA.736@.TK2MSFTNGP12.phx.gbl...
>I have a case when the same query returns two different results, depending
>on the generated exec plan. I managed to reproduce it on different servers
>and databases.
> Heres the code, the trigger should generate new number max(Broj)+1 but the
> result here is 1 because join in the selec max() part returns empty result
> set.
> If you substitute part in the join on n.prefix = i.prefix with n.prefix =
> convert(varchar(10), i.prefix) then the trigger works.
> Notice that the convert function doesnt actually do anything, its there as
> an example, feel free to use any method to force different exec plan.
>
> if exists(select * from sysobjects where xtype='U' and name='tbl')
> drop table tbl
> go
> create table tbl (
> [id] int identity(1,1) not null primary key clustered,
> prefix varchar(10) not null,
> broj int null
> )
> go
> insert tbl (prefix, broj) values( '2006/01-', 54)
> insert tbl (prefix, broj) values( '2006/01-', 53)
> insert tbl (prefix, broj) values( '2006/01-', 52)
> insert tbl (prefix, broj) values( '2006/01-', 51)
> insert tbl (prefix, broj) values( '2006/01-', 50)
> insert tbl (prefix, broj) values( '2006/01-', 49)
> insert tbl (prefix, broj) values( '2006/01-', 48)
> insert tbl (prefix, broj) values( '2006/01-', 47)
> insert tbl (prefix, broj) values( '2006/01-', 46)
> insert tbl (prefix, broj) values( '2006/01-', 45)
> insert tbl (prefix, broj) values( '2006/01-', 44)
> insert tbl (prefix, broj) values( '2006/01-', 43)
> insert tbl (prefix, broj) values( '2006/01-', 42)
> insert tbl (prefix, broj) values( '2006/01-', 41)
> insert tbl (prefix, broj) values( '2006/01-', 40)
> insert tbl (prefix, broj) values( '2006/01-', 39)
> insert tbl (prefix, broj) values( '2006/01-', 38)
> insert tbl (prefix, broj) values( '2006/01-', 37)
> insert tbl (prefix, broj) values( '2006/01-', 36)
> go
> if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
> drop trigger tr_tbl_ins
> go
> create trigger tr_tbl_ins on tbl
> for insert
> as
> update tbl
> set
> broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
> n.prefix = i.prefix),0)+1
> where
> [id] = (select [id] from inserted)
> go
>
> insert into tbl (prefix)
> values ('2006/01-')
> select * from tbl order by broj
> -- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
> returns NULL value
>|||It sure looks like a bug to me.
The UPDATE in the trigger can be simplified somewhat. There is no
need to include another instance of tbl in the subquery, it can be
written as a corellated subquery. This version did not have the
problem:
update tbl
set
broj = coalesce((select max(n.broj) from tbl n
where n.prefix = tbl.prefix),0)+1
where
[id] = (select [id] from inserted)
Of course you must already realize that the trigger will provide
duplicate values if more than one row is INSERTED at a time. This is
considered a vulnerability, and I think the trigger should have a test
that enforces that there is only one row in INSERTED.
Roy Harvey
Beacon Falls, CT
On Fri, 31 Mar 2006 10:31:01 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:

>I have a case when the same query returns two different results, depending
>on the generated exec plan. I managed to reproduce it on different servers
>and databases.
>Heres the code, the trigger should generate new number max(Broj)+1 but the
>result here is 1 because join in the selec max() part returns empty result
>set.
>If you substitute part in the join on n.prefix = i.prefix with n.prefix =
>convert(varchar(10), i.prefix) then the trigger works.
>Notice that the convert function doesnt actually do anything, its there as
>an example, feel free to use any method to force different exec plan.
>
>if exists(select * from sysobjects where xtype='U' and name='tbl')
> drop table tbl
>go
>create table tbl (
> [id] int identity(1,1) not null primary key clustered,
> prefix varchar(10) not null,
> broj int null
> )
>go
>insert tbl (prefix, broj) values( '2006/01-', 54)
>insert tbl (prefix, broj) values( '2006/01-', 53)
>insert tbl (prefix, broj) values( '2006/01-', 52)
>insert tbl (prefix, broj) values( '2006/01-', 51)
>insert tbl (prefix, broj) values( '2006/01-', 50)
>insert tbl (prefix, broj) values( '2006/01-', 49)
>insert tbl (prefix, broj) values( '2006/01-', 48)
>insert tbl (prefix, broj) values( '2006/01-', 47)
>insert tbl (prefix, broj) values( '2006/01-', 46)
>insert tbl (prefix, broj) values( '2006/01-', 45)
>insert tbl (prefix, broj) values( '2006/01-', 44)
>insert tbl (prefix, broj) values( '2006/01-', 43)
>insert tbl (prefix, broj) values( '2006/01-', 42)
>insert tbl (prefix, broj) values( '2006/01-', 41)
>insert tbl (prefix, broj) values( '2006/01-', 40)
>insert tbl (prefix, broj) values( '2006/01-', 39)
>insert tbl (prefix, broj) values( '2006/01-', 38)
>insert tbl (prefix, broj) values( '2006/01-', 37)
>insert tbl (prefix, broj) values( '2006/01-', 36)
>go
>if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
>drop trigger tr_tbl_ins
>go
>create trigger tr_tbl_ins on tbl
>for insert
>as
>update tbl
>set
> broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
>n.prefix = i.prefix),0)+1
>where
> [id] = (select [id] from inserted)
>go
>
>insert into tbl (prefix)
>values ('2006/01-')
>select * from tbl order by broj
>-- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
>returns NULL value
>|||This is not the real code, it is simplified as musch as possible to show a
point. And, yes, there are ways to fix this but thats not the point. Now,
where should I report this?
MC
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:db6q22529ujosandv3442g81j72e4i2j6q@.
4ax.com...
> It sure looks like a bug to me.
> The UPDATE in the trigger can be simplified somewhat. There is no
> need to include another instance of tbl in the subquery, it can be
> written as a corellated subquery. This version did not have the
> problem:
> update tbl
> set
> broj = coalesce((select max(n.broj) from tbl n
> where n.prefix = tbl.prefix),0)+1
> where
> [id] = (select [id] from inserted)
> Of course you must already realize that the trigger will provide
> duplicate values if more than one row is INSERTED at a time. This is
> considered a vulnerability, and I think the trigger should have a test
> that enforces that there is only one row in INSERTED.
> Roy Harvey
> Beacon Falls, CT
> On Fri, 31 Mar 2006 10:31:01 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
> wrote:
>|||I could see that it was a simplified example.
The way I always did this was to open a case with Microsoft. This
cost some $$$ up front, but it was always refunded in the long run
when it proved to be a bug. Haven't had to do that in some years
though.
Roy
On Fri, 31 Mar 2006 14:21:39 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:

>This is not the real code, it is simplified as musch as possible to show a
>point. And, yes, there are ways to fix this but thats not the point. Now,
>where should I report this?
>
>MC|||> Now,
> where should I report this?
http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message news:uiyBBvLVGHA.2156@.tk2msftngp13.phx.gb
l...
> This is not the real code, it is simplified as musch as possible to show a
> point. And, yes, there are ways to fix this but thats not the point. Now,
> where should I report this?
>
> MC
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:db6q22529ujosandv3442g81j72e4i2j6q@.
4ax.com...
>|||Maybe you should report a bug:
http://lab.msdn.microsoft.com/produ...ck/Default.aspx
ML
http://milambda.blogspot.com/|||MC (marko_culo#@.#yahoo#.#com#) writes:
> I have a case when the same query returns two different results, depending
> on the generated exec plan. I managed to reproduce it on different servers
> and databases.
> Heres the code, the trigger should generate new number max(Broj)+1 but the
> result here is 1 because join in the selec max() part returns empty result
> set.
> If you substitute part in the join on n.prefix = i.prefix with n.prefix =
> convert(varchar(10), i.prefix) then the trigger works.
> Notice that the convert function doesnt actually do anything, its there as
> an example, feel free to use any method to force different exec plan.
Definitely a bug, and you should report it on
http://lab.msdn.microsoft.com/ProductFeedback/ if you haven't already
done so. I tested it on the CTP of SP1 for SQL 2005, and the bug
exists there as well.
The bug appears to related to the virtual inserted table. I rewrote
the MAX query in a way which is more palatable to me:
select max(n.broj)
from tbl n
where exists(select * from inserted i where n.prefix = i.prefix)
However, it still returns NULL.
If I do:
SELECT * INTO #inserted FROM tbl
and then use #inserted in the trigger, I get the correct result.
Note: while the use if SELECT INTO is palatable, since you don't
have to copy the table definition in the trigger, my recommendation
is to use a table variable instead (which only should the columns
actually referenced the trigger). I did use SELECT INTO for a while,
but I found that it gave us performance problems.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I've reported the bug, however I cant seem to check the status. It seems
that part of the site is down at the moment. If you want to look at it, i
created it under 'mculo'
MC
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9797F2B151D23Yazorman@.127.0.0.1...
> MC (marko_culo#@.#yahoo#.#com#) writes:
> Definitely a bug, and you should report it on
> http://lab.msdn.microsoft.com/ProductFeedback/ if you haven't already
> done so. I tested it on the CTP of SP1 for SQL 2005, and the bug
> exists there as well.
> The bug appears to related to the virtual inserted table. I rewrote
> the MAX query in a way which is more palatable to me:
> select max(n.broj)
> from tbl n
> where exists(select * from inserted i where n.prefix = i.prefix)
> However, it still returns NULL.
> If I do:
> SELECT * INTO #inserted FROM tbl
> and then use #inserted in the trigger, I get the correct result.
> Note: while the use if SELECT INTO is palatable, since you don't
> have to copy the table definition in the trigger, my recommendation
> is to use a table variable instead (which only should the columns
> actually referenced the trigger). I did use SELECT INTO for a while,
> but I found that it gave us performance problems.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||MC (marko_culo#@.#yahoo#.#com#) writes:
> I've reported the bug, however I cant seem to check the status. It seems
> that part of the site is down at the moment. If you want to look at it, i
> created it under 'mculo'
Yeah, I also had problems with the site yesterday. I guess it will come back
later today. Keep in mind that it is still only six o'clock in the morning
in Redmond.
But I did see your bug Saturday. As you had failed to attach(*) the repro,
I augmented the bug with the information from the newsgroup.
(*) It's very easy to go wrong, due to the poor inteface on the site. First
browse the file path, and then press Add Attachment. Took me some time to
figure that out the first time.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Bringing text into a database

I have a report that is generated out of another system, that is 180
characters wide. It resembles the layout of an old COBOL type of report.
What I need to do to is to bring it into a database. I've set the field
to be 180 characters (have tried char, varchar, nvarchar, text) but when
I go to import the data, I notice that it gets truncated around position
150 or so, no matter what data type I choose.
What I want to do is to bring this report in, as a single field, and be
able to move it out as a single line.
What's the limit on varchar, char, text and so on? Why is the wizard
truncating the firstline around position 150?
Thanks,
BCThe limit is 8000. Perhaps the wizard is the issue. Can you create the DTS
package manually? It may be that the input row contains a character that's
supposed to be used as a delimiter.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Blasting Cap" <goober@.christian.net> wrote in message
news:%237EjS3KPGHA.2668@.tk2msftngp13.phx.gbl...
I have a report that is generated out of another system, that is 180
characters wide. It resembles the layout of an old COBOL type of report.
What I need to do to is to bring it into a database. I've set the field
to be 180 characters (have tried char, varchar, nvarchar, text) but when
I go to import the data, I notice that it gets truncated around position
150 or so, no matter what data type I choose.
What I want to do is to bring this report in, as a single field, and be
able to move it out as a single line.
What's the limit on varchar, char, text and so on? Why is the wizard
truncating the firstline around position 150?
Thanks,
BC|||Tom Moreau wrote:
> The limit is 8000. Perhaps the wizard is the issue. Can you create the D
TS
> package manually? It may be that the input row contains a character that'
s
> supposed to be used as a delimiter.
>
The wizard in DTS is the same wizard. Best I can figure is that a CRLF
is at the end of each line, and it varies as to what position it's in.
It appears that the first line of the report dictates where the "hard"
column end appears thoughout the rest of the report.
BC|||CRLF is the standard row terminator. You may need a hex editor to track
down exactly where it is.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23RijHHLPGHA.1760@.TK2MSFTNGP10.phx.gbl...
Tom Moreau wrote:
> The limit is 8000. Perhaps the wizard is the issue. Can you create the
> DTS
> package manually? It may be that the input row contains a character
> that's
> supposed to be used as a delimiter.
>
The wizard in DTS is the same wizard. Best I can figure is that a CRLF
is at the end of each line, and it varies as to what position it's in.
It appears that the first line of the report dictates where the "hard"
column end appears thoughout the rest of the report.
BC