Cursor is not open when executing stored procedure

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)

Cursor is not open when executing stored procedure

Author

Topic

ultimate_senthil
Starting Member

13 Posts

Posted - 2009-10-14 : 09:44:42

Dear All,

How to avoid Cursor is not open when executing stored procedure in SQL Server.

I have created a stored procedure to insert records using while loop it works fine. its inserts 50000 records and the time taken for this 11minutes in my pc.
The same procedure I'm executing in multiple system simultaneously
its throws an error like Cursor is not open.. My requirement is to allow multiple users to insert as many records that they want to create simultaneously. for your reference I have included my procedure below.

CREATE PROCEDURE [dbo].[Trackem_Sp_CreateBN]
(
@MN as nvarchar(30)=null,
@SD as nvarchar(20)=null,
@Ct as int=null,
@Wt as int=null,
@Qty as int =null
)
AS
BEGIN

DECLARE @IsMNExists int;
SET @IsMNExists = 0;

SET @IsMNExists=(SELECT COUNT(*) FROM dbo.Ept WHERE MN=@MN)

IF (@IsMNExists>0)
BEGIN

UPDATE Ept
SET [SD] = @SD,
[Ct] = @Ct,
[Wt] = @Wt,
Qty = @Qty
WHERE MN = @MN

END
ELSE
BEGIN
INSERT INTO dbo.Ept
(
MN,
SD,
C,
[Wt],
Qty
)
VALUES
(
@MN,
@SD,
@Ct,
@Wt,
@Qty
)
END

DECLARE @index int;
SET @index = 0;

Declare @dtCurr datetime
set @dtCurr = Getdate()

WHILE @index < @Qty
BEGIN
GETDATETIMEAGAIN:
BEGIN
set @dtCurr = DATEADD(ms,002,Convert(datetime,@dtCurr,121))
END
declare @nvCurr nvarchar(25)
set @nvCurr = (select CONVERT(varchar(23),@dtCurr,121))

Declare @BN nvarchar(17)=''

declare @nvAddMilli nvarchar(25)

set @nvAddMilli = (select CONVERT(varchar(23),DATEADD(ms,002,Convert(datetime,@nvCurr,121)),121))
set @nvAddMilli = (select Replace(@nvAddMilli,'-',''))
set @nvAddMilli = (select Replace(@nvAddMilli,':',''))
set @nvAddMilli = (select Replace(@nvAddMilli,'.',''))
SET @BN = (select Replace(@nvAddMilli,' ',''))

DECLARE @iBNCount int
SET @iBNCount=0

SET @iBNCount =(SELECT COUNT(*) FROM dbo.Items WHERE BN=@BN)

IF @iBNCount>0 GOTO GETDATETIMEAGAIN

PRINT(@BarcodeNo)

INSERT INTO dbo.Items
(
IB,
MN
)
VALUES
(
@BN,
@MN
)
SET @index = @index + 1;

set @dtCurr = DATEADD(ms,002,Convert(datetime,@dtCurr,121))
END

END

senthilkumar

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-14 : 09:58:29

Are you getting the error in SSMS? Or in the calling application, e.g. Visual Basic?

ultimate_senthil
Starting Member

13 Posts

Posted - 2009-10-14 : 10:14:28

In SQLSERVER i'm getting the error. I have not tried using C#.

senthilkumar

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-14 : 14:11:22

When executing that stored procedure you get an error about a cursor? Are you using a cursor somewhere else that we cannot see?

ultimate_senthil
Starting Member

13 Posts

Posted - 2009-10-14 : 23:03:42

Yes, When executing the stored procedure I get an error about a cursor.But I have not used cursor anywhere in my stored procedure.

senthilkumar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-15 : 02:28:02

The same procedure I'm executing in multiple system simultaneously

What do you mean by that?

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-15 : 12:59:31

I'm not sure what would cause SQL to complain about a cursor. Presumably, something to do with the WHILE loop. If you wanted I'm pretty sure you could use a set based solution and avoid looping altogether (and probably get some tremendous performance gains in the process).

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-15 : 14:03:22

Do either of these tables have triggers? Perhaps the cursor code is there.
Ept
Items

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-15 : 14:18:09

quote:
Originally posted by TG

Do either of these tables have triggers? Perhaps the cursor code is there.
Ept
Items

Be One with the Optimizer
TG

Good call. I tend to only use triggers to enforce temporal constraints and so I hardly ever use them in day to day work, thus that tasty nugget didn't pop into my head. That and 2 hours of sleep and caffeinated to the hilt probably doesn't help. ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources