Showing posts with label sp1. Show all posts
Showing posts with label sp1. Show all posts

Tuesday, March 27, 2012

CURSOR OPERATION CONFLICT

I've got a Windows 2003 SERVER SP1 running sql server 2000 SP4 that is
doing something I've never seen before. Regardless of what database or
table, if I add a new record or try to update a record using an
ADODB.Recordset, I get a CURSOR OPERATION CONFLICT error. This same
code works fine at about 50 other client sites running a similar
environment so I have a feeling there is something wrong with this
particular sql server. The same error occurs on any machine I run the
code on that hits that particular sql server so I don't think it's a
MDAC issue. If I change the cursor location to adUseClient, it works
but that's not a solution for me because it would require alot of code
changes and since this code works fine at other sites, I don't think
that is the solution. Anyone ever encountered a similar issue?
Here's a quick example of how I can reproduce the error, this is just a
vbscript demonstrating the problem:
set cnn=CreateObject("ADODB.Connection")
set rs=CreateObject("ADODB.Recordset")
cnn.open strConnectionString
'Opening it as adOpenKeyset, adLockOptmisitc
rs.Open "Select * From [test]", cnn,1, 3
rs.AddNew
rs.fields("Description").value="test2"
'getting error when the update is called Cursor Operation Conflict
rs.update
rs.close
msgbox "done"
'Windows 2003 Server Standard Edition SERVICE PACK1
'SQL SERVER 2000 SP4
'MDAC VERSION 2.8
I solved the problem.. Another company shares the same sql server that
we use for our application and they had many of the settings under the
"Default Connection Options" (which is under the SQL SERVER
Settings->Connections options tab) checked off, I unchecked all of them
(which is the way it is by default when sql server is installed) and
that cleared up the problem.
|||Just for the record: it seems that it was the "No count" attribute that
was causing the problem. This can be set using:
EXEC sp_configure 'user options', 512
Apparently, all server-side cursors in ADO rely on the number of
affected rows which is returned after the execution of certain
statements. Setting the "No count" attribute in "Default connection
options" (which is equivalent to executing "SET NOCOUNT ON" in each
connection that is opened) suppresses those messages, so ADO gets
confused.
For more informations, see:
http://support.microsoft.com/default...B;EN-US;195491
Razvan

CURSOR OPERATION CONFLICT

I've got a Windows 2003 SERVER SP1 running sql server 2000 SP4 that is
doing something I've never seen before. Regardless of what database or
table, if I add a new record or try to update a record using an
ADODB.Recordset, I get a CURSOR OPERATION CONFLICT error. This same
code works fine at about 50 other client sites running a similar
environment so I have a feeling there is something wrong with this
particular sql server. The same error occurs on any machine I run the
code on that hits that particular sql server so I don't think it's a
MDAC issue. If I change the cursor location to adUseClient, it works
but that's not a solution for me because it would require alot of code
changes and since this code works fine at other sites, I don't think
that is the solution. Anyone ever encountered a similar issue?
Here's a quick example of how I can reproduce the error, this is just a
vbscript demonstrating the problem:
set cnn=CreateObject("ADODB.Connection")
set rs=CreateObject("ADODB.Recordset")
cnn.open strConnectionString
'Opening it as adOpenKeyset, adLockOptmisitc
rs.Open "Select * From [test]", cnn,1, 3
rs.AddNew
rs.fields("Description").value="test2"
'getting error when the update is called Cursor Operation Conflict
rs.update
rs.close
msgbox "done"
'Windows 2003 Server Standard Edition SERVICE PACK1
'SQL SERVER 2000 SP4
'MDAC VERSION 2.8I solved the problem.. Another company shares the same sql server that
we use for our application and they had many of the settings under the
"Default Connection Options" (which is under the SQL SERVER
Settings->Connections options tab) checked off, I unchecked all of them
(which is the way it is by default when sql server is installed) and
that cleared up the problem.|||Just for the record: it seems that it was the "No count" attribute that
was causing the problem. This can be set using:
EXEC sp_configure 'user options', 512
Apparently, all server-side cursors in ADO rely on the number of
affected rows which is returned after the execution of certain
statements. Setting the "No count" attribute in "Default connection
options" (which is equivalent to executing "SET NOCOUNT ON" in each
connection that is opened) suppresses those messages, so ADO gets
confused.
For more informations, see:
http://support.microsoft.com/defaul...KB;EN-US;195491
Razvansql

CURSOR OPERATION CONFLICT

I've got a Windows 2003 SERVER SP1 running sql server 2000 SP4 that is
doing something I've never seen before. Regardless of what database or
table, if I add a new record or try to update a record using an
ADODB.Recordset, I get a CURSOR OPERATION CONFLICT error. This same
code works fine at about 50 other client sites running a similar
environment so I have a feeling there is something wrong with this
particular sql server. The same error occurs on any machine I run the
code on that hits that particular sql server so I don't think it's a
MDAC issue. If I change the cursor location to adUseClient, it works
but that's not a solution for me because it would require alot of code
changes and since this code works fine at other sites, I don't think
that is the solution. Anyone ever encountered a similar issue?
Here's a quick example of how I can reproduce the error, this is just a
vbscript demonstrating the problem:
set cnn=CreateObject("ADODB.Connection")
set rs=CreateObject("ADODB.Recordset")
cnn.open strConnectionString
'Opening it as adOpenKeyset, adLockOptmisitc
rs.Open "Select * From [test]", cnn,1, 3
rs.AddNew
rs.fields("Description").value="test2"
'getting error when the update is called Cursor Operation Conflict
rs.update
rs.close
msgbox "done"
'Windows 2003 Server Standard Edition SERVICE PACK1
'SQL SERVER 2000 SP4
'MDAC VERSION 2.8I solved the problem.. Another company shares the same sql server that
we use for our application and they had many of the settings under the
"Default Connection Options" (which is under the SQL SERVER
Settings->Connections options tab) checked off, I unchecked all of them
(which is the way it is by default when sql server is installed) and
that cleared up the problem.|||Just for the record: it seems that it was the "No count" attribute that
was causing the problem. This can be set using:
EXEC sp_configure 'user options', 512
Apparently, all server-side cursors in ADO rely on the number of
affected rows which is returned after the execution of certain
statements. Setting the "No count" attribute in "Default connection
options" (which is equivalent to executing "SET NOCOUNT ON" in each
connection that is opened) suppresses those messages, so ADO gets
confused.
For more informations, see:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;195491
Razvan

Cursor loop is broken

In a stored procedure (SP1) I am looping through a cursor with records
from Table1. Each record in the cursor is inserted into Table2.
Insert trigger on Table2 is inserting the record into Table3 (in
another DB).
In the insert trigger on Table3, a series on checks are done on the
inserted record and in case of an error, an email is sent and the
trigger returns.
This break the cursorloop in SP1 and the rest of the records in the
cursor is not treated.
How do I make sure that all records are treated?
This is the flow:
-- SP1 --
DECLARE csrListe CURSOR FOR SELECT felt1 FROM Table1
OPEN csrListe
-- The first record is treated here...
:
-- Treat the rest
WHILE @.@.FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM csrListe INTO @.feltet
IF @.@.FETCH_STATUS = 0 BEGIN
blah-blah-blah
INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat) VALUES
(@.Ordrenr, @.Status, @.Dato, @.Result)
END
END
CLOSE csrListe
DEALLOCATE csrListe
-- Table2_ITrig --
INSERT INTO db2.dbo.Table3 SELECT * FROM inserted
-- Table3_ITrig --
SET NOCOUNT ON
DECLARE @.STATUS int
DECLARE @.DATOTID smalldatetime
DECLARE @.RESULT int
SELECT @.ORDRENR = (SELECT ORDRENR FROM INSERTED)
SELECT @.STATUS = (SELECT STATUS FROM INSERTED)
SELECT @.DATOTID = (SELECT DATO FROM INSERTED)
SELECT @.RESULT = (SELECT RESULT FROM INSERTED)
SET XACT_ABORT ON
IF NOT @.STATUS IN (1,2,3,4,5,6,9,10) BEGIN
SELECT @.ERR = 'ERROR - unknown status = ' + CAST(@.ORDRENR as char(4))
UPDATE Table3 SET RESULTAT=2 WHERE ORDRENUMMER=@.ORDRENR
EXEC @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'me@.here.dk',
@.TO = N'you@.here.dk',
@.priority = N'HIGH',
@.subject = N'Status error',
@.message = N'Status error',
@.type = N'text/plain',
@.server = 'smtp.here.dk'
RETURN
END
The mail is send so it must be the final RETURN that is causing the
trouble.sblar wrote:
> In a stored procedure (SP1) I am looping through a cursor with records
> from Table1. Each record in the cursor is inserted into Table2.
> Insert trigger on Table2 is inserting the record into Table3 (in
> another DB).
> In the insert trigger on Table3, a series on checks are done on the
> inserted record and in case of an error, an email is sent and the
> trigger returns.
> This break the cursorloop in SP1 and the rest of the records in the
> cursor is not treated.
> How do I make sure that all records are treated?
> This is the flow:
> -- SP1 --
> DECLARE csrListe CURSOR FOR SELECT felt1 FROM Table1
> OPEN csrListe
> -- The first record is treated here...
> :
> -- Treat the rest
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> FETCH NEXT FROM csrListe INTO @.feltet
> IF @.@.FETCH_STATUS = 0 BEGIN
> blah-blah-blah
> INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat) VALUES
> (@.Ordrenr, @.Status, @.Dato, @.Result)
> END
> END
> CLOSE csrListe
> DEALLOCATE csrListe
> -- Table2_ITrig --
> INSERT INTO db2.dbo.Table3 SELECT * FROM inserted
> -- Table3_ITrig --
> SET NOCOUNT ON
> DECLARE @.STATUS int
> DECLARE @.DATOTID smalldatetime
> DECLARE @.RESULT int
>
> SELECT @.ORDRENR = (SELECT ORDRENR FROM INSERTED)
> SELECT @.STATUS = (SELECT STATUS FROM INSERTED)
> SELECT @.DATOTID = (SELECT DATO FROM INSERTED)
> SELECT @.RESULT = (SELECT RESULT FROM INSERTED)
> SET XACT_ABORT ON
>
> IF NOT @.STATUS IN (1,2,3,4,5,6,9,10) BEGIN
> SELECT @.ERR = 'ERROR - unknown status = ' + CAST(@.ORDRENR as char(4))
> UPDATE Table3 SET RESULTAT=2 WHERE ORDRENUMMER=@.ORDRENR
> EXEC @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = N'me@.here.dk',
> @.TO = N'you@.here.dk',
> @.priority = N'HIGH',
> @.subject = N'Status error',
> @.message = N'Status error',
> @.type = N'text/plain',
> @.server = 'smtp.here.dk'
> RETURN
> END
> The mail is send so it must be the final RETURN that is causing the
> trouble.
Do not use cursors in triggers. Doubly important, do not send email
from a trigger.
See:
http://groups.google.co.uk/group/mi...b273c91159441f2
David Portas
SQL Server MVP
--|||Thanks David, I understand your point about email and will consider
another path, but the problem here doesn't seem to be the email which
is sent ok. The cursor is in the sp not the trigger.
/S=F8ren|||"Sren Larsen" <sblar1@.surfpost.dk> wrote in message
news:1136633487.767264.30440@.g49g2000cwa.googlegroups.com...
Thanks David, I understand your point about email and will consider
another path, but the problem here doesn't seem to be the email which
is sent ok. The cursor is in the sp not the trigger.
/Sren
First, I suggest you get rid of the cursor. Use an INSERT ... SELECT
statement instead:
INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat)
SELECT Ordrenr, Status, Dato, Resultat
FROM ... ?
I expect there was more processing that you left out of your post but I can
only suggest a solution for what you posted.
Secondly, you need to modify your trigger to handle multiple rows properly.
Example:
/* This will FAIL if more than one row is inserted/updated */
SELECT @.ORDRENR = (SELECT ORDRENR FROM INSERTED)
If you don't send emails from the trigger then you won't need to assign the
column values to variables.
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> First, I suggest you get rid of the cursor. Use an INSERT ... SELECT
> statement instead:
> INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat)
> SELECT Ordrenr, Status, Dato, Resultat
> FROM ... ?
> I expect there was more processing that you left out of your post but I
> can only suggest a solution for what you posted.
> Secondly, you need to modify your trigger to handle multiple rows
> properly.
> Example:
> /* This will FAIL if more than one row is inserted/updated */
> SELECT @.ORDRENR = (SELECT ORDRENR FROM INSERTED)
> If you don't send emails from the trigger then you won't need to assign
> the column values to variables.
Hey, I've already said all of that! (Except the point of not sending
mail from a trigger.) But I said it in a different newsgroup, as Sren
posted the message independently to two newsgroups. With the result
that I and David waste our time to say the same thing.
Please do not do that again!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks David.
The cursor is in the sp not the trigger. I understand your point about
email though, and I will consider another path. But, this doesn't seem
to be the problem here as the email is sent.|||Sren Larsen (sblar1_thisisnotme@.surfpost.dk) writes:
> Anyway, thanks for your answers. I'm aware of the problem if my trigger
> receives multiple rows, which it dont cause its only called from my SP
> with the cursor loop. I would however very much like to avoid this way
> of processing but I can't see how if I want to do some processing on
> each row inserted. For example:
> if Status = 1
> set @.result = 2
> if Status = 2
> set @.result = 3
> update sometable set Status = @.Status where number = select number from
> inserted
> Any suggestions?
Without knowledge of the business problem, it's difficult to suggest a
complete solution. But for the particular problem you appear to illustrate
you can use the CASE expression:
UPDATE a
SET result = CASE b.status
WHEN 1 THEN 'OK'
WHEN 2 THEN 'OK with warnings'
WHEN 3 THEN 'Failed'
ELSE 'Complete disaster'
END
FROM a
JOIN b ON a.col = b.col
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> skrev i en meddelelse
news:Xns9745A1206A4E9Yazorman@.127.0.0.1...
> Without knowledge of the business problem, it's difficult to suggest a
> complete solution. But for the particular problem you appear to illustrate
> you can use the CASE expression:
> UPDATE a
> SET result = CASE b.status
> WHEN 1 THEN 'OK'
> WHEN 2 THEN 'OK with warnings'
> WHEN 3 THEN 'Failed'
> ELSE 'Complete disaster'
> END
> FROM a
> JOIN b ON a.col = b.col
>
Aha - thats neat. Does this mean that inserted are traversed and a.result
would be updated for every row in inserted and can it be done more than once
like this?
UPDATE a
SET result = CASE inserted.status
WHEN 1 THEN 'OK'
WHEN 2 THEN 'OK with warnings'
WHEN 3 THEN 'Failed'
ELSE 'Complete disaster'
END
FROM a
JOIN inserted ON a.col = inserted.col
UPDATE c
SET somefield = CASE inserted.someotherfield
WHEN 1 THEN 11
WHEN 2 THEN 12
WHEN 3 THEN 13
ELSE 0
END
FROM c
JOIN inserted ON c.col = inserted.col
Will this do an update of both a and c based on inserted rows?
/Sren|||I almost forgot; why is the cursorloop broken in the first place? There was
no error in trigger, unless RETURN is considered an error!
/Sren|||Sren Larsen (sblar1_thisisnotme@.surfpost.dk) writes:
> Aha - thats neat. Does this mean that inserted are traversed and
> a.result would be updated for every row in inserted and can it be done
> more than once like this?
> UPDATE a
> SET result = CASE inserted.status
> WHEN 1 THEN 'OK'
> WHEN 2 THEN 'OK with warnings'
> WHEN 3 THEN 'Failed'
> ELSE 'Complete disaster'
> END
> FROM a
> JOIN inserted ON a.col = inserted.col
> UPDATE c
> SET somefield = CASE inserted.someotherfield
> WHEN 1 THEN 11
> WHEN 2 THEN 12
> WHEN 3 THEN 13
> ELSE 0
> END
> FROM c
> JOIN inserted ON c.col = inserted.col
> Will this do an update of both a and c based on inserted rows?
Yes. CASE is extremely powerful when working with set-based operations.
The above is a simplifed form. The more general form is like this:
CASE WHEN somecolumn IN (1, 2, 3) THEN 'This'
WHEN othercolumn = 'A' AND somecolumn = 34 THEN 'That'
WHEN somecolumn < 0 THEN CASE col WHEN 1 THEN 'J' ELSE 'N' END
END
So you can test for more general conditions, and you can nest CASE.
The conditions are always evaluated top-down, and evaluation stops
as soon one matches. If no condition is true, and there is on ELSE,
the value is NULL.
Important to understand is that CASE is an *expression*, and as a
an expression it always return the same data type. If you try:
CASE something WHEN 1 THEN 0 ELSE 'x' END
this will fail, when something is not 1, because this CASE expression
returns an integer value, as integer is higher than char in the
data-type precedence in SQL Server.

> I almost forgot; why is the cursorloop broken in the first place? There
> was no error in trigger, unless RETURN is considered an error!
Was there a ROLLBACK? A rollback in a trigge aborts execution.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Cursor loop is broken

In a stored procedure (SP1) I am looping through a cursor with records
from Table1. Each record in the cursor is inserted into Table2.
Insert trigger on Table2 is inserting the record into Table3 (in
another DB).
In the insert trigger on Table3, a series of checks are done on the
inserted record and in case of an error, an email is sent and the
trigger returns.
This break the cursorloop in SP1 and the rest of the records in the
cursor is not treated.
How do I make sure that all records are treated?

This is the flow:

-- SP1 ----------
DECLARE csrListe CURSOR FOR SELECT felt1 FROM Table1

OPEN csrListe

-- The first record is treated here...
:

-- Treat the rest
WHILE @.@.FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM csrListe INTO @.feltet
IF @.@.FETCH_STATUS = 0 BEGIN
blah-blah-blah
INSERT INTO Table2 (Ordrenr, Status, Dato, Resultat) VALUES

(@.Ordrenr, @.Status, @.Dato, @.Result)
END
END
CLOSE csrListe
DEALLOCATE csrListe

-- Table2_ITrig ----------
INSERT INTO db2.dbo.Table3 SELECT * FROM inserted

-- Table3_ITrig ----------
SET NOCOUNT ON

DECLARE @.STATUS int
DECLARE @.DATOTID smalldatetime
DECLARE @.RESULT int

SELECT @.ORDRENR = (SELECT ORDRENR FROM INSERTED)
SELECT @.STATUS = (SELECT STATUS FROM INSERTED)
SELECT @.DATOTID = (SELECT DATO FROM INSERTED)
SELECT @.RESULT = (SELECT RESULT FROM INSERTED)

SET XACT_ABORT ON

IF NOT @.STATUS IN (1,2,3,4,5,6,9,10) BEGIN
SELECT @.ERR = 'ERROR - unknown status = ' + CAST(@.ORDRENR as char(4))

UPDATE Table3 SET RESULTAT=2 WHERE ORDRENUMMER=@.ORDRENR

EXEC @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N...@.here.dk',
@.TO = N...@.here.dk',
@.priority = N'HIGH',
@.subject = N'Status error',
@.message = N'Status error',
@.type = N'text/plain',
@.server = 'smtp.here.dk'

RETURN
END

The mail is send so it must be the final RETURN that is causing the
trouble.Sren Larsen (sblar1@.surfpost.dk) writes:
> In a stored procedure (SP1) I am looping through a cursor with records
> from Table1. Each record in the cursor is inserted into Table2.
> Insert trigger on Table2 is inserting the record into Table3 (in
> another DB).
> In the insert trigger on Table3, a series of checks are done on the
> inserted record and in case of an error, an email is sent and the
> trigger returns.
> This break the cursorloop in SP1 and the rest of the records in the
> cursor is not treated.
> How do I make sure that all records are treated?

An error in a trigger aborts the batch. Thus, in SQL 2000, there is no
way to handle the situation in T-SQL, you would need to have a client
program that reacts on the error, and restarts the loop, but leaving
out the row that causes problems. In SQL 2005, you could use TRY-CATCH
to handle the situation.

But why are you running a loop in the first place? The normal procedure
to insert rows from one table to another is to say:

INSERT tbl2 (...)
SELECT ...
FROM tbl1

Of course, this would mean that if any of the rows are erroneous, then
all rows inserted would be rolled back by the trigger on Table3. But this
can be handled in different ways. (But exactly how, it's difficult to
say as I don't know the business requirements.)

The reason you should insert all, and not run a cursor, is that performance
for a cursor can be disastrous. If we are talking less than < 100 rows, it's
may be not that big deal. If we are talking 10000 rows, it can mean a
difference in processing time of 30 minutes instead of 30 seconds.

> -- Table3_ITrig ----------
> SET NOCOUNT ON
>
> DECLARE @.STATUS int
> DECLARE @.DATOTID smalldatetime
> DECLARE @.RESULT int
>
> SELECT @.ORDRENR = (SELECT ORDRENR FROM INSERTED)
> SELECT @.STATUS = (SELECT STATUS FROM INSERTED)
> SELECT @.DATOTID = (SELECT DATO FROM INSERTED)
> SELECT @.RESULT = (SELECT RESULT FROM INSERTED)

This trigger is poorly implemented. A trigger fires once per statement,
and must be able to handle multi-row operations.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Sunday, March 25, 2012

Cursor for a result set returned by a stored proc

Hi all,

i got a question. I have a stored procedure sp1 calling another stored procedure sp2 using Exec sp2 cmd.

sp2 returns 2 rows in the result set.
Now I want to loop thru those 2 rows and get access to its column values
from sp1. How can this be acheived?

thanks for your help

omair-I came across this after reading your post:

One legitimate use for temp tables is to use them to pass recordsets from a nested stored procedure to a calling stored procedure. This is the only way to pass recordsets from one stored procedure to another. When you do this, follow the other tips on this Web site to maximize temp table performance. [6.5, 7.0, 2000] Updated 3-6-2006

Hope it helps.

Hannah

Tuesday, February 14, 2012

CSRSS.EXE and SP3

Hi,
I've had this same issue on two machines, one Win2k3 Server and a XP SP1
machine.
The problem is that after installing SP3 opening any SQL Gui tool (I think
it's only that as osql.exe don't cause this) including the SQL Service
Manager causes CSRSS.EXE to spike at 100% and basically render the machine
unusable. Closer inspection reveals that it's continually respawning
whichever application you opened.
I've seen this referenced [1] a while back but no real cause/solutions :S
Any help would greatly appreciated as it's quite urgent that I get a working
SQL server.
Thanks in advance,
Armand
[1]
http://www.derkeiler.com/Newsgroups/microsoft.public.sqlserver.security/2003-01/5353.htmlJust a quick update - osql.exe causes exactly the same effect.
"Armand du Plessis" wrote:
> Hi,
> I've had this same issue on two machines, one Win2k3 Server and a XP SP1
> machine.
> The problem is that after installing SP3 opening any SQL Gui tool (I think
> it's only that as osql.exe don't cause this) including the SQL Service
> Manager causes CSRSS.EXE to spike at 100% and basically render the machine
> unusable. Closer inspection reveals that it's continually respawning
> whichever application you opened.
> I've seen this referenced [1] a while back but no real cause/solutions :S
> Any help would greatly appreciated as it's quite urgent that I get a working
> SQL server.
> Thanks in advance,
> Armand
> [1]
> http://www.derkeiler.com/Newsgroups/microsoft.public.sqlserver.security/2003-01/5353.html

cscript execution problems after windows updates/ie7 install

after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
***********************
declare @.script varchar(8000)
set @.script = 'cscript.exe'
create table #output (
output varchar(8000)
)
insert into #output
exec master.dbo.xp_cmdshell @.script
select * from #output
drop table #output
***********************
returns NULL
Any ideas?
my error was slightly different and I tried all sorts of things from checking
the nessessary permissions for sql service accounts to proxy accounts etc. at
the end I ended up manually uninstalling one by one all microsoft patches I
installed that caused this and it fixed it!
check that the service account sql is running under has permission for the
following:
NTFS for C:\Program Files\Microsoft SQL Server\MSSQL
Read and write for registry keys at and under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
HKEY_LOCAL_MACHINE\System\CurrentControlset\Servic es\MSSQLServer
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Perflib
HKEY_LOCAL_MACHINE\Software\Clients\Mail
User Rights:
Act as Part of the Operating System = SeTcbPrivilege
Bypass Traverse Checking = SeChangeNotify
Lock Pages In Memory = SeLockMemory
Log on as a Batch Job = SeBatchLogonRight
Log on as a Service = SeServiceLogonRight
Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
good luck
"Laurence Neville" wrote:

> George - did you figure the answer out? I am having a very similar problem -
> I used to run a VBS file that lives on a remote server with cscript through
> XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread in
> this group:
> Access is Denied when accessing files on remote server thru XP_CMDSHELL (was
> working last week)
>
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
>
>

cscript execution problems after windows updates/ie7 install

after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
***********************
declare @.script varchar(8000)
set @.script = 'cscript.exe'
create table #output (
output varchar(8000)
)
insert into #output
exec master.dbo.xp_cmdshell @.script
select * from #output
drop table #output
***********************
returns NULL
Any ideas?George - did you figure the answer out? I am having a very similar problem -
I used to run a VBS file that lives on a remote server with cscript through
XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread in
this group:
Access is Denied when accessing files on remote server thru XP_CMDSHELL (was
working last week)
"George" <George@.discussions.microsoft.com> wrote in message
news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
> after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
> SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
> ***********************
> declare @.script varchar(8000)
> set @.script = 'cscript.exe'
> create table #output (
> output varchar(8000)
> )
> insert into #output
> exec master.dbo.xp_cmdshell @.script
> select * from #output
> drop table #output
> ***********************
> returns NULL
> Any ideas?|||my error was slightly different and I tried all sorts of things from checking
the nessessary permissions for sql service accounts to proxy accounts etc. at
the end I ended up manually uninstalling one by one all microsoft patches I
installed that caused this and it fixed it!
check that the service account sql is running under has permission for the
following:
NTFS for C:\Program Files\Microsoft SQL Server\MSSQL
Read and write for registry keys at and under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQLServer
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Perflib
HKEY_LOCAL_MACHINE\Software\Clients\Mail
User Rights:
Act as Part of the Operating System = SeTcbPrivilege
Bypass Traverse Checking = SeChangeNotify
Lock Pages In Memory = SeLockMemory
Log on as a Batch Job = SeBatchLogonRight
Log on as a Service = SeServiceLogonRight
Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
good luck
"Laurence Neville" wrote:
> George - did you figure the answer out? I am having a very similar problem -
> I used to run a VBS file that lives on a remote server with cscript through
> XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread in
> this group:
> Access is Denied when accessing files on remote server thru XP_CMDSHELL (was
> working last week)
>
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
> > after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
> > SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
> > ***********************
> > declare @.script varchar(8000)
> > set @.script = 'cscript.exe'
> > create table #output (
> > output varchar(8000)
> > )
> >
> > insert into #output
> > exec master.dbo.xp_cmdshell @.script
> >
> > select * from #output
> >
> > drop table #output
> > ***********************
> > returns NULL
> >
> > Any ideas?
>
>

cscript execution problems after windows updates/ie7 install

after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
***********************
declare @.script varchar(8000)
set @.script = 'cscript.exe'
create table #output (
output varchar(8000)
)
insert into #output
exec master.dbo.xp_cmdshell @.script
select * from #output
drop table #output
***********************
returns NULL
Any ideas?George - did you figure the answer out? I am having a very similar problem -
I used to run a VBS file that lives on a remote server with cscript through
XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread in
this group:
Access is Denied when accessing files on remote server thru XP_CMDSHELL (was
working last week)
"George" <George@.discussions.microsoft.com> wrote in message
news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
> after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
> SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
> ***********************
> declare @.script varchar(8000)
> set @.script = 'cscript.exe'
> create table #output (
> output varchar(8000)
> )
> insert into #output
> exec master.dbo.xp_cmdshell @.script
> select * from #output
> drop table #output
> ***********************
> returns NULL
> Any ideas?|||my error was slightly different and I tried all sorts of things from checkin
g
the nessessary permissions for sql service accounts to proxy accounts etc. a
t
the end I ended up manually uninstalling one by one all microsoft patches I
installed that caused this and it fixed it!
check that the service account sql is running under has permission for the
following:
NTFS for C:\Program Files\Microsoft SQL Server\MSSQL
Read and write for registry keys at and under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MS
SQLServer
HKEY_LOCAL_MACHINE\System\CurrentControl
set\Services\MSSQLServer
HKEY_LOCAL_MACHINE\Software\Microsoft\Wi
ndows NT\CurrentVersion\Perflib
HKEY_LOCAL_MACHINE\Software\Clients\Mail
User Rights:
Act as Part of the Operating System = SeTcbPrivilege
Bypass Traverse Checking = SeChangeNotify
Lock Pages In Memory = SeLockMemory
Log on as a Batch Job = SeBatchLogonRight
Log on as a Service = SeServiceLogonRight
Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
good luck
"Laurence Neville" wrote:

> George - did you figure the answer out? I am having a very similar problem
-
> I used to run a VBS file that lives on a remote server with cscript throug
h
> XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread
in
> this group:
> Access is Denied when accessing files on remote server thru XP_CMDSHELL (w
as
> working last week)
>
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
>
>