a fatal error of sqlserver2005

I find many errors these days in my sqlserver 2005. description is :

SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xfab258f6; actual: 0x7ab258fb). It occurred during a read of page (1:387359) in database ID 20 at offset 0x000000bd23e000 in file 'F:\SQL2005_Data\UKDB.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

when I run dbcc checkdb, it report "Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC which prevented further processing. Please contact Product Support."

sqlserver is :

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

OS is : Win2003 Server Standard edition with sp1

hardware is :

two hardisk with RAID 1 on Silicon image 3114 RAID controller

It sounds like you have a corrupt database.
Some types of corruption result in severe errors.

As a first step, you should consider restoring this database from a good backup.

Be sure to check your system event log to see if any hardware errors are being logged.

The invalid checksum indicates that the storage of the database has been changed after we last wrote it. Flakey hardware is the main reason that checksums were added in sql2005.

If this problem persists, let us know.

Also, let me know if you'd like me to use the contact information in your forum profile to communicate with you offline.

|||

I find my memory has problem. It cause the sql error, thank you for your help.

sql

A cursor with the name 'TESTING' does not exist.

Hi,
I have created a simple stored procedure and I am getting some errors in it.
I couldn't figure out why the error is.
Any help would be appreciated.
Here is my sp:
---
CREATE PROCEDURE dbo.test
(
@.ID int,
@.NUMBERS nvarchar(2000)
)
AS
DECLARE @.REF int
EXEC('DECLARE TESTING CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT REF FROM TABLE1 WHERE ID IN (SELECT DISTINCT ID
FROM TABLE2
WHERE (DS_ID IN (8))
AND (TABLE2.NUMBERS IN (SELECT CONVERT(nvarchar(20),VALUE) COLLATE
SQL_Latin1_General_CP1_CI_AS FROM fn_Split('''+@.NUMBERS+''','+''',''' + ')))
AND (REF <= 0) AND (S_ID ='+ @.ID + '))
GROUP BY REF')
OPEN TESTING
FETCH NEXT FROM TESTING
INTO @.REF
WHILE @.@.FETCH_STATUS = 0
BEGIN
--doing something
FETCH NEXT FROM TESTING INTO @.REF
END
CLOSE TESTING
DEALLOCATE TESTING
---
When I try to run this, the errors I get
Server: Msg 16916, Level 16, State 1, Procedure test, Line 21
A cursor with the name 'TESTING' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure test, Line 22
A cursor with the name 'TESTING' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure test, Line 33
A cursor with the name 'TESTING' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure test, Line 34
A cursor with the name 'TESTING' does not exist.
Thanks
KiranKiran,
The rest of the Cursor code need to be within the EXEC statement.
Gopi
"Kiran" <kiran_nospam@.gmail.com> wrote in message
news:O0WJF0eWFHA.2540@.tk2msftngp13.phx.gbl...
> Hi,
> I have created a simple stored procedure and I am getting some errors in
> it.
> I couldn't figure out why the error is.
> Any help would be appreciated.
> Here is my sp:
> ---
> CREATE PROCEDURE dbo.test
> (
> @.ID int,
> @.NUMBERS nvarchar(2000)
> )
> AS
>
> DECLARE @.REF int
>
>
> EXEC('DECLARE TESTING CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT REF FROM TABLE1 WHERE ID IN (SELECT DISTINCT ID
> FROM TABLE2
> WHERE (DS_ID IN (8))
> AND (TABLE2.NUMBERS IN (SELECT CONVERT(nvarchar(20),VALUE) COLLATE
> SQL_Latin1_General_CP1_CI_AS FROM fn_Split('''+@.NUMBERS+''','+''',''' +
> ')))
> AND (REF <= 0) AND (S_ID ='+ @.ID + '))
> GROUP BY REF')
> OPEN TESTING
> FETCH NEXT FROM TESTING
> INTO @.REF
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --doing something
> FETCH NEXT FROM TESTING INTO @.REF
> END
> CLOSE TESTING
> DEALLOCATE TESTING
> ---
> When I try to run this, the errors I get
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 21
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 22
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 33
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 34
> A cursor with the name 'TESTING' does not exist.
> Thanks
> Kiran
>|||Don't declare it as a local cursor, that makes it be local to the EXEC. And,
consider if you can do
without the cursor in the first place, tend to be code easier to read and pe
rform better.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kiran" <kiran_nospam@.gmail.com> wrote in message news:O0WJF0eWFHA.2540@.tk2msftngp13.phx.gb
l...
> Hi,
> I have created a simple stored procedure and I am getting some errors in i
t.
> I couldn't figure out why the error is.
> Any help would be appreciated.
> Here is my sp:
> ---
> CREATE PROCEDURE dbo.test
> (
> @.ID int,
> @.NUMBERS nvarchar(2000)
> )
> AS
>
> DECLARE @.REF int
>
>
> EXEC('DECLARE TESTING CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT REF FROM TABLE1 WHERE ID IN (SELECT DISTINCT ID
> FROM TABLE2
> WHERE (DS_ID IN (8))
> AND (TABLE2.NUMBERS IN (SELECT CONVERT(nvarchar(20),VALUE) COLLATE
> SQL_Latin1_General_CP1_CI_AS FROM fn_Split('''+@.NUMBERS+''','+''',''' + ')
))
> AND (REF <= 0) AND (S_ID ='+ @.ID + '))
> GROUP BY REF')
> OPEN TESTING
> FETCH NEXT FROM TESTING
> INTO @.REF
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --doing something
> FETCH NEXT FROM TESTING INTO @.REF
> END
> CLOSE TESTING
> DEALLOCATE TESTING
> ---
> When I try to run this, the errors I get
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 21
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 22
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 33
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 34
> A cursor with the name 'TESTING' does not exist.
> Thanks
> Kiran
>|||Thanks a ton Tibor.
Kiran kumar
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ed39iCfWFHA.616@.TK2MSFTNGP12.phx.gbl...
> Don't declare it as a local cursor, that makes it be local to the EXEC.
> And, consider if you can do without the cursor in the first place, tend to
> be code easier to read and perform better.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kiran" <kiran_nospam@.gmail.com> wrote in message
> news:O0WJF0eWFHA.2540@.tk2msftngp13.phx.gbl...
>

a connection could not be made to the data source

I am trying to process a cube still it failed since "Errors in the high-level relational engine. A connection could not be made to the data source with the DataSource of "MY-DATASOURCE", Name of "MY-DATASOURCE").

The sql instance is running on the same machine in where I am working the solution. Can someone provide some hints on what to do to resolve this issue?

By looking at the "AdventuresWorks" sample the main difference that I have in my solution is that I have an instance and the AW sample is pointing to the default instance using the "localhost" reference only. My datasource connection is refered as "MyHost\MySqlInstance"...
I used the wong impersonation settings! I changed those and now I am processing the cube.

9MB of errors in log file each time RS report is run

I have a report that is running extremely slowly. The Reporting
Services log files seem to generate 9MB of errors each time I run the
report. The error is the text below repeated over and over. Can anyone
help?
Thanks,
Burt
<Header>
<Product>Microsoft SQL Server Reporting Services Version
8.00.743.00</Product>
<Locale>en-US</Locale>
<TimeZone>Pacific Daylight Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServer__07_01_2004_11_38_28.log</Path>
<SystemName>SFCMSREPORTS</SystemName>
<OSName>Microsoft Windows NT 5.0.2195.0</OSName>
<OSVersion>5.0.2195.0</OSVersion>
</Header>
aspnet_wp!processing!1c24!7/1/2004-11:38:28:: a ASSERT: Assertion
failed! Call stack:
Microsoft.ReportingServices.ReportProcessing.ExprHostObjectModel.IndexedExprHost.get_Item(Int32
index)
Microsoft.ReportingServices.ReportProcessing.be.a(e A_0, ObjectType
A_1, String A_2, String A_3)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+as.a(e
A_0, String A_1)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+as.c()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.b()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.e()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.h()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.c()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ac.b()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.d()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.g()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.a(a5
A_0)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.f()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.a(Boolean&
A_0)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.b()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.a(Object
A_0)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+s.a()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+s.a(Object
A_0)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+bb.a(ParameterInfoCollection
A_0, Boolean A_1, GetReportChunk A_2)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(Report
A_0, ProcessingContext A_1, Boolean A_2, GetReportChunk A_3, ax A_4,
DateTime A_5, z& A_6)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(DateTime
executionTimeStamp, GetReportChunk getCompiledDefinitionCallback,
ProcessingContext pc, RenderingContext rc)
Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext
reportContext, ItemProperties properties, ParameterInfoCollection
effectiveParameters, Guid reportId, ClientRequest session, String
description, ReportSnapshot intermediateSnapshot,
DataSourceInfoCollection thisReportDataSources, Boolean
cachingRequested, Warning[]& warnings, ReportSnapshot&
resultSnapshotData, DateTime& executionDateTime,
RuntimeDataSourceInfoCollection& alldataSources, UserProfileState&
usedUserProfile)
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService
rs, CatalogItemContext reportContext, ClientRequest session,
JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection&
effectiveParameters, String[]& secondaryStreamNames)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory
streamFactory)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport()
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType
itemType)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.ProcessRequest(HttpContext
context)
System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep.Execute()
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
completedSynchronously)
System.Web.HttpApplication.ResumeSteps(Exception error)
System.Web.HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext
context, AsyncCallback cb, Object extraData)
System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
System.Web.HttpRuntime.ProcessRequest(HttpWorkerRequest wr)
System.Web.Hosting.ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32
iWRType)Hi Burt, could you please send report definition?
"Burt" burt_5920@.yahoo.com wrote in message
news:19e5f39f.0407011047.ccfd350@.posting.google.com...
> I have a report that is running extremely slowly. The Reporting
> Services log files seem to generate 9MB of errors each time I run the
> report. The error is the text below repeated over and over. Can anyone
> help?
> Thanks,
> Burt
> <Header>
> <Product>Microsoft SQL Server Reporting Services Version
> 8.00.743.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>Pacific Daylight Time</TimeZone>
> <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\LogFiles\ReportServer__07_01_2004_11_38_28.log</Path>
> <SystemName>SFCMSREPORTS</SystemName>
> <OSName>Microsoft Windows NT 5.0.2195.0</OSName>
> <OSVersion>5.0.2195.0</OSVersion>
> </Header>
> aspnet_wp!processing!1c24!7/1/2004-11:38:28:: a ASSERT: Assertion
> failed! Call stack:
>
Microsoft.ReportingServices.ReportProcessing.ExprHostObjectModel.IndexedExpr
Host.get_Item(Int32
> index)
> Microsoft.ReportingServices.ReportProcessing.be.a(e A_0, ObjectType
> A_1, String A_2, String A_3)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+as.a(e
> A_0, String A_1)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+as.c()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.b()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.e()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.h()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.c()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ac.b()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.d()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.g()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.a(a5
> A_0)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.f()
>
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.a(Boolean&
> A_0)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.b()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.a(Object
> A_0)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+s.a()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+s.a(Object
> A_0)
>
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+bb.a(Parameter
InfoCollection
> A_0, Boolean A_1, GetReportChunk A_2)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(Report
> A_0, ProcessingContext A_1, Boolean A_2, GetReportChunk A_3, ax A_4,
> DateTime A_5, z& A_6)
>
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(D
ateTime
> executionTimeStamp, GetReportChunk getCompiledDefinitionCallback,
> ProcessingContext pc, RenderingContext rc)
>
Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContex
t
> reportContext, ItemProperties properties, ParameterInfoCollection
> effectiveParameters, Guid reportId, ClientRequest session, String
> description, ReportSnapshot intermediateSnapshot,
> DataSourceInfoCollection thisReportDataSources, Boolean
> cachingRequested, Warning[]& warnings, ReportSnapshot&
> resultSnapshotData, DateTime& executionDateTime,
> RuntimeDataSourceInfoCollection& alldataSources, UserProfileState&
> usedUserProfile)
>
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(Catalog
ItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters)
>
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters, String[]&
> secondaryStreamNames)
> Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
>
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
>
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RS
Service
> rs, CatalogItemContext reportContext, ClientRequest session,
> JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection&
> effectiveParameters, String[]& secondaryStreamNames)
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(
HttpResponseStreamFactory
> streamFactory)
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(
)
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(It
emType
> itemType)
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageCon
tent()
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.ProcessReques
t(HttpContext
> context)
>
System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplicati
on+IExecutionStep.Execute()
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
> completedSynchronously)
> System.Web.HttpApplication.ResumeSteps(Exception error)
>
System.Web.HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(
HttpContext
> context, AsyncCallback cb, Object extraData)
> System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
> System.Web.HttpRuntime.ProcessRequest(HttpWorkerRequest wr)
> System.Web.Hosting.ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32
> iWRType)

80004005 ODBC SQL Server Driver shared memory or TCP/IP sockets er

Hi all,
I have been getting these errors on simple ASP code on my web site:
|80004005|[Microsoft][ODBC_SQL_Server_Driver][TCP/IP_Sockets]ConnectionWrite_(send()).
|80004005|[Microsoft][ODBC_SQL_Server_Driver]Timeout_expired
|80004005|[Microsoft][ODBC_SQL_Server_Driver][Shared_Memory]SQL_Server_does_not_exist_or_access_denied.
|80004005|[Microsoft][ODBC_SQL_Server_Driver][TCP/IP_Sockets]SQL_Server_does_not_exist_or_access_denied.
|80004005|[Microsoft][ODBC_SQL_Server_Driver]Timeout_expired
The SQL 2000 is Standard Edition, SP3 and OS is Windows 2003 Standard edition.
Previously I never faced this problem before. These errors come
intermittently, at no specific time. It appears randomly at any time.
The only thing I have to do is reboot the server to solve this issue.
please tell me if there's anything to solve this issue.
reboot?
"tapman" wrote:

> Hi all,
> I have been getting these errors on simple ASP code on my web site:
> |80004005|[Microsoft][ODBC_SQL_Server_Driver][TCP/IP_Sockets]ConnectionWrite_(send()).
> |80004005|[Microsoft][ODBC_SQL_Server_Driver]Timeout_expired
> |80004005|[Microsoft][ODBC_SQL_Server_Driver][Shared_Memory]SQL_Server_does_not_exist_or_access_denied.
>
> |80004005|[Microsoft][ODBC_SQL_Server_Driver][TCP/IP_Sockets]SQL_Server_does_not_exist_or_access_denied.
> |80004005|[Microsoft][ODBC_SQL_Server_Driver]Timeout_expired
> The SQL 2000 is Standard Edition, SP3 and OS is Windows 2003 Standard edition.
> Previously I never faced this problem before. These errors come
> intermittently, at no specific time. It appears randomly at any time.
> The only thing I have to do is reboot the server to solve this issue.
> please tell me if there's anything to solve this issue.
>

701 error

We are experiencing the same errors and same "lock out" behavior. We have the same version/patch level of SQL installed but are in a 32 bit env. The box has 32GB of memory. Nether the bPool or VAS show any pressure when the "out of system memory error" occurs. Since this error usually indicates a VAS problem we tried increasing VAS to 1GB w/the -g flag. It made no difference. PSS has been working on the case for 3 weeks. They dont seem to be finding any evidince of memory pressure either. When I last spole to the escalation engineer yesterday it seemed that they are going to recommend reducing the number of databases on the server. I asked for clarification as to whether we are hitting a 32 bit barrior, an instance limitation, or both. I am awaiting the answer. How many databases do you have on your server? We had between 1700 and 1900 (the number varies) at times when the error occured. We are now at 1500, and have not had the error in the 2 days since reducing the number of databases...
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
Steve,
Further comment.
I have been having the problem about once a month for several months and
have not had any joy in discovering what to do about it. Other notes on my
case:
- Once this starts, my CPU utilization will go to very close to 100%. If I
log out some heavy users, my CPU will dip to 70% or 80% for about 20
seconds, then climb back to near 100%.
- At this point, spid 1 is the busiest of the spids. (Which makes it seem
that the SQL Server is eating up its resources trying to find resources for
users.)
- Very Strange Discovery. One time I found that one workstation which had
been running a very large transaction, would, if turned off, drop the server
CPU to normal. But if I turned that workstation back on, even though I did
not access the server, the server workload would go back to 100%. It was as
if the SQL Server remembered the workstation and would try to do something
for it. (The next day this workstation did not have such an effect.) As I
said, Very Strange.
We also are on 32-bit using AWE memory, but only 12 GB. (We also only have
about 60 databases on the machine, nothing like your 1500+.) This weekend,
we are upgrading to 64-bit Windows 2003/SQL Server 2005. We will see if
this recurs, or whether it is an AWE flaw.
This thread from a Red Gate user suggests the problem is not free memory,
but free contiguous memory.
[url]http://www.red-gate.com/MessageBoard/viewtopic.php?p=15220&sid=9c1df0b8ddef4fc6745b1ce1 33f1e6b1[/url]
So, if very large queries and cursor usage are responsible for fragmenting
memory (per something I read elsewhere) I don't know what to do except to
schedule periodic restarts of the the SQL Server.
RLF
<steve> wrote in message news:200722311948sgoodwin@.autotask.com...
> We are experiencing the same errors and same "lock out" behavior. We have
> the same version/patch level of SQL installed but are in a 32 bit env. The
> box has 32GB of memory. Nether the bPool or VAS show any pressure when the
> "out of system memory error" occurs. Since this error usually indicates a
> VAS problem we tried increasing VAS to 1GB w/the -g flag. It made no
> difference. PSS has been working on the case for 3 weeks. They dont seem
> to be finding any evidince of memory pressure either. When I last spole to
> the escalation engineer yesterday it seemed that they are going to
> recommend reducing the number of databases on the server. I asked for
> clarification as to whether we are hitting a 32 bit barrior, an instance
> limitation, or both. I am awaiting the answer. How many databases do you
> have on your server? We had between 1700 and 1900 (the number varies) at
> times when the error occured. We are now at 1500, and have not had the
> error in the 2 days since reducing the number of databases...
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com

701 error

We are experiencing the same errors and same "lock out" behavior. We have th
e same version/patch level of SQL installed but are in a 32 bit env. The box
has 32GB of memory. Nether the bPool or VAS show any pressure when the "out
of system memory error" oc
curs. Since this error usually indicates a VAS problem we tried increasing V
AS to 1GB w/the -g flag. It made no difference. PSS has been working on the
case for 3 weeks. They dont seem to be finding any evidince of memory pressu
re either. When I last spol
e to the escalation engineer yesterday it seemed that they are going to reco
mmend reducing the number of databases on the server. I asked for clarificat
ion as to whether we are hitting a 32 bit barrior, an instance limitation, o
r both. I am awaiting the a
nswer. How many databases do you have on your server? We had between 1700 an
d 1900 (the number varies) at times when the error occured. We are now at 15
00, and have not had the error in the 2 days since reducing the number of da
tabases...
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.comSteve,
Further comment.
I have been having the problem about once a month for several months and
have not had any joy in discovering what to do about it. Other notes on my
case:
- Once this starts, my CPU utilization will go to very close to 100%. If I
log out some heavy users, my CPU will dip to 70% or 80% for about 20
seconds, then climb back to near 100%.
- At this point, spid 1 is the busiest of the spids. (Which makes it seem
that the SQL Server is eating up its resources trying to find resources for
users.)
- Very Strange Discovery. One time I found that one workstation which had
been running a very large transaction, would, if turned off, drop the server
CPU to normal. But if I turned that workstation back on, even though I did
not access the server, the server workload would go back to 100%. It was as
if the SQL Server remembered the workstation and would try to do something
for it. (The next day this workstation did not have such an effect.) As I
said, Very Strange.
We also are on 32-bit using AWE memory, but only 12 GB. (We also only have
about 60 databases on the machine, nothing like your 1500+.) This weekend,
we are upgrading to 64-bit Windows 2003/SQL Server 2005. We will see if
this recurs, or whether it is an AWE flaw.
This thread from a Red Gate user suggests the problem is not free memory,
but free contiguous memory.
http://www.red-gate.com/MessageBoar...
b1ce133f1e6b1
So, if very large queries and cursor usage are responsible for fragmenting
memory (per something I read elsewhere) I don't know what to do except to
schedule periodic restarts of the the SQL Server.
RLF
<steve> wrote in message news:200722311948sgoodwin@.autotask.com...
> We are experiencing the same errors and same "lock out" behavior. We have
> the same version/patch level of SQL installed but are in a 32 bit env. The
> box has 32GB of memory. Nether the bPool or VAS show any pressure when the
> "out of system memory error" occurs. Since this error usually indicates a
> VAS problem we tried increasing VAS to 1GB w/the -g flag. It made no
> difference. PSS has been working on the case for 3 weeks. They dont seem
> to be finding any evidince of memory pressure either. When I last spole to
> the escalation engineer yesterday it seemed that they are going to
> recommend reducing the number of databases on the server. I asked for
> clarification as to whether we are hitting a 32 bit barrior, an instance
> limitation, or both. I am awaiting the answer. How many databases do you
> have on your server? We had between 1700 and 1900 (the number varies) at
> times when the error occured. We are now at 1500, and have not had the
> error in the 2 days since reducing the number of databases...
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com

401 2 and repeat request errors

I have sort of asked this elsewhere, but with new information the questions
has changed slightly, so apologies for the broken thread.
I have a web service sending messages through to a SQL server.
I have integrated security on (no anonymous). For one application, when I
run the application i get an unauthorized error and the logs hold these
lines:
2006-02-21 10:32:58 173.1.1.1 GET
/ApplicationWebservice/DocumentContent.asmx - 80 - 172.25.1.1 - 401 2
2148074254
2006-02-21 10:32:58 173.1.1.1 GET
/ApplicationWebservice/DocumentContent.asmx - 80 LONDON\ShamS00 172.25.1.1 -
200 0 0
2006-02-21 10:32:59 173.1.1.1 GET /ApplicationWebservice/Blank.html - 80 -
172.25.1.1
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.N ET+CLR+1.1.4322)
401 2 2148074254
2006-02-21 10:32:59 173.1.1.1 GET /ApplicationWebservice/Blank.html - 80
LONDON\ShamS00 172.25.1.1
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.N ET+CLR+1.1.4322)
200 0 0
2006-02-21 10:32:59 173.1.1.1 POST
/applicationwebservice/documentcontent.asmx - 80 - 172.25.1.1
Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+ Protocol+1.1.4322.2032)
401 2 2148074254
2006-02-21 10:32:59 173.1.1.1 POST
/applicationwebservice/documentcontent.asmx - 80 - 172.25.1.1
Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+ Protocol+1.1.4322.2032)
401 2 2148074254
What I am confused about is why are there two entries for each request (one
with authentication and one without), and then the next question would be
why does the last 'request' have two entries, both without authentiacation.
perhaps this is an impossible question to answer, but i thought i'd see if
anyone out there had an idea.
I still seem to have my delegation problem, another application seems to be
sending through commands properly, bt not getting an answer from the web
service, but i've decided to take a break from that one.
Any help/thoughts would be apprecitaed.
Thanks
Saira
Where is your web service running? Are you using the http endpoint in SQL
Server 2005? If not, I'm not sure where SQL Server fits into this picture.
The double messages through authentication are normal. The client will try
without authentication and then try with authentication once it find out
what the server needs.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Saira" <Saira@.Bayonet.com> wrote in message
news:uWb%23ZftNGHA.2300@.TK2MSFTNGP15.phx.gbl...
>I have sort of asked this elsewhere, but with new information the questions
>has changed slightly, so apologies for the broken thread.
> I have a web service sending messages through to a SQL server.
> I have integrated security on (no anonymous). For one application, when I
> run the application i get an unauthorized error and the logs hold these
> lines:
> 2006-02-21 10:32:58 173.1.1.1 GET
> /ApplicationWebservice/DocumentContent.asmx - 80 - 172.25.1.1 - 401 2
> 2148074254
> 2006-02-21 10:32:58 173.1.1.1 GET
> /ApplicationWebservice/DocumentContent.asmx - 80 LONDON\ShamS00
> 172.25.1.1 - 200 0 0
> 2006-02-21 10:32:59 173.1.1.1 GET /ApplicationWebservice/Blank.html - 80 -
> 172.25.1.1
> Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.N ET+CLR+1.1.4322)
> 401 2 2148074254
> 2006-02-21 10:32:59 173.1.1.1 GET /ApplicationWebservice/Blank.html - 80
> LONDON\ShamS00 172.25.1.1
> Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.N ET+CLR+1.1.4322)
> 200 0 0
> 2006-02-21 10:32:59 173.1.1.1 POST
> /applicationwebservice/documentcontent.asmx - 80 - 172.25.1.1
> Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+ Protocol+1.1.4322.2032)
> 401 2 2148074254
> 2006-02-21 10:32:59 173.1.1.1 POST
> /applicationwebservice/documentcontent.asmx - 80 - 172.25.1.1
> Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+ Protocol+1.1.4322.2032)
> 401 2 2148074254
> What I am confused about is why are there two entries for each request
> (one with authentication and one without), and then the next question
> would be why does the last 'request' have two entries, both without
> authentiacation. perhaps this is an impossible question to answer, but i
> thought i'd see if anyone out there had an idea.
> I still seem to have my delegation problem, another application seems to
> be sending through commands properly, bt not getting an answer from the
> web service, but i've decided to take a break from that one.
> Any help/thoughts would be apprecitaed.
> Thanks
> Saira
>
|||Thanks, on the authentication, i sort of figured that, but was not positive.
I am not taking advantage of any of the new SQL 2005 features yet.
My setup is a recently installed SQL Server 2005 box, hosting an upgraded
SQL 2000 database.
The client (XP SP2) talks to the web server (IIS 6, in a seperate IP
segment), and the web server requests the data from the database. I had
assumed that I would not need to make massive changes for the upgrade to
2005, is this not the case?
Saira
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:u3wS5gvNGHA.2884@.TK2MSFTNGP12.phx.gbl...
> Where is your web service running? Are you using the http endpoint in SQL
> Server 2005? If not, I'm not sure where SQL Server fits into this
> picture. The double messages through authentication are normal. The
> client will try without authentication and then try with authentication
> once it find out what the server needs.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Saira" <Saira@.Bayonet.com> wrote in message
> news:uWb%23ZftNGHA.2300@.TK2MSFTNGP15.phx.gbl...
>
|||None that I can think of.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Saira" <Saira@.Bayonet.com> wrote in message
news:erGMpHwNGHA.428@.tk2msftngp13.phx.gbl...
> Thanks, on the authentication, i sort of figured that, but was not
> positive.
> I am not taking advantage of any of the new SQL 2005 features yet.
> My setup is a recently installed SQL Server 2005 box, hosting an upgraded
> SQL 2000 database.
> The client (XP SP2) talks to the web server (IIS 6, in a seperate IP
> segment), and the web server requests the data from the database. I had
> assumed that I would not need to make massive changes for the upgrade to
> 2005, is this not the case?
> Saira
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:u3wS5gvNGHA.2884@.TK2MSFTNGP12.phx.gbl...
>

401 2 and repeat request errors

I have sort of asked this elsewhere, but with new information the questions
has changed slightly, so apologies for the broken thread.
I have a web service sending messages through to a SQL server.
I have integrated security on (no anonymous). For one application, when I
run the application i get an unauthorized error and the logs hold these
lines:
2006-02-21 10:32:58 173.1.1.1 GET
/ApplicationWebservice/DocumentContent.asmx - 80 - 172.25.1.1 - 401 2
2148074254
2006-02-21 10:32:58 173.1.1.1 GET
/ApplicationWebservice/DocumentContent.asmx - 80 LONDON\ShamS00 172.25.1.1 -
200 0 0
2006-02-21 10:32:59 173.1.1.1 GET /ApplicationWebservice/Blank.html - 80 -
172.25.1.1
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322)
401 2 2148074254
2006-02-21 10:32:59 173.1.1.1 GET /ApplicationWebservice/Blank.html - 80
LONDON\ShamS00 172.25.1.1
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322)
200 0 0
2006-02-21 10:32:59 173.1.1.1 POST
/applicationwebservice/documentcontent.asmx - 80 - 172.25.1.1
Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+1.1.4322.
2032)
401 2 2148074254
2006-02-21 10:32:59 173.1.1.1 POST
/applicationwebservice/documentcontent.asmx - 80 - 172.25.1.1
Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+1.1.4322.
2032)
401 2 2148074254
What I am confused about is why are there two entries for each request (one
with authentication and one without), and then the next question would be
why does the last 'request' have two entries, both without authentiacation.
perhaps this is an impossible question to answer, but i thought i'd see if
anyone out there had an idea.
I still seem to have my delegation problem, another application seems to be
sending through commands properly, bt not getting an answer from the web
service, but i've decided to take a break from that one.
Any help/thoughts would be apprecitaed.
Thanks
SairaWhere is your web service running? Are you using the http endpoint in SQL
Server 2005? If not, I'm not sure where SQL Server fits into this picture.
The double messages through authentication are normal. The client will try
without authentication and then try with authentication once it find out
what the server needs.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Saira" <Saira@.Bayonet.com> wrote in message
news:uWb%23ZftNGHA.2300@.TK2MSFTNGP15.phx.gbl...
>I have sort of asked this elsewhere, but with new information the questions
>has changed slightly, so apologies for the broken thread.
> I have a web service sending messages through to a SQL server.
> I have integrated security on (no anonymous). For one application, when I
> run the application i get an unauthorized error and the logs hold these
> lines:
> 2006-02-21 10:32:58 173.1.1.1 GET
> /ApplicationWebservice/DocumentContent.asmx - 80 - 172.25.1.1 - 401 2
> 2148074254
> 2006-02-21 10:32:58 173.1.1.1 GET
> /ApplicationWebservice/DocumentContent.asmx - 80 LONDON\ShamS00
> 172.25.1.1 - 200 0 0
> 2006-02-21 10:32:59 173.1.1.1 GET /ApplicationWebservice/Blank.html - 80 -
> 172.25.1.1
> Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322)
> 401 2 2148074254
> 2006-02-21 10:32:59 173.1.1.1 GET /ApplicationWebservice/Blank.html - 80
> LONDON\ShamS00 172.25.1.1
> Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322)
> 200 0 0
> 2006-02-21 10:32:59 173.1.1.1 POST
> /applicationwebservice/documentcontent.asmx - 80 - 172.25.1.1
> Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+1.1.432
2.2032)
> 401 2 2148074254
> 2006-02-21 10:32:59 173.1.1.1 POST
> /applicationwebservice/documentcontent.asmx - 80 - 172.25.1.1
> Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+1.1.432
2.2032)
> 401 2 2148074254
> What I am confused about is why are there two entries for each request
> (one with authentication and one without), and then the next question
> would be why does the last 'request' have two entries, both without
> authentiacation. perhaps this is an impossible question to answer, but i
> thought i'd see if anyone out there had an idea.
> I still seem to have my delegation problem, another application seems to
> be sending through commands properly, bt not getting an answer from the
> web service, but i've decided to take a break from that one.
> Any help/thoughts would be apprecitaed.
> Thanks
> Saira
>|||Thanks, on the authentication, i sort of figured that, but was not positive.
I am not taking advantage of any of the new SQL 2005 features yet.
My setup is a recently installed SQL Server 2005 box, hosting an upgraded
SQL 2000 database.
The client (XP SP2) talks to the web server (IIS 6, in a seperate IP
segment), and the web server requests the data from the database. I had
assumed that I would not need to make massive changes for the upgrade to
2005, is this not the case?
Saira
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:u3wS5gvNGHA.2884@.TK2MSFTNGP12.phx.gbl...
> Where is your web service running? Are you using the http endpoint in SQL
> Server 2005? If not, I'm not sure where SQL Server fits into this
> picture. The double messages through authentication are normal. The
> client will try without authentication and then try with authentication
> once it find out what the server needs.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Saira" <Saira@.Bayonet.com> wrote in message
> news:uWb%23ZftNGHA.2300@.TK2MSFTNGP15.phx.gbl...
>|||None that I can think of.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Saira" <Saira@.Bayonet.com> wrote in message
news:erGMpHwNGHA.428@.tk2msftngp13.phx.gbl...
> Thanks, on the authentication, i sort of figured that, but was not
> positive.
> I am not taking advantage of any of the new SQL 2005 features yet.
> My setup is a recently installed SQL Server 2005 box, hosting an upgraded
> SQL 2000 database.
> The client (XP SP2) talks to the web server (IIS 6, in a seperate IP
> segment), and the web server requests the data from the database. I had
> assumed that I would not need to make massive changes for the upgrade to
> 2005, is this not the case?
> Saira
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:u3wS5gvNGHA.2884@.TK2MSFTNGP12.phx.gbl...
>

4 queries into 1 query

I have 4 queries that run fine, giving me the number of different clocking errors for employees. I would like to run just 1 query if possible.

Query 1:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, COUNT(A.CLOCKSEQ) AS LateEarly
from clockwise.Employee E, clockwise.clocking A,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d,ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.WorkDate >= to_date('05-01-2005','dd-mm-yyyy') AND A.WorkDate <= to_date('14-01-2005','dd-mm-yyyy')
AND E.Clk_Roster = '!*'
AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '5005'
AND (f.POST_EFF_END_DT >= to_date('05-01-2005','dd-mm-yyyy') or f.POST_EFF_END_DT is null)
AND ((A.CLOCKSEQ = 1 AND A.CLOCKIN > 600) OR (A.CLOCKSEQ = 2 AND A.CLOCKOUT < 960))
GROUP BY E.EmpNo, E.FirstName, E.Surname
ORDER BY E.Surname ASC

Gives me: 11111, John, Smith, 3

Query 2:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, COUNT(A.CLOCKSEQ) AS Lunch
from clockwise.Employee E, clockwise.clocking A,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d,ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.WorkDate >= to_date('05-01-2005','dd-mm-yyyy') AND A.WorkDate <= to_date('14-01-2005','dd-mm-yyyy')
AND E.Clk_Roster = '!*'
AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '5005'
AND (f.POST_EFF_END_DT >= to_date('05-01-2005','dd-mm-yyyy') or f.POST_EFF_END_DT is null)
AND ((A.CLOCKSEQ = 1 AND A.CLOCKOUT < 750) OR (A.CLOCKSEQ = 2 AND A.CLOCKIN > 870))
GROUP BY E.EmpNo, E.FirstName, E.Surname
ORDER BY E.Surname ASC

Gives me: 11111, John, Smith, 1

Query 3:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, COUNT(A.Mins) AS HoursDown
FROM clockwise.Employee E, clockwise.Correcti A,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d, ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.WorkDate >= to_date('14-01-2005','dd-mm-yyyy') AND A.WorkDate <= to_date('14-01-2005','dd-mm-yyyy')
AND E.Clk_Roster = '!*'
AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '5005'
AND (f.POST_EFF_END_DT >= to_date('14-01-2005','dd-mm-yyyy') or f.POST_EFF_END_DT is null)
AND A.CorrCode = '+[' AND A.Mins < -600
GROUP BY E.EmpNo, E.FirstName, E.Surname
ORDER BY E.Surname ASC

Gives me: 11111, John, Smith, 1

Query 4:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, COUNT(A.CLOCKSEQ) AS ForgotToClock
FROM clockwise.Employee E, clockwise.clocking A,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d, ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.WorkDate >= to_date('05-01-2005','dd-mm-yyyy') AND A.WorkDate <= to_date('14-01-2005','dd-mm-yyyy')
AND E.Clk_Roster = '!*'
AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '5005'
AND (f.POST_EFF_END_DT >= to_date('05-01-2005','dd-mm-yyyy') or f.POST_EFF_END_DT is null)
AND ((A.CLOCKSEQ = 1
AND A.EmpID NOT IN (SELECT DISTINCT EmpID
FROM clockwise.clocking H
WHERE H.CLOCKSEQ = 2
AND H.WorkDate = A.WorkDate))
OR (A.CLOCKSEQ = 2 AND A.CLOCKOUT = 0)
OR (A.CLOCKSEQ = 1 AND A.CLOCKIN >= 720))
GROUP BY E.EmpNo, E.FirstName, E.Surname
ORDER BY E.Surname ASC

Gives me: 11111, John, Smith, 5

I would like to get 1 row with 11111, John, Smith, 3, 1, 1, 5.

Query 3 select information from the Correcti table, the other 3 queries select information from the Clocking table otherwise the 4 other tables in the queries are the same. The condition that gives the result for each query comes after the AND (f.POST_EFF_END_DT >= to_date('05-01-2005','dd-mm-yyyy') or f.POST_EFF_END_DT is null) line.

Query 1:
AND ((A.CLOCKSEQ = 1 AND A.CLOCKIN > 600) OR (A.CLOCKSEQ = 2 AND A.CLOCKOUT < 960))

Query 2:
AND ((A.CLOCKSEQ = 1 AND A.CLOCKOUT < 750) OR (A.CLOCKSEQ = 2 AND A.CLOCKIN > 870))

Query 3:
AND A.CorrCode = '+[' AND A.Mins < -600

Query 4:
AND ((A.CLOCKSEQ = 1
AND A.EmpID NOT IN (SELECT DISTINCT EmpID
FROM clockwise.clocking H
WHERE H.CLOCKSEQ = 2
AND H.WorkDate = A.WorkDate))
OR (A.CLOCKSEQ = 2 AND A.CLOCKOUT = 0)
OR (A.CLOCKSEQ = 1 AND A.CLOCKIN >= 720))

Thanks for any help with this.
Mark.you might want to look into the UNION operator|||Hi r937,

I've tried union, but I can't seem to get the output I need.

Using union all I can get is 4 records:
11111, John, Smith, 3
11111, John, Smith, 1
11111, John, Smith, 1
11111, John, Smith, 5

instead of 11111, John, Smith, 3, 1, 1, 5.

If only 2 infringements occurr, I'll only get 2 rows and I don't know what these infrigements are for, LateEarly, Lunch, HoursDown or ForgotToClock.

I've also tried just using a union, but adding extra redundant fields to the select statements.
Using the first 2 queries I added an extra field after COUNT(A.CLOCKSEQ) AS LateEarly in the first select and before COUNT(A.CLOCKSEQ) AS Lunch in the second query.
But I get the following:
11111, John, Smith, 3, 3
11111, John, Smith, 1, 1
22222, Joan, Smith, 1, 1

What I need to get is:
11111, John, Smith, 3, 1
22222, Joan, Smith, , 1

any ideas?

Thanks,
Mark.|||Try adding a literal column to the union to indicate LateEarly, Lunch, HoursDown or ForgotToClock and then put the union into a subquery and put a CASE statement on the literal column to put the rows into columns.

3701 Errors after installing SP2

Environment:
SQL Server 2005 Enterprise Edition SP2
Windows 2003 R2 x64 Enterprise
80+ Databases, all upgraded to compatibility 90
Since upgrading to SP2 for SQL Server 2005, my scheduled jobs based on
Maintenance Plans which include Statistics Updates, Index Rebuilding and
Index Reorganization tasks have reported failure. The failed job history
indicates the following: “The Execute method on the task returned error code
0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.).”
Although the job fails, I’m not convinced that it’s not also completing its
tasks. A trace on the activity of the SQL Agent while the jobs are running
indicates that these jobs are properly executing their Alter Table statements
without incident and the duration of execution seems to be roughly the same
as before.
The above seemed like an unsatisfactorily vague error, so I decided to run a
bit more extensive trace to see exactly what errors were being raised while
the job was run. Doing so revealed the following error: Event Class: User
Error Message; ApplicationName: Microsoft SQL Server Management Studio;
Error: 3701; TextData: “Cannot drop the table
'#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do
not have permission.”
This error did not occur prior to the upgrade to SP2, and I’ve noticed
similar errors being trapped in traces run on my other SQL Servers post-SP2
when trying to do multi-database maintenance plans. I have expanded my trace
to trap all of the RPC and SQL textdata created by the SQL Agent when running
my jobs, looking in particular for the text
‘#tmp_sp_db_vardecimal_storage_format'. I found on several occasions (at
least 4 times for each database altered within the job) that a “drop table
#tmp_sp_db_vardecimal_storage_format” was being called within the maintenance
plan without any corresponding create table statement.
Though I don’t doubt that my maintenance plans are succeeding at some level,
I’m bothered by all of these unnecessary 3701 errors raised by the “drop
table #tmp_sp_db_vardecimal_storage_format” statements. I believe that this
is a bug in the post-SP2 Management Studio code and wonder if there’s some
kind of workaround to avoid this, or some patch on the near horizon.
Thanks,
Barb
Hi Barb
"Barb" wrote:

> Environment:
> SQL Server 2005 Enterprise Edition SP2
> Windows 2003 R2 x64 Enterprise
> 80+ Databases, all upgraded to compatibility 90
>
> Since upgrading to SP2 for SQL Server 2005, my scheduled jobs based on
> Maintenance Plans which include Statistics Updates, Index Rebuilding and
> Index Reorganization tasks have reported failure. The failed job history
> indicates the following: “The Execute method on the task returned error code
> 0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.).”
> Although the job fails, I’m not convinced that it’s not also completing its
> tasks. A trace on the activity of the SQL Agent while the jobs are running
> indicates that these jobs are properly executing their Alter Table statements
> without incident and the duration of execution seems to be roughly the same
> as before.
>
> The above seemed like an unsatisfactorily vague error, so I decided to run a
> bit more extensive trace to see exactly what errors were being raised while
> the job was run. Doing so revealed the following error: Event Class: User
> Error Message; ApplicationName: Microsoft SQL Server Management Studio;
> Error: 3701; TextData: “Cannot drop the table
> '#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do
> not have permission.”
>
> This error did not occur prior to the upgrade to SP2, and I’ve noticed
> similar errors being trapped in traces run on my other SQL Servers post-SP2
> when trying to do multi-database maintenance plans. I have expanded my trace
> to trap all of the RPC and SQL textdata created by the SQL Agent when running
> my jobs, looking in particular for the text
> ‘#tmp_sp_db_vardecimal_storage_format'. I found on several occasions (at
> least 4 times for each database altered within the job) that a “drop table
> #tmp_sp_db_vardecimal_storage_format” was being called within the maintenance
> plan without any corresponding create table statement.
>
> Though I don’t doubt that my maintenance plans are succeeding at some level,
> I’m bothered by all of these unnecessary 3701 errors raised by the “drop
> table #tmp_sp_db_vardecimal_storage_format” statements. I believe that this
> is a bug in the post-SP2 Management Studio code and wonder if there’s some
> kind of workaround to avoid this, or some patch on the near horizon.
>
> Thanks,
> Barb
vardecimal is a new datatype introduced in SP2. Have you tried to run
sp_db_vardecimal_storage_format
http://msdn2.microsoft.com/en-us/library/bb326653.aspx
John
|||On Feb 27, 11:58 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Barb
>
>
> "Barb" wrote:
>
>
>
>
> vardecimal is a new datatype introduced inSP2. Have you tried to run
> sp_db_vardecimal_storage_formathttp://msdn2.microsoft.com/en-us/library/bb326653.aspx
> John- Hide quoted text -
> - Show quoted text -
If you run a User Error Trace and look for 3701 errors, they pop up
not only when the packages are being run, but also when you're trying
to create Maint. jobs in SQL Server Management Studio. Whether
sp_db_vardecimal_storage_format is enabled or disabled for a database
makes no difference.
|||Hi,
Have you found any solution to this as I am also facing the similar problem
after updrading to SP2. Please let me know the solution as I am completely
stuck at this point.
MS

3701 Errors after installing SP2

Environment:
SQL Server 2005 Enterprise Edition SP2
Windows 2003 R2 x64 Enterprise
80+ Databases, all upgraded to compatibility 90
Since upgrading to SP2 for SQL Server 2005, my scheduled jobs based on
Maintenance Plans which include Statistics Updates, Index Rebuilding and
Index Reorganization tasks have reported failure. The failed job history
indicates the following: “The Execute method on the task returned error c
ode
0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.).”
Although the job fails, I’m not convinced that it’s not also completing
its
tasks. A trace on the activity of the SQL Agent while the jobs are running
indicates that these jobs are properly executing their Alter Table statement
s
without incident and the duration of execution seems to be roughly the same
as before.
The above seemed like an unsatisfactorily vague error, so I decided to run a
bit more extensive trace to see exactly what errors were being raised while
the job was run. Doing so revealed the following error: Event Class: User
Error Message; ApplicationName: Microsoft SQL Server Management Studio;
Error: 3701; TextData: “Cannot drop the table
'#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do
not have permission.”
This error did not occur prior to the upgrade to SP2, and I’ve noticed
similar errors being trapped in traces run on my other SQL Servers post-SP2
when trying to do multi-database maintenance plans. I have expanded my trac
e
to trap all of the RPC and SQL textdata created by the SQL Agent when runnin
g
my jobs, looking in particular for the text
‘#tmp_sp_db_vardecimal_storage_format'
. I found on several occasions (at
least 4 times for each database altered within the job) that a “drop table
#tmp_sp_db_vardecimal_storage_format” was being called within the maintena
nce
plan without any corresponding create table statement.
Though I don’t doubt that my maintenance plans are succeeding at some leve
l,
I’m bothered by all of these unnecessary 3701 errors raised by the “drop
table #tmp_sp_db_vardecimal_storage_format” statements. I believe that th
is
is a bug in the post-SP2 Management Studio code and wonder if there’s some
kind of workaround to avoid this, or some patch on the near horizon.
Thanks,
BarbHi Barb
"Barb" wrote:

> Environment:
> SQL Server 2005 Enterprise Edition SP2
> Windows 2003 R2 x64 Enterprise
> 80+ Databases, all upgraded to compatibility 90
>
> Since upgrading to SP2 for SQL Server 2005, my scheduled jobs based on
> Maintenance Plans which include Statistics Updates, Index Rebuilding and
> Index Reorganization tasks have reported failure. The failed job history
> indicates the following: “The Execute method on the task returned error
code
> 0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.).
> Although the job fails, I’m not convinced that it’s not also completin
g its
> tasks. A trace on the activity of the SQL Agent while the jobs are runnin
g
> indicates that these jobs are properly executing their Alter Table stateme
nts
> without incident and the duration of execution seems to be roughly the sam
e
> as before.
>
> The above seemed like an unsatisfactorily vague error, so I decided to run
a
> bit more extensive trace to see exactly what errors were being raised whil
e
> the job was run. Doing so revealed the following error: Event Class: Us
er
> Error Message; ApplicationName: Microsoft SQL Server Management Studio;
> Error: 3701; TextData: “Cannot drop the table
> '#tmp_sp_db_vardecimal_storage_format', because it does not exist or you d
o
> not have permission.”
>
> This error did not occur prior to the upgrade to SP2, and I’ve noticed
> similar errors being trapped in traces run on my other SQL Servers post-SP
2
> when trying to do multi-database maintenance plans. I have expanded my tr
ace
> to trap all of the RPC and SQL textdata created by the SQL Agent when runn
ing
> my jobs, looking in particular for the text
> ‘#tmp_sp_db_vardecimal_storage_format'
. I found on several occasions (a
t
> least 4 times for each database altered within the job) that a “drop tab
le
> #tmp_sp_db_vardecimal_storage_format” was being called within the mainte
nance
> plan without any corresponding create table statement.
>
> Though I don’t doubt that my maintenance plans are succeeding at some le
vel,
> I’m bothered by all of these unnecessary 3701 errors raised by the “dr
op
> table #tmp_sp_db_vardecimal_storage_format” statements. I believe that
this
> is a bug in the post-SP2 Management Studio code and wonder if there’s so
me
> kind of workaround to avoid this, or some patch on the near horizon.
>
> Thanks,
> Barb
vardecimal is a new datatype introduced in SP2. Have you tried to run
sp_db_vardecimal_storage_format
http://msdn2.microsoft.com/en-us/library/bb326653.aspx
John|||On Feb 27, 11:58 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Barb
>
>
> "Barb" wrote:
>
>
>
>
>
>
>
>
> vardecimal is a new datatype introduced inSP2. Have you tried to run
> sp_db_vardecimal_storage_formathttp://msdn2.microsoft.com/en-us/library/bb
326653.aspx
> John- Hide quoted text -
> - Show quoted text -
If you run a User Error Trace and look for 3701 errors, they pop up
not only when the packages are being run, but also when you're trying
to create Maint. jobs in SQL Server Management Studio. Whether
sp_db_vardecimal_storage_format is enabled or disabled for a database
makes no difference.|||Hi,
Have you found any solution to this as I am also facing the similar problem
after updrading to SP2. Please let me know the solution as I am completely
stuck at this point.
MS

3701 Errors after installing SP2

Environment:
SQL Server 2005 Enterprise Edition SP2
Windows 2003 R2 x64 Enterprise
80+ Databases, all upgraded to compatibility 90
Since upgrading to SP2 for SQL Server 2005, my scheduled jobs based on
Maintenance Plans which include Statistics Updates, Index Rebuilding and
Index Reorganization tasks have reported failure. The failed job history
indicates the following: â'The Execute method on the task returned error code
0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.).â'
Although the job fails, Iâ'm not convinced that itâ's not also completing its
tasks. A trace on the activity of the SQL Agent while the jobs are running
indicates that these jobs are properly executing their Alter Table statements
without incident and the duration of execution seems to be roughly the same
as before.
The above seemed like an unsatisfactorily vague error, so I decided to run a
bit more extensive trace to see exactly what errors were being raised while
the job was run. Doing so revealed the following error: Event Class: User
Error Message; ApplicationName: Microsoft SQL Server Management Studio;
Error: 3701; TextData: â'Cannot drop the table
'#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do
not have permission.â'
This error did not occur prior to the upgrade to SP2, and Iâ've noticed
similar errors being trapped in traces run on my other SQL Servers post-SP2
when trying to do multi-database maintenance plans. I have expanded my trace
to trap all of the RPC and SQL textdata created by the SQL Agent when running
my jobs, looking in particular for the text
â'#tmp_sp_db_vardecimal_storage_format'. I found on several occasions (at
least 4 times for each database altered within the job) that a â'drop table
#tmp_sp_db_vardecimal_storage_formatâ' was being called within the maintenance
plan without any corresponding create table statement.
Though I donâ't doubt that my maintenance plans are succeeding at some level,
Iâ'm bothered by all of these unnecessary 3701 errors raised by the â'drop
table #tmp_sp_db_vardecimal_storage_formatâ' statements. I believe that this
is a bug in the post-SP2 Management Studio code and wonder if thereâ's some
kind of workaround to avoid this, or some patch on the near horizon.
Thanks,
BarbHi Barb
"Barb" wrote:
> Environment:
> SQL Server 2005 Enterprise Edition SP2
> Windows 2003 R2 x64 Enterprise
> 80+ Databases, all upgraded to compatibility 90
>
> Since upgrading to SP2 for SQL Server 2005, my scheduled jobs based on
> Maintenance Plans which include Statistics Updates, Index Rebuilding and
> Index Reorganization tasks have reported failure. The failed job history
> indicates the following: â'The Execute method on the task returned error code
> 0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.).â'
> Although the job fails, Iâ'm not convinced that itâ's not also completing its
> tasks. A trace on the activity of the SQL Agent while the jobs are running
> indicates that these jobs are properly executing their Alter Table statements
> without incident and the duration of execution seems to be roughly the same
> as before.
>
> The above seemed like an unsatisfactorily vague error, so I decided to run a
> bit more extensive trace to see exactly what errors were being raised while
> the job was run. Doing so revealed the following error: Event Class: User
> Error Message; ApplicationName: Microsoft SQL Server Management Studio;
> Error: 3701; TextData: â'Cannot drop the table
> '#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do
> not have permission.â'
>
> This error did not occur prior to the upgrade to SP2, and Iâ've noticed
> similar errors being trapped in traces run on my other SQL Servers post-SP2
> when trying to do multi-database maintenance plans. I have expanded my trace
> to trap all of the RPC and SQL textdata created by the SQL Agent when running
> my jobs, looking in particular for the text
> â'#tmp_sp_db_vardecimal_storage_format'. I found on several occasions (at
> least 4 times for each database altered within the job) that a â'drop table
> #tmp_sp_db_vardecimal_storage_formatâ' was being called within the maintenance
> plan without any corresponding create table statement.
>
> Though I donâ't doubt that my maintenance plans are succeeding at some level,
> Iâ'm bothered by all of these unnecessary 3701 errors raised by the â'drop
> table #tmp_sp_db_vardecimal_storage_formatâ' statements. I believe that this
> is a bug in the post-SP2 Management Studio code and wonder if thereâ's some
> kind of workaround to avoid this, or some patch on the near horizon.
>
> Thanks,
> Barb
vardecimal is a new datatype introduced in SP2. Have you tried to run
sp_db_vardecimal_storage_format
http://msdn2.microsoft.com/en-us/library/bb326653.aspx
John|||On Feb 27, 11:58 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Barb
>
>
> "Barb" wrote:
> > Environment:
> > SQL Server 2005 Enterprise EditionSP2
> > Windows 2003 R2 x64 Enterprise
> > 80+ Databases, all upgraded to compatibility 90
> > Since upgrading toSP2for SQL Server 2005, my scheduled jobs based on
> > Maintenance Plans which include Statistics Updates, Index Rebuilding and
> > Index Reorganization tasks have reported failure. The failed job history
> > indicates the following: "The Execute method on the task returned error code
> > 0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.)."
> > Although the job fails, I'm not convinced that it's not also completing its
> > tasks. A trace on the activity of the SQL Agent while the jobs are running
> > indicates that these jobs are properly executing their Alter Table statements
> > without incident and the duration of execution seems to be roughly the same
> > as before.
> > The above seemed like an unsatisfactorily vague error, so I decided to run a
> > bit more extensive trace to see exactly what errors were being raised while
> > the job was run. Doing so revealed the following error: Event Class: User
> > Error Message; ApplicationName: Microsoft SQL Server Management Studio;
> > Error: 3701; TextData: "Cannot drop the table
> > '#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do
> > not have permission."
> > This error did not occur prior to the upgrade toSP2, and I've noticed
> > similar errors being trapped in traces run on my other SQL Servers post-SP2
> > when trying to do multi-database maintenance plans. I have expanded my trace
> > to trap all of the RPC and SQL textdata created by the SQL Agent when running
> > my jobs, looking in particular for the text
> > '#tmp_sp_db_vardecimal_storage_format'. I found on several occasions (at
> > least 4 times for each database altered within the job) that a "drop table
> > #tmp_sp_db_vardecimal_storage_format" was being called within the maintenance
> > plan without any corresponding create table statement.
> > Though I don't doubt that my maintenance plans are succeeding at some level,
> > I'm bothered by all of these unnecessary 3701 errors raised by the "drop
> > table #tmp_sp_db_vardecimal_storage_format" statements. I believe that this
> > is a bug in the post-SP2Management Studio code and wonder if there's some
> > kind of workaround to avoid this, or some patch on the near horizon.
> > Thanks,
> > Barb
> vardecimal is a new datatype introduced inSP2. Have you tried to run
> sp_db_vardecimal_storage_formathttp://msdn2.microsoft.com/en-us/library/bb326653.aspx
> John- Hide quoted text -
> - Show quoted text -
If you run a User Error Trace and look for 3701 errors, they pop up
not only when the packages are being run, but also when you're trying
to create Maint. jobs in SQL Server Management Studio. Whether
sp_db_vardecimal_storage_format is enabled or disabled for a database
makes no difference.|||Hi,
Have you found any solution to this as I am also facing the similar problem
after updrading to SP2. Please let me know the solution as I am completely
stuck at this point.
MS

3624 Errors

I have a 2-node cluster (32-bit) at 8.00.818 that has approximately 100
databases of varying sizes that are used to feed some tax websites. Recently
we did a repair on one of the databases with a DBCC CHECKDB with
ALLOW_DATA_LOSS to repair some issues with one database. That went well.
Last weekend we started getting 3624 errors.
2007-02-05 12:04:28.99 spid88 Error: 3624, Severity: 20, State: 1.
2007-02-05 12:04:27.98 spid88 SQL Server Assertion: File:
<R:\sql\ntdbms\storeng\drs\include\record.inl>, lin
2007-02-05 12:04:27.98 spid88 Stack Signature for the dump is 0xD13C4E9C
2007-02-05 12:04:21.49 spid88 Using 'dbghelp.dll' version '4.0.5'...
2007-02-05 12:04:16.90 spid122 Error: 3624, Severity: 20, State: 1.
2007-02-05 12:04:16.15 spid122 SQL Server Assertion: File:
<R:\sql\ntdbms\storeng\drs\include\record.inl>, lin
2007-02-05 12:04:16.15 spid122 Stack Signature for the dump is 0xACA26EF6
2007-02-05 12:04:07.01 spid122 Using 'dbghelp.dll' version '4.0.5'...
Looking at the dumps it takes, seems like most of these get generated when a
backupjobs runs that backs up to off-site EMC devices. Any ideas on how I
can get this fixed or what to look at?
When you get these dumps like this it is best to open a support incident
with Microsoft and have them debug it. Do you get these dumps when you do a
native backup?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Cathy Soloway" <CathySoloway@.discussions.microsoft.com> wrote in message
news:DBF05514-9A16-4C95-84DF-E850D9EAF878@.microsoft.com...
>I have a 2-node cluster (32-bit) at 8.00.818 that has approximately 100
> databases of varying sizes that are used to feed some tax websites.
> Recently
> we did a repair on one of the databases with a DBCC CHECKDB with
> ALLOW_DATA_LOSS to repair some issues with one database. That went well.
> Last weekend we started getting 3624 errors.
> 2007-02-05 12:04:28.99 spid88 Error: 3624, Severity: 20, State: 1.
> 2007-02-05 12:04:27.98 spid88 SQL Server Assertion: File:
> <R:\sql\ntdbms\storeng\drs\include\record.inl>, lin
> 2007-02-05 12:04:27.98 spid88 Stack Signature for the dump is 0xD13C4E9C
> 2007-02-05 12:04:21.49 spid88 Using 'dbghelp.dll' version '4.0.5'...
> 2007-02-05 12:04:16.90 spid122 Error: 3624, Severity: 20, State: 1.
> 2007-02-05 12:04:16.15 spid122 SQL Server Assertion: File:
> <R:\sql\ntdbms\storeng\drs\include\record.inl>, lin
> 2007-02-05 12:04:16.15 spid122 Stack Signature for the dump is 0xACA26EF6
> 2007-02-05 12:04:07.01 spid122 Using 'dbghelp.dll' version '4.0.5'...
> Looking at the dumps it takes, seems like most of these get generated when
> a
> backupjobs runs that backs up to off-site EMC devices. Any ideas on how I
> can get this fixed or what to look at?
|||The thing that is confusion is this R: drive location is actually a
CD-ROM--that's why I suspect hardware problems the most. The backups work
intermittently from jobs and or native launch.
"Cathy Soloway" wrote:

> I have a 2-node cluster (32-bit) at 8.00.818 that has approximately 100
> databases of varying sizes that are used to feed some tax websites. Recently
> we did a repair on one of the databases with a DBCC CHECKDB with
> ALLOW_DATA_LOSS to repair some issues with one database. That went well.
> Last weekend we started getting 3624 errors.
> 2007-02-05 12:04:28.99 spid88 Error: 3624, Severity: 20, State: 1.
> 2007-02-05 12:04:27.98 spid88 SQL Server Assertion: File:
> <R:\sql\ntdbms\storeng\drs\include\record.inl>, lin
> 2007-02-05 12:04:27.98 spid88 Stack Signature for the dump is 0xD13C4E9C
> 2007-02-05 12:04:21.49 spid88 Using 'dbghelp.dll' version '4.0.5'...
> 2007-02-05 12:04:16.90 spid122 Error: 3624, Severity: 20, State: 1.
> 2007-02-05 12:04:16.15 spid122 SQL Server Assertion: File:
> <R:\sql\ntdbms\storeng\drs\include\record.inl>, lin
> 2007-02-05 12:04:16.15 spid122 Stack Signature for the dump is 0xACA26EF6
> 2007-02-05 12:04:07.01 spid122 Using 'dbghelp.dll' version '4.0.5'...
> Looking at the dumps it takes, seems like most of these get generated when a
> backupjobs runs that backs up to off-site EMC devices. Any ideas on how I
> can get this fixed or what to look at?
|||First of all, you could apply SP 4 (2039) and the publicly available
Cumulative Hotfix Rollup (2187). There have been quite a few fixes in the
last...4 years (yes, SP3 + MS03-031, build 818 was released in 1Q03 and
3Q03) that you might want to apply.
The R: drive you are referring to may be an old link in the registry keys.
Look at HKLM\SOFTWARE\Microsoft SQL Server\<instance name> for named
instances or HKLM\SOFTWARE\Microsoft\MSSQL for default instances.
Sincerely,
Anthony Thomas

"Cathy Soloway" <CathySoloway@.discussions.microsoft.com> wrote in message
news:A9A7A5B3-E9CE-4F74-A161-44868CF3FE1B@.microsoft.com...
> The thing that is confusion is this R: drive location is actually a
> CD-ROM--that's why I suspect hardware problems the most. The backups
work[vbcol=seagreen]
> intermittently from jobs and or native launch.
> "Cathy Soloway" wrote:
Recently[vbcol=seagreen]
well.[vbcol=seagreen]
0xD13C4E9C[vbcol=seagreen]
0xACA26EF6[vbcol=seagreen]
when a[vbcol=seagreen]
I[vbcol=seagreen]
|||I did a full registry search for that path on both nodes and it's not there.
That's why I thought something was amiss hardware wise. I'm continuing to
monitor and have to research any implications of applying SP4 plus the
following rollup, but that's not a quick fix. The system currently is at
8.00.818.
"Cathy Soloway" wrote:

> I have a 2-node cluster (32-bit) at 8.00.818 that has approximately 100
> databases of varying sizes that are used to feed some tax websites. Recently
> we did a repair on one of the databases with a DBCC CHECKDB with
> ALLOW_DATA_LOSS to repair some issues with one database. That went well.
> Last weekend we started getting 3624 errors.
> 2007-02-05 12:04:28.99 spid88 Error: 3624, Severity: 20, State: 1.
> 2007-02-05 12:04:27.98 spid88 SQL Server Assertion: File:
> <R:\sql\ntdbms\storeng\drs\include\record.inl>, lin
> 2007-02-05 12:04:27.98 spid88 Stack Signature for the dump is 0xD13C4E9C
> 2007-02-05 12:04:21.49 spid88 Using 'dbghelp.dll' version '4.0.5'...
> 2007-02-05 12:04:16.90 spid122 Error: 3624, Severity: 20, State: 1.
> 2007-02-05 12:04:16.15 spid122 SQL Server Assertion: File:
> <R:\sql\ntdbms\storeng\drs\include\record.inl>, lin
> 2007-02-05 12:04:16.15 spid122 Stack Signature for the dump is 0xACA26EF6
> 2007-02-05 12:04:07.01 spid122 Using 'dbghelp.dll' version '4.0.5'...
> Looking at the dumps it takes, seems like most of these get generated when a
> backupjobs runs that backs up to off-site EMC devices. Any ideas on how I
> can get this fixed or what to look at?
||| The R drive was probably a network share on the developer/build machine
at Microsoft where the code was compiled.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Cathy Soloway" <CathySoloway@.discussions.microsoft.com> wrote in message
news:A9A7A5B3-E9CE-4F74-A161-44868CF3FE1B@.microsoft.com...[vbcol=seagreen]
> The thing that is confusion is this R: drive location is actually a
> CD-ROM--that's why I suspect hardware problems the most. The backups
> work
> intermittently from jobs and or native launch.
> "Cathy Soloway" wrote: