Folks, hi.
Any idea why this patently different items return the same binary_checksum value:
select
binary_checksum('X', convert(datetime,'2006-13-12'), convert(datetime,'2007-17-04'), convert(datetime, null),'X','X','X','X','X','S','X') [1],
binary_checksum('X', convert(datetime, '2006-28-11'), convert(datetime,'2007-17-04'), convert(datetime, null),'X','X','X','X','X','B','X') [2]
The core of a very major system is based on these computing unique values for a unique set of expressions!Because BINARY_CHECKSUM is not guaranteed to be unique. From BOL:
BINARY_CHECKSUM(*), computed on any row of a table, returns the same value as long the row is not subsequently modified. BINARY_CHECKSUM(*) will return a different value for most, but not all, changes to the row, and can be used to detect most row modifications.|||The core of a very major system is based on these computing unique values for a unique set of expressions!
Oops. Somebody should have done some research first, eh?|||Oops. Somebody should have done some research first, eh?
At the risk of overstepping my bounds: don't be an anacedent.
Regards,
hmscott|||Point taken...|||A most helpful forum indeed it has to be said.|||Here is how CHECKSUM is calculated. You can see for yourself how Microsoft do and why many checksums are the same.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832|||Thanks Peso.sql
Showing posts with label idea. Show all posts
Showing posts with label idea. Show all posts
Sunday, March 25, 2012
binary_checksum and rowsize error?
Hi,
I can see that by using the object ID rather that the object name, the
following SQL query works. Has anybody got any idea what is causing the
error?
-- Works OK
select o.id
,checksum_agg(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.id
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select object_name(o.id)
,checksum_agg(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by object_name(o.id)
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select o.name
,checksum_agg(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.name
-- Workaround
select getdate()
,object_name(x.id)
,check_sum
from (select m.id
,checksum_agg(binary_checksum(=ADm.text)) as check_sum
from syscomments m
inner join
sysobjects o
on m.id =3D o.id
where o.xtype in ('FN','IF','P','TF','TR','V')
group by m.id) as x=20
Regards=20
LiamIt works at my database. The cause is that there is a row in syscomments
that has a rather long value in the text column, and that pushes the rowsize
of an intermediate result set over the maximum row size. SQL Server uses
only an estimate from the statistics to determine how large the largest row
can be in any intermediate result sets, so it can create an execution plan
that might fail with very long rows, especially when the very long rows are
rare. You can avoid this by using OPTION(ROBUST PLAN).
Jacco Schalkwijk
SQL Server MVP
<liam.caffrey@.gmail.com> wrote in message
news:1117784150.912579.240300@.g14g2000cwa.googlegroups.com...
Hi,
I can see that by using the object ID rather that the object name, the
following SQL query works. Has anybody got any idea what is causing the
error?
-- Works OK
select o.id
,checksum_agg(binary_checksum(_m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.id
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select object_name(o.id)
,checksum_agg(binary_checksum(_m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by object_name(o.id)
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select o.name
,checksum_agg(binary_checksum(_m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.name
-- Workaround
select getdate()
,object_name(x.id)
,check_sum
from (select m.id
,checksum_agg(binary_checksum(_m.text)) as check_sum
from syscomments m
inner join
sysobjects o
on m.id = o.id
where o.xtype in ('FN','IF','P','TF','TR','V')
group by m.id) as x
Regards
Liam
I can see that by using the object ID rather that the object name, the
following SQL query works. Has anybody got any idea what is causing the
error?
-- Works OK
select o.id
,checksum_agg(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.id
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select object_name(o.id)
,checksum_agg(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by object_name(o.id)
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select o.name
,checksum_agg(binary_checksum(=ADm.text))
from sysobjects o
,syscomments m
where o.id =3D m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.name
-- Workaround
select getdate()
,object_name(x.id)
,check_sum
from (select m.id
,checksum_agg(binary_checksum(=ADm.text)) as check_sum
from syscomments m
inner join
sysobjects o
on m.id =3D o.id
where o.xtype in ('FN','IF','P','TF','TR','V')
group by m.id) as x=20
Regards=20
LiamIt works at my database. The cause is that there is a row in syscomments
that has a rather long value in the text column, and that pushes the rowsize
of an intermediate result set over the maximum row size. SQL Server uses
only an estimate from the statistics to determine how large the largest row
can be in any intermediate result sets, so it can create an execution plan
that might fail with very long rows, especially when the very long rows are
rare. You can avoid this by using OPTION(ROBUST PLAN).
Jacco Schalkwijk
SQL Server MVP
<liam.caffrey@.gmail.com> wrote in message
news:1117784150.912579.240300@.g14g2000cwa.googlegroups.com...
Hi,
I can see that by using the object ID rather that the object name, the
following SQL query works. Has anybody got any idea what is causing the
error?
-- Works OK
select o.id
,checksum_agg(binary_checksum(_m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.id
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select object_name(o.id)
,checksum_agg(binary_checksum(_m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by object_name(o.id)
-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select o.name
,checksum_agg(binary_checksum(_m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.name
-- Workaround
select getdate()
,object_name(x.id)
,check_sum
from (select m.id
,checksum_agg(binary_checksum(_m.text)) as check_sum
from syscomments m
inner join
sysobjects o
on m.id = o.id
where o.xtype in ('FN','IF','P','TF','TR','V')
group by m.id) as x
Regards
Liam
Binary Primary Key
I have a 256 bit hash value to identify chunks of data. Would it be a good idea to create a 32-byte binary field as the primary key or encode the bytes to a string?
Well, to me if your natural key really is binary then that is the way I would designate the primary key -- as binary. I'd still like to hear other opinions, though
Tuesday, March 20, 2012
Big problem with triggers
I have little problem and I dont have any idea how to make my trigger.
I have table MyTable where I have many column with almost same name and same
type (Grp1,Grp2,Grp3,Grp4...Grp50 char(1)).
I`d like to make some trigger on UPDATE this table. I must to check which
column was changed.
For example to check if Grp1 was changed I can try this:
ALTER TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS
DECLARE @.OldValue char(1)
DECLARE @.NewValue char(1)
BEGIN
SET @.OldValue = (SELECT Grp1 FROM DELETED)
SET @.NewValue = (SELECT Grp1 FROM INSERTED)
IF (@.OldValue <> @.NewValue)
BEGIN
...
...
...
END
END
Of course I can do this step by step, for all columns, but it`s not good
option.
I tried to make this trigger more dynamicaly, but this not worked (Server:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'DELETED'.)
ALTER TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS
DECLARE @.OldValue char(1)
DECLARE @.NewValue char(1)
DECLARE @.MyQry VarChar(1000)
DECLARE @.ActGrp VarChar(2)
BEGIN
@.ActGrp = '1'
SET @.MyQry ='
SET @.OldValue = (SELECT Grp'+@.ActGrp+' FROM DELETED)
SET @.NewValue = (SELECT Grp'+@.ActGrp+' FROM INSERTED)
IF (@.OldValue <> @.NewValue)
BEGIN
...
...
...
END
'
END
:(
Can anybody help me? How can I easy check all Grp1...Grp50 to know where
colums was changed?Hi
Triggers are activated on a statement and not per row changed, therefore the
use of variables in your trigger to store old and new values will not
produce predictable results.
In general it is better to keep your triggers as short as possible to avoid
prolonging your transactions. If you are trying to produce and audit trail
then I would keep the comparisons external to the trigger and follow the
example of auditing in
http://msdn.microsoft.com/library/d...create_4hk5.asp
John
"BUSHII" <piotrl@.robcom.com.pl> wrote in message
news:cbbcqa$g62$1@.atlantis.news.tpi.pl...
> I have little problem and I dont have any idea how to make my trigger.
> I have table MyTable where I have many column with almost same name and
same
> type (Grp1,Grp2,Grp3,Grp4...Grp50 char(1)).
> I`d like to make some trigger on UPDATE this table. I must to check which
> column was changed.
> For example to check if Grp1 was changed I can try this:
> ALTER TRIGGER MyTrigger
> ON MyTable
> AFTER UPDATE
> AS
> DECLARE @.OldValue char(1)
> DECLARE @.NewValue char(1)
> BEGIN
> SET @.OldValue = (SELECT Grp1 FROM DELETED)
> SET @.NewValue = (SELECT Grp1 FROM INSERTED)
>
> IF (@.OldValue <> @.NewValue)
> BEGIN
> ...
> ...
> ...
> END
> END
>
> Of course I can do this step by step, for all columns, but it`s not good
> option.
> I tried to make this trigger more dynamicaly, but this not worked (Server:
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'DELETED'.)
> ALTER TRIGGER MyTrigger
> ON MyTable
> AFTER UPDATE
> AS
> DECLARE @.OldValue char(1)
> DECLARE @.NewValue char(1)
> DECLARE @.MyQry VarChar(1000)
> DECLARE @.ActGrp VarChar(2)
> BEGIN
> @.ActGrp = '1'
> SET @.MyQry ='
> SET @.OldValue = (SELECT Grp'+@.ActGrp+' FROM DELETED)
> SET @.NewValue = (SELECT Grp'+@.ActGrp+' FROM INSERTED)
>
> IF (@.OldValue <> @.NewValue)
> BEGIN
> ...
> ...
> ...
> END
> '
> END
> :(
> Can anybody help me? How can I easy check all Grp1...Grp50 to know where
> colums was changed?sql
big problem with sql server 2000 msde sp 4 (stops working by accident)
Hi,
I get the following error-message from sqlserver 2000 (msde edition)
I have no idea what is going wrong.
I was only able to get back to working state by
DBCC CHECKDB ALLOW-DATA-LOSS
(or restoring a backup)
But i do not want the db to crash at my customers ...
In the knowledge-base I found:
"An assertion or Msg 7987 may occur when an operation is performed on an
instance of SQL Server"
without any reason why inconsitencies can occur
This bug is at least known since 5th april 2005 ... and still known for
sql-server 9 (2005 I think)
I hope I oversaw something ... or does SQLServer realy stops to work by
chance?
please help!
2006-07-26 03:43:04.00 spid51 ex_raise2: Exception raised, major=79,
minor=87, severity=22, attempting to create symptom dump
2006-07-26 03:43:04.28 spid51 Using 'dbghelp.dll' version '4.0.5'
*Dump thread - spid = 51, PSS = 0x414491a8, EC = 0x414494d8
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 7/26/2006
Time: 5:32:26 AM
User: N/A
Computer: HLX02
Description:
Error: 7987, Severity: 22, State: 3
A possible database consistency problem has been detected on database
'faroer'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database
'faroer'.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 33 1f 00 00 16 00 00 00 3......
0008: 06 00 00 00 48 00 4c 00 ...H.L.
0010: 58 00 30 00 32 00 00 00 X.0.2...
0018: 07 00 00 00 66 00 61 00 ...f.a.
0020: 72 00 6f 00 65 00 72 00 r.o.e.r.
0028: 00 00 ..Sascha Bohnenkamp wrote:
> Hi,
> I get the following error-message from sqlserver 2000 (msde edition)
> I have no idea what is going wrong.
> I was only able to get back to working state by
> DBCC CHECKDB ALLOW-DATA-LOSS
> (or restoring a backup)
> But i do not want the db to crash at my customers ...
> In the knowledge-base I found:
> "An assertion or Msg 7987 may occur when an operation is performed on an
> instance of SQL Server"
> without any reason why inconsitencies can occur
> This bug is at least known since 5th april 2005 ... and still known for
> sql-server 9 (2005 I think)
> I hope I oversaw something ... or does SQLServer realy stops to work by
> chance?
> please help!
> 2006-07-26 03:43:04.00 spid51 ex_raise2: Exception raised, major=79,
> minor=87, severity=22, attempting to create symptom dump
> 2006-07-26 03:43:04.28 spid51 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 51, PSS = 0x414491a8, EC = 0x414494d8
>
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 7/26/2006
> Time: 5:32:26 AM
> User: N/A
> Computer: HLX02
> Description:
> Error: 7987, Severity: 22, State: 3
> A possible database consistency problem has been detected on database
> 'faroer'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database
> 'faroer'.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Data:
> 0000: 33 1f 00 00 16 00 00 00 3......
> 0008: 06 00 00 00 48 00 4c 00 ...H.L.
> 0010: 58 00 30 00 32 00 00 00 X.0.2...
> 0018: 07 00 00 00 66 00 61 00 ...f.a.
> 0020: 72 00 6f 00 65 00 72 00 r.o.e.r.
> 0028: 00 00 ..
No, SQL Server does not "stop working by accident". Most likely you
have flaky hardware that is causing corruption in your database file.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben schrieb:
> No, SQL Server does not "stop working by accident". Most likely you
> have flaky hardware that is causing corruption in your database file.
well ... are there any chance to find someting to help find the problem
in the log files of the server (or in the dumps) ?
From teh stack dump I cannot see any i/o problems ...
* Short Stack Dump
* 009BA08C Module(sqlservr+005BA08C) (GetOSErrString+00004F68)
* 009BA9B5 Module(sqlservr+005BA9B5) (GetOSErrString+00005891)
* 006EE757 Module(sqlservr+002EE757) (SQLExit+00186C60)
* 005FA686 Module(sqlservr+001FA686) (SQLExit+00092B8F)
* 006BA785 Module(sqlservr+002BA785) (SQLExit+00152C8E)
* 004040DA Module(sqlservr+000040DA)
* 0042ADAE Module(sqlservr+0002ADAE)
* 0042A3D3 Module(sqlservr+0002A3D3)
* 0043638B Module(sqlservr+0003638B)
* 0043288B Module(sqlservr+0003288B)
* 00432542 Module(sqlservr+00032542)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
* 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
* 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
* 004398A5 Module(sqlservr+000398A5)
* 004398A5 Module(sqlservr+000398A5)
* 0041D396 Module(sqlservr+0001D396)
* 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
* 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
* 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
* 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 004194B9 Module(sqlservr+000194B9)
* 004193E4 Module(sqlservr+000193E4)
* 00429EAA Module(sqlservr+00029EAA)
* 00415D04 Module(sqlservr+00015D04)
* 00416214 Module(sqlservr+00016214)
* 00415F28 Module(sqlservr+00015F28)
* 0076C7D0 Module(sqlservr+0036C7D0) (SQLExit+00204CD9)
* 007704EF Module(sqlservr+003704EF) (SQLExit+002089F8)
* 0077099C Module(sqlservr+0037099C) (SQLExit+00208EA5)
* 006332EC Module(sqlservr+002332EC) (SQLExit+000CB7F5)
* 0043D005 Module(sqlservr+0003D005)
* 0042598D Module(sqlservr+0002598D)
* 41075309 Module(ums+00005309) (UmsThreadScheduler::ExitUser+00000459)|||Sascha Bohnenkamp wrote:
> Tracy McKibben schrieb:
>> No, SQL Server does not "stop working by accident". Most likely you
>> have flaky hardware that is causing corruption in your database file.
> well ... are there any chance to find someting to help find the problem
> in the log files of the server (or in the dumps) ?
> From teh stack dump I cannot see any i/o problems ...
> * Short Stack Dump
> * 009BA08C Module(sqlservr+005BA08C) (GetOSErrString+00004F68)
> * 009BA9B5 Module(sqlservr+005BA9B5) (GetOSErrString+00005891)
> * 006EE757 Module(sqlservr+002EE757) (SQLExit+00186C60)
> * 005FA686 Module(sqlservr+001FA686) (SQLExit+00092B8F)
> * 006BA785 Module(sqlservr+002BA785) (SQLExit+00152C8E)
> * 004040DA Module(sqlservr+000040DA)
> * 0042ADAE Module(sqlservr+0002ADAE)
> * 0042A3D3 Module(sqlservr+0002A3D3)
> * 0043638B Module(sqlservr+0003638B)
> * 0043288B Module(sqlservr+0003288B)
> * 00432542 Module(sqlservr+00032542)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
> * 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
> * 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
> * 004398A5 Module(sqlservr+000398A5)
> * 004398A5 Module(sqlservr+000398A5)
> * 0041D396 Module(sqlservr+0001D396)
> * 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
> * 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
> * 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
> * 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 004194B9 Module(sqlservr+000194B9)
> * 004193E4 Module(sqlservr+000193E4)
> * 00429EAA Module(sqlservr+00029EAA)
> * 00415D04 Module(sqlservr+00015D04)
> * 00416214 Module(sqlservr+00016214)
> * 00415F28 Module(sqlservr+00015F28)
> * 0076C7D0 Module(sqlservr+0036C7D0) (SQLExit+00204CD9)
> * 007704EF Module(sqlservr+003704EF) (SQLExit+002089F8)
> * 0077099C Module(sqlservr+0037099C) (SQLExit+00208EA5)
> * 006332EC Module(sqlservr+002332EC) (SQLExit+000CB7F5)
> * 0043D005 Module(sqlservr+0003D005)
> * 0042598D Module(sqlservr+0002598D)
> * 41075309 Module(ums+00005309) (UmsThreadScheduler::ExitUser+00000459)
Doesn't have to be an I/O problem - it could be bad RAM, a bad CPU, any
number of things. You should run some hardware diagnostics on the
machine, perhaps even open an incident with Microsoft - they can help
you decipher the logs.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
I get the following error-message from sqlserver 2000 (msde edition)
I have no idea what is going wrong.
I was only able to get back to working state by
DBCC CHECKDB ALLOW-DATA-LOSS
(or restoring a backup)
But i do not want the db to crash at my customers ...
In the knowledge-base I found:
"An assertion or Msg 7987 may occur when an operation is performed on an
instance of SQL Server"
without any reason why inconsitencies can occur
This bug is at least known since 5th april 2005 ... and still known for
sql-server 9 (2005 I think)
I hope I oversaw something ... or does SQLServer realy stops to work by
chance?
please help!
2006-07-26 03:43:04.00 spid51 ex_raise2: Exception raised, major=79,
minor=87, severity=22, attempting to create symptom dump
2006-07-26 03:43:04.28 spid51 Using 'dbghelp.dll' version '4.0.5'
*Dump thread - spid = 51, PSS = 0x414491a8, EC = 0x414494d8
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 7/26/2006
Time: 5:32:26 AM
User: N/A
Computer: HLX02
Description:
Error: 7987, Severity: 22, State: 3
A possible database consistency problem has been detected on database
'faroer'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database
'faroer'.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 33 1f 00 00 16 00 00 00 3......
0008: 06 00 00 00 48 00 4c 00 ...H.L.
0010: 58 00 30 00 32 00 00 00 X.0.2...
0018: 07 00 00 00 66 00 61 00 ...f.a.
0020: 72 00 6f 00 65 00 72 00 r.o.e.r.
0028: 00 00 ..Sascha Bohnenkamp wrote:
> Hi,
> I get the following error-message from sqlserver 2000 (msde edition)
> I have no idea what is going wrong.
> I was only able to get back to working state by
> DBCC CHECKDB ALLOW-DATA-LOSS
> (or restoring a backup)
> But i do not want the db to crash at my customers ...
> In the knowledge-base I found:
> "An assertion or Msg 7987 may occur when an operation is performed on an
> instance of SQL Server"
> without any reason why inconsitencies can occur
> This bug is at least known since 5th april 2005 ... and still known for
> sql-server 9 (2005 I think)
> I hope I oversaw something ... or does SQLServer realy stops to work by
> chance?
> please help!
> 2006-07-26 03:43:04.00 spid51 ex_raise2: Exception raised, major=79,
> minor=87, severity=22, attempting to create symptom dump
> 2006-07-26 03:43:04.28 spid51 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 51, PSS = 0x414491a8, EC = 0x414494d8
>
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 7/26/2006
> Time: 5:32:26 AM
> User: N/A
> Computer: HLX02
> Description:
> Error: 7987, Severity: 22, State: 3
> A possible database consistency problem has been detected on database
> 'faroer'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database
> 'faroer'.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Data:
> 0000: 33 1f 00 00 16 00 00 00 3......
> 0008: 06 00 00 00 48 00 4c 00 ...H.L.
> 0010: 58 00 30 00 32 00 00 00 X.0.2...
> 0018: 07 00 00 00 66 00 61 00 ...f.a.
> 0020: 72 00 6f 00 65 00 72 00 r.o.e.r.
> 0028: 00 00 ..
No, SQL Server does not "stop working by accident". Most likely you
have flaky hardware that is causing corruption in your database file.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben schrieb:
> No, SQL Server does not "stop working by accident". Most likely you
> have flaky hardware that is causing corruption in your database file.
well ... are there any chance to find someting to help find the problem
in the log files of the server (or in the dumps) ?
From teh stack dump I cannot see any i/o problems ...
* Short Stack Dump
* 009BA08C Module(sqlservr+005BA08C) (GetOSErrString+00004F68)
* 009BA9B5 Module(sqlservr+005BA9B5) (GetOSErrString+00005891)
* 006EE757 Module(sqlservr+002EE757) (SQLExit+00186C60)
* 005FA686 Module(sqlservr+001FA686) (SQLExit+00092B8F)
* 006BA785 Module(sqlservr+002BA785) (SQLExit+00152C8E)
* 004040DA Module(sqlservr+000040DA)
* 0042ADAE Module(sqlservr+0002ADAE)
* 0042A3D3 Module(sqlservr+0002A3D3)
* 0043638B Module(sqlservr+0003638B)
* 0043288B Module(sqlservr+0003288B)
* 00432542 Module(sqlservr+00032542)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
* 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
* 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
* 004398A5 Module(sqlservr+000398A5)
* 004398A5 Module(sqlservr+000398A5)
* 0041D396 Module(sqlservr+0001D396)
* 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
* 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
* 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
* 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 004194B9 Module(sqlservr+000194B9)
* 004193E4 Module(sqlservr+000193E4)
* 00429EAA Module(sqlservr+00029EAA)
* 00415D04 Module(sqlservr+00015D04)
* 00416214 Module(sqlservr+00016214)
* 00415F28 Module(sqlservr+00015F28)
* 0076C7D0 Module(sqlservr+0036C7D0) (SQLExit+00204CD9)
* 007704EF Module(sqlservr+003704EF) (SQLExit+002089F8)
* 0077099C Module(sqlservr+0037099C) (SQLExit+00208EA5)
* 006332EC Module(sqlservr+002332EC) (SQLExit+000CB7F5)
* 0043D005 Module(sqlservr+0003D005)
* 0042598D Module(sqlservr+0002598D)
* 41075309 Module(ums+00005309) (UmsThreadScheduler::ExitUser+00000459)|||Sascha Bohnenkamp wrote:
> Tracy McKibben schrieb:
>> No, SQL Server does not "stop working by accident". Most likely you
>> have flaky hardware that is causing corruption in your database file.
> well ... are there any chance to find someting to help find the problem
> in the log files of the server (or in the dumps) ?
> From teh stack dump I cannot see any i/o problems ...
> * Short Stack Dump
> * 009BA08C Module(sqlservr+005BA08C) (GetOSErrString+00004F68)
> * 009BA9B5 Module(sqlservr+005BA9B5) (GetOSErrString+00005891)
> * 006EE757 Module(sqlservr+002EE757) (SQLExit+00186C60)
> * 005FA686 Module(sqlservr+001FA686) (SQLExit+00092B8F)
> * 006BA785 Module(sqlservr+002BA785) (SQLExit+00152C8E)
> * 004040DA Module(sqlservr+000040DA)
> * 0042ADAE Module(sqlservr+0002ADAE)
> * 0042A3D3 Module(sqlservr+0002A3D3)
> * 0043638B Module(sqlservr+0003638B)
> * 0043288B Module(sqlservr+0003288B)
> * 00432542 Module(sqlservr+00032542)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
> * 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
> * 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
> * 004398A5 Module(sqlservr+000398A5)
> * 004398A5 Module(sqlservr+000398A5)
> * 0041D396 Module(sqlservr+0001D396)
> * 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
> * 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
> * 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
> * 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 004194B9 Module(sqlservr+000194B9)
> * 004193E4 Module(sqlservr+000193E4)
> * 00429EAA Module(sqlservr+00029EAA)
> * 00415D04 Module(sqlservr+00015D04)
> * 00416214 Module(sqlservr+00016214)
> * 00415F28 Module(sqlservr+00015F28)
> * 0076C7D0 Module(sqlservr+0036C7D0) (SQLExit+00204CD9)
> * 007704EF Module(sqlservr+003704EF) (SQLExit+002089F8)
> * 0077099C Module(sqlservr+0037099C) (SQLExit+00208EA5)
> * 006332EC Module(sqlservr+002332EC) (SQLExit+000CB7F5)
> * 0043D005 Module(sqlservr+0003D005)
> * 0042598D Module(sqlservr+0002598D)
> * 41075309 Module(ums+00005309) (UmsThreadScheduler::ExitUser+00000459)
Doesn't have to be an I/O problem - it could be bad RAM, a bad CPU, any
number of things. You should run some hardware diagnostics on the
machine, perhaps even open an incident with Microsoft - they can help
you decipher the logs.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Sunday, March 11, 2012
BI Studio shuts automatically
I am having a weird trouble with BI Studio. Whenever I start a integration services project the BI Studio just closes automatically. any idea whats going on here? is this a bug? I am already on sp2.
Hello,
are you sure you have necessary SSIS components installed. Has it been working on that machine at all?
Thanks.
Sunday, February 19, 2012
Best way to retrieve Oracle data into SS2000 near real-time?
I have no idea where to post this kind of question, so here it is!
I have a requirement to retrieve oracle 10 data into SS2000 in as near real-time as possible (stupid users!) and join with resident SS data for on-demand reporting. (We use SS replication to populate some reporting tables from other SS2000 instances and this has spoiled the users as well as the developers! )
I would like to know if there are any clever ways of doing this, or if a plain-old DTS package running in some kind of loop is the practical answer. 1 minute delay is probably too long . . . I don't know if data can be pushed from the oracle side. Or if we need to write a Service and use it to suck and push.
Any suggestions?
thanks!
In a bank I worked a while back we had a DTS package that runs five hours a day five days a week taking DB2 AS400 deposits to the web first 7.0 then 2000 almost real time. The package runs as a job with the Agent using xp_cmdshell and it works like a charm. Hope this helps.
Subscribe to:
Posts (Atom)