My sample cursor

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:
— Create date:
— Description:
— =============================================
ALTER PROCEDURE [dbo].[COPY_MASTER]

@scenarioID int,
@NewScenarioName varchar(50)

AS
BEGIN

DECLARE @NewScenarioID int

DECLARE @CurrentPropertyID int
DECLARE @NewPropertyID int

DECLARE @CurrentAssetID int

DECLARE @NewAssetID int

DECLARE @CurrentHeadleaseID int
DECLARE @CurrentSubleaseID int

SET NOCOUNT ON;

— COPY SCENARIO
print ‘ – BEGINNING TO COPY SCENARIO – ‘ + Convert(varchar(20), @scenarioID)

EXEC COPY_Scenario @scenarioID, @NewScenarioName, @NewScenarioID OUTPUT
DECLARE Properties_Cursor CURSOR FOR

SELECT propertyID
FROM [Property]
WHERE ScenarioID = @scenarioID

OPEN Properties_Cursor

FETCH NEXT FROM Properties_Cursor
INTO @CurrentPropertyID

WHILE @@FETCH_STATUS = 0

BEGIN
print ‘=====================================================’
print ‘ — The current propertyID is – ‘ + Convert(varchar(20), @CurrentPropertyID)

EXEC COPY_Properties @scenarioID, @NewScenarioID, @CurrentPropertyID, @NewPropertyID OUTPUT

— GET ALL ASSETS TO LOOP THROUGH ————————
DECLARE Assets_Cursor CURSOR FOR
SELECT DISTINCT AssetID from [asset] WHERE propertyID = @CurrentPropertyID

OPEN Assets_Cursor

FETCH NEXT FROM Assets_Cursor
INTO @CurrentAssetID

WHILE @@FETCH_STATUS = 0

BEGIn
print ”
print ‘ — The current assetID is – ‘ + Convert(varchar(20), @CurrentAssetID)

EXEC COPY_Assets @NewPropertyID, @CurrentAssetID, @NewAssetID OUTPUT

EXEC COPY_Headleases @CurrentAssetID, @NewAssetID, @CurrentHeadleaseID OUTPUT

IF @CurrentHeadleaseID > 0
BEGIN
print ‘ —– The current HeadleaseID is – ‘ + Convert(varchar(20), @CurrentHeadleaseID)
END
ELSE
print ‘ —– NO HEADLEASE’

EXEC COPY_Subleases @CurrentAssetID, @NewAssetID, @CurrentSubleaseID OUTPUT

IF @CurrentSubleaseID > 0
BEGIN
print ‘ —– The current SubleaseID is – ‘ + Convert(varchar(20), @CurrentSubleaseID)
END
ELSE
print ‘ —– NO SUBLEASE’

FETCH NEXT FROM Assets_Cursor
INTO @CurrentAssetID
END

CLOSE Assets_Cursor
DEALLOCATE Assets_Cursor
———————————————————

FETCH NEXT FROM Properties_Cursor
INTO @CurrentPropertyID
END

CLOSE Properties_Cursor
DEALLOCATE Properties_Cursor

print ‘*******************************************************’
print ‘*******************************************************’
print ‘***** THE SCENARIO – ‘ + Convert(varchar(20), @scenarioID) + ‘ WAS COPIED SUCCESSFULLY ****’
print ‘*******************************************************’
print ‘*******************************************************’

END

Advertisements

Determining the correct w3wp process

Determining the correct w3wp process can be a bit tricky. To isolate the correct process:
– From the server machine, open a command prompt
– Navigate to the System32 directory and run the following script: iisapp.vbs. This script will list all the currently running w3wp.exe processes, listing their PID and associated application pool id. The application pool id is the only relation between the pid and your application.
– Use the PID to identify the correct w3wp.exe on the “Attach to Process” screen.