Executing a SP driving me crazy

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 2000 Forums

SQL Server Development (2000)

Executing a SP driving me crazy

Author

Topic

adeturner
Starting Member

2 Posts

Posted - 2007-07-10 : 07:47:10

I have a stored procedure with a cursor within it. If I call the SP from ASP the sp does not run all rows for the cursor. If I use the exact same statement in QA it runs fine. ??? As a work around I have a job that checks the contents of a table and executes the string in one of the fields(this again works fine). I have been doing this kind of thing for years and never come across this before. Has anyone got any ideas ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-10 : 09:08:30

how about not using cursors??

is there an error you get?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-10 : 09:47:32

Also, can you post the SP code?

Madhivanan

Failing to plan is Planning to fail

adeturner
Starting Member

2 Posts

Posted - 2007-07-11 : 05:28:31

SQL 2000 latest SP
Using ADO
No Errors
I know there are other options than cursors, but the question is why does QA work fine and ASP does not I'm using the same exec... with paramaters in both methods ???

Here is the code, fairly basic...

CREATE PROCEDURE [dbo].[DesignBySolution] @WPID nvarchar(20),@PersonID nvarchar(20),@Blank tinyint,@Clone tinyint
AS

Declare @AssetID nvarchar(20),@AssetNo nvarchar(20),@Status bit,@NewID nvarchar(20),@NewAssetNo nvarchar(20),@LocPID nvarchar(20)
Declare @PID nvarchar(20),@R_PID nvarchar(20),@Approved bit,@Discontinued bit
Declare @LT62ID nvarchar(20),@ID nvarchar(20),@ContactID nvarchar(20),@Count int,@TempID nvarchar(20)

Set NoCount On

DECLARE mycursor CURSOR LOCAL FOR
-- return assets in the WP
Select AssetID from V0100 inner join assets on V0100.AssetID=Assets.ID where WorkPID=@WPID and Assets.Status=0 or Assets.Status=205
OPEN mycursor

FETCH NEXT FROM mycursor INTO @AssetID
WHILE @@FETCH_STATUS = 0
BEGIN

-- get details for existing asset
Select @LocPID=LocPID,@AssetNo=AssetNo,@Status=Status,@NewID=ReplacementAssetID,@NewAssetNo=NewAssetNo,@PID=ProductID,@R_PID=ReplaceWith,@Approved=Approved,@Discontinued=Discontinued from DesignBySolution1 DBS1 where DBS1.AssetID=@AssetID

-- is there a replacement required and none yet created
if isnull(@NewID,'')='' and isnull(@R_PID,'')<>'' Begin
-- create new Asset, set status to required
Select @NewID=dbo.UID2(getdate())
Set @NewAssetNo=@NewID
insert into Assets(ID,AssetNo,PID,Status) values (@NewID,@NewAssetNo,@R_PID,200)

-- create location association if it doesnt exist
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),50,Object1ID,@NewAssetNo from LT50 where Object2ID=@AssetNo

-- create WP association if it doesnt exist
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),10,Object1ID,@NewID from LT10 where Object2ID=@AssetID

--create link between assets
update assets set ReplacementAssetID=@NewID where ID=@AssetID
End

--cycle thru each existing user
DECLARE mycursor2 CURSOR LOCAL FOR
Select ID,Object2ID from LT62 where Object1ID=@Assetno
OPEN mycursor2
FETCH NEXT FROM mycursor2 INTO @ID,@ContactID
WHILE @@FETCH_STATUS = 0
BEGIN
-- create a LT62 for the new asset if it doesnt exist for each user of the existing asset
Set @LT62ID=''
Select @LT62ID from LT62 where Object1ID=@NewAssetNo and Object2ID=@ContactID
if isnull(@LT62ID,'')='' Begin
Set @LT62ID=dbo.uid2(getdate())
insert into Linktable(ID,Type,Object1ID,Object2ID) select @LT62ID,62,@NewAssetNo,@ContactID
End

-- firstly check if existing asset's user has a blank profile
--software
Set @Count=0
Select @Count=count(ID) from LT63 where Object1ID=@ID
if @Count>0 Begin
-- add any software where it does not already exist
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),63,@LT62ID,coalesce(Replacewith,ProductID) from Catalogue inner join LT63 on Catalogue.ProductID=LT63.Object2ID where LT63.Object1ID=@ID and Catalogue.Discontinued<>1 and not exists(select LT63_2.ID from LT63 LT63_2 where LT63_2.Object1ID=@LT62ID and (LT63_2.Object2ID=Catalogue.ProductID or LT63_2.Object2ID=Catalogue.Replacewith))
End
Else Begin
--blank profile. We need to check what options have been set
if @blank<>1 Begin
if @Clone=1 Begin
--clone any user from the same location
Select Top 1 @TempID=LT62.ID from LT62 inner join LT63 on LT62.ID=LT63.Object1ID
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),63,@LT62ID,coalesce(Replacewith,ProductID) from Catalogue inner join LT63 on Catalogue.ProductID=LT63.Object2ID where LT63.Object1ID=@TempID and Catalogue.Discontinued<>1 and not exists(select LT63_2.ID from LT63 LT63_2 where LT63_2.Object1ID=@LT62ID and (LT63_2.Object2ID=Catalogue.ProductID or LT63_2.Object2ID=Catalogue.Replacewith))
End
if isnull(@PersonID,'')<>'' Begin
-- clone a specified users profile
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),63,@LT62ID,coalesce(Replacewith,ProductID) from Catalogue inner join LT63 on Catalogue.ProductID=LT63.Object2ID where LT63.Object1ID=@PersonID and Catalogue.Discontinued<>1 and not exists(select LT63_2.ID from LT63 LT63_2 where LT63_2.Object1ID=@LT62ID and (LT63_2.Object2ID=Catalogue.ProductID or LT63_2.Object2ID=Catalogue.Replacewith))
End
End
End

--services
Set @Count=0
Select @Count=count(ID) from LT64 where Object1ID=@ID
if @Count>0 Begin
-- add any services where they do not already exist
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),64,@LT62ID,Object2ID from LT64 where LT64.Object1ID=@ID and not exists(select LT64_2.ID from LT64 LT64_2 where LT64_2.Object1ID=@LT62ID and LT64_2.Object2ID=LT64.Object2ID)
End
Else Begin
--blank profile. We need to check what options have been set
if @blank<>1 Begin
if @Clone=1 Begin
--clone a user from the same location
Select Top 1 @TempID=LT62.ID from LT62 inner join LT64 on LT62.ID=LT64.Object1ID
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),64,@LT62ID,Object2ID from LT64 where LT64.Object1ID=@TempID and not exists(select LT64_2.ID from LT64 LT64_2 where LT64_2.Object1ID=@LT62ID and LT64_2.Object2ID=LT64.Object2ID)
End
if isnull(@PersonID,'')<>'' Begin
-- clone a specified users profile
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),64,@LT62ID,Object2ID from LT64 where LT64.Object1ID=@PersonID and not exists(select LT64_2.ID from LT64 LT64_2 where LT64_2.Object1ID=@LT62ID and LT64_2.Object2ID=LT64.Object2ID)
End
End
End

FETCH NEXT FROM mycursor2 INTO @ID,@ContactID
END

CLOSE mycursor2
DEALLOCATE mycursor2

FETCH NEXT FROM mycursor INTO @AssetID
END

CLOSE mycursor
DEALLOCATE mycursor

GO

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2007-07-11 : 06:43:26

[code]
CREATE PROCEDURE [dbo].[DesignBySolution] @WPID nvarchar(20),@PersonID nvarchar(20),@Blank tinyint,@Clone tinyint
AS

Declare @AssetID nvarchar(20),@AssetNo nvarchar(20),@Status bit,@NewID nvarchar(20),@NewAssetNo nvarchar(20),@LocPID nvarchar(20)
Declare @PID nvarchar(20),@R_PID nvarchar(20),@Approved bit,@Discontinued bit
Declare @LT62ID nvarchar(20),@ID nvarchar(20),@ContactID nvarchar(20),@Count int,@TempID nvarchar(20)

Set NoCount On

DECLARE mycursor CURSOR LOCAL FOR
-- return assets in the WP
Select AssetID from V0100 inner join assets on V0100.AssetID=Assets.ID where WorkPID=@WPID and Assets.Status=0 or Assets.Status=205
OPEN mycursor

FETCH NEXT FROM mycursor INTO @AssetID
WHILE @@FETCH_STATUS = 0
BEGIN

-- get details for existing asset
Select @LocPID=LocPID,@AssetNo=AssetNo,@Status=Status,@NewID=ReplacementAssetID,@NewAssetNo=NewAssetNo,@PID=ProductID,@R_PID=ReplaceWith,@Approved=Approved,@Discontinued=Discontinued from DesignBySolution1 DBS1 where DBS1.AssetID=@AssetID

-- is there a replacement required and none yet created
if isnull(@NewID,'')='' and isnull(@R_PID,'')<>'' Begin
-- create new Asset, set status to required
Select @NewID=dbo.UID2(getdate())
Set @NewAssetNo=@NewID
insert into Assets(ID,AssetNo,PID,Status) values (@NewID,@NewAssetNo,@R_PID,200)

-- create location association if it doesnt exist
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),50,Object1ID,@NewAssetNo from LT50 where Object2ID=@AssetNo

-- create WP association if it doesnt exist
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),10,Object1ID,@NewID from LT10 where Object2ID=@AssetID

--create link between assets
update assets set ReplacementAssetID=@NewID where ID=@AssetID
End

--cycle thru each existing user
DECLARE mycursor2 CURSOR LOCAL FOR
Select ID,Object2ID from LT62 where Object1ID=@Assetno
OPEN mycursor2
FETCH NEXT FROM mycursor2 INTO @ID,@ContactID
WHILE @@FETCH_STATUS = 0
BEGIN
-- create a LT62 for the new asset if it doesnt exist for each user of the existing asset
Set @LT62ID=''
Select @LT62ID from LT62 where Object1ID=@NewAssetNo and Object2ID=@ContactID
if isnull(@LT62ID,'')='' Begin
Set @LT62ID=dbo.uid2(getdate())
insert into Linktable(ID,Type,Object1ID,Object2ID) select @LT62ID,62,@NewAssetNo,@ContactID
End

-- firstly check if existing asset's user has a blank profile
--software
Set @Count=0
Select @Count=count(ID) from LT63 where Object1ID=@ID
if @Count>0 Begin
-- add any software where it does not already exist
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),63,@LT62ID,coalesce(Replacewith,ProductID) from Catalogue inner join LT63 on Catalogue.ProductID=LT63.Object2ID where LT63.Object1ID=@ID and Catalogue.Discontinued<>1 and not exists(select LT63_2.ID from LT63 LT63_2 where LT63_2.Object1ID=@LT62ID and (LT63_2.Object2ID=Catalogue.ProductID or LT63_2.Object2ID=Catalogue.Replacewith))
End
Else Begin
--blank profile. We need to check what options have been set
if @blank<>1 Begin
if @Clone=1 Begin
--clone any user from the same location
Select Top 1 @TempID=LT62.ID from LT62 inner join LT63 on LT62.ID=LT63.Object1ID
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),63,@LT62ID,coalesce(Replacewith,ProductID) from Catalogue inner join LT63 on Catalogue.ProductID=LT63.Object2ID where LT63.Object1ID=@TempID and Catalogue.Discontinued<>1 and not exists(select LT63_2.ID from LT63 LT63_2 where LT63_2.Object1ID=@LT62ID and (LT63_2.Object2ID=Catalogue.ProductID or LT63_2.Object2ID=Catalogue.Replacewith))
End
if isnull(@PersonID,'')<>'' Begin
-- clone a specified users profile
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),63,@LT62ID,coalesce(Replacewith,ProductID) from Catalogue inner join LT63 on Catalogue.ProductID=LT63.Object2ID where LT63.Object1ID=@PersonID and Catalogue.Discontinued<>1 and not exists(select LT63_2.ID from LT63 LT63_2 where LT63_2.Object1ID=@LT62ID and (LT63_2.Object2ID=Catalogue.ProductID or LT63_2.Object2ID=Catalogue.Replacewith))
End
End
End

--services
Set @Count=0
Select @Count=count(ID) from LT64 where Object1ID=@ID
if @Count>0 Begin
-- add any services where they do not already exist
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),64,@LT62ID,Object2ID from LT64 where LT64.Object1ID=@ID and not exists(select LT64_2.ID from LT64 LT64_2 where LT64_2.Object1ID=@LT62ID and LT64_2.Object2ID=LT64.Object2ID)
End
Else Begin
--blank profile. We need to check what options have been set
if @blank<>1 Begin
if @Clone=1 Begin
--clone a user from the same location
Select Top 1 @TempID=LT62.ID from LT62 inner join LT64 on LT62.ID=LT64.Object1ID
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),64,@LT62ID,Object2ID from LT64 where LT64.Object1ID=@TempID and not exists(select LT64_2.ID from LT64 LT64_2 where LT64_2.Object1ID=@LT62ID and LT64_2.Object2ID=LT64.Object2ID)
End
if isnull(@PersonID,'')<>'' Begin
-- clone a specified users profile
insert into Linktable(ID,Type,Object1ID,Object2ID) select dbo.uid2(getdate()),64,@LT62ID,Object2ID from LT64 where LT64.Object1ID=@PersonID and not exists(select LT64_2.ID from LT64 LT64_2 where LT64_2.Object1ID=@LT62ID and LT64_2.Object2ID=LT64.Object2ID)
End
End
End

FETCH NEXT FROM mycursor2 INTO @ID,@ContactID
END

CLOSE mycursor2
DEALLOCATE mycursor2

FETCH NEXT FROM mycursor INTO @AssetID
END

CLOSE mycursor
DEALLOCATE mycursor

GO
[/code]

-------------
Charlie

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-11 : 10:35:41

I think I just went blind...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-11 : 11:09:38

that's what happenes when you get an acute case of cursoritis, brett.

better take a 'rita or two

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources