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

Between Question

What am I doing wrong in the statement:

q = "Select * Into etclog_holding from etclog where box# BETWEEN (" &
Box1 & " and " & Box2 & ")"

It gives me the following error message: Incorrect syntax near the
keyword 'and'

the following code is what i am working with but it is not working as
it should. I am trying to pull data selected in the inputbox into
ectlog_holding table and run a report off there but keep getting the
errors. Stated above.

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
conn.CursorLocation = adUseClient
conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=ETC;Data
Source=PHNOTES01"
conn.Open

conn.Execute "drop table etclog_holding"
Box1 = Trim(InputBox("Enter starting box#:"))
Box2 = Trim(InputBox("Enter ending box#:"))
q = "Select * Into etclog_holding from etclog where box# Between (" &
Box1 & " and " & Box2 & ")"
rs.Open q, conn, adOpenDynamic, adLockOptimistic

Dim rs1 As New ADODB.Recordset
q = "select * from etclog_holding"
rs1.Open q, conn, adOpenDynamic, adLockOptimistic

Any ideas??On 16 Feb 2005 14:22:39 -0800, pkruti@.hotmail.com wrote:

>What am I doing wrong in the statement:
>q = "Select * Into etclog_holding from etclog where box# BETWEEN (" &
>Box1 & " and " & Box2 & ")"

Hi pkruti,

Try this instead:

q = "Select * Into etclog_holding from etclog where box# BETWEEN " &
Box1 & " and " & Box2

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(pkruti@.hotmail.com) writes:
> What am I doing wrong in the statement:
> q = "Select * Into etclog_holding from etclog where box# BETWEEN (" &
> Box1 & " and " & Box2 & ")"
> It gives me the following error message: Incorrect syntax near the
> keyword 'and'

Have you tried to work out the syntax from reading the topic on
BETWEEN in Books Online? Hint: it's simpler than that you are trying
to make it to be.

Generally, syntax issues are best investigated by using Books Online,
since there are both syntax graphs and examples to work from. It may
take some time first, but it pays back in the long run.

> conn.Execute "drop table etclog_holding"
> Box1 = Trim(InputBox("Enter starting box#:"))
> Box2 = Trim(InputBox("Enter ending box#:"))
> q = "Select * Into etclog_holding from etclog where box# Between (" &
> Box1 & " and " & Box2 & ")"
> rs.Open q, conn, adOpenDynamic, adLockOptimistic
> Dim rs1 As New ADODB.Recordset
> q = "select * from etclog_holding"
> rs1.Open q, conn, adOpenDynamic, adLockOptimistic

There are several things in this snippet I don't like:

1) Creating a table is pointless. Just get the recordset from the
underlying table.
2) Don't use dynamic cursors unless you absolutely have to. And for a
report you don't. Best is to use client-side cursors, which you
by specifying .CursorLocation on the connection object.
3) Do you have validation on InputBox, so that you only get numeric
data from it? Else the user can specify evil things to get your
bacth do other things that you intended. Use parameterized statements
instead. For examples, see
http://authors.aspalliance.com/stev...cles/sprocs.asp

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you Hugo that worked =)

Best way to import a text file every day

I'm in the midst of writing some code to import a file, and then realized
that maybe that's entirely the wrong way to do it. The file in question is
not "simply" formatted, it's based on a semicolon delimiter and has a few
"informational" lines scattered through the document that need to be filtered
out (section headers).
Is this something that the DTS should be doing for us? Can it...
1) read in files that it sees are new?
2) deal with sometimes variant records?
If so, any pointers on where to start reading up? The MS docs that I've seen
so far are rather "dense".
Maury
Maury,
DTS can be used to accoplish the task. What you need is the ActiveX script
task. So check it out. It's more about VB script than about sql then.
There is also a group for DTS where you may find more help.
Quentin
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3DAC19B9-6780-4EBF-B682-9787CF0C04F9@.microsoft.com...
> I'm in the midst of writing some code to import a file, and then realized
> that maybe that's entirely the wrong way to do it. The file in question is
> not "simply" formatted, it's based on a semicolon delimiter and has a few
> "informational" lines scattered through the document that need to be
> filtered
> out (section headers).
> Is this something that the DTS should be doing for us? Can it...
> 1) read in files that it sees are new?
> 2) deal with sometimes variant records?
> If so, any pointers on where to start reading up? The MS docs that I've
> seen
> so far are rather "dense".
> Maury
|||Hi Maury

> The file in question is
> not "simply" formatted, it's based on a semicolon delimiter and has a few
> "informational" lines scattered through the document that need to be
filtered
> out (section headers).
>
the simple way to import a file is preparing the file to be imported. Can
you write a simple program to convert your file to *fix* format without
headers (or with only one header line)? Such a program can work approx. the
same time as DTS import, so the completion time will be only twice bigger
...
Vlastik
|||Thanks guys, I'll take a look. I'm very familiar with VB so this sounds like
the right way to go.

Best way to import a text file every day

I'm in the midst of writing some code to import a file, and then realized
that maybe that's entirely the wrong way to do it. The file in question is
not "simply" formatted, it's based on a semicolon delimiter and has a few
"informational" lines scattered through the document that need to be filtered
out (section headers).
Is this something that the DTS should be doing for us? Can it...
1) read in files that it sees are new?
2) deal with sometimes variant records?
If so, any pointers on where to start reading up? The MS docs that I've seen
so far are rather "dense".
MauryMaury,
DTS can be used to accoplish the task. What you need is the ActiveX script
task. So check it out. It's more about VB script than about sql then.
There is also a group for DTS where you may find more help.
Quentin
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3DAC19B9-6780-4EBF-B682-9787CF0C04F9@.microsoft.com...
> I'm in the midst of writing some code to import a file, and then realized
> that maybe that's entirely the wrong way to do it. The file in question is
> not "simply" formatted, it's based on a semicolon delimiter and has a few
> "informational" lines scattered through the document that need to be
> filtered
> out (section headers).
> Is this something that the DTS should be doing for us? Can it...
> 1) read in files that it sees are new?
> 2) deal with sometimes variant records?
> If so, any pointers on where to start reading up? The MS docs that I've
> seen
> so far are rather "dense".
> Maury|||Hi Maury
> The file in question is
> not "simply" formatted, it's based on a semicolon delimiter and has a few
> "informational" lines scattered through the document that need to be
filtered
> out (section headers).
>
the simple way to import a file is preparing the file to be imported. Can
you write a simple program to convert your file to *fix* format without
headers (or with only one header line)? Such a program can work approx. the
same time as DTS import, so the completion time will be only twice bigger
...
Vlastik|||Thanks guys, I'll take a look. I'm very familiar with VB so this sounds like
the right way to go.

Best way to import a text file every day

I'm in the midst of writing some code to import a file, and then realized
that maybe that's entirely the wrong way to do it. The file in question is
not "simply" formatted, it's based on a semicolon delimiter and has a few
"informational" lines scattered through the document that need to be filtere
d
out (section headers).
Is this something that the DTS should be doing for us? Can it...
1) read in files that it sees are new?
2) deal with sometimes variant records?
If so, any pointers on where to start reading up? The MS docs that I've seen
so far are rather "dense".
MauryMaury,
DTS can be used to accoplish the task. What you need is the ActiveX script
task. So check it out. It's more about VB script than about sql then.
There is also a group for DTS where you may find more help.
Quentin
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3DAC19B9-6780-4EBF-B682-9787CF0C04F9@.microsoft.com...
> I'm in the midst of writing some code to import a file, and then realized
> that maybe that's entirely the wrong way to do it. The file in question is
> not "simply" formatted, it's based on a semicolon delimiter and has a few
> "informational" lines scattered through the document that need to be
> filtered
> out (section headers).
> Is this something that the DTS should be doing for us? Can it...
> 1) read in files that it sees are new?
> 2) deal with sometimes variant records?
> If so, any pointers on where to start reading up? The MS docs that I've
> seen
> so far are rather "dense".
> Maury|||Hi Maury

> The file in question is
> not "simply" formatted, it's based on a semicolon delimiter and has a few
> "informational" lines scattered through the document that need to be
filtered
> out (section headers).
>
the simple way to import a file is preparing the file to be imported. Can
you write a simple program to convert your file to *fix* format without
headers (or with only one header line)? Such a program can work approx. the
same time as DTS import, so the completion time will be only twice bigger
...
Vlastik|||Thanks guys, I'll take a look. I'm very familiar with VB so this sounds like
the right way to go.