"Private" cursors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

"Private" cursors

Author

Topic

GustiX
Starting Member

28 Posts

Posted - 2011-05-25 : 05:17:28

Hi

I have a stored procedure which is using a cursor.
I wanted to execute this procedure simultaneously
with different parameters (scheduled jobs) but soon
found out that it is not possible because of the cursor...

Can I somehow define the cursor in the stored procedure
as "local" or "private"?

Cheers,
Agust

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-05-25 : 05:50:30

running 2 such jobs side by side with a cursor is technically possible.

the problem probably is the existence of the cursor in the first place...ie what it is doing is stopping you running same Effectively in parallel.

cursurs are evil....tell us what you are doing and we'll see if there is a simpler set-based way to do it...and then the main problem should go away.

GustiX
Starting Member

28 Posts

Posted - 2011-05-25 : 15:38:25

Here is the procedure I am working on. I simplified it a bit but the core is the same
Basically it is a script that pulls data from one DB to another and marks the data
on the remote side as "pulled" when finished.

CREATE PROCEDURE [dbo].[spTEST] (@LinkedServerName varchar(80))
AS

IF OBJECT_ID('MyLinkedServer') IS NOT NULL DROP SYNONYM MyLinkedServer
EXEC('CREATE SYNONYM MyLinkedServer FOR [' + @LinkedServerName + '].[DBName].[dbo].[TableName]')

DECLARE @TRNO uniqueidentifier
DECLARE @IDX int
DECLARE @LINKIDX int
DECLARE KDS_TRAN CURSOR FOR
SELECT TOP 1000
TRNO,
IDX,
LINKIDX,
FROM
MyLinkedServer
WHERE
PULLED = 0

OPEN KDS_TRAN
FETCH NEXT FROM KDS_TRAN INTO @TRNO,@IDX,@LINKIDX
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
--BEGIN TRAN T1
INSERT INTO HBK_KDS_TRAN (TRNO,IDX,LINKIDX) VALUES (@TRNO,@IDX,@LINKIDX)
UPDATE MyLinkedServer set PULLED = 1 WHERE TRNO = @TRNO AND IDX = @IDX AND LINKIDX = @LINKIDX
--COMMIT TRAN T1
END TRY
BEGIN CATCH
--ROLLBACK TRAN T1
END CATCH
FETCH NEXT FROM KDS_TRAN INTO @TRNO,@IDX,@LINKIDX
END

CLOSE KDS_TRAN
DEALLOCATE KDS_TRAN
GO

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-05-26 : 08:02:33

basic structure should be below (you may have to play with the syntax to get same working)....but when it does it'll blow the cursor performance out of the water.

begin transaction
insert into abc
select cola, colb, colc from mytable
where pulled = 0

update mytable
set pulled = 1
from
mytable
inner join "inserted"
on inserted.cola = mytable.cola
and inserted.colb = mytable.colb
and inserted.colc = mytable.colc
where mytable.pulled = 0
commit transaction

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-05-27 : 09:15:24

I agree its often better do without the cursor but these 2 may be options:

- Run in separate sessions
- use DECLARE CURSOR LOCAL [url]http://stackoverflow.com/questions/3524816/sql-server-stored-procedure-cursor-name-conflict[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources