cursor problem

Actually My proble is

DECLARE @.SNO INT
DECLARE CURS_FOR_SNO CURSOR FOR SELECT * FROM MACB where cntrl_no='DC000429' and isnull(listed,'')='U' AND ISNULL(SNO,'')='' ORDER BY SNO
OPEN CURS_FOR_SNO
FETCH NEXT FROM CURS_FOR_SNO
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.I=@.I + 1
SET @.SNO=@.X + @.I
UPDATE MACB SET SNO=@.SNO where CURRENT OF CURS_FOR_SNO
FETCH NEXT FROM CURS_FOR_SNO
END
CLOSE CURS_FOR_SNO
DEALLOCATE CURS_FOR_SNO

it gives read only column but i want to update that so plz, solve my problem

YOu will have to pout the UPDATE syntax after the Select statement, for more information and samples, look in the BOL.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||plz give any example qry for update syntax|||

Hi,

you have defined a "Order by" which declares the cursor as readonly. Leave the Orderby and put a FOR UPDATE after your cursor declaration:

DECLARE CURS_FOR_SNO CURSOR FOR SELECT * FROM MACB where cntrl_no='DC000429' and isnull(listed,'')='U' AND ISNULL(SNO,'')='' FOR UPDATE

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||You don't need to a cursor to perform these DML operations. You can do this with a single DML statement that uses a correlated sub-query for example to count the rows and assign the SNO value.|||Sure thats one thing I forgot to mention, set based statements are in most cases faster than cursor based ones. Cursors are only not avoidable in cases you can′t use a setbased command like executing a stored procedure for n-rows (like sending mails)

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de

cursor and update

Hi,

I have a table t1 (part_id int, gen_code int) part_id is a primary key.

I have a cursor on t1 for part_id , I take part_id
go thro different tables , do calculations and update gen_code for that part_id in t1 . This process is very slow and I see waittype 'LATCH_EX' and 'CXPACKET' all the time in process info .

do I have to declare cursor for update? how does it diff from read_only cursor.

please reply how do I make this process faster.

thanks,
RamIt would be helpful if you woul dexplain your process in more detail. Otherwise, it's even for a guru difficult to look into a crystal ball.|||Update cursor may even slow down the process by locking rows

Cursor already ...

Hi! Ive got a little problem with the trigger bellow:

CREATE TRIGGER GSVD_PARAMETRO
ON TSVD_PARAMETROS
FOR INSERT
AS
DECLARE @.cod_dado int,@.cod_grupo int, @.cod_plano int, @.empresa int, @.contrato int,
@.termo int, @.pagto char(2), @.grupo int, @.cota_grupo numeric, @.cod_per int
DECLARE inserts CURSOR FOR
SELECT COD_DADO, COD_GRUPO, COD_PLANO
FROM INSERTED
DECLARE empresas CURSOR FOR
SELECT T.COD_TERMO,T.TIP_PAGTO
FROM VSVD_EMPRESA E, TSVD_CONTRATO C, TSVD_TERMO T
WHERE C.COD_EMPRESA = E.COD_EMPRESA
AND T.COD_CONTRATO = C.COD_CONTRATO
AND DATEDIFF(MONTH,T.DAT_FIM_USO,GETDATE()) <= 12

OPEN inserts

FETCH NEXT FROM inserts INTO @.cod_dado, @.cod_grupo, @.cod_plano

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(
SELECT 1
FROM TSVD_PLANO PL
WHERE NOT EXISTS (
SELECT P.COD_PLANO
FROM TSVD_PARAMETROS P
WHERE P.COD_PLANO = PL.COD_PLANO
AND P.COD_DADO = @.cod_dado)
)
BEGIN

OPEN empresas
FETCH NEXT FROM empresas INTO @.termo, @.pagto
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.pagto = 'V'
BEGIN
SELECT DISTINCT @.grupo=G.COD_GRUPO,@.cota_grupo=G.QTD_COTA_GRUPO
FROM TSVD_GRUPO G, TSVD_PARAMETROS P, TSVD_TERMO T
WHERE T.COD_TERMO = @.termo
AND P.COD_GRUPO = G.COD_GRUPO
AND P.COD_PLANO = T.COD_PLANO
AND P.COD_DADO = @.cod_dado

SELECT @.cod_per = P.COD_PER
FROM TSVD_PERIODO P
WHERE P.COD_TERMO = @.termo

IF NOT EXISTS(
SELECT 1
FROM TSVD_SALDO S
WHERE S.COD_GRUPO = @.grupo
AND S.COD_PER = @.cod_per)
BEGIN
INSERT INTO TSVD_SALDO
(COD_GRUPO,COD_PER,QTD_COTA)
VALUES
(@.grupo,@.cod_per,(@.cota_grupo * 12))
END
END
ELSE
BEGIN
SELECT DISTINCT @.grupo=G.COD_GRUPO,@.cota_grupo=G.QTD_COTA_GRUPO
FROM TSVD_GRUPO G, TSVD_PARAMETROS P, TSVD_TERMO T
WHERE T.COD_TERMO = @.termo
AND P.COD_GRUPO = G.COD_GRUPO
AND P.COD_PLANO = T.COD_PLANO
AND P.COD_DADO = @.cod_dado

DECLARE periodos CURSOR FOR
SELECT P.COD_PER
FROM TSVD_PERIODO P
WHERE P.COD_TERMO = @.termo

OPEN periodos

FETCH NEXT FROM periodos INTO @.cod_per

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(
SELECT 1
FROM TSVD_SALDO S
WHERE S.COD_GRUPO = @.grupo
AND S.COD_PER = @.cod_per)
BEGIN
INSERT INTO TSVD_SALDO
(COD_GRUPO,COD_PER,QTD_COTA)
VALUES
(@.grupo,@.cod_per,@.cota_grupo)
END

FETCH NEXT FROM periodos INTO @.cod_per
END

CLOSE periodos

DEALLOCATE periodos

END

FETCH NEXT FROM empresas INTO @.termo,@.pagto
END
CLOSE empresas
DEALLOCATE empresas
END
FETCH NEXT FROM inserts INTO @.cod_dado, @.cod_grupo, @.cod_plano
END
CLOSE inserts
DEALLOCATE inserts

And its returning:

Server: Msg 16915, Level 16, State 1, Procedure GSVD_PARAMETRO, Line 25
A cursor with the name 'empresas' already exists.
The statement has been terminated.

I cant figure it out! Could someone please help?

Thank you all!A cursor...bad enough...In a TRIGGER?

Wow...you're in for a world of hurt...

If the trigger fires before another trigger firing you'll already have the cursor declared...

Also, if it failed it will leave the cursor open...I don't see any error handling...

I would rethink your strategy...|||How about those conditions in if statement are not "YES" .. where are you closing the cursor ? Like Brett mentioned , check errors !!|||Sorry guys. Im new in sqlserver (came from oracle), and things are really different here, also Im developing using a wordpad (thats a long story...). So I dont quite know how to handle errors within a trigger (also its hard to find documentation on microsofts library), but Ill answer as I can:
Its like this

OPEN INSERTS CURSOR -FOR MULTI-ROWS(SO I WAS TOLD...)
WHILE INSERTS CURSOR
IF -SOMETHING
DECLARE CURSOR
OPEN CURSOR
WHILE CURSOR
DO STUFF
END LOOP
CLOSE CURSOR
DEALLOCATE CURSOR - HERES WHERE IT SHOULD PREVENT
THE TRIGGER FINDING THE CURSOR
DECLARED IN THE NEXT TIME
(OR SO I WAS TOLD...)
END IF
END LOOP

Hope that helps.

Cursor

I have a table with the following data:
14794 3 Trond
14794 4 has
14794 5 new car
Here is the table:
CREATE TABLE [IText] (
[FKID] [int] NOT NULL ,
[SortIndex] [int] NOT NULL ,
[Content] [ntext] COLLATE Danish_Norwegian_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
I then want to create a stored procedure that takes a parameter and returns
the ID (FKID) and a concantinated string "Trond has a new car". This because
all 3 records has the same FKID (14794)
Here is the sproc so far:
CREATE PROCEDURE testSPROC @.ID int
AS
DECLARE @.Concantinated varchar(500)
DECLARE @.ConcantinatedFinal varchar(500)
DECLARE @.FKID int
DECLARE Content_cursor CURSOR for
SELECT FKID, CAST(Content as VARCHAR(500)) FROM IText WHERE FKID = @.ID
OPEN Content_cursor
Fetch next from Content_cursor
INTO @.FKID, @.Concantinated
WHILE @.@.FETCH_STATUS=0
BEGIN
SET @.ConcantinatedFinal = @.ConcantinatedFinal +' ' +@.Concantinated
Fetch next from Content_cursor
INTO @.FKID, @.Concantinated
END
CLOSE Content_cursor
deallocate Content_cursor
SELECT @.FKID as FKID, @.ConcantinatedFinal as ConString
GO
The thing is that ConcantinatedFinal is returning nothing (NULL). If i
change SET @.ConcantinatedFinal = @.ConcantinatedFinal +' ' +@.Concantinated to
SET @.ConcantinatedFinal = @.Concantinated it will return "new car"
I am so lost on this one. Can anyone see what i am doing wrong?
I am running this on a SQL 2000 server
best regards
TrondYou have to initialize the variable before using it in an operation (if not,
it is null).
...
DECLARE @.ConcantinatedFinal varchar(500)
DECLARE @.FKID int
set @.ConcantinatedFinal = ''
...
AMB
"Trond" wrote:

> I have a table with the following data:
> 14794 3 Trond
> 14794 4 has
> 14794 5 new car
> Here is the table:
> CREATE TABLE [IText] (
> [FKID] [int] NOT NULL ,
> [SortIndex] [int] NOT NULL ,
> [Content] [ntext] COLLATE Danish_Norwegian_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> END
> I then want to create a stored procedure that takes a parameter and return
s
> the ID (FKID) and a concantinated string "Trond has a new car". This becau
se
> all 3 records has the same FKID (14794)
>
> Here is the sproc so far:
> CREATE PROCEDURE testSPROC @.ID int
> AS
> DECLARE @.Concantinated varchar(500)
> DECLARE @.ConcantinatedFinal varchar(500)
> DECLARE @.FKID int
> DECLARE Content_cursor CURSOR for
> SELECT FKID, CAST(Content as VARCHAR(500)) FROM IText WHERE FKID = @.ID
> OPEN Content_cursor
> Fetch next from Content_cursor
> INTO @.FKID, @.Concantinated
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> SET @.ConcantinatedFinal = @.ConcantinatedFinal +' ' +@.Concantinated
> Fetch next from Content_cursor
> INTO @.FKID, @.Concantinated
> END
> CLOSE Content_cursor
> deallocate Content_cursor
> SELECT @.FKID as FKID, @.ConcantinatedFinal as ConString
> GO
>
> The thing is that ConcantinatedFinal is returning nothing (NULL). If i
> change SET @.ConcantinatedFinal = @.ConcantinatedFinal +' ' +@.Concantinated
to
> SET @.ConcantinatedFinal = @.Concantinated it will return "new car"
> I am so lost on this one. Can anyone see what i am doing wrong?
> I am running this on a SQL 2000 server
> best regards
> Trond
>
>|||Thank you. It works fine now thanks to you. Feel a lil stupid since i did
not see that one myself, but sometimes that just one of those things that
happends in a developers life :-)
Final SPROC:
----
--
CREATE PROCEDURE testSPROC @.ID int
AS
DECLARE @.Concantinated varchar(500)
DECLARE @.ConcantinatedFinal varchar(500)
DECLARE @.FKID int
set @.ConcantinatedFinal = ''
DECLARE Content_cursor CURSOR for
SELECT FKID, CAST(Content as VARCHAR(500)) FROM IText WHERE FKID = @.ID
OPEN Content_cursor
Fetch next from Content_cursor
INTO @.FKID, @.Concantinated
WHILE @.@.FETCH_STATUS=0
BEGIN
SET @.ConcantinatedFinal = @.ConcantinatedFinal +' ' +@.Concantinated
Fetch next from Content_cursor
INTO @.FKID, @.Concantinated
END
CLOSE Content_cursor
deallocate Content_cursor
SELECT @.FKID as FKID, RTRIM(LTRIM(@.ConcantinatedFinal)) as ConString
GO
----
--
Best regards
Trond
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:EFAD8EAC-69F4-4E8A-AB97-148A43798BAB@.microsoft.com...
> You have to initialize the variable before using it in an operation (if
not,
> it is null).
> ...
> DECLARE @.ConcantinatedFinal varchar(500)
> DECLARE @.FKID int
> set @.ConcantinatedFinal = ''
> ...
>
> AMB
> "Trond" wrote:
>
returns
because
+@.Concantinated to|||Do not feel bad, I have been there too.
AMB
"Trond" wrote:

> Thank you. It works fine now thanks to you. Feel a lil stupid since i did
> not see that one myself, but sometimes that just one of those things that
> happends in a developers life :-)
> Final SPROC:
> ----
--
> --
> CREATE PROCEDURE testSPROC @.ID int
> AS
> DECLARE @.Concantinated varchar(500)
> DECLARE @.ConcantinatedFinal varchar(500)
> DECLARE @.FKID int
> set @.ConcantinatedFinal = ''
>
> DECLARE Content_cursor CURSOR for
> SELECT FKID, CAST(Content as VARCHAR(500)) FROM IText WHERE FKID = @.ID
> OPEN Content_cursor
> Fetch next from Content_cursor
> INTO @.FKID, @.Concantinated
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> SET @.ConcantinatedFinal = @.ConcantinatedFinal +' ' +@.Concantinated
> Fetch next from Content_cursor
> INTO @.FKID, @.Concantinated
> END
> CLOSE Content_cursor
> deallocate Content_cursor
> SELECT @.FKID as FKID, RTRIM(LTRIM(@.ConcantinatedFinal)) as ConString
> GO
> ----
--
> --
> Best regards
> Trond
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:EFAD8EAC-69F4-4E8A-AB97-148A43798BAB@.microsoft.com...
> not,
> returns
> because
> +@.Concantinated to
>
>|||(oops, fast fingers in a web-based UI)
DECLARE Content_Cursor LOCAL FAST_FORWARD FOR SELECT ...
OPEN Content_Cursor
WHILE 1 = 1 BEGIN
FETCH NEXT FROM Content_Cursor INTO ...
IF @.@.FETCH_STATUS <> 0 BREAK
..
END

CTE and XQuery

I'ev been playing around with CTE's and trying to query a table that has the following structure

tbl Projects{
id (int),
Project (xml)
}

sample data within the project column is
id = 100
Project =
'<Projects>
<SubProjects>
<id>150</id>
<id>160</id>
<id>170</id>
</SubProjects>
</Projects>'

basically i'm trying to set up a recursive query to get all sub projects for a given project.. this is PART of a bigger query ..

WITH MySubProjects(SubProjects)
AS
(
SELECT TblProjects.*, P.SubProjectIDs.value('(.)[1]','int') AS SubProjects
FROM RB_Projects TblProjects
CROSS APPLY Project.nodes('/Projects/SubProjects/id') AS P(SubProjectIDs)
where id = 100
)

but i keep getting the following error
Incorrect syntax near ')'.

does CTE's not like xquery functions within it's expression?n/m

guess i gotta use the CTE after definine it...

"A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE

statement that
references some or all the CTE columns."
http://msdn2.microsoft.com/en-us/library/ms175972.aspx