error 1813 with xp_cmdshell

Hi all,
I am trying to launch a xp_cmdshell from a stored procedure to execute a BCP
.
This error occured when my sp is called :
System.Web.HttpUnhandledException: Exception of type
System.Web.HttpUnhandledException was thrown. -->
System.Data.SqlClient.SqlException: A severe error occurred on the current
command.
The results, if any, should be discarded. xp_cmdshell failed to execute,
error 1813 occured while executing CreateProcessAsUserW.
I use SQLServer 2000 SP4 and a system account to launch the xp_cmdshell. The
SQL server agent log on with this same account.
Any idea?
I will appreciate any help.
Regards.
LGHi,
xp_cmdshell executes with the security contect of the SQL Agent Proxy
account when executed by non symin users. This requires you to allow
non-symin users to execute xp_cmdshell (uncheck the 'Only users with
symin privileges...' checkbox under SQL Server Agent properties --> Job
System) and specify a Windows account for the SQL Agent proxy with the
permissions needed to run you application).
See the below URL:-
http://support.microsoft.com/defaul...b;en-us;Q264155
Thanks
Hari
SQL Server MVP
"Laurent G" <LaurentG@.discussions.microsoft.com> wrote in message
news:13FBAEA9-A146-49E0-A42C-1B939CC3DA06@.microsoft.com...
> Hi all,
> I am trying to launch a xp_cmdshell from a stored procedure to execute a
> BCP.
> This error occured when my sp is called :
> System.Web.HttpUnhandledException: Exception of type
> System.Web.HttpUnhandledException was thrown. -->
> System.Data.SqlClient.SqlException: A severe error occurred on the current
> command.
> The results, if any, should be discarded. xp_cmdshell failed to execute,
> error 1813 occured while executing CreateProcessAsUserW.
> I use SQLServer 2000 SP4 and a system account to launch the xp_cmdshell.
> The
> SQL server agent log on with this same account.
> Any idea?
> I will appreciate any help.
> Regards.
> LG|||Thanks a lot Hari, it works now.
"Hari Pra" wrote:

> Hi,
> xp_cmdshell executes with the security contect of the SQL Agent Proxy
> account when executed by non symin users. This requires you to allow
> non-symin users to execute xp_cmdshell (uncheck the 'Only users with
> symin privileges...' checkbox under SQL Server Agent properties --> Job
> System) and specify a Windows account for the SQL Agent proxy with the
> permissions needed to run you application).
> See the below URL:-
> http://support.microsoft.com/defaul...b;en-us;Q264155
>
> Thanks
> Hari
> SQL Server MVP
> "Laurent G" <LaurentG@.discussions.microsoft.com> wrote in message
> news:13FBAEA9-A146-49E0-A42C-1B939CC3DA06@.microsoft.com...
>
>

Error 17805 Invalid buffer received from Client when executing stored Procedure

Hello,
I am receiving this error when I try to execute a Stored Procedure in
SQL Server 2000 using SYBASE dblib.
I have been able to connect to connect to SQL Server from Unix, but
when I try to execute a Stored Procedure via dblib, I get the error:
"Error 17805 Invalid buffer received from Client"
My Stored Procedure does not receive parameters, so I don't know what
is happening.
If I execute a simple SQL Statement, it works fine
I have a SQL Server 2000 running SP 4.
anyone can help?
Thank you in advanced
Jose Manuel
Jose Manuel wrote:
> Hello,
> I am receiving this error when I try to execute a Stored Procedure in
> SQL Server 2000 using SYBASE dblib.
> I have been able to connect to connect to SQL Server from Unix, but
> when I try to execute a Stored Procedure via dblib, I get the error:
> "Error 17805 Invalid buffer received from Client"
> My Stored Procedure does not receive parameters, so I don't know what
> is happening.
> If I execute a simple SQL Statement, it works fine
> I have a SQL Server 2000 running SP 4.
> anyone can help?
> Thank you in advanced
> Jose Manuel
Just curious: Why are you executing SQL on SQL Server 2000 usin the
Sybase dbLib instead of a SQL Server client library?
David Gugick
Imceda Software
www.imceda.com
|||Jose Manuel,
I am having exactlly the same problem as you had.
Could you please tell me how did you solve it?
Thanks
creo que voy varios pasos atras de ti. ojal me puedas ayudar. tengo el
mismo problema.
te ha contactado alguien ?
lo pudiste resolver?
gracias.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Error 16916: A cursor with the name "tentative_cursor" does not exist

Hi,

I am creating a stored procedure which contains the declaration of a cursor, fetch the cursor, close the cursor, deallocate cursor. I tried executing this code in SQL analyzer and found working. But when I use the same code inside a create stored procedure, it throws the following error message.

Error 16916: A cursor with the name "tentative_cursor" does not exist

As a result, I am not able to proceed in creation of stored procedure with the above SQL statements.

Can any one help me where the problem is..

My SQL statements..
---
DECLARE tentative_cursor CURSOR
FOR select * from TbTentativeBookingTable where ExpTime < GETDATE()
GO
OPEN tentative_cursor
GO
DECLARE @.transactionno BIGINT
...
...
FETCH ...
CLOSE tentative_cursor
---

Thanks
-SudhakarThe GO keyword serves to terminate a SQL batch, so it implicitly ends a stored procedure declaration. There may be other problems too, but at the very least you need to remove the GO keyword(s).

-PatP

Error 156 Creating View

I tried writing the following statement in a stored procedure, and I get an
Error message: "Error 156: Incorrect syntax near the keyword 'VIEW'."
CREATE VIEW VW_TotalAccepted
AS
SELECT [CustID], [ReqNo], Sum([AcceptedQty]) AS [Total]
FROM [DelDetail]
GROUP BY [DelDetail].[CustID], [DelDetail].[ReqNo]
What's wrong with the syntax? Isn't it the way to create a View?You cannot create a VIEW inside a SP
"wrytat" wrote:

> I tried writing the following statement in a stored procedure, and I get a
n
> Error message: "Error 156: Incorrect syntax near the keyword 'VIEW'."
> CREATE VIEW VW_TotalAccepted
> AS
> SELECT [CustID], [ReqNo], Sum([AcceptedQty]) AS [Total]
> FROM [DelDetail]
> GROUP BY [DelDetail].[CustID], [DelDetail].[ReqNo]
> What's wrong with the syntax? Isn't it the way to create a View?|||Then... Just ask a stupid question regarding View. If I add a new row or
change some values of some columns in a table that is included in a View
after creating the View, will the change be reflected in the View?
"Chandra" wrote:
> You cannot create a VIEW inside a SP
> "wrytat" wrote:
>|||Yes. A view doesn't store any data, it is only a "view" into the data stored
in your tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:B14BFAD9-CF63-4645-A61C-86AA8854F4D1@.microsoft.com...
> Then... Just ask a stupid question regarding View. If I add a new row or
> change some values of some columns in a table that is included in a View
> after creating the View, will the change be reflected in the View?
> "Chandra" wrote:
>|||Yes. View doesnt store any data. Selecting rows from a view is as good as
executing the query that created it.
We go for views because, it improves the performance of a query.
thanks and regards
Chandra
"wrytat" wrote:
> Then... Just ask a stupid question regarding View. If I add a new row or
> change some values of some columns in a table that is included in a View
> after creating the View, will the change be reflected in the View?
> "Chandra" wrote:
>

Error 15274: Access to the remote server is denied because the current security context is not t

Hi,

I have a stored procedure servicing a queue (sql 2005).

The stored procedure updates some data in a different server and database.

If the queue contains messages and I execute manually the stored procedure, I don't have any problem at all.

However, if I set the queue to use the same stored procedure, it gives me the following error:

Error 15274: Access to the remote server is denied because the current security context is not trusted.

Any ideas?

Thanks,

Aron

Please, see if the following link to a similar question on this forum has the answer for your particular scenario: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1064271&SiteID=1

If the answers in this thread do not help you to resolve the problem, please let us know and we will be glad to provide further assistance.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Error 1429: A server cursor cannot be opened...

Using SQL native client from VFP 9.0 to SQL Server 2005 64 bit SP1 (happened before SP1 too)..

We have a stored procedure that returns 6 result sets. This SP uses 2 cursors. It is rather lengthy - I'll post the code if needed.

This SP works fine when called from VFP 99 percent of the time. Normally takes 2 to 3 secunds to execute.

Once in a while we will get a return from SQL ..

"OLE IDispatch exception code 0 from Microsoft SQL Native Client: A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor..."

The OLE error code is 1429. An OLE Exception code 3604 is also returned.

When this happens the SP will return the same error when executed for the same parameters over and over when called from VFP. When called directly from SQL management console it will normally work for the same parameters, although once in a while it will just hang (and not timeout apparently). In that case it will also hang from SQLCMD command line utility as well.

Wait a few hours and the SP will run fine for the same parameters in VFP. This happens even in the middle of the night when there is no possibility that data is being changed.

Here's the really fun part...

Open the SP source for modification (ALTER PROCEDURE) in management console and execute it (no changes at all, just let it recompile). Immediately it will work fine when called with the same parameters called from VFP or anywhere else (even if it was one of the rare instances where it hung in management console). This works EVERY TIME.

Sooo... I edited and executed the SP with the WITH RECOMPILE option assuming that that should do the trick (same as alter procedure/executing from management console right?). NOPE. Same problems. In order to work around the problem when the error occurs, I HAVE TO alter procedure and execute the code from management console.

Help?

Bill Kuhn - MCSE

The Kuhn Group, Inc.

http://www.kuhngroup.com

Did you deallocate your cursors in your code ? Would be nice to see the skeleton code, not the whole one, but the pure cursor code you implemented.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Yes - both cursors are closed and deallocated

Following is the entire stored procedure. The cursors are curs_Exams_pkeys and curs_Raw_CompData ..

ALTER procedure [dbo].[Comp_Data_for_Person]

@.persons_pkey int,

@.Latest_Date datetime

with recompile

as

SET NOCOUNT ON

declare @.ExamCount smallint

declare @.TopExam_pkey int

declare @.Exam1pkey int, @.Exam2pkey int, @.Exam3pkey int, @.Exam4pkey int, @.Exam5pkey int, @.Exam6pkey int, @.This_Exam_pkey int

declare @.ExamIndex tinyint

declare @.CurrentClients_pkey smallint

declare @.CurrentPanel smallint

-- following vars are used when FETCHing data from curs_RAW_CompData

declare @.Session_date datetime, @.TestNumber int, @.DataType tinyint, @.Result varchar(65),@.Description varchar(200), @.Class varchar(100), @.PrintFlag char(1), @.Formatted varchar(200),@.NewFlag char(1),@.CheckText varchar(200),@.PanelTestGroup varchar(200),@.PrintLevel int, @.Exams_pkey int

declare @.Current_TestNumber int, @.Current_Session_date datetime

if @.Latest_Date is null

set @.Latest_Date = '12/31/2099'

set transaction isolation level read uncommitted -- added by wsk 8/14/2006 to see if this helps

-- make table of exams that will print on this report

create table #ExamTable (session_date datetime,van smallint,name varchar(60),exams_pkey int,persons_pkey int, clients_pkey int,Questionnaire_Title varchar(254),panel smallint)

set @.ExamCount =

(select count(*) from exams join testsession on exams.testsession_pkey=testsession.pkey where persons_pkey=@.persons_pkey and session_date<=@.Latest_Date)

if @.ExamCount <7

insert into #ExamTable

select testsession.session_date,testsession.van,dbo.fullnamenormalatexamdate(exams.pkey) as name,exams.pkey,exams.persons_pkey,exams.clients_pkey,

(select top 1 isnull(description,'') from translationcodes where testnumber=1000000000 and value=(select top 1 isnull(cast(result as int),0) from documentdata where exams_pkey=exams.pkey and testnumber=1000000000 and datatype=11)) as Questionnaire_Title, exams.panel

from exams join testsession on exams.testsession_pkey=testsession.pkey

where exams.persons_pkey = @.persons_pkey and session_date <= @.Latest_Date

order by testsession.session_date desc

else

insert into #ExamTable

select * from (select top 5 testsession.session_date,testsession.van,dbo.fullnamenormalatexamdate(exams.pkey) as name,exams.pkey,exams.persons_pkey,exams.clients_pkey,

(select top 1 isnull(description,'') from translationcodes where testnumber=1000000000 and value=(select top 1 isnull(cast(result as int),0) from documentdata where exams_pkey=exams.pkey and testnumber=1000000000 and datatype=11)) as Questionnaire_Title, exams.panel

from exams join testsession on exams.testsession_pkey=testsession.pkey

where exams.persons_pkey = @.persons_pkey and session_date <= @.Latest_Date

order by testsession.session_date desc) as d1

union

select * from (select top 1 testsession.session_date,testsession.van,dbo.fullnamenormalatexamdate(exams.pkey) as name,exams.pkey,exams.persons_pkey,exams.clients_pkey,

(select top 1 isnull(description,'') from translationcodes where testnumber=1000000000 and value=(select top 1 isnull(cast(result as int),0) from documentdata where exams_pkey=exams.pkey and testnumber=1000000000 and datatype=11)) as Questionnaire_Title, exams.panel

from exams join testsession on exams.testsession_pkey=testsession.pkey

where exams.persons_pkey = @.persons_pkey and session_date <= @.Latest_Date

order by testsession.session_date) as d2

order by session_date desc

-- output list of exams in this data

select * from #ExamTable

--@.TopExam_pkey is key to latest exam for this person - this exam governs what testnumbers are printed in compdata

set @.TopExam_pkey = (select top 1 exams_pkey from #ExamTable order by session_date desc)

set @.CurrentClients_pkey = (select top 1 clients_pkey from #ExamTable order by session_date desc)

set @.CurrentPanel = (select top 1 panel from #ExamTable order by session_date desc)

-- get session_dates for all exams that will be reported on

set @.ExamIndex = 1

declare curs_Exams_pkeys cursor STATIC for select exams_pkey from #ExamTable order by session_date

open curs_Exams_pkeys

fetch next from curs_Exams_pkeys into @.This_Exam_pkey

while @.@.FETCH_STATUS = 0

begin

if @.ExamIndex = 1

set @.Exam1pkey = @.This_Exam_pkey

else if @.ExamIndex = 2

set @.Exam2pkey = @.This_Exam_pkey

else if @.ExamIndex = 3

set @.Exam3pkey = @.This_Exam_pkey

else if @.ExamIndex = 4

set @.Exam4pkey = @.This_Exam_pkey

else if @.ExamIndex = 5

set @.Exam5pkey = @.This_Exam_pkey

else if @.ExamIndex = 6

set @.Exam6pkey = @.This_Exam_pkey

set @.ExamIndex = @.ExamIndex + 1

fetch next from curs_Exams_pkeys into @.This_Exam_pkey

end

close curs_Exams_pkeys

deallocate curs_Exams_pkeys

-- output report header info

select dbo.fullnamenormalatexamdate(ex.pkey) as completename,dbo.ssnatexamdate(ex.pkey) as ssn,dbo.justdate(ts.session_date) as testdate,ts.van,ex.pid,ex.panel,

dbo.addressatexamdate(ex.pkey) as address,dbo.citystatezipatexamdate(ex.pkey) as citystatezip,adm.telephone,dbo.justdate(p.dob) as DOB, left(isnull(adm.employment,' '),2) as employmentyears,substring(isnull(adm.employment,' '),3,2) as employmentmonths,

isnull(cladm.payrollnum,'') as payrollnum,isnull(cladm.payrollnumber,'') as payrollnumber,isnull(cladm.jobcode,'') as jobcode,cl.client,cl.clientname,

clloc.location,clloc.description as locationdescription,dbo.genderatexamdate(ex.pkey) as gender,dbo.ageatexamdate(ex.pkey) as age,

isnull(cladm.memberssn,'') as memberssn, isnull(cladm.employeeid,'') as employeeid,

dbo.lastnameatexamdate(ex.pkey) as lastname,

dbo.firstnameatexamdate(ex.pkey) as firstname,

dbo.middlenameatexamdate(ex.pkey) as middlename

from exams ex join testsession ts on ex.testsession_pkey=ts.pkey

join clients cl on cl.pkey=ex.clients_pkey

join clientlocations clloc on ts.clientlocations_pkey=clloc.pkey

join clientadmin cladm on cladm.exams_pkey=ex.pkey

join administrative adm on adm.exams_pkey = ex.pkey

join persons p on ex.persons_pkey=p.pkey

where ex.pkey=@.TopExam_Pkey

create table #ReportTests (testnumber int)

insert into #ReportTests (testnumber)

select distinct medicaldata.testnumber from medicaldata where medicaldata.exams_pkey=@.TopExam_pkey and dbo.get_printflag(medicaldata.testnumber,medicaldata.datatype,medicaldata.result) in ('P','S')

union

select distinct labdata.testnumber from labdata where labdata.exams_pkey=@.TopExam_pkey and dbo.get_printflag(labdata.testnumber,labdata.datatype,labdata.result) in ('P','S')

create table #Final_CompData (TestNumber int, DataType tinyint, Description varchar(200), Class varchar(100), PrintFlag char(1), NewFlag char(1), CheckText varchar(200), PanelTestGroup varchar(200), PrintLevel int, Exam1Result varchar(2000),Exam2Result varchar(2000), Exam3Result varchar(2000), Exam4Result varchar(2000), Exam5Result varchar(2000), Exam6Result varchar(2000))

create index [testnumber_datatype] on #Final_CompData (testnumber,datatype)

declare curs_Raw_CompData cursor STATIC FOR

-- output medical compdata

select ts.session_date,

d1.testnumber,d1.datatype,d1.result,

dbo.testdescription(testnumber) as description,d1.class,dbo.get_printflag(d1.testnumber,d1.datatype,d1.result) as printflag,

rtrim(dbo.formatresult_new(testnumber,datatype,result))+' '+rtrim(dbo.testunits_by_datatype(testnumber,datatype)) as formatted,

d1.checkflag as newflag, d1.checktext,

(select top 1 ptg.testgroup from paneltestgroups ptg join clientpanels cp on ptg.clientpanels_pkey=cp.pkey

join testgroups tg on ptg.testgroup = tg.testgroup_descr

where tg.testnumber=d1.testnumber and cp.clients_pkey=@.CurrentClients_pkey and cp.panel=@.CurrentPanel) as paneltestgroup,

d1.printlevel,d1.exams_pkey

from

((select 'MEDICALDATA' as datatable,m.pkey as datatable_pkey,exams_pkey,m.testnumber,m.datatype,result,

dbo.referencerangecheck(e.pkey,m.testnumber,m.datatype,m.result) as checkflag,

dbo.referencerangetext(e.pkey,m.testnumber,m.datatype,m.result) as checktext,m.flag as oldflag,

e.testsession_pkey,e.clients_pkey,e.panel,tests.printlevel,tests.class,tests.printflag

from exams e join testsession t on e.testsession_pkey=t.pkey

join medicaldata m on m.exams_pkey=e.pkey

join tests on m.testnumber=tests.testnumber and m.datatype=tests.datatype

where e.pkey in (select exams_pkey from #ExamTable) and tests.testnumber in (select testnumber from #ReportTests)

)

union

(select 'LABDATA' as datatable,l.pkey as datatable_pkey,exams_pkey,l.testnumber,l.datatype,result,

dbo.referencerangecheck(e.pkey,l.testnumber,l.datatype,l.result) as checkflag,

dbo.referencerangetext(e.pkey,l.testnumber,l.datatype,l.result) as checktext,l.flag as oldflag,

e.testsession_pkey,e.clients_pkey,e.panel,tests.printlevel,tests.class,tests.printflag

from exams e join testsession t on e.testsession_pkey=t.pkey

join labdata l on l.exams_pkey=e.pkey

join tests on l.testnumber=tests.testnumber and l.datatype=tests.datatype

where e.pkey in (select exams_pkey from #ExamTable) and tests.testnumber in (select testnumber from #ReportTests)

))

as d1

join testsession ts on ts.pkey=d1.testsession_pkey

order by printlevel,testnumber,session_date -- session_date,van,pid,name,testnumber

open curs_Raw_CompData

fetch next from curs_Raw_CompData into @.Session_date, @.TestNumber, @.DataType, @.Result, @.Description , @.Class, @.PrintFlag, @.Formatted, @.NewFlag,@.CheckText, @.PanelTestGroup, @.PrintLevel, @.Exams_pkey

while @.@.FETCH_STATUS = 0

BEGIN

if @.Exams_pkey = @.TopExam_pkey -- use class,description,checktext,printflag,etc from this one

begin

if exists (select testnumber from #Final_CompData where testnumber=@.TestNumber and datatype = @.Datatype)

begin

update #Final_CompData

set Description = @.Description,Class = @.Class,PrintFlag = @.PrintFlag,NewFlag = @.NewFlag,CheckText = @.CheckText,PanelTestGroup = @.PanelTestGroup,PrintLevel = @.PrintLevel

where testnumber = @.TestNumber and datatype=@.DataType

end

else

begin

insert into #Final_CompData (testnumber,datatype,description,class,printflag,newflag,checktext,paneltestgroup,printlevel)

values (@.testnumber,@.datatype,@.description,@.class,@.printflag,@.newflag,@.checktext,@.paneltestgroup,@.printlevel)

end

end

else -- @.Exams_pkey is not = @.TopExam_pkey - only carry testnumber, datatype, and result info

begin

if not exists (select testnumber from #Final_CompData where testnumber=@.TestNumber and datatype = @.Datatype)

begin

insert into #Final_CompData (testnumber,datatype) values (@.TestNumber,@.DataType)

end

end

-- update correct Exam?Result

if @.Exams_pkey = @.Exam1pkey

begin

update #Final_CompData set Exam1Result = (select rtrim(isnull(exam1result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(10) + rtrim(@.Formatted)

where testnumber = @.TestNumber and datatype = @.DataType

if @.NewFlag > ''

begin

if @.Exams_pkey = @.TopExam_pkey

update #Final_CompData set Exam1Result = (select rtrim(isnull(exam1result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

else

update #Final_CompData set Exam1Result = (select rtrim(isnull(exam1result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

end

end

else if @.Exams_pkey = @.Exam2pkey

begin

update #Final_CompData set Exam2Result = (select rtrim(isnull(exam2result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(10) + rtrim(@.Formatted)

where testnumber = @.TestNumber and datatype = @.DataType

if @.NewFlag > ''

begin

if @.Exams_pkey = @.TopExam_pkey

update #Final_CompData set Exam2Result = (select rtrim(isnull(exam2result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

else

update #Final_CompData set Exam2Result = (select rtrim(isnull(exam2result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

end

end

else if @.Exams_pkey = @.Exam3pkey

begin

update #Final_CompData set Exam3Result = (select rtrim(isnull(exam3result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(10) + rtrim(@.Formatted)

where testnumber = @.TestNumber and datatype = @.DataType

if @.NewFlag > ''

begin

if @.Exams_pkey = @.TopExam_pkey

update #Final_CompData set Exam3Result = (select rtrim(isnull(exam3result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

else

update #Final_CompData set Exam3Result = (select rtrim(isnull(exam3result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

end

end

else if @.Exams_pkey = @.Exam4pkey

begin

update #Final_CompData set Exam4Result = (select rtrim(isnull(exam4result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(10) + rtrim(@.Formatted)

where testnumber = @.TestNumber and datatype = @.DataType

if @.NewFlag > ''

begin

if @.Exams_pkey = @.TopExam_pkey

update #Final_CompData set Exam4Result = (select rtrim(isnull(exam4result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

else

update #Final_CompData set Exam4Result = (select rtrim(isnull(exam4result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

end

end

else if @.Exams_pkey = @.Exam5pkey

begin

update #Final_CompData set Exam5Result = (select rtrim(isnull(exam5result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(10) + rtrim(@.Formatted)

where testnumber = @.TestNumber and datatype = @.DataType

if @.NewFlag > ''

begin

if @.Exams_pkey = @.TopExam_pkey

update #Final_CompData set Exam5Result = (select rtrim(isnull(exam5result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

else

update #Final_CompData set Exam5Result = (select rtrim(isnull(exam5result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

end

end

else if @.Exams_pkey = @.Exam6pkey

begin

update #Final_CompData set Exam6Result = (select rtrim(isnull(exam6result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(10) + rtrim(@.Formatted)

where testnumber = @.TestNumber and datatype = @.DataType

if @.NewFlag > ''

begin

if @.Exams_pkey = @.TopExam_pkey

update #Final_CompData set Exam6Result = (select rtrim(isnull(exam6result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

else

update #Final_CompData set Exam6Result = (select rtrim(isnull(exam6result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(42) + char(42)

where testnumber = @.TestNumber and datatype = @.DataType

end

end

fetch next from curs_Raw_CompData into @.Session_date, @.TestNumber, @.DataType, @.Result, @.Description , @.Class, @.PrintFlag, @.Formatted, @.NewFlag, @.CheckText, @.PanelTestGroup, @.PrintLevel, @.Exams_pkey

END

close curs_Raw_CompData

deallocate curs_Raw_CompData

-- knock off preceeding CR's (ugly way to do it but it works for the moment)

update #Final_CompData

set Exam1Result=substring(Exam1Result,2,len(Exam1Result)-1),

Exam2Result=substring(Exam2Result,2,len(Exam2Result)-1),

Exam3Result=substring(Exam3Result,2,len(Exam3Result)-1),

Exam4Result=substring(Exam4Result,2,len(Exam4Result)-1),

Exam5Result=substring(Exam5Result,2,len(Exam5Result)-1),

Exam6Result=substring(Exam6Result,2,len(Exam6Result)-1)

select * from #Final_CompData where printflag in ('P','S') order by printlevel,testnumber

drop table #Final_CompData

-- output history data

exec dbo.Historydataforexam @.TopExam_pkey

-- output any custom field info from clientadmin table

exec dbo.Get_Custom_ClientAdmin_Fields_TextBlock @.TopExam_pkey

-- get ILO info for ILO report

select ts.session_date,ts.van as van,

dbo.pidAtExamDate(exams_pkey) as pid,

dbo.fullnamenormalatexamdate(exams_pkey) as name,

dbo.genderatexamdate(exams_pkey) as gender,dbo.ageatexamdate(exams_pkey) as age,

d1.testnumber,d1.datatype,

dbo.testdescription(testnumber) as description,d1.class,dbo.get_printflag(d1.testnumber,d1.datatype,d1.result) as printflag,

d1.result,

rtrim(dbo.formatresult_new(testnumber,datatype,result))+' '+rtrim(dbo.testunits_by_datatype(testnumber,datatype)) as formatted,

d1.oldflag,d1.checkflag as newflag, d1.checktext,datatable,datatable_pkey,d1.exams_pkey,

(select top 1 ptg.testgroup from paneltestgroups ptg join clientpanels cp on ptg.clientpanels_pkey=cp.pkey

join testgroups tg on ptg.testgroup = tg.testgroup_descr

where tg.testnumber=d1.testnumber and cp.clients_pkey=@.CurrentClients_pkey and cp.panel=@.CurrentPanel) as paneltestgroup,

d1.printlevel

from

(select 'MEDICALDATA' as datatable,m.pkey as datatable_pkey,exams_pkey,m.testnumber,m.datatype,result,

dbo.referencerangecheck(e.pkey,m.testnumber,m.datatype,m.result) as checkflag,

dbo.referencerangetext(e.pkey,m.testnumber,m.datatype,m.result) as checktext,m.flag as oldflag,

e.testsession_pkey,e.clients_pkey,e.panel,tests.printlevel,tests.class,tests.printflag

from exams e join testsession t on e.testsession_pkey=t.pkey

join medicaldata m on m.exams_pkey=e.pkey

join tests on m.testnumber=tests.testnumber and m.datatype=tests.datatype

where e.pkey in (select exams_pkey from #ExamTable) and tests.testnumber between 2261800 and 2264999

)

as d1

join testsession ts on ts.pkey=d1.testsession_pkey

order by printlevel,testnumber,session_date -- session_date,van,pid,name,testnumber

drop table #ExamTable -- should not be needed

drop table #ReportTests -- should not be needed

Error 1203

I am finding Error 1203 Severity 20 periodically in my trace.
This seems to correlate with intermittent failed insert statements performed
by a stored procedure. The stored procedure is executed shortly before this
error is recorded.
Nothing is written to the SQL Server log.
The error is occurring on SQL Server 2000 Enterprise, SP 2, build 2195,
running on W2K server.
The same error occurs in a test database on SQL Server 2000 Standard, no
service packs, build 3790.
I found one reference to it in response to a question on winnetmag.com:
What is error msg 1203, "attempting to unlock unowned resource "?
Answered: This error indicates a bug in SQL Server. Several bugs that caused
this error were fixed in SQL Server 2000, service pack 1. If you can create
a script that will allow anyone to reproduce this problem, you should send
it in to Microsoft SQL Server support.
I can't reproduce this except in our database. dbcc checkdb indicates no
errors.
I am concerned about data loss without any error to detect it. Can anyone
help me with this?
Thanks
Bill
Hi
Error 1203 is bad. Process ID %d attempting to unlock unowned resource %.*ls.
There are a number of KB articles indicating that it occurs up to version
8.00.661 (KB 326999). SP3a is 8.00.760. Get your servers to at least SQL
Server sp3a and see if the error still occurs. Plus, if you are not on SQL
Server 2000 sp3a, you are exposed to the Slammer virus.
Regards
Mike
"bill" wrote:

> I am finding Error 1203 Severity 20 periodically in my trace.
> This seems to correlate with intermittent failed insert statements performed
> by a stored procedure. The stored procedure is executed shortly before this
> error is recorded.
> Nothing is written to the SQL Server log.
> The error is occurring on SQL Server 2000 Enterprise, SP 2, build 2195,
> running on W2K server.
> The same error occurs in a test database on SQL Server 2000 Standard, no
> service packs, build 3790.
> I found one reference to it in response to a question on winnetmag.com:
> What is error msg 1203, "attempting to unlock unowned resource "?
> Answered: This error indicates a bug in SQL Server. Several bugs that caused
> this error were fixed in SQL Server 2000, service pack 1. If you can create
> a script that will allow anyone to reproduce this problem, you should send
> it in to Microsoft SQL Server support.
> I can't reproduce this except in our database. dbcc checkdb indicates no
> errors.
> I am concerned about data loss without any error to detect it. Can anyone
> help me with this?
> Thanks
> Bill
>
>
|||Mike:
SQL Server SP3a DOES NOT contain the fix for the "Slammer Worm" virus,
MS03-031. This was originally launched as build 817 but caused a but in the
DtsGui.dll. This fix replaced the previous edition to build 818. This
release caused a password issue when using SEM. This fix is build 819.
SP3a is build 760.
The newly released SQL Server 2000 Release A is a slipstream of the pre SP3a
release code. If one uses this, then only SP3a need to be applied.
Build 818:
http://support.microsoft.com/?scid=k...852&sid=global
Build 819:
http://support.microsoft.com/kb/826161
Release A:
http://support.microsoft.com/default...b;en-us;826818
Sincerely,
Anthony Thomas

"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:94447A54-2C1F-483F-8D71-1584645C4BCA@.microsoft.com...
> Hi
> Error 1203 is bad. Process ID %d attempting to unlock unowned resource
%.*ls.[vbcol=seagreen]
> There are a number of KB articles indicating that it occurs up to version
> 8.00.661 (KB 326999). SP3a is 8.00.760. Get your servers to at least SQL
> Server sp3a and see if the error still occurs. Plus, if you are not on SQL
> Server 2000 sp3a, you are exposed to the Slammer virus.
> Regards
> Mike
>
> "bill" wrote:
performed[vbcol=seagreen]
this[vbcol=seagreen]
caused[vbcol=seagreen]
create[vbcol=seagreen]
send[vbcol=seagreen]
no[vbcol=seagreen]
anyone[vbcol=seagreen]
|||Anthony,
SQL Server 2000 Service Pack 3a does protect against Slammer. The
original patch to protect against Slammer was MS02-039. SQL Server
SP3a, available at
http://www.microsoft.com/downloads/d...isplaylang=en,
"corrects vulnerabilities including the Slammer (W32.slammer) virus."
It will not remove Slammer if it is present, and the page says "SQL 2000
Servers infected by the "Slammer" worm virus should follow instructions
on the Microsoft TechNet Web site before installing SP3a."
According to
http://www.microsoft.com/downloads/d...isplayLang=en,
"Instances of SQL Server 2000 with Service Pack 2 (SP2) and security
patch MS02-039, MS02-043, MS02-056, or MS02-061, or instances with SP3
or later, are not vulnerable to the slammer worm."
Release A also protects against Slammer, but does not contain other
fixes from SP3a.
Steve Kass
Drew University
AnthonyThomas wrote:

>Mike:
>SQL Server SP3a DOES NOT contain the fix for the "Slammer Worm" virus,
>MS03-031. This was originally launched as build 817 but caused a but in the
>DtsGui.dll. This fix replaced the previous edition to build 818. This
>release caused a password issue when using SEM. This fix is build 819.
>SP3a is build 760.
>The newly released SQL Server 2000 Release A is a slipstream of the pre SP3a
>release code. If one uses this, then only SP3a need to be applied.
>Build 818:
>http://support.microsoft.com/?scid=k...852&sid=global
>
>Build 819:
>http://support.microsoft.com/kb/826161
>
>Release A:
>http://support.microsoft.com/default...b;en-us;826818
>
>Sincerely,
>
>Anthony Thomas
>
>
|||Then why the need for MS03-031 for post SP3 or SP3a, both of which are only the 760 builds, while the MS03-031 was originally 817, then 818, closely followed by 819? Since the hot fixes and service packs are cumulative, why didn't the post SP2 hot fixes make it into the SP3(a) releases? Again, if they did, then why the need for the 817 through 819 builds?
Sincerely,
Anthony Thomas

"Steve Kass" <skass@.drew.edu> wrote in message news:%23qrsygtyEHA.1300@.TK2MSFTNGP14.phx.gbl...
Anthony,
SQL Server 2000 Service Pack 3a does protect against Slammer. The
original patch to protect against Slammer was MS02-039. SQL Server
SP3a, available at
http://www.microsoft.com/downloads/d...isplaylang=en,
"corrects vulnerabilities including the Slammer (W32.slammer) virus."
It will not remove Slammer if it is present, and the page says "SQL 2000
Servers infected by the "Slammer" worm virus should follow instructions
on the Microsoft TechNet Web site before installing SP3a."
According to
http://www.microsoft.com/downloads/d...isplayLang=en,
"Instances of SQL Server 2000 with Service Pack 2 (SP2) and security
patch MS02-039, MS02-043, MS02-056, or MS02-061, or instances with SP3
or later, are not vulnerable to the slammer worm."
Release A also protects against Slammer, but does not contain other
fixes from SP3a.
Steve Kass
Drew University
AnthonyThomas wrote:

>Mike:
>
>SQL Server SP3a DOES NOT contain the fix for the "Slammer Worm" virus,
>MS03-031. This was originally launched as build 817 but caused a but in the
>DtsGui.dll. This fix replaced the previous edition to build 818. This
>release caused a password issue when using SEM. This fix is build 819.
>SP3a is build 760.
>
>The newly released SQL Server 2000 Release A is a slipstream of the pre SP3a
>release code. If one uses this, then only SP3a need to be applied.
>
>Build 818:
>
>http://support.microsoft.com/?scid=k...852&sid=global
>
>
>Build 819:
>
>http://support.microsoft.com/kb/826161
>
>
>Release A:
>
>http://support.microsoft.com/default...b;en-us;826818
>
>
>Sincerely,
>
>
>Anthony Thomas
>
>
>
>|||I don't understand your question at all. Here's what I said:
Anthony,
SQL Server 2000 Service Pack 3a does protect against Slammer. The
original patch to protect against Slammer was MS02-039. SQL Server
SP3a, available at
http://www.microsoft.com/downloads/d...isplaylang=en,
"corrects vulnerabilities including the Slammer (W32.slammer)
virus." It will not remove Slammer if it is present, and the page
says "SQL 2000 Servers infected by the "Slammer" worm virus should
follow instructions on the Microsoft TechNet Web site before
installing SP3a."
According to
http://www.microsoft.com/downloads/d...isplayLang=en,
"Instances of SQL Server 2000 with Service Pack 2 (SP2) and security
patch MS02-039, MS02-043, MS02-056, or MS02-061, or instances with
SP3 or later, are not vulnerable to the slammer worm."
Release A also protects against Slammer, but does not contain other
fixes from SP3a.
Steve Kass
Drew University
You had incorrectly stated "SQL Server SP3a DOES NOT contain the fix for
the "Slammer Worm" virus MS03-031," and I corrected your misstatement.
It does. SQL Server build 818 appeared after Service Pack 3 (Service
Pack 3/3a is build 760), and build 818 corrected three post-Slammer
security vulnerabilities identified in MS03-031.
Slammer appeared after sp3 had been released, and it did not affect
anyone who had already installed sp3, or who had installed the even
early post-sp2 fix correcting the vulnerability ultimately exploited by
Slammer (pre-Slammer, pre-sp2 bulletin MS02-039). Everything to do with
Slammer (recognition of the vulnerability, the security bulletin, the
patch, the service pack that contained the patch, and finally the
exploit) happened well before the later and unrelated bulletin MS03-031
(build 818, which was post-sp3, not pre-sp3).
SK
AnthonyThomas wrote:

>Then why the need for MS03-031 for post SP3 or SP3a, both of which are only the 760 builds, while the MS03-031 was originally 817, then 818, closely followed by 819? Since the hot fixes and service packs are cumulative, why didn't the post SP2 hot fixes
make it into the SP3(a) releases? Again, if they did, then why the need for the 817 through 819 builds?
>Sincerely,
>
>Anthony Thomas
>
>

Error 1203

I am finding Error 1203 Severity 20 periodically in my trace.
This seems to correlate with intermittent failed insert statements performed
by a stored procedure. The stored procedure is executed shortly before this
error is recorded.
Nothing is written to the SQL Server log.
The error is occurring on SQL Server 2000 Enterprise, SP 2, build 2195,
running on W2K server.
The same error occurs in a test database on SQL Server 2000 Standard, no
service packs, build 3790.
I found one reference to it in response to a question on winnetmag.com:
What is error msg 1203, "attempting to unlock unowned resource "?
Answered: This error indicates a bug in SQL Server. Several bugs that caused
this error were fixed in SQL Server 2000, service pack 1. If you can create
a script that will allow anyone to reproduce this problem, you should send
it in to Microsoft SQL Server support.
I can't reproduce this except in our database. dbcc checkdb indicates no
errors.
I am concerned about data loss without any error to detect it. Can anyone
help me with this?
Thanks
BillHi
Error 1203 is bad. Process ID %d attempting to unlock unowned resource %.*ls
.
There are a number of KB articles indicating that it occurs up to version
8.00.661 (KB 326999). SP3a is 8.00.760. Get your servers to at least SQL
Server sp3a and see if the error still occurs. Plus, if you are not on SQL
Server 2000 sp3a, you are exposed to the Slammer virus.
Regards
Mike
"bill" wrote:

> I am finding Error 1203 Severity 20 periodically in my trace.
> This seems to correlate with intermittent failed insert statements perform
ed
> by a stored procedure. The stored procedure is executed shortly before th
is
> error is recorded.
> Nothing is written to the SQL Server log.
> The error is occurring on SQL Server 2000 Enterprise, SP 2, build 2195,
> running on W2K server.
> The same error occurs in a test database on SQL Server 2000 Standard, no
> service packs, build 3790.
> I found one reference to it in response to a question on winnetmag.com:
> What is error msg 1203, "attempting to unlock unowned resource "?
> Answered: This error indicates a bug in SQL Server. Several bugs that caus
ed
> this error were fixed in SQL Server 2000, service pack 1. If you can creat
e
> a script that will allow anyone to reproduce this problem, you should send
> it in to Microsoft SQL Server support.
> I can't reproduce this except in our database. dbcc checkdb indicates no
> errors.
> I am concerned about data loss without any error to detect it. Can anyone
> help me with this?
> Thanks
> Bill
>
>|||Mike:
SQL Server SP3a DOES NOT contain the fix for the "Slammer Worm" virus,
MS03-031. This was originally launched as build 817 but caused a but in the
DtsGui.dll. This fix replaced the previous edition to build 818. This
release caused a password issue when using SEM. This fix is build 819.
SP3a is build 760.
The newly released SQL Server 2000 Release A is a slipstream of the pre SP3a
release code. If one uses this, then only SP3a need to be applied.
Build 818:
http://support.microsoft.com/?scid=...2852&sid=global
Build 819:
http://support.microsoft.com/kb/826161
Release A:
http://support.microsoft.com/defaul...kb;en-us;826818
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:94447A54-2C1F-483F-8D71-1584645C4BCA@.microsoft.com...
> Hi
> Error 1203 is bad. Process ID %d attempting to unlock unowned resource
%.*ls.[vbcol=seagreen]
> There are a number of KB articles indicating that it occurs up to version
> 8.00.661 (KB 326999). SP3a is 8.00.760. Get your servers to at least SQL
> Server sp3a and see if the error still occurs. Plus, if you are not on SQL
> Server 2000 sp3a, you are exposed to the Slammer virus.
> Regards
> Mike
>
> "bill" wrote:
>
performed[vbcol=seagreen]
this[vbcol=seagreen]
caused[vbcol=seagreen]
create[vbcol=seagreen]
send[vbcol=seagreen]
no[vbcol=seagreen]
anyone[vbcol=seagreen]|||Anthony,
SQL Server 2000 Service Pack 3a does protect against Slammer. The
original patch to protect against Slammer was MS02-039. SQL Server
SP3a, available at
http://www.microsoft.com/downloads/...displaylang=en,
"corrects vulnerabilities including the Slammer (W32.slammer) virus."
It will not remove Slammer if it is present, and the page says "SQL 2000
Servers infected by the "Slammer" worm virus should follow instructions
on the Microsoft technet Web site before installing SP3a."
According to
http://www.microsoft.com/downloads/...DisplayLang=en,
"Instances of SQL Server 2000 with Service Pack 2 (SP2) and security
patch MS02-039, MS02-043, MS02-056, or MS02-061, or instances with SP3
or later, are not vulnerable to the slammer worm."
Release A also protects against Slammer, but does not contain other
fixes from SP3a.
Steve Kass
Drew University
AnthonyThomas wrote:

>Mike:
>SQL Server SP3a DOES NOT contain the fix for the "Slammer Worm" virus,
>MS03-031. This was originally launched as build 817 but caused a but in th
e
>DtsGui.dll. This fix replaced the previous edition to build 818. This
>release caused a password issue when using SEM. This fix is build 819.
>SP3a is build 760.
>The newly released SQL Server 2000 Release A is a slipstream of the pre SP3
a
>release code. If one uses this, then only SP3a need to be applied.
>Build 818:
>http://support.microsoft.com/?scid=...2852&sid=global
>
>Build 819:
>http://support.microsoft.com/kb/826161
>
>Release A:
>http://support.microsoft.com/defaul...kb;en-us;826818
>
>Sincerely,
>
>Anthony Thomas
>
>|||Then why the need for MS03-031 for post SP3 or SP3a, both of which are only
the 760 builds, while the MS03-031 was originally 817, then 818, closely fol
lowed by 819? Since the hot fixes and service packs are cumulative, why did
n't the post SP2 hot fixes make it into the SP3(a) releases? Again, if they
did, then why the need for the 817 through 819 builds?
Sincerely,
Anthony Thomas
--
"Steve Kass" <skass@.drew.edu> wrote in message news:%23qrsygtyEHA.1300@.TK2
MSFTNGP14.phx.gbl...
Anthony,
SQL Server 2000 Service Pack 3a does protect against Slammer. The
original patch to protect against Slammer was MS02-039. SQL Server
SP3a, available at
http://www.microsoft.com/downloads/...displaylang=en,
"corrects vulnerabilities including the Slammer (W32.slammer) virus."
It will not remove Slammer if it is present, and the page says "SQL 2000
Servers infected by the "Slammer" worm virus should follow instructions
on the Microsoft technet Web site before installing SP3a."
According to
http://www.microsoft.com/downloads/...DisplayLang=en,
"Instances of SQL Server 2000 with Service Pack 2 (SP2) and security
patch MS02-039, MS02-043, MS02-056, or MS02-061, or instances with SP3
or later, are not vulnerable to the slammer worm."
Release A also protects against Slammer, but does not contain other
fixes from SP3a.
Steve Kass
Drew University
AnthonyThomas wrote:

>Mike:
>
>SQL Server SP3a DOES NOT contain the fix for the "Slammer Worm" virus,
>MS03-031. This was originally launched as build 817 but caused a but in
the
>DtsGui.dll. This fix replaced the previous edition to build 818. This
>release caused a password issue when using SEM. This fix is build 819.
>SP3a is build 760.
>
>The newly released SQL Server 2000 Release A is a slipstream of the pre S
P3a
>release code. If one uses this, then only SP3a need to be applied.
>
>Build 818:
>
>http://support.microsoft.com/?scid=...2852&sid=global
>
>
>Build 819:
>
>http://support.microsoft.com/kb/826161
>
>
>Release A:
>
>http://support.microsoft.com/defaul...kb;en-us;826818
>
>
>Sincerely,
>
>
>Anthony Thomas
>
>
>
>|||I don't understand your question at all. Here's what I said:
Anthony,
SQL Server 2000 Service Pack 3a does protect against Slammer. The
original patch to protect against Slammer was MS02-039. SQL Server
SP3a, available at
http://www.microsoft.com/downloads/...displaylang=en,
"corrects vulnerabilities including the Slammer (W32.slammer)
virus." It will not remove Slammer if it is present, and the page
says "SQL 2000 Servers infected by the "Slammer" worm virus should
follow instructions on the Microsoft technet Web site before
installing SP3a."
According to
http://www.microsoft.com/downloads/...DisplayLang=en,
"Instances of SQL Server 2000 with Service Pack 2 (SP2) and security
patch MS02-039, MS02-043, MS02-056, or MS02-061, or instances with
SP3 or later, are not vulnerable to the slammer worm."
Release A also protects against Slammer, but does not contain other
fixes from SP3a.
Steve Kass
Drew University
You had incorrectly stated "SQL Server SP3a DOES NOT contain the fix for
the "Slammer Worm" virus MS03-031," and I corrected your misstatement.
It does. SQL Server build 818 appeared after Service Pack 3 (Service
Pack 3/3a is build 760), and build 818 corrected three post-Slammer
security vulnerabilities identified in MS03-031.
Slammer appeared after sp3 had been released, and it did not affect
anyone who had already installed sp3, or who had installed the even
early post-sp2 fix correcting the vulnerability ultimately exploited by
Slammer (pre-Slammer, pre-sp2 bulletin MS02-039). Everything to do with
Slammer (recognition of the vulnerability, the security bulletin, the
patch, the service pack that contained the patch, and finally the
exploit) happened well before the later and unrelated bulletin MS03-031
(build 818, which was post-sp3, not pre-sp3).
SK
AnthonyThomas wrote:

>Then why the need for MS03-031 for post SP3 or SP3a, both of which are only the 760
builds, while the MS03-031 was originally 817, then 818, closely followed by 819?
Since the hot fixes and service packs are cumulative, why didn't the post SP2 hot fi
xes
make it into the SP3(a) releases? Again, if they did, then why the need for the 817 through
819 builds?
>Sincerely,
>
>Anthony Thomas
>
>

error 1069: The service did not start due to logon problems

The local sql server that was working fine earlier does not start now. I have already created tables and accessed them using stored procedures.

I get an error 1069: The service did not start due to logon problems. I have only changed my domain password. I do not remember what authentication I chose for log on.

Any suggestions would be helpful.

Thanks

try this article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q282254

error 1069: The service did not start due to logon problems

The local sql server that was working fine earlier does not start now. I have already created tables and accessed them using stored procedures.

I get an error 1069: The service did not start due to logon problems. I have only changed my domain password. I do not remember what authentication I chose for log on.

Any suggestions would be helpful.

Thanks

try this article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q282254

Error 0: Syntax error or Access violation

Hi,
I'm writing a stored procedure and when I click on the Check Syntax button its giving me the error in the subject. I'm not really sure whats wrong with this. Here is my Stored Procedure code. Any help wud be appreciated.

CREATE PROC CabsSchedule_Insert
{
@.JulianDatesmallint,
@.SiteCodesmallint,
@.CalendarDaysmallint,
@.BillPeriodsmallint,
@.WorkDaysmallint,
@.CalDayBillRcvd varchar(30),
@.Remarksvarchar(50)
}
 AS
INSERT INTO CabsSchedule
(JulianDate, SiteCode, CalendarDay, BillPeriod, WorkDay, CalDayBillRcvd, Remarks)
VALUES
(@.JulianDate, @.SiteCode, @.CalendarDay, @.BillPeriod, @.WorkDay, @.CalDayBillRcvd, @.Remarks)

Thanks,check the datatypes of the columns and the values that you are trying to put into them. other than that i dont see anything else. you might also want tocheck this if you havent already.

hth|||Hi Dinakar,
Thanks for the reply. I got this thing to work. I'm not really sure how SQL Server behaves if I had an extra space inbetween my proc but removing the flower brackets ({) and
adjusting the CabsSchedule in the insert statement right beside the field names did the trick.

CREATE PROC CabsSchedule_Insert
@.JulianDatesmallint,
@.SiteCodesmallint,
@.CalendarDaysmallint,
@.BillPeriodsmallint,
@.WorkDaysmallint,
@.CalDayBillRcvd varchar(30),
@.Remarksvarchar(50)
 AS
INSERT INTO
CabsSchedule(JulianDate, SiteCode, CalendarDay, BillPeriod, WorkDay, CalDayBillRcvd, Remarks)
VALUES
(@.JulianDate, @.SiteCode, @.CalendarDay, @.BillPeriod, @.WorkDay, @.CalDayBillRcvd, @.Remarks)

Thanks again!

error [could not find st.proc. "user _get total users"]

I installed a cd program and the sql database 2000 included in the cd.
However, when launching the program I get a message "could not find stored
procedure user-get total users'. If I continue I get a second later on "could
not find stored procedure 'user -name in use '. The provider has no idea .
System is a P4 XP home sp2
I am not a specialist so before going to deep dive in the systems, is there
any thing "simple" than can be done ?From what CD do you install, is it a vendor specific Database (MSDE). What
"Program" are you starting when you get that error. Seems to me that this
might be a vendor specific problem, if you got an individual coded app.
More Information would be great.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"pink bear" <pink bear @.discussions.microsoft.com> schrieb im Newsbeitrag
news:FC323DB9-58A0-4BCF-BA75-8A6792D79CE3@.microsoft.com...
>I installed a cd program and the sql database 2000 included in the cd.
> However, when launching the program I get a message "could not find
> stored
> procedure user-get total users'. If I continue I get a second later on
> "could
> not find stored procedure 'user -name in use '. The provider has no idea .
> System is a P4 XP home sp2
> I am not a specialist so before going to deep dive in the systems, is
> there
> any thing "simple" than can be done ?|||Thanks Jens, It is a multimedia software , when installing it also proposes
to install SQL.The error starts after launching the program ( ican see part
of it) , it seems that it sends a login not registered in SQL, there must be
somewhere a table to update .
I have reintsalled everything including the sql, but the problem is still
there.
the strange thing is that in the very first install worked fine. I had to
reinstall x p and to reinstall all the programs, all went fine except for
this one.
"Jens Sü�meyer" wrote:
> From what CD do you install, is it a vendor specific Database (MSDE). What
> "Program" are you starting when you get that error. Seems to me that this
> might be a vendor specific problem, if you got an individual coded app.
> More Information would be great.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "pink bear" <pink bear @.discussions.microsoft.com> schrieb im Newsbeitrag
> news:FC323DB9-58A0-4BCF-BA75-8A6792D79CE3@.microsoft.com...
> >I installed a cd program and the sql database 2000 included in the cd.
> > However, when launching the program I get a message "could not find
> > stored
> > procedure user-get total users'. If I continue I get a second later on
> > "could
> > not find stored procedure 'user -name in use '. The provider has no idea .
> > System is a P4 XP home sp2
> > I am not a specialist so before going to deep dive in the systems, is
> > there
> > any thing "simple" than can be done ?
>
>

Error ["Unspecified Error"] while inserting data from XML to database using a Stored P

I am gettng the following error message when I try inserting data from a XML to the database (using a Stored Proc). The value of an attribute in the XML tag is very long, and when I try splitting that into multiple strings and insert each one separately to the database, to analyze the issue, I find that all the strings are getting inserted individually without any problem. But when I insert the concatenated form of the string as a whole then it gives me this problem.

Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1

The error description is 'Unspecified error'.

Msg 8179, Level 16, State 5, Procedure InsertWinAPIInfo, Line 61

Could not find prepared statement with handle 0.

The statement has been terminated.

Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1

sp_xml_removedocument: The value supplied for parameter number 1 is invalid.

I have tried searching on the net and did not find any possible solution for this. Please help me.You are probably hitting some limit on attribute length of the tag, and this is causing sp_xml_preparedocument to fail.|||You are probably hitting some limit on attribute length of the tag, and this is causing sp_xml_preparedocument to fail. I could not find any documentation as to the maximum length allowed for an attribute but internally I think sp_xml_preparedocument uses msxml parser so you can review docs for msxml parser to see what the limits are.

Error : Cannot specify an index or locking hint for a remote data source

We are migrating from sql 7 to sql 2000 and for the time
being leaving the database in sql 7 compatabiliy mode.
Some of the stored procedures reference the tables on a
linked server. The linked server is running sql 2000 and
the database is sql 2000.
When I run the following command
select * from linked_server.database.dbo.tablename (nolock)
I receive the following error
Cannot specify an index or locking hint for a remote data
source.
If I remove the () around nolock the statement runs.
The question(s) I have
1. Did I set something up wrong?
2. Without the () is the nolock still performing the same
function?
3. Assumining I did not error on the configuration, Why do
I receive this error now, and not before?
4. Any ideas to fix the problem without removing the ()?
Thanks in advance for any suggestions/ideas.
John WilliamsWithout the brackets around nolock, the SQL query engine is assigning the
word 'nolock' as an alias to your table in the select statement (i.e.
equivalent to 'as [nolock]').
R
"John Williams" <anonymous@.discussions.microsoft.com> wrote in message
news:27f3501c4636b$941fa990$a301280a@.phx.gbl...
> We are migrating from sql 7 to sql 2000 and for the time
> being leaving the database in sql 7 compatabiliy mode.
> Some of the stored procedures reference the tables on a
> linked server. The linked server is running sql 2000 and
> the database is sql 2000.
> When I run the following command
> select * from linked_server.database.dbo.tablename (nolock)
> I receive the following error
> Cannot specify an index or locking hint for a remote data
> source.
> If I remove the () around nolock the statement runs.
> The question(s) I have
> 1. Did I set something up wrong?
> 2. Without the () is the nolock still performing the same
> function?
> 3. Assumining I did not error on the configuration, Why do
> I receive this error now, and not before?
> 4. Any ideas to fix the problem without removing the ()?
> Thanks in advance for any suggestions/ideas.
> John Williams|||Can you not create a stored procedure on the remote data source that
specifies the hint?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"John Williams" <anonymous@.discussions.microsoft.com> wrote in message
news:27f3501c4636b$941fa990$a301280a@.phx.gbl...
> We are migrating from sql 7 to sql 2000 and for the time
> being leaving the database in sql 7 compatabiliy mode.
> Some of the stored procedures reference the tables on a
> linked server. The linked server is running sql 2000 and
> the database is sql 2000.
> When I run the following command
> select * from linked_server.database.dbo.tablename (nolock)
> I receive the following error
> Cannot specify an index or locking hint for a remote data
> source.
> If I remove the () around nolock the statement runs.
> The question(s) I have
> 1. Did I set something up wrong?
> 2. Without the () is the nolock still performing the same
> function?
> 3. Assumining I did not error on the configuration, Why do
> I receive this error now, and not before?
> 4. Any ideas to fix the problem without removing the ()?
> Thanks in advance for any suggestions/ideas.
> John Williams|||I am not sure if I understand what you are asking? Are you
suggesting that the stored procedure we run on the server
that has the syntax.
Select & from linkerserver.database.dbo.table1 and instead
run a stored procedure on the linked server that
accomplishes the same thing? I can look into this as an
option.
Thanks...
>--Original Message--
>Can you not create a stored procedure on the remote data
source that
>specifies the hint?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"John Williams" <anonymous@.discussions.microsoft.com>
wrote in message
>news:27f3501c4636b$941fa990$a301280a@.phx.gbl...
>> We are migrating from sql 7 to sql 2000 and for the time
>> being leaving the database in sql 7 compatabiliy mode.
>> Some of the stored procedures reference the tables on a
>> linked server. The linked server is running sql 2000 and
>> the database is sql 2000.
>> When I run the following command
>> select * from linked_server.database.dbo.tablename
(nolock)
>> I receive the following error
>> Cannot specify an index or locking hint for a remote
data
>> source.
>> If I remove the () around nolock the statement runs.
>> The question(s) I have
>> 1. Did I set something up wrong?
>> 2. Without the () is the nolock still performing the
same
>> function?
>> 3. Assumining I did not error on the configuration, Why
do
>> I receive this error now, and not before?
>> 4. Any ideas to fix the problem without removing the ()?
>> Thanks in advance for any suggestions/ideas.
>> John Williams
>
>.
>|||Thanks,
That an excellent point, so I am going to get unwanted
results by removing the (), I am concerned about locking
though, do you know since I cannot specify the nolock
hint, am I getting the benefit of (nolock) somehow?
>--Original Message--
>Without the brackets around nolock, the SQL query engine
is assigning the
>word 'nolock' as an alias to your table in the select
statement (i.e.
>equivalent to 'as [nolock]').
>R
>"John Williams" <anonymous@.discussions.microsoft.com>
wrote in message
>news:27f3501c4636b$941fa990$a301280a@.phx.gbl...
>> We are migrating from sql 7 to sql 2000 and for the time
>> being leaving the database in sql 7 compatabiliy mode.
>> Some of the stored procedures reference the tables on a
>> linked server. The linked server is running sql 2000 and
>> the database is sql 2000.
>> When I run the following command
>> select * from linked_server.database.dbo.tablename
(nolock)
>> I receive the following error
>> Cannot specify an index or locking hint for a remote
data
>> source.
>> If I remove the () around nolock the statement runs.
>> The question(s) I have
>> 1. Did I set something up wrong?
>> 2. Without the () is the nolock still performing the
same
>> function?
>> 3. Assumining I did not error on the configuration, Why
do
>> I receive this error now, and not before?
>> 4. Any ideas to fix the problem without removing the ()?
>> Thanks in advance for any suggestions/ideas.
>> John Williams
>
>.
>|||Yes, I think you will find that if you create a stored procedure on the
remote server, instead of running an ad hoc query locally, you will have
more freedom over transactions within the proc, locking hints, etc. You
should always be using stored procs as opposed to ad hoc statements
anyway...
--
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:274bb01c46371$b02b52a0$a501280a@.phx.gbl...
> I am not sure if I understand what you are asking? Are you
> suggesting that the stored procedure we run on the server
> that has the syntax.
> Select & from linkerserver.database.dbo.table1 and instead
> run a stored procedure on the linked server that
> accomplishes the same thing? I can look into this as an
> option.
> Thanks...
>
> >--Original Message--
> >Can you not create a stored procedure on the remote data
> source that
> >specifies the hint?
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >
> >
> >"John Williams" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:27f3501c4636b$941fa990$a301280a@.phx.gbl...
> >> We are migrating from sql 7 to sql 2000 and for the time
> >> being leaving the database in sql 7 compatabiliy mode.
> >> Some of the stored procedures reference the tables on a
> >> linked server. The linked server is running sql 2000 and
> >> the database is sql 2000.
> >>
> >> When I run the following command
> >> select * from linked_server.database.dbo.tablename
> (nolock)
> >>
> >> I receive the following error
> >> Cannot specify an index or locking hint for a remote
> data
> >> source.
> >>
> >> If I remove the () around nolock the statement runs.
> >>
> >> The question(s) I have
> >>
> >> 1. Did I set something up wrong?
> >> 2. Without the () is the nolock still performing the
> same
> >> function?
> >> 3. Assumining I did not error on the configuration, Why
> do
> >> I receive this error now, and not before?
> >> 4. Any ideas to fix the problem without removing the ()?
> >>
> >> Thanks in advance for any suggestions/ideas.
> >>
> >> John Williams
> >
> >
> >.
> >

Error : "Parallel worker query thread was involved in a deadlock" in SQL Se

We are running four session of application and each session is running
the same stored procedure against SQL Server database running SP3
patch.
All these four session are doing data intense operation and after some
time three of the process are getting completed correctly and the
fourth give us an DeadLock error message.
The error message which is displayed in the application is normal SQL
Server deadlock message.
But in the profiler we are seeing the below mentioned error message
//
Lock : Deadlock Chain : Parallel worker query thread was involved in a
deadlock" in profiler
//
We know that this error is cause by SQL Server optimization
methodology, we know it has to do with
parallel threads deadlocking each other.
Now we are looking for any documentation or information from the
Microsoft about this, any info will be appreciated.
Regards
Basharat<basharatw@.hotmail.com> wrote in message
news:1115667066.094653.87860@.f14g2000cwb.googlegroups.com...
> We are running four session of application and each session is running
> the same stored procedure against SQL Server database running SP3
> patch.
> All these four session are doing data intense operation and after some
> time three of the process are getting completed correctly and the
> fourth give us an DeadLock error message.
> The error message which is displayed in the application is normal SQL
> Server deadlock message.
> But in the profiler we are seeing the below mentioned error message
> //
> Lock : Deadlock Chain : Parallel worker query thread was involved in a
> deadlock" in profiler
> //
> We know that this error is cause by SQL Server optimization
> methodology, we know it has to do with
> parallel threads deadlocking each other.
> Now we are looking for any documentation or information from the
> Microsoft about this, any info will be appreciated.
> Regards
> Basharat
>
Take a look at the "max degree of parallelism" and "maxdop" query hints in
the books online.
I don't know if it will help in this situation or not.
Rick Sawtell
MCT, MCSD, MCDBA|||> We are running four session of application and each session is running
> the same stored procedure against SQL Server database running SP3
> patch.
> All these four session are doing data intense operation and after some
> time three of the process are getting completed correctly and the
> fourth give us an DeadLock error message.
> The error message which is displayed in the application is normal SQL
> Server deadlock message.
> But in the profiler we are seeing the below mentioned error message
> //
> Lock : Deadlock Chain : Parallel worker query thread was involved in a
> deadlock" in profiler
> //
> We know that this error is cause by SQL Server optimization
> methodology, we know it has to do with
> parallel threads deadlocking each other.
That sounds exactly l ike the problem I was having about two months ago on a
2-cpu server.
There is a MAXDOP option you can specify to set the "maximum degree of
parallelism" on a given SQL statement. I can't remember the sp that we had
to tweak that way, and I couldn't find it in the help index. I believe I
found it by dowing a full text search of BOL. For us, the solution was to
provide the MAXDOP hint and set it to 1 (I think).
If you can get to a source with more than a 3 month history of this NG, you
could search for posts containing MAXDOP from a thread that I started
between 2 & three months ago.
Perhaps someone else can chime in with something more precise.
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei
<basharatw@.hotmail.com> wrote in message
news:1115667066.094653.87860@.f14g2000cwb.googlegroups.com...
> Now we are looking for any documentation or information from the
> Microsoft about this, any info will be appreciated.
> Regards
> Basharat
>

Error - procedure expects parameter, which was not supplied

Hi,

I created a stored procedure in the sql server. I try to insert a record from the aspx page. But I keep getting this error,
"procedure expects parameter <@.firstname>, which was not supplied". This is what I am doing.

cmd.CommandText = "proc_insertuser";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.firstname", SqlDbType.VarChar, 50);
cmd.Parameters["@.firstname"].Value = txtFirstName.Text
...other parameters

I checked the parameter names, it matches correctly. The parameter does get added to the collection. I checked it using cmd.Parameters.Contains("@.firstname") and the value is also correct. Using query analyzer I executed the procedure, I am able to insert a record.

Can anyone tell me what could be the problem.
TAICan you use SQL Profiler? Perhaps that will help diagnose the problem. Is there some other parameter that is missing and being mis-reported?|||if you are using VB.NET I would try replacing your brackets surrounding your parameter name w/ parenthesis instead.

 cmd.Parameters("@.firstname").Value = txtFirstName.Text

HTH

Sandy

Error

I get this error message when I alter my stored procedure. Can someone help me out plsAdd parentheses around the string.

-PatP|||Why aren't you using the templates I showed you?

In any event, post the code not a picture...

But switch to

IF @.@.ERROR <> 0

But my guess is it's before that|||I'm 99% sure that the problem is just needing parentheses around the string after RAISERROR.

She probably got the 0 <> @.@.error from some of my code. Is there anybody that can guess why I almost always put constants on the left?

-PatP|||No clue...I thought that wasn't a good thing....|||The problem goes back to the days of cruddy compilers, and poor language syntax. This is just an example, but you can interpret the construct:SELECT @.a = 0to mean two very different things. You can only interpret the construct:SELECT 0 = @.aone way. Once upon a time, Transact-SQL could get confused, and many other tools still have the same problem. By always using an expression that can't be an l-value on the left side of a comparison, there is almost no chance that the code can be mis-interpreted.

There was actually one mainframe language that would let you assign new values to constants. That one confounded even me, but it was such a weird thing that I'm not too defensive about it! I doubt that it will happen again, but it was weird being allowed to redefine the value of zero!

-PatP|||I did get the @.@.Error from pats coding and I looked it up on the internet after I did a post here and that helped me to determine how to better use it and troubleshoot. I do love Google..sometime Books online does not give you enough information. Also you guys those Transact SQL books youi refered me to are awesome. they answer alot of question and are a wonderful source of info on programming (for those of us new to the programming concept)|||works great guys thanks again :):):)

Error

I am trying to learn how to create error checking in my stored procedures, and apparently I am having some problems with it can anyone help me out pls??

CREATE PROCEDURE [InsertGCTerms]
AS
INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms]([TM #],[FirstName],[LastName],[SocialSecurityNumber],[DateHired],[DepartmentName],[Title])
SELECT a.[TM#], a.LASTNAME, a.FIRSTNAME, a.[SSN#], a.HIREDATE,
a.[DEPT#], a.JOBTITLE FROM GOVEMPLYS AS a
WHERE a.STATUS = 'TERMINATED'
RETURN
GOCan you coach us just a bit on what to do if an error occurs? You can always use the "panic, scream, and shout" approach, but I generally prefer something just a bit more elegant!

-PatP|||I was trying to use the @.@. Error system function|||It appears that I'm not making my question very clear. Let's go for the ever loving example, maybe that will help!CREATE PROCEDURE [InsertGCTerms]
AS

INSERT INTO [CommissionEmployee_Exclusionsdb].[dbo].[GCEmployeeTerms] (
[TM #], [FirstName], [LastName]
, [SocialSecurityNumber], [DateHired], [DepartmentName]
, [Title] ) SELECT
a.[TM#], a.FIRSTNAME, a.LASTNAME
, a.[SSN#], a.HIREDATE , a.[DEPT#]
, a.JOBTITLE
FROM GOVEMPLYS AS a
WHERE a.STATUS = 'TERMINATED'

IF 0 <> @.@.error RAISERROR ('Scream and shout, run and panic!')

RETURN
GO-PatP|||Ok sorry I see your point..LOL that was funny, need a good laugh in the morning. sorry I didnt make myself clear. But basically pat you already answered my question and now I see where I went wrong. I am so glad I ordered the http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=bsq1M1M1GT&isbn=1565924010&itm=1 Book this will help me out tremendously, I need a book all about programming in sql.|||I would look at

Ken Hendersons Book (http://search.barnesandnoble.com/booksearch/isbninquiry.asp?userid=6F22YrtiAX&pwb=1&ean=9780201615760)

Also...did someone say error handling?

http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx|||Yes I originally ordered that book but apparently it was out of stock or something, they refunded my money back got the message via email. Yes I was inquiring about Error Handling|||Amazon out of stock?

I doubt it

And did you look at my link?

Follow the steps to save the tql file to your machine...|||ok Brett I ordered the book, those two should help me out tremendously. Thank you :)|||Great...but did you look at the error handling tql file in my blog?|||Yes Brett I am looking that over now thank yoiu

Erroneously deleted stored procedures

I have a teammate that, in an excess of zeal, managed to delete a majority of stored procedures on a production database, causing numerous jobs to fail. Additionally, there were no receint backups of this database taken, but the transaction log is still in place and has not been truncated. Is there any way to recover these stored procedures that I am unaware of?If the database is in the full recovery mode, you may be
able to do point-in-time restore to get back your deleted
procedures (if the log backup chain has not been broken):
1. Do a BACKUP LOG first
2. Do (using pubs as example)
restore database pubs from disk='...'
rstore log ... -- if any
restore log pubs from disk='...' -- the last log backup
with recovery, stopat = '20031010 17:59:59'
The STOPAT time should be before the time the procedures
were deleted.
Linchi
>--Original Message--
>I have a teammate that, in an excess of zeal, managed to
delete a majority of stored procedures on a production
database, causing numerous jobs to fail. Additionally,
there were no receint backups of this database taken, but
the transaction log is still in place and has not been
truncated. Is there any way to recover these stored
procedures that I am unaware of?
>.
>

Erratic Performance of SQL Server

Hello,

We have a complex system with many stored procedures, the same procedures run every day at about the same time. We are noticing severe fluctuations in performance. One day a procedure will take 150 minutes, the next day 250 minutes, the next day 100 minutes. A graph of the performance looks like a voice graph or a lie detector for a criminal. We are using SQL Server 2000 on an Itanium. Any suggestions or hints about how to stablize? This is happening for all of our procedures that run 24 hours a day.

Hi.

Are you running with SQL Server 2000 service pack 4 (for build 2039) and the last 2000 cumulative hotfix package (for build 2187)?

Regards,

Gary.

|||Stored procedure performance is always going to be directly relative to the load on the server, the number of rows being processed, the nature of the queries, i.e. read only versus updates and, in particular, on the performance of the I/O devices which can certainly vary if your stored procedures are competing against other processes trying to access the same data.

What is the stored procedure doing and what kind of data volumes are we talking about, i.e. a thousand rows, 10 million rows? Are there any cursors? Are there any other applications competing for the same data, i.e. an OLTP system?
|||

You might want to take the following steps:

1. Check the DBCC SHOW_STATISTICS and rowmodctr values (under the sysindexes table -- The rowmodctr value should be as close to ZERO as possible) and find out if the statistics for the database and the tables involved are out of date. If yes, please update the statistics with a full scan or 100% sampling rate.

2. Make sure that you are not running into a parameter sniffing issue. Please refer the following article for more details:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

http://msdn2.microsoft.com/en-us/library/ms190439.aspx

Sporadic bursts in SP performance which leads to inconsistent duration for a stored procedure would be due to recompilation of the SP with a bad input parameter which generates a bad plan.

Also, try and see if recompiling the stored procedure at that particular time helps.

|||You need to itemize your durations. If you run Profiler, you will see real execution costs, such as amount of CPU and number of reads/writes. Are these values wildly different from day to day? Note that different durations may be caused by:
- locking, when you procedure spends significant time in lock waiting state;
- network delays. Your procedure may be waiting for the client to receive the result sets.

For instance, you can run one and the same query twice and get the same CPU, the same reads, but very different duration, because one time there were no exclusive locks to wait for, and the other time the procedure spent most of the time in lock ewaiting state. Another example is when you invoke a query from SSMS, and most of the duration is spent by SSMS drawing a grid. If you switch to text mode and rerun the query, you may get a shorter duration.|||

Thanks for your replies, I'll look at these in more detail, but I want to provide some additional information.

We are running SQL Server 2000, Enterprise edition, 64-bit, version 8.00.2039 SP4. Our server is not shared by any other application, but we have two instances with concurrent processing. The two instances use separate databases, but each instance has 3 job queues that can run procesing tasks simultaneously. Locking could be an issue on one instance, but not on the other instance. We are observing the same erratic processing times on both instances. Our SQL server uses a SAN, which is used by other applications, but the processing performed by the other applications is very light. We have several different procedures, most are not parameterized. Some of our longest running stored procedures perform a fairly simple SELECT INTO with joins on master data tables -- but these procedures are the most erratic with regard to different processing times. The data volumes for these procedures is up to about 20 million records. Locking is not an issue in these procedures. Since all of our procedures seem to be erratic, I'm wondering if there might be a problem with the SQL server optimizer in our configuration.

|||Have you monitored your TempDB's? If they're too small, it will have to keep extending which is a serious drag on performance. If your stored procedures are doing a lot of sorts, group by's, and are using a lot of temp tables this might add further credence to the TempDB issue. Since you're using SELECT INTO the operations are unlogged so I doubt that's your problem. You also might monitor your QIO's on your SAN devices to see if there is a lot of contention, particularly on the device with TempDB on it. (If at all possible you might move the TempDB's to their own devices.) You might also consider creating one TempDB file for each processor if you haven't already and turning on Traceflag 1118.

Just some thoughts. Hope it helps.