More Extremely weird SQL problem.

I believe SQL Server 2000 EE has a bug in it. I think this
because of the article here :
http://support.microsoft.com/default.aspx?scid=kb;en-us;274765
and my symptoms:
It started happening again. It lasted longer this time.
Here is the testing I did while the problem was happening.
INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
FROM subscriber, Listmember
WHERE Listmember.LM_C_ID = subscriber.id_email
AND ((subscriber.banned = 0
AND subscriber.id_email < 5000000)
AND (Listmember.LM_L_ID = 253))
still produced all records where id_email was > or <
5000000 . around 600K
but if I do the this query
SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
FROM subscriber, Listmember
WHERE Listmember.LM_C_ID = subscriber.id_email
AND ((subscriber.banned = 0
AND subscriber.id_email < 5000000)
AND (Listmember.LM_L_ID = 253))
it is only the records where id_email < 5000000 . I made
two identical new tables called recipient_test
and subscriber_test and filled them with the same data as
the others. Ran the above queries and they both worked
correctly. If I use the original Recipient but the new
subscriber_test table, it works, if I use Subscriber and
Recipient_test, it works. I did not create indexes or
Foreign key contstraints on the test tables. So that could
be a possibility.
Another clue is I could reconfigure the server to Max
degree of Parallelism = 1 and both worked during, then I
could reconfigure it back to MAXDOP = 0 (or 2 or 3, I tried
both) and it would go back to happening again.
So, it has something to do with MAXDOP, and maybe the
reason it happens sometimes and not others is because of
the cost of threshold for parallelism setting being 5. When
it thinks it needs parallelism it happens, and when the
server thinks it doesn't need parallelism, it doesn't.
It is embedded in a Java program call Accucast. The
Accucast software hasn't changed for two years. This only
started happening when we migrated from SQL standard SP3 on
W2K server to SQL Enterprise SP3 and Windows Server 2003.
the database we recreated. New tables, new indexes, FK's
etc. The two tables are in different filegroups. All
indexes except the pk clustered indexes on each are in
different filegroups as well.
Recipient table in recipient Filegroup
pk on recipient on rc_d_id, rc_c_id in recipient filegroup
other indexes on recipient are in recipient_ix filegroup
same set up on subscriber
id_email is pk
both pk are clustered.
It's a Dell 2650 Dual Intel 3.06mb 1mb cache Xeon , 6 gb
ECC memory, 5 Maxtor 145 Gb 10K scsi drives configure as
RAID 10 with Dell Perc4/DC (spanned raid 1) with 1 hot
spare. Windows Server 2003 all patched up. SQL Server
Enterprise Edition SP3a. Everything seems to be fine but
this. I can't figure it out.
Any ideas?
ThanksNick,
A few thoughts:
- Does the query plan actually include a hash join? You say that the tables
are quite well indexed, and this would usually cause the Query Optimizer to
use either nested loops or Merge Joins rather than hash joins, and the
article you referred to talks about problems with hash joins only.
- Have you recently run DBCC CHECKTABLE on the affected tables? This kind of
behaviour is maybe caused by a corrupted non clustered index.
These are the two things I would look at first, with the information you
have given us.
--
Jacco Schalkwijk
SQL Server MVP
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:017601c3bf59$182ef130$a501280a@.phx.gbl...
> I believe SQL Server 2000 EE has a bug in it. I think this
> because of the article here :
> http://support.microsoft.com/default.aspx?scid=kb;en-us;274765
> and my symptoms:
> It started happening again. It lasted longer this time.
> Here is the testing I did while the problem was happening.
> INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> FROM subscriber, Listmember
> WHERE Listmember.LM_C_ID = subscriber.id_email
> AND ((subscriber.banned = 0
> AND subscriber.id_email < 5000000)
> AND (Listmember.LM_L_ID = 253))
> still produced all records where id_email was > or <
> 5000000 . around 600K
> but if I do the this query
> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> FROM subscriber, Listmember
> WHERE Listmember.LM_C_ID = subscriber.id_email
> AND ((subscriber.banned = 0
> AND subscriber.id_email < 5000000)
> AND (Listmember.LM_L_ID = 253))
> it is only the records where id_email < 5000000 . I made
> two identical new tables called recipient_test
> and subscriber_test and filled them with the same data as
> the others. Ran the above queries and they both worked
> correctly. If I use the original Recipient but the new
> subscriber_test table, it works, if I use Subscriber and
> Recipient_test, it works. I did not create indexes or
> Foreign key contstraints on the test tables. So that could
> be a possibility.
> Another clue is I could reconfigure the server to Max
> degree of Parallelism = 1 and both worked during, then I
> could reconfigure it back to MAXDOP = 0 (or 2 or 3, I tried
> both) and it would go back to happening again.
> So, it has something to do with MAXDOP, and maybe the
> reason it happens sometimes and not others is because of
> the cost of threshold for parallelism setting being 5. When
> it thinks it needs parallelism it happens, and when the
> server thinks it doesn't need parallelism, it doesn't.
> It is embedded in a Java program call Accucast. The
> Accucast software hasn't changed for two years. This only
> started happening when we migrated from SQL standard SP3 on
> W2K server to SQL Enterprise SP3 and Windows Server 2003.
> the database we recreated. New tables, new indexes, FK's
> etc. The two tables are in different filegroups. All
> indexes except the pk clustered indexes on each are in
> different filegroups as well.
> Recipient table in recipient Filegroup
> pk on recipient on rc_d_id, rc_c_id in recipient filegroup
> other indexes on recipient are in recipient_ix filegroup
> same set up on subscriber
> id_email is pk
> both pk are clustered.
> It's a Dell 2650 Dual Intel 3.06mb 1mb cache Xeon , 6 gb
> ECC memory, 5 Maxtor 145 Gb 10K scsi drives configure as
> RAID 10 with Dell Perc4/DC (spanned raid 1) with 1 hot
> spare. Windows Server 2003 all patched up. SQL Server
> Enterprise Edition SP3a. Everything seems to be fine but
> this. I can't figure it out.
>
> Any ideas?
> Thanks
>|||One says this for dbcc checktable:
dbcc checktable ('Recipient') with all_errormsgs
DBCC results for 'Recipient'.
There are 37121847 rows in 160756 pages for
object 'Recipient'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
I can't do a repair option at the moment because it is a
production db. However, would the above tell me if
something was wrong? Did the same thing for the
Subscriber table and Listmember table. All the same
message just different row count and page count.
here is the output from showplan_text on and then the
query:
StmtText
--
SET STATISTICS PROFILE ON
(1 row(s) affected)
StmtText
---
---
---
---
--
INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
SELECT DISTINCT 22000, subscriber.id_email, '2003-12-09'
FROM subscriber, Listmember
WHERE Listmember.LM_C_ID = subscriber.id_email
AND ((subscriber.banned = 0
AND subscriber.id_email < 5000000)
(1 row(s) affected)
StmtText
---
---
---
---
--
|--Index Insert(OBJECT:([accucast].[dbo].[Recipient].
[IX_Recipient]), SET:([RC_D_ID1014]=[Recipient].[RC_D_ID],
[RC_C_ID1013]=[Recipient].[RC_C_ID], [IdxBmk1012]=[Bmk1007]))
|--Sort(ORDER BY:([Recipient].[RC_C_ID] ASC,
[Recipient].[RC_D_ID] ASC, [Bmk1007] ASC))
|--Assert(WHERE:(If (NOT([Pass1011]) AND
([Expr1010] IS NULL)) then 0 else NULL))
|--Nested Loops(Left Semi Join, WHERE:
([Recipient].[RC_C_ID] IS NULL)OUTER REFERENCES:
([Recipient].[RC_C_ID]), DEFINE:([Expr1010] = [PROBE
VALUE]))
|--Clustered Index Insert(OBJECT:
([accucast].[dbo].[Recipient].[PK_Recipient]), SET:
([Recipient].[RC_CONT_ERR]=NULL, [Recipient].[RC_ADDR_ERR]
=NULL, [Recipient].[RC_READ]=NULL, [Recipient].[RC_DATE]
='Dec 9 2003 12:00AM', [Recipient].
| |--Top(ROWCOUNT est 0)
| |--Parallelism(Gather
Streams, ORDER BY:([Subscriber].[id_email] ASC))
| |--Merge Join(Inner
Join, MERGE:([Listmember].[LM_C_ID])=([Subscriber].
[id_email]), RESIDUAL:([Subscriber].[id_email]=[Listmember].[LM_C_ID]))
| |--Parallelism
(Repartition Streams, PARTITION COLUMNS:([Listmember].
[LM_C_ID]), ORDER BY:([Listmember].[LM_C_ID] ASC))
| | |--Index
Seek(OBJECT:([accucast].[dbo].[Listmember].
[IX_Listmember]), SEEK:([Listmember].[LM_L_ID]=138)
ORDERED FORWARD)
| |--Parallelism
(Repartition Streams, PARTITION COLUMNS:([Subscriber].
[id_email]), ORDER BY:([Subscriber].[id_email] ASC))
| |--Index
Seek(OBJECT:([accucast].[dbo].[Subscriber].
[IX_subscriber_1]), SEEK:([Subscriber].[banned]=0 AND
[Subscriber].[id_email] < 5000000) ORDERED FORWARD)
|--Row Count Spool
|--Clustered Index Seek(OBJECT:
([accucast].[dbo].[Subscriber].[PK_subscriber]), SEEK:
([Subscriber].[id_email]=[Recipient].[RC_C_ID]) ORDERED
FORWARD)
(14 row(s) affected)
StmtText
--
SET STATISTICS PROFILE OFF
(1 row(s) affected)
Any help would be appreciated.
Thanks.
Nick
>--Original Message--
>Nick,
>A few thoughts:
>- Does the query plan actually include a hash join? You
say that the tables
>are quite well indexed, and this would usually cause the
Query Optimizer to
>use either nested loops or Merge Joins rather than hash
joins, and the
>article you referred to talks about problems with hash
joins only.
>- Have you recently run DBCC CHECKTABLE on the affected
tables? This kind of
>behaviour is maybe caused by a corrupted non clustered
index.
>These are the two things I would look at first, with the
information you
>have given us.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Nick" <anonymous@.discussions.microsoft.com> wrote in
message
>news:017601c3bf59$182ef130$a501280a@.phx.gbl...
>> I believe SQL Server 2000 EE has a bug in it. I think
this
>> because of the article here :
>> http://support.microsoft.com/default.aspx?scid=kb;en-
us;274765
>> and my symptoms:
>> It started happening again. It lasted longer this time.
>> Here is the testing I did while the problem was
happening.
>> INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
>> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
>> FROM subscriber, Listmember
>> WHERE Listmember.LM_C_ID = subscriber.id_email
>> AND ((subscriber.banned = 0
>> AND subscriber.id_email < 5000000)
>> AND (Listmember.LM_L_ID = 253))
>> still produced all records where id_email was > or <
>> 5000000 . around 600K
>> but if I do the this query
>> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
>> FROM subscriber, Listmember
>> WHERE Listmember.LM_C_ID = subscriber.id_email
>> AND ((subscriber.banned = 0
>> AND subscriber.id_email < 5000000)
>> AND (Listmember.LM_L_ID = 253))
>> it is only the records where id_email < 5000000 . I made
>> two identical new tables called recipient_test
>> and subscriber_test and filled them with the same data
as
>> the others. Ran the above queries and they both worked
>> correctly. If I use the original Recipient but the new
>> subscriber_test table, it works, if I use Subscriber and
>> Recipient_test, it works. I did not create indexes or
>> Foreign key contstraints on the test tables. So that
could
>> be a possibility.
>> Another clue is I could reconfigure the server to Max
>> degree of Parallelism = 1 and both worked during, then I
>> could reconfigure it back to MAXDOP = 0 (or 2 or 3, I
tried
>> both) and it would go back to happening again.
>> So, it has something to do with MAXDOP, and maybe the
>> reason it happens sometimes and not others is because of
>> the cost of threshold for parallelism setting being 5.
When
>> it thinks it needs parallelism it happens, and when the
>> server thinks it doesn't need parallelism, it doesn't.
>> It is embedded in a Java program call Accucast. The
>> Accucast software hasn't changed for two years. This
only
>> started happening when we migrated from SQL standard
SP3 on
>> W2K server to SQL Enterprise SP3 and Windows Server
2003.
>> the database we recreated. New tables, new indexes, FK's
>> etc. The two tables are in different filegroups. All
>> indexes except the pk clustered indexes on each are in
>> different filegroups as well.
>> Recipient table in recipient Filegroup
>> pk on recipient on rc_d_id, rc_c_id in recipient
filegroup
>> other indexes on recipient are in recipient_ix filegroup
>> same set up on subscriber
>> id_email is pk
>> both pk are clustered.
>> It's a Dell 2650 Dual Intel 3.06mb 1mb cache Xeon , 6 gb
>> ECC memory, 5 Maxtor 145 Gb 10K scsi drives configure as
>> RAID 10 with Dell Perc4/DC (spanned raid 1) with 1 hot
>> spare. Windows Server 2003 all patched up. SQL Server
>> Enterprise Edition SP3a. Everything seems to be fine but
>> this. I can't figure it out.
>>
>> Any ideas?
>> Thanks
>>
>
>.
>|||Hi Nick,
The results you get indicate that there are no problems with the indexes,
which I thought could be a cause of the problem you have. If something was
wrong you would have gotten error messages in Query Analyzer so that would
have been quite clear.
The showplan shows, as I expected, that the query does not use a hash join
but uses one nested loop and one merge join. So the information in the
knowledge base article does not apply to this problem.
If you can't find any more inforamtion about it in the Knowledge Base, it
might indeed be a bug and the best thing to do would be to open a case with
Microsoft Product Support. If it is a bug you won't be charged.
--
Jacco Schalkwijk
SQL Server MVP
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:073101c3bff4$193bf0f0$a001280a@.phx.gbl...
> One says this for dbcc checktable:
> dbcc checktable ('Recipient') with all_errormsgs
> DBCC results for 'Recipient'.
> There are 37121847 rows in 160756 pages for
> object 'Recipient'.
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> I can't do a repair option at the moment because it is a
> production db. However, would the above tell me if
> something was wrong? Did the same thing for the
> Subscriber table and Listmember table. All the same
> message just different row count and page count.
>
> here is the output from showplan_text on and then the
> query:
> StmtText
> --
> SET STATISTICS PROFILE ON
> (1 row(s) affected)
> StmtText
>
>
> ---
> ---
> ---
> ---
> --
> INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
> SELECT DISTINCT 22000, subscriber.id_email, '2003-12-09'
> FROM subscriber, Listmember
> WHERE Listmember.LM_C_ID = subscriber.id_email
> AND ((subscriber.banned = 0
> AND subscriber.id_email < 5000000)
> (1 row(s) affected)
> StmtText
>
>
> ---
> ---
> ---
> ---
> --
> |--Index Insert(OBJECT:([accucast].[dbo].[Recipient].
> [IX_Recipient]), SET:([RC_D_ID1014]=[Recipient].[RC_D_ID],
> [RC_C_ID1013]=[Recipient].[RC_C_ID], [IdxBmk1012]=> [Bmk1007]))
> |--Sort(ORDER BY:([Recipient].[RC_C_ID] ASC,
> [Recipient].[RC_D_ID] ASC, [Bmk1007] ASC))
> |--Assert(WHERE:(If (NOT([Pass1011]) AND
> ([Expr1010] IS NULL)) then 0 else NULL))
> |--Nested Loops(Left Semi Join, WHERE:
> ([Recipient].[RC_C_ID] IS NULL)OUTER REFERENCES:
> ([Recipient].[RC_C_ID]), DEFINE:([Expr1010] = [PROBE
> VALUE]))
> |--Clustered Index Insert(OBJECT:
> ([accucast].[dbo].[Recipient].[PK_Recipient]), SET:
> ([Recipient].[RC_CONT_ERR]=NULL, [Recipient].[RC_ADDR_ERR]
> =NULL, [Recipient].[RC_READ]=NULL, [Recipient].[RC_DATE]
> ='Dec 9 2003 12:00AM', [Recipient].
> | |--Top(ROWCOUNT est 0)
> | |--Parallelism(Gather
> Streams, ORDER BY:([Subscriber].[id_email] ASC))
> | |--Merge Join(Inner
> Join, MERGE:([Listmember].[LM_C_ID])=([Subscriber].
> [id_email]), RESIDUAL:([Subscriber].[id_email]=> [Listmember].[LM_C_ID]))
> | |--Parallelism
> (Repartition Streams, PARTITION COLUMNS:([Listmember].
> [LM_C_ID]), ORDER BY:([Listmember].[LM_C_ID] ASC))
> | | |--Index
> Seek(OBJECT:([accucast].[dbo].[Listmember].
> [IX_Listmember]), SEEK:([Listmember].[LM_L_ID]=138)
> ORDERED FORWARD)
> | |--Parallelism
> (Repartition Streams, PARTITION COLUMNS:([Subscriber].
> [id_email]), ORDER BY:([Subscriber].[id_email] ASC))
> | |--Index
> Seek(OBJECT:([accucast].[dbo].[Subscriber].
> [IX_subscriber_1]), SEEK:([Subscriber].[banned]=0 AND
> [Subscriber].[id_email] < 5000000) ORDERED FORWARD)
> |--Row Count Spool
> |--Clustered Index Seek(OBJECT:
> ([accucast].[dbo].[Subscriber].[PK_subscriber]), SEEK:
> ([Subscriber].[id_email]=[Recipient].[RC_C_ID]) ORDERED
> FORWARD)
> (14 row(s) affected)
> StmtText
> --
> SET STATISTICS PROFILE OFF
> (1 row(s) affected)
>
> Any help would be appreciated.
> Thanks.
> Nick
> >--Original Message--
> >Nick,
> >
> >A few thoughts:
> >- Does the query plan actually include a hash join? You
> say that the tables
> >are quite well indexed, and this would usually cause the
> Query Optimizer to
> >use either nested loops or Merge Joins rather than hash
> joins, and the
> >article you referred to talks about problems with hash
> joins only.
> >- Have you recently run DBCC CHECKTABLE on the affected
> tables? This kind of
> >behaviour is maybe caused by a corrupted non clustered
> index.
> >
> >These are the two things I would look at first, with the
> information you
> >have given us.
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"Nick" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:017601c3bf59$182ef130$a501280a@.phx.gbl...
> >> I believe SQL Server 2000 EE has a bug in it. I think
> this
> >> because of the article here :
> >>
> >> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;274765
> >>
> >> and my symptoms:
> >>
> >> It started happening again. It lasted longer this time.
> >> Here is the testing I did while the problem was
> happening.
> >>
> >> INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
> >> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> >> FROM subscriber, Listmember
> >> WHERE Listmember.LM_C_ID = subscriber.id_email
> >> AND ((subscriber.banned = 0
> >> AND subscriber.id_email < 5000000)
> >> AND (Listmember.LM_L_ID = 253))
> >>
> >> still produced all records where id_email was > or <
> >> 5000000 . around 600K
> >> but if I do the this query
> >>
> >> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> >> FROM subscriber, Listmember
> >> WHERE Listmember.LM_C_ID = subscriber.id_email
> >> AND ((subscriber.banned = 0
> >> AND subscriber.id_email < 5000000)
> >> AND (Listmember.LM_L_ID = 253))
> >>
> >> it is only the records where id_email < 5000000 . I made
> >> two identical new tables called recipient_test
> >> and subscriber_test and filled them with the same data
> as
> >> the others. Ran the above queries and they both worked
> >> correctly. If I use the original Recipient but the new
> >> subscriber_test table, it works, if I use Subscriber and
> >> Recipient_test, it works. I did not create indexes or
> >> Foreign key contstraints on the test tables. So that
> could
> >> be a possibility.
> >>
> >> Another clue is I could reconfigure the server to Max
> >> degree of Parallelism = 1 and both worked during, then I
> >> could reconfigure it back to MAXDOP = 0 (or 2 or 3, I
> tried
> >> both) and it would go back to happening again.
> >>
> >> So, it has something to do with MAXDOP, and maybe the
> >> reason it happens sometimes and not others is because of
> >> the cost of threshold for parallelism setting being 5.
> When
> >> it thinks it needs parallelism it happens, and when the
> >> server thinks it doesn't need parallelism, it doesn't.
> >>
> >> It is embedded in a Java program call Accucast. The
> >> Accucast software hasn't changed for two years. This
> only
> >> started happening when we migrated from SQL standard
> SP3 on
> >> W2K server to SQL Enterprise SP3 and Windows Server
> 2003.
> >> the database we recreated. New tables, new indexes, FK's
> >> etc. The two tables are in different filegroups. All
> >> indexes except the pk clustered indexes on each are in
> >> different filegroups as well.
> >>
> >> Recipient table in recipient Filegroup
> >> pk on recipient on rc_d_id, rc_c_id in recipient
> filegroup
> >> other indexes on recipient are in recipient_ix filegroup
> >>
> >> same set up on subscriber
> >> id_email is pk
> >>
> >> both pk are clustered.
> >>
> >> It's a Dell 2650 Dual Intel 3.06mb 1mb cache Xeon , 6 gb
> >> ECC memory, 5 Maxtor 145 Gb 10K scsi drives configure as
> >> RAID 10 with Dell Perc4/DC (spanned raid 1) with 1 hot
> >> spare. Windows Server 2003 all patched up. SQL Server
> >> Enterprise Edition SP3a. Everything seems to be fine but
> >> this. I can't figure it out.
> >>
> >>
> >>
> >> Any ideas?
> >>
> >> Thanks
> >>
> >>
> >
> >
> >.
> >|||Hi Nick,
Can you report back here when you have had an answer? I'm curious.
--
Jacco Schalkwijk
SQL Server MVP
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:0f0b01c3c006$8d231810$a401280a@.phx.gbl...
> Thanks man. I'll do that.
> >--Original Message--
> >Hi Nick,
> >
> >The results you get indicate that there are no problems
> with the indexes,
> >which I thought could be a cause of the problem you have.
> If something was
> >wrong you would have gotten error messages in Query
> Analyzer so that would
> >have been quite clear.
> >
> >The showplan shows, as I expected, that the query does
> not use a hash join
> >but uses one nested loop and one merge join. So the
> information in the
> >knowledge base article does not apply to this problem.
> >
> >If you can't find any more inforamtion about it in the
> Knowledge Base, it
> >might indeed be a bug and the best thing to do would be
> to open a case with
> >Microsoft Product Support. If it is a bug you won't be
> charged.
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"Nick" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:073101c3bff4$193bf0f0$a001280a@.phx.gbl...
> >> One says this for dbcc checktable:
> >>
> >> dbcc checktable ('Recipient') with all_errormsgs
> >>
> >> DBCC results for 'Recipient'.
> >> There are 37121847 rows in 160756 pages for
> >> object 'Recipient'.
> >> DBCC execution completed. If DBCC printed error
> messages,
> >> contact your system administrator.
> >>
> >> I can't do a repair option at the moment because it is a
> >> production db. However, would the above tell me if
> >> something was wrong? Did the same thing for the
> >> Subscriber table and Listmember table. All the same
> >> message just different row count and page count.
> >>
> >>
> >> here is the output from showplan_text on and then the
> >> query:
> >>
> >> StmtText
> >> --
> >> SET STATISTICS PROFILE ON
> >>
> >> (1 row(s) affected)
> >>
> >> StmtText
> >>
> >>
> >>
> >>
> >> ---
> --
> >> ---
> --
> >> ---
> --
> >> ---
> --
> >> --
> >> INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
> >> SELECT DISTINCT 22000, subscriber.id_email, '2003-12-09'
> >> FROM subscriber, Listmember
> >> WHERE Listmember.LM_C_ID = subscriber.id_email
> >> AND ((subscriber.banned = 0
> >> AND subscriber.id_email < 5000000)
> >>
> >> (1 row(s) affected)
> >>
> >> StmtText
> >>
> >>
> >>
> >>
> >> ---
> --
> >> ---
> --
> >> ---
> --
> >> ---
> --
> >> --
> >> |--Index Insert(OBJECT:([accucast].[dbo].[Recipient].
> >> [IX_Recipient]), SET:([RC_D_ID1014]=[Recipient].
> [RC_D_ID],
> >> [RC_C_ID1013]=[Recipient].[RC_C_ID], [IdxBmk1012]=> >> [Bmk1007]))
> >> |--Sort(ORDER BY:([Recipient].[RC_C_ID] ASC,
> >> [Recipient].[RC_D_ID] ASC, [Bmk1007] ASC))
> >> |--Assert(WHERE:(If (NOT([Pass1011]) AND
> >> ([Expr1010] IS NULL)) then 0 else NULL))
> >> |--Nested Loops(Left Semi Join, WHERE:
> >> ([Recipient].[RC_C_ID] IS NULL)OUTER REFERENCES:
> >> ([Recipient].[RC_C_ID]), DEFINE:([Expr1010] = [PROBE
> >> VALUE]))
> >> |--Clustered Index Insert(OBJECT:
> >> ([accucast].[dbo].[Recipient].[PK_Recipient]), SET:
> >> ([Recipient].[RC_CONT_ERR]=NULL, [Recipient].
> [RC_ADDR_ERR]
> >> =NULL, [Recipient].[RC_READ]=NULL, [Recipient].[RC_DATE]
> >> ='Dec 9 2003 12:00AM', [Recipient].
> >> | |--Top(ROWCOUNT est 0)
> >> | |--Parallelism(Gather
> >> Streams, ORDER BY:([Subscriber].[id_email] ASC))
> >> | |--Merge Join(Inner
> >> Join, MERGE:([Listmember].[LM_C_ID])=([Subscriber].
> >> [id_email]), RESIDUAL:([Subscriber].[id_email]=> >> [Listmember].[LM_C_ID]))
> >> | |--Parallelism
> >> (Repartition Streams, PARTITION COLUMNS:([Listmember].
> >> [LM_C_ID]), ORDER BY:([Listmember].[LM_C_ID] ASC))
> >> | | |--Index
> >> Seek(OBJECT:([accucast].[dbo].[Listmember].
> >> [IX_Listmember]), SEEK:([Listmember].[LM_L_ID]=138)
> >> ORDERED FORWARD)
> >> | |--Parallelism
> >> (Repartition Streams, PARTITION COLUMNS:([Subscriber].
> >> [id_email]), ORDER BY:([Subscriber].[id_email] ASC))
> >> | |--Index
> >> Seek(OBJECT:([accucast].[dbo].[Subscriber].
> >> [IX_subscriber_1]), SEEK:([Subscriber].[banned]=0 AND
> >> [Subscriber].[id_email] < 5000000) ORDERED FORWARD)
> >> |--Row Count Spool
> >> |--Clustered Index Seek
> (OBJECT:
> >> ([accucast].[dbo].[Subscriber].[PK_subscriber]), SEEK:
> >> ([Subscriber].[id_email]=[Recipient].[RC_C_ID]) ORDERED
> >> FORWARD)
> >>
> >> (14 row(s) affected)
> >>
> >> StmtText
> >> --
> >> SET STATISTICS PROFILE OFF
> >>
> >> (1 row(s) affected)
> >>
> >>
> >>
> >> Any help would be appreciated.
> >>
> >> Thanks.
> >>
> >> Nick
> >>
> >> >--Original Message--
> >> >Nick,
> >> >
> >> >A few thoughts:
> >> >- Does the query plan actually include a hash join? You
> >> say that the tables
> >> >are quite well indexed, and this would usually cause
> the
> >> Query Optimizer to
> >> >use either nested loops or Merge Joins rather than hash
> >> joins, and the
> >> >article you referred to talks about problems with hash
> >> joins only.
> >> >- Have you recently run DBCC CHECKTABLE on the affected
> >> tables? This kind of
> >> >behaviour is maybe caused by a corrupted non clustered
> >> index.
> >> >
> >> >These are the two things I would look at first, with
> the
> >> information you
> >> >have given us.
> >> >
> >> >--
> >> >Jacco Schalkwijk
> >> >SQL Server MVP
> >> >
> >> >
> >> >"Nick" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:017601c3bf59$182ef130$a501280a@.phx.gbl...
> >> >> I believe SQL Server 2000 EE has a bug in it. I
> think
> >> this
> >> >> because of the article here :
> >> >>
> >> >> http://support.microsoft.com/default.aspx?scid=kb;en-
> >> us;274765
> >> >>
> >> >> and my symptoms:
> >> >>
> >> >> It started happening again. It lasted longer this
> time.
> >> >> Here is the testing I did while the problem was
> >> happening.
> >> >>
> >> >> INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
> >> >> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> >> >> FROM subscriber, Listmember
> >> >> WHERE Listmember.LM_C_ID = subscriber.id_email
> >> >> AND ((subscriber.banned = 0
> >> >> AND subscriber.id_email < 5000000)
> >> >> AND (Listmember.LM_L_ID = 253))
> >> >>
> >> >> still produced all records where id_email was > or <
> >> >> 5000000 . around 600K
> >> >> but if I do the this query
> >> >>
> >> >> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> >> >> FROM subscriber, Listmember
> >> >> WHERE Listmember.LM_C_ID = subscriber.id_email
> >> >> AND ((subscriber.banned = 0
> >> >> AND subscriber.id_email < 5000000)
> >> >> AND (Listmember.LM_L_ID = 253))
> >> >>
> >> >> it is only the records where id_email < 5000000 . I
> made
> >> >> two identical new tables called recipient_test
> >> >> and subscriber_test and filled them with the same
> data
> >> as
> >> >> the others. Ran the above queries and they both
> worked
> >> >> correctly. If I use the original Recipient but the
> new
> >> >> subscriber_test table, it works, if I use Subscriber
> and
> >> >> Recipient_test, it works. I did not create indexes or
> >> >> Foreign key contstraints on the test tables. So that
> >> could
> >> >> be a possibility.
> >> >>
> >> >> Another clue is I could reconfigure the server to Max
> >> >> degree of Parallelism = 1 and both worked during,
> then I
> >> >> could reconfigure it back to MAXDOP = 0 (or 2 or 3, I
> >> tried
> >> >> both) and it would go back to happening again.
> >> >>
> >> >> So, it has something to do with MAXDOP, and maybe the
> >> >> reason it happens sometimes and not others is
> because of
> >> >> the cost of threshold for parallelism setting being
> 5.
> >> When
> >> >> it thinks it needs parallelism it happens, and when
> the
> >> >> server thinks it doesn't need parallelism, it
> doesn't.
> >> >>
> >> >> It is embedded in a Java program call Accucast. The
> >> >> Accucast software hasn't changed for two years. This
> >> only
> >> >> started happening when we migrated from SQL standard
> >> SP3 on
> >> >> W2K server to SQL Enterprise SP3 and Windows Server
> >> 2003.
> >> >> the database we recreated. New tables, new indexes,
> FK's
> >> >> etc. The two tables are in different filegroups. All
> >> >> indexes except the pk clustered indexes on each are
> in
> >> >> different filegroups as well.
> >> >>
> >> >> Recipient table in recipient Filegroup
> >> >> pk on recipient on rc_d_id, rc_c_id in recipient
> >> filegroup
> >> >> other indexes on recipient are in recipient_ix
> filegroup
> >> >>
> >> >> same set up on subscriber
> >> >> id_email is pk
> >> >>
> >> >> both pk are clustered.
> >> >>
> >> >> It's a Dell 2650 Dual Intel 3.06mb 1mb cache Xeon ,
> 6 gb
> >> >> ECC memory, 5 Maxtor 145 Gb 10K scsi drives
> configure as
> >> >> RAID 10 with Dell Perc4/DC (spanned raid 1) with 1
> hot
> >> >> spare. Windows Server 2003 all patched up. SQL Server
> >> >> Enterprise Edition SP3a. Everything seems to be fine
> but
> >> >> this. I can't figure it out.
> >> >>
> >> >>
> >> >>
> >> >> Any ideas?
> >> >>
> >> >> Thanks
> >> >>
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

More date format grief

I'm investigating a bug a customer has reported in our database
abstraction layer, and it's making me very unhappy.

Brief summary:
I have a database abstraction layer which is intended to mediate
between webapps and arbitrary database backends using JDBC. I am very
unwilling indeed to write special-case code for particular
databases. Our code has worked satisfactorily with many databases,
including many instances MS SQLServer 2000 databases using the
com.microsoft.sqlserver.SQLServerDriver.

However, in this instance, the database won't accept dates. It won't
accept dates in the java.sql.Date.toString() format (which is the ANSI
SQL 92 format) and it won't accept dates in the ISO8601 format if they
have a zone offset (which in the general case they do) - even if that
zone offset is 'Z'.

I find, by reading on Usenet, that SQL Server doesn't have a default
date format. Furthermore, it doesn't take it's date format from
Windows Regional settings.

So how, for the love of God and Little Fishes, do I persuade a SQL
Server database to accept ANSI SQL 92 dates, permanently, not on a
per-session basis?

--
simon@.jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

;; all in all you're just another click in the call
;;-- Minke BouyedSimon,

I'll agree this is very frustrating, but there is no
easy answer, since there is no international standard for
representation of datetime values. ISO-8601 has a huge
number of options, and SQL Server accepts at least a couple
of the ISO-8601 alternatives.

If you have timezone information in data and want one
product that works with all back ends, then you've probably
got trouble. Not all products support timezones, so your
data will end up with different values on different products.
If you want consistency, either eliminate or convert the
timezone data in your front end, and send every back end you
connect to datetime values as strings of the form
'YYYYMMDD HH:MM:SS' (seconds optional or with fractional
seconds as well).

I thought you could also use '{d YYYY-MM-DD}' and it would
work regardless of settings (unlike 'YYYY-MM-DD' which depends
on date format settings). Not sure about this last bit, though.

Does SQL Server have a default date format? This is several questions:

Q. Does SQL Server display dates as character strings in a
consistent way?
A. SQL Server doesn't display anything. IDEs and front-ends do.

Q. Does SQL Server CAST dates to strings with a consistent format?
A. No. This depends on language settings.

Q. Can SQL Server convert dates to strings with a consistent format?
A. Yes, with CONVERT(varchar..., <format>) and string functions.

Q. Does SQL Server import every ISO-8601-allowed date correctly?
A. No. It does import a few of them correctly and consistently:
YYYYMMDD HH:MM:SS.fff and YYYY-MM-DDTHH:MM:SS.fff for example.
As far as I know, there is no timezone support.

Q. Does CONVERT(datetime, ...) with format codes convert
consistently?
A. No. The documentation does not make this clear, but
all the numerical, delimited formats except for the ISO
format with the T depend on the connection's language or
dateformat setting (dateformat overrides language, I believe).

Why doesn't SQL Server consistently convert SQL-92 date strings?
Good question. It converts SQL-92 timestamp (without timezone)
correctly, but not date-only.

It will, if the date format at the time of conversion is mdy,
ymd, or myd, but I don't think that's a great solution.

What's the safest date format to use?
Probably 'YYYYMMDD HH:MM:SS.[fff]', an ISO format, since if
someone truncates it to date-only, it won't break, like the
SQL-92 timestamp form.

-- Steve Kass
-- Drew University
-- Ref: 4BA55F69-6565-4B87-BB19-E223787FDB91

Simon Brooke wrote:
> I'm investigating a bug a customer has reported in our database
> abstraction layer, and it's making me very unhappy.
> Brief summary:
> I have a database abstraction layer which is intended to mediate
> between webapps and arbitrary database backends using JDBC. I am very
> unwilling indeed to write special-case code for particular
> databases. Our code has worked satisfactorily with many databases,
> including many instances MS SQLServer 2000 databases using the
> com.microsoft.sqlserver.SQLServerDriver.
> However, in this instance, the database won't accept dates. It won't
> accept dates in the java.sql.Date.toString() format (which is the ANSI
> SQL 92 format) and it won't accept dates in the ISO8601 format if they
> have a zone offset (which in the general case they do) - even if that
> zone offset is 'Z'.
> I find, by reading on Usenet, that SQL Server doesn't have a default
> date format. Furthermore, it doesn't take it's date format from
> Windows Regional settings.
> So how, for the love of God and Little Fishes, do I persuade a SQL
> Server database to accept ANSI SQL 92 dates, permanently, not on a
> per-session basis?|||Steve Kass <skass@.drew.edu> writes:

> Simon Brooke wrote:
> > I have a database abstraction layer which is intended to mediate
> > between webapps and arbitrary database backends using JDBC. I am very
> > unwilling indeed to write special-case code for particular
> > databases. Our code has worked satisfactorily with many databases,
> > including many instances MS SQLServer 2000 databases using the
> > com.microsoft.sqlserver.SQLServerDriver.

> > However, in this instance, the database won't accept dates. It won't
> > accept dates in the java.sql.Date.toString() format (which is the ANSI
> > SQL 92 format) and it won't accept dates in the ISO8601 format if they
> > have a zone offset (which in the general case they do) - even if that
> > zone offset is 'Z'.
> > I find, by reading on Usenet, that SQL Server doesn't have a default
> > date format. Furthermore, it doesn't take it's date format from
> > Windows Regional settings. So how, for the love of God and Little
> > Fishes, do I persuade a SQL
> > Server database to accept ANSI SQL 92 dates, permanently, not on a
> > per-session basis?

> Q. Does SQL Server import every ISO-8601-allowed date correctly?
> A. No. It does import a few of them correctly and consistently:
> YYYYMMDD HH:MM:SS.fff and YYYY-MM-DDTHH:MM:SS.fff for example.

Yes, but, actually, that's not a valid ISO-8601 format, because it
doesn't include a timezone. Furthermore, I don't have the luxury of
being able to generate custom code for every database. Surely it must
be _possible_ to persuade SQL Server to conform to ANSI 92?

> Why doesn't SQL Server consistently convert SQL-92 date strings?
> Good question. It converts SQL-92 timestamp (without timezone)
> correctly, but not date-only.

No, it doesn't. That is where all this grief started: we've been
sending that to SQL Server for years and in every other installation
it has worked, but now we have a customer using MS SQL Server 2000 who
is having that fail consistently and repeatedly on one of their boxes
(they have another box running identical software on which it is not
failing, and on our box which we've one everythintg possible to make
identical it doesn't fail). I've done everything I can to find a
difference in setup between the boxes and so far I've failed.

--
simon@.jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

;; all in all you're just another click in the call
;;-- Minke Bouyed|||
Simon Brooke wrote:
> Steve Kass <skass@.drew.edu> writes:
>
>>Simon Brooke wrote:
>>
>>>I have a database abstraction layer which is intended to mediate
>>>between webapps and arbitrary database backends using JDBC. I am very
>>>unwilling indeed to write special-case code for particular
>>>databases. Our code has worked satisfactorily with many databases,
>>>including many instances MS SQLServer 2000 databases using the
>>>com.microsoft.sqlserver.SQLServerDriver.
>
>>>However, in this instance, the database won't accept dates. It won't
>>>accept dates in the java.sql.Date.toString() format (which is the ANSI
>>>SQL 92 format) and it won't accept dates in the ISO8601 format if they
>>>have a zone offset (which in the general case they do) - even if that
>>>zone offset is 'Z'.
>>>I find, by reading on Usenet, that SQL Server doesn't have a default
>>>date format. Furthermore, it doesn't take it's date format from
>>>Windows Regional settings. So how, for the love of God and Little
>>>Fishes, do I persuade a SQL
>>>Server database to accept ANSI SQL 92 dates, permanently, not on a
>>>per-session basis?
>
>>Q. Does SQL Server import every ISO-8601-allowed date correctly?
>>A. No. It does import a few of them correctly and consistently:
>> YYYYMMDD HH:MM:SS.fff and YYYY-MM-DDTHH:MM:SS.fff for example.
>
> Yes, but, actually, that's not a valid ISO-8601 format, because it
> doesn't include a timezone. Furthermore, I don't have the luxury of
> being able to generate custom code for every database. Surely it must
> be _possible_ to persuade SQL Server to conform to ANSI 92?
My reference is ISO8601:2000E (December, 2000), and I don't see
where a timezone is required. Do you have the paragraph number?

Section 5.4 describes point-in-time representations, and says "The
zone designator is empty if use is made of the local time of the
day in accordance...", referring to earlier sections that give
offer hhmm, hh:mm, hhmmss, hh:mm:ss, hh:mm,m, hhmm,m, hh, etc.,
etc., as possible date formats.

It also gives Basic (no hyphens) and extended (with hyphens) formats
for everything, without as far as I can see mandating one or the
other. It would be nice if SQL Server understood them all, but it
does understand the one with hyphens and a T (ISO allows the T to be
omitted if no ambiguity results, though I couldn't see where
any would regarding other ISO formats - probably missed something
crazy like week numbers in BC years that used a T.)

SQL Server also understands the one with no hyphens or T.
It looks ok in ISO to omit date separators but include time
separators.
>>Why doesn't SQL Server consistently convert SQL-92 date strings?
>>Good question. It converts SQL-92 timestamp (without timezone)
>>correctly, but not date-only.
>
> No, it doesn't. That is where all this grief started: we've been
> sending that to SQL Server for years and in every other installation
> it has worked, but now we have a customer using MS SQL Server 2000 who
> is having that fail consistently and repeatedly on one of their boxes
> (they have another box running identical software on which it is not
> failing, and on our box which we've one everythintg possible to make
> identical it doesn't fail). I've done everything I can to find a
> difference in setup between the boxes and so far I've failed.

My slip. SQL Server doesn't understand SQL-92

TIMESTAMP '2003-02-22 23:34:43.123' at all, as in

CAST(TIMESTAMP '2003-02-22 23:34:43.123' as DATETIME)

but I doubt you are construction CAST(TIMESTAMP ...
expressions. SQL Server uses
{ts '1996-12-19 11:11:11.000'} to represent
a timestamp literal, and interprets it unambiguously,
as far as I know, as it does the date literal format of
{d '1996-12-19'}

Without the {ts ... }, these strings alone, like all
numeric delimited date formats, when implicitely
converted to dates follow the relative positions of
d and m in the dateformat setting in effect implicit
from the language selection or explicitly set.

set dateformat dmy
go
declare @.d datetime
set @.d = {ts '1996-12-19 11:11:11.000'}
select @.d
go
declare @.d datetime
set @.d = {d '1996-12-19'}
select @.d
go
declare @.d datetime
set @.d = '1996-12-19 11:11:11.000'
select @.d
go
declare @.d datetime
set @.d = '1996-12-19'
select @.d

I don't know why you would have trouble with this if the
settings were right, but maybe there's some driver parameter
buried in the registry, or some other setting that's not
obvious. Perhaps someone wanted us_english but dmy, and
got the bright idea of modifying the syslanguages table!

Does that bum server error out on this??

set dateformat dmy
declare @.d datetime
set @.d = '2003-02-19'

If the server is installed as us_english, and no one
has changed the dateformat setting or modified syslanguages,
I think it should work and might be a case for product
support. On the other hand, I wouldn't
want a product that depended on the language of installation.

SK|||Simon

This may be useless, but you don't seem to have a lot SQL Server
2000-specific info.
SQL server interprets data based on a 'collation' which is set at
install time, and can be overridden manually in an SQL statement.
To find the default collation for the database, the user will need to
right click on the SQL server instance in Enterprise Manager and
choose 'properties'. The default collation is displayed as part of
the basinc database information.
This can only be changed if the databases on the server are rebuilt.
Some information below from the SQL server 'man pages'

I haven't had the problems you describe, but if there is a
configuration difference between two installs, which is causing the
problem you describe, this is likely to be it.
From what I understand, you are saying that one installation processes
the dates OK, and the other does not. So SQL Server 2000 will do the
job, it is just not configured correctly on one of the servers.

good luck

Ben McIntyre

<snip>
-----------------------

Collation Options for International Support
In Microsoft SQL Server 2000, it is not required to separately
specify code page and sort order for character data, and the collation
used for Unicode data. Instead, specify the collation name and sorting
rules to use. The term, collation, refers to a set of rules that
determine how data is sorted and compared. Character data is sorted
using rules that define the correct character sequence, with options
for specifying case-sensitivity, accent marks, kana character types,
and character width. Microsoft SQL Server 2000 collations include
these groupings:

Windows collations
Windows collations define rules for storing character data based on
the rules defined for an associated Windows locale. The base Windows
collation rules specify which alphabet or language is used when
dictionary sorting is applied, as well as the code page used to store
non-Unicode character data. For more information, see Collations.

SQL collations
SQL collations are provided for compatibility with sort orders in
earlier versions of Microsoft SQL Server. For more information, see
Using SQL Collations.

Changing Collations After Setup
When you set up SQL Server 2000, it is important to use the correct
collation settings. You can change collation settings after running
Setup, but you must rebuild the databases and reload the data. It is
recommended that you develop a standard within your organization for
these options. Many server-to-server activities can fail if the
collation settings are not consistent across servers.

----------------------
How To

How to rebuild the master database (Rebuild Master utility)
To rebuild the master database

Shutdown Microsoft SQL Server 2000, and then run Rebuildm.exe. This
is located in the Program Files\Microsoft SQL Server\80\Tools\Binn
directory.

In the Rebuild Master dialog box, click Browse.

In the Browse for Folder dialog box, select the \Data folder on the
SQL Server 2000 compact disc or in the shared network directory from
which SQL Server 2000 was installed, and then click OK.

Click Settings. In the Collation Settings dialog box, verify or change
settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not
match the collation selected during setup. You can select the same
settings used during setup or select new collation settings. When
done, click OK.

In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.

Note To continue, you may need to stop a server that is running.
</snip>|||ben_spam@.mailcity.com (Ben McIntyre) writes:

> Simon
> This may be useless, but you don't seem to have a lot SQL Server
> 2000-specific info.
> SQL server interprets data based on a 'collation' which is set at
> install time, and can be overridden manually in an SQL statement.
> To find the default collation for the database, the user will need to
> right click on the SQL server instance in Enterprise Manager and
> choose 'properties'. The default collation is displayed as part of
> the basinc database information.
> This can only be changed if the databases on the server are rebuilt.
> Some information below from the SQL server 'man pages'
> I haven't had the problems you describe, but if there is a
> configuration difference between two installs, which is causing the
> problem you describe, this is likely to be it.
> From what I understand, you are saying that one installation processes
> the dates OK, and the other does not. So SQL Server 2000 will do the
> job, it is just not configured correctly on one of the servers.

OK, thanks for that. The collation sequence on the database on our
test box (which _does_ work) is SQL_Latin1_General_CP1_CI_AS. My
customer isn't yet in this morning so I can't ring and check what his
is.

I'll post a resolution once I get it sorted in case anyone searches
google any time in the future for a similar problem.

Just so that, in future, I know the general solution, which 'bit' of the
collation name is it which affects date sequencing? I mean, for
example, if in future I have a similar problem with a customer not in
the Latin1 area, what collation advice to I offer?

Cheers

Simon

--
simon@.jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

[ This mind intentionally left blank ]|||> Just so that, in future, I know the general solution, which 'bit' of the
> collation name is it which affects date sequencing?

You mean datetime datattype? That is not affected by collations at all. If this is your problem, you
might want to post the problem at hand again (It has been "aged out").

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver

"Simon Brooke" <simon@.jasmine.org.uk> wrote in message
news:87n0dtot6r.fsf@.gododdin.internal.jasmine.org. uk...
> ben_spam@.mailcity.com (Ben McIntyre) writes:
> > Simon
> > This may be useless, but you don't seem to have a lot SQL Server
> > 2000-specific info.
> > SQL server interprets data based on a 'collation' which is set at
> > install time, and can be overridden manually in an SQL statement.
> > To find the default collation for the database, the user will need to
> > right click on the SQL server instance in Enterprise Manager and
> > choose 'properties'. The default collation is displayed as part of
> > the basinc database information.
> > This can only be changed if the databases on the server are rebuilt.
> > Some information below from the SQL server 'man pages'
> > I haven't had the problems you describe, but if there is a
> > configuration difference between two installs, which is causing the
> > problem you describe, this is likely to be it.
> > From what I understand, you are saying that one installation processes
> > the dates OK, and the other does not. So SQL Server 2000 will do the
> > job, it is just not configured correctly on one of the servers.
> OK, thanks for that. The collation sequence on the database on our
> test box (which _does_ work) is SQL_Latin1_General_CP1_CI_AS. My
> customer isn't yet in this morning so I can't ring and check what his
> is.
> I'll post a resolution once I get it sorted in case anyone searches
> google any time in the future for a similar problem.
> Just so that, in future, I know the general solution, which 'bit' of the
> collation name is it which affects date sequencing? I mean, for
> example, if in future I have a similar problem with a customer not in
> the Latin1 area, what collation advice to I offer?
> Cheers
> Simon
> --
> simon@.jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/
> [ This mind intentionally left blank ]|||Ben McIntyre (ben_spam@.mailcity.com) writes:
> SQL server interprets data based on a 'collation' which is set at
> install time, and can be overridden manually in an SQL statement.
> To find the default collation for the database, the user will need to
> right click on the SQL server instance in Enterprise Manager and
> choose 'properties'. The default collation is displayed as part of
> the basinc database information.

Collation apply to string columns, not to datetime columns.

The two commands that affect how strings is interpreted are
SET DATEFORMAT and SET LANGUAGE.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.corners tone.se> writes:

> > Just so that, in future, I know the general solution, which 'bit' of the
> > collation name is it which affects date sequencing?
> You mean datetime datattype? That is not affected by collations at all. If this is your problem, you
> might want to post the problem at hand again (It has been "aged out").

Ouch, I feared that.

Briefly, I have a piece of cross-platform Java code which is used in
production environments against at least five different database
backends. Many installations use SQL Server and have been running
reliably since 1998, and several installations use SQL Server 2000
with the com.microsoft.sqlserver.SqlServerDriver satisfactorily.

Yesterday, one of our customers reported a problem and on
investigation we found that their (new) installation wasn't accepting
dates properly. It would not accept the date 28th August 2003 at all,
and when (at my suggestion) they tried 4th August 2003, they got back
8th April 2003, which showed we had a date format problem.

The code asks the database for the column type of each column and
formats the data appropriately; because SQL Server doesn't support
date fields it responds that the date/time fields which on other
databases would be date fields are of type java.sql.Types.TIMESTAMP,
and consequently my code formats them as ANSI 92 timestamp format,
namely

yyyy-mm-dd hh:mm:ss.fffffffff

As I say, we've got loads of SQL Server installations which are
working quite happily with this. We've got exactly one which isn't. We
haven't been able to reproduce the bug on our test machine. We haven't
been able to identify any difference in configuration between the
machine that doesn't work and ones which do.

I'm very unwilling indeed to write special purpose code for different
database backends as it will lead to maintenance problems (I know this,
because we have one special purpose hack to work around an Oracle
misfeature). I'd like to resolve this problem if I can by specifying
the required SQL Server configuration.

We've today sent the customer a patch which dumps and deletes the
database and recreates it with the collation which we have on our
test box but it sounds from what you are saying as though this is
unlikely to work.

Can you offer any other suggestions?

Many thanks

Simon, not much impressed by Microsoft at the best of times.

--
simon@.jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

;; Friends don't send friends HTML formatted emails.|||[microsoft.public.sqlserver.setup removed - can't send to two mail
servers at once, unfortunately...]

Simon,

Can you see what

DBCC USEROPTIONS

returns on the connection that is failing? And if there are no
differences from other servers, whether the syslanguages table has not
been modified?

SK

Simon Brooke wrote:
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.corners tone.se> writes:
>
>>>Just so that, in future, I know the general solution, which 'bit' of the
>>>collation name is it which affects date sequencing?
>>
>>You mean datetime datattype? That is not affected by collations at all. If this is your problem, you
>>might want to post the problem at hand again (It has been "aged out").
>
> Ouch, I feared that.
> Briefly, I have a piece of cross-platform Java code which is used in
> production environments against at least five different database
> backends. Many installations use SQL Server and have been running
> reliably since 1998, and several installations use SQL Server 2000
> with the com.microsoft.sqlserver.SqlServerDriver satisfactorily.
> Yesterday, one of our customers reported a problem and on
> investigation we found that their (new) installation wasn't accepting
> dates properly. It would not accept the date 28th August 2003 at all,
> and when (at my suggestion) they tried 4th August 2003, they got back
> 8th April 2003, which showed we had a date format problem.
> The code asks the database for the column type of each column and
> formats the data appropriately; because SQL Server doesn't support
> date fields it responds that the date/time fields which on other
> databases would be date fields are of type java.sql.Types.TIMESTAMP,
> and consequently my code formats them as ANSI 92 timestamp format,
> namely
> yyyy-mm-dd hh:mm:ss.fffffffff
> As I say, we've got loads of SQL Server installations which are
> working quite happily with this. We've got exactly one which isn't. We
> haven't been able to reproduce the bug on our test machine. We haven't
> been able to identify any difference in configuration between the
> machine that doesn't work and ones which do.
> I'm very unwilling indeed to write special purpose code for different
> database backends as it will lead to maintenance problems (I know this,
> because we have one special purpose hack to work around an Oracle
> misfeature). I'd like to resolve this problem if I can by specifying
> the required SQL Server configuration.
> We've today sent the customer a patch which dumps and deletes the
> database and recreates it with the collation which we have on our
> test box but it sounds from what you are saying as though this is
> unlikely to work.
> Can you offer any other suggestions?
> Many thanks
> Simon, not much impressed by Microsoft at the best of times.|||Hi,

Simon Brooke wrote:

[...]

> So how, for the love of God and Little Fishes, do I persuade a SQL
> Server database to accept ANSI SQL 92 dates, permanently, not on a
> per-session basis?

Back when I was a ASP programmer the way do deal with this was to format the
date like "dd-MMM-yyyy", where "MMM" is the three-letter abbreviation of
the month.
This works because the Database understands how to read the dd-MMM-yyyy
format. this behaviour is not particular to SQL Server, I just tried it in
JDBC/PostgreSQL (don't have access to MSSQL right now) and it works
also... I would be surprised if it didn't worked in JDBC/MSSQL.

CREATE TABLE public.tbl_test
(
datefield date
) ;

********** JAVA *************
Connection c = getConnection();
PreparedStatement statement = c.prepareStatement("INSERT INTO
tbl_test(datefield) VALUES (?)");

statement.setObject(1, "10-Sep-2003");
statement.execute();
statement.clearParameters();
statement.close();
**********************************

SELECT * FROM tbl_test;
datefield
----
2003-09-10
(1 row)

There is a catch though, you have to be carefull with what you write as
"MMM", if the DB server is configured in other language other than English
the month abbreviation must comply to that language.

I hope this helps.

Regards,
Luis Neves|||Simon Brooke (simon@.jasmine.org.uk) writes:
> The code asks the database for the column type of each column and
> formats the data appropriately; because SQL Server doesn't support
> date fields it responds that the date/time fields which on other
> databases would be date fields are of type java.sql.Types.TIMESTAMP,
> and consequently my code formats them as ANSI 92 timestamp format,
> namely
> yyyy-mm-dd hh:mm:ss.fffffffff
> As I say, we've got loads of SQL Server installations which are
> working quite happily with this. We've got exactly one which isn't.

Which smells no bit of luck, given that you post with a UK address.

Try this script:

SET DATEFORMAT dmy
SELECT convert(datetime, '2002-12-18 12:12:12.000') -- Fails
go
SET DATEFORMAT mdy
SELECT convert(datetime, '2002-12-18 12:12:12.000') -- Passes
go
SET LANGUAGE British
SELECT convert(datetime, '2002-12-18 12:12:12.000') -- Fails
go
SET LANGUAGE us_english
SELECT convert(datetime, '2002-12-18 12:12:12.000') -- Passes
go

The dateformat setting is a pure run-time setting. However, changing
language also changes the dateformat setting. And the language can
be set by a default on a login with sp_defaultlanguage. Finally, there
is a server configuration option that determines the default language
for new logins.

If your java app logs in with a certain login, you can probably mandate
that the default language of this login should be one that has a dateformat
of ymd or mdy, for instance Swedish.

If you can't mandate the language, it seems that you need to adapt your
app how much you hate it.

I should add that this problem appears because you are sending down
raw SQL statements to SQL Server, rather than parameterized queries
or RPC calls to stored procedures. If you do this, the client library
will handle the date format and pass SQL Server a binary value which
is not subject to settings. Whether this is possible to do in Java, I
have no idea, but client libraries such as ODBC and ADO supports it,
so why not JDBC?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Steve Kass <skass@.drew.edu> writes:

> [microsoft.public.sqlserver.setup removed - can't send to two mail
> servers at once, unfortunately...]
> Simon,
> Can you see what
> DBCC USEROPTIONS
> returns on the connection that is failing?

I'm sorry, how do I do this? I'm not by any means a SQL Server
expert. I tried it in query analyzer and got:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sbcc'

When I try it over the JDBC connection I get:
DBCC USEROPTIONS
SQL Error
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]No rows affected.
DBCC USEROPTIONS;

SQL Error
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Syntax error at token 0, line 0 offset 0.

> And if there are no
> differences from other servers, whether the syslanguages table has not
> been modified?

There does not appear to be syslanguages table in the database. There
are plenty of other 'dbo.sysxxx' tables, but not syslanguages. This is
SQL Server 2000.

--
simon@.jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/

A message from our sponsor: This site is now in free fall|||"Simon Brooke" <simon@.jasmine.org.uk> wrote in message
news:878yp38qg1.fsf@.gododdin.internal.jasmine.org. uk...
> Steve Kass <skass@.drew.edu> writes:
> > [microsoft.public.sqlserver.setup removed - can't send to two mail
> > servers at once, unfortunately...]
> > Simon,
> > Can you see what
> > DBCC USEROPTIONS
> > returns on the connection that is failing?
> I'm sorry, how do I do this? I'm not by any means a SQL Server
> expert. I tried it in query analyzer and got:
> Server: Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'sbcc'

Umm, here is simply a typo. This will work in Query Analyzter.|||Simon Brooke (simon@.jasmine.org.uk) writes:
> Steve Kass <skass@.drew.edu> writes:
>> [microsoft.public.sqlserver.setup removed - can't send to two mail
>> servers at once, unfortunately...]
>>
>> Simon,
>>
>> Can you see what
>>
>> DBCC USEROPTIONS
>>
>> returns on the connection that is failing?
> I'm sorry, how do I do this? I'm not by any means a SQL Server
> expert. I tried it in query analyzer and got:
> Server: Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'sbcc'

As Greg Strider pointed out, you gave a typo, and I don't want to
be sarcastic or anything, but double-checking what you typed, before
you ask for help, may increase your effectivenesss.

> When I try it over the JDBC connection I get:
> DBCC USEROPTIONS
> SQL Error
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]No rows
> affected.
> DBCC USEROPTIONS;
> SQL Error
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Syntax
> error at token 0, line 0 offset 0.

Some DBCC commands produces their output as messages, which could
confuse some drivers. However, USEROPTIONS always produce a result set.
Maybe the JDBC driver is too smart for its own good and performs its
own parsing, and don't recognize the command. Not knowing about
JDBC I cannot really help.

> There does not appear to be syslanguages table in the database. There
> are plenty of other 'dbo.sysxxx' tables, but not syslanguages. This is
> SQL Server 2000.

syslanguages is in master. I would hold it as unlikely that someone
has changed syslanguages.

In any case, I seem to recall that I tried to explained exactly what
was going on a couple of days ago. Did you see that post?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Simon Brooke <simon@.jasmine.org.uk> writes:

> Briefly, I have a piece of cross-platform Java code which is used in
> production environments against at least five different database
> backends. Many installations use SQL Server and have been running
> reliably since 1998, and several installations use SQL Server 2000
> with the com.microsoft.sqlserver.SqlServerDriver satisfactorily.
> Yesterday, one of our customers reported a problem and on
> investigation we found that their (new) installation wasn't accepting
> dates properly. It would not accept the date 28th August 2003 at all,
> and when (at my suggestion) they tried 4th August 2003, they got back
> 8th April 2003, which showed we had a date format problem.
> The code asks the database for the column type of each column and
> formats the data appropriately; because SQL Server doesn't support
> date fields it responds that the date/time fields which on other
> databases would be date fields are of type java.sql.Types.TIMESTAMP,
> and consequently my code formats them as ANSI 92 timestamp format,
> namely
> yyyy-mm-dd hh:mm:ss.fffffffff
> As I say, we've got loads of SQL Server installations which are
> working quite happily with this. We've got exactly one which isn't. We
> haven't been able to reproduce the bug on our test machine. We haven't
> been able to identify any difference in configuration between the
> machine that doesn't work and ones which do.

OK, just for the record here is the resolution of this issue.

What we found was that on the servers which worked, the user logins
used by the application had language set to 'English', and not either
'US English' or 'British English'. We set the language on the server
that didn't work to 'English', and it worked.

Many thanks to everyone who helped!

--
simon@.jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/
Das Internet is nicht fuer gefingerclicken und giffengrabben... Ist
nicht fuer gewerken bei das dumpkopfen. Das mausklicken sichtseeren
keepen das bandwit-spewin hans in das pockets muss; relaxen und
watchen das cursorblinken. -- quoted from the jargon filesql