I have a stored procedure that runs as a step in a scheduled job. For
some reason the job does not seem to finish when ran from the job but
does fine when run from a window in SQL Query.
I know the job is not working because the number of rows that are
inserted into the table (see code) is considerably less than the manual
runnning of it.
I have included the code for the stored procedure, the output from the
job, and the output from the manual run.
I know somebody will probably ask WHY I am using a cursor. We have no
control over the possibility of having a PK conflict since the data
comes from outside sources. If I do it as just a INSERT INTO..SELECT
than nothing goes in when I have a violation. As a business rule we
would rather have MOST of the data inserted into the historical tables
with a log of the ones that did not make it. We can then go back and
deal with the ones that did not go in.
Of course, if there is a better way I would love to hear it...
Number Rows
-----------
10456 vNormalizedClearingPosition_Sage
10407 ClearingPosition
51 Will cause PK violation
SQL Command
-----------
EXEC spExportToClearingPosition 'Sage'
Code
----
CREATE PROCEDURE spExportToClearingPosition (
@clearingFirm VARCHAR(10),
@reportDate DATETIME = NULL
)
AS
SET NOCOUNT ON
-- If report date is not specified use todays date.
SET @reportDate = COALESCE(@reportDate, CONVERT(VARCHAR(10), GetDate(),
101))
DECLARE
@err INT,
@errMsg VARCHAR(50),
@descMsg VARCHAR(150)
-- declare variables for holding values during cursor looping
DECLARE
@source VARCHAR(10),
@rawRowId INT,
@tradeDate DATETIME,
@symbol VARCHAR(15),
@identity VARCHAR(15),
@identitySource VARCHAR(10),
@exchange VARCHAR(5),
@account VARCHAR(10),
@name VARCHAR(75),
@securityType VARCHAR(15),
@position INT,
@closingPrice DECIMAL(18, 6),
@expiry DATETIME,
@optionStrikePrice DECIMAL(18, 6),
@optionSide VARCHAR(1),
@optionMultiplier INT,
@underlyingSymbol VARCHAR(15),
@underlyingIdentity VARCHAR(15),
@underlyingIdentitySource VARCHAR(10),
@underlyingName VARCHAR(75),
@underlyingClosingPrice DECIMAL(18, 6),
@underlyingDividendDate DATETIME,
@underlyingDividendPrice DECIMAL(18, 6)
-- ************************************************** ***********
-- Remove existing rows from historical table for specific
-- report date and just for specified clearing firm.
-- ************************************************** ***********
-- set source for deletion (will also check for valid clearing firm)
IF UPPER(@clearingFirm) = 'MERRILL'
SET @source = 'Merrill'
ELSE
IF UPPER(@clearingFirm) = 'SAGE'
SET @source = 'Sage'
ELSE
IF UPPER(@clearingFirm) = 'PAX'
SET @source = 'Pax'
ELSE
BEGIN
-- invalid clearing firm
RAISERROR('Invalid clearing firm "%s" was passed in.', 16, 1,
@clearingFirm)
RETURN -100
END
DELETE FROM Historical.dbo.ClearingPosition
WHERE
[ReportDate] = @reportDate
AND [Source] = @source
-- ************************************************** ***********
-- Populate cursor based on clearing firm.
-- ************************************************** ***********
IF UPPER(@clearingFirm) = 'MERRILL'
DECLARE cPosition CURSOR FAST_FORWARD
FOR SELECT
[ReportDate], [Source], [RawRowId],
[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePrice], [OptionSide], [OptionMultiplier],
[UnderlyingSymbol], [UnderlyingIdentity], [UnderlyingIdentitySource],
[UnderlyingName], [UnderlyingClosingPrice], [UnderlyingDividendDate],
[UnderlyingDividendPrice]
FROM
vNormalizedClearingPosition_Merrill
WHERE
[ReportDate] = @reportDate
ELSE
IF UPPER(@clearingFirm) = 'SAGE'
DECLARE cPosition CURSOR FAST_FORWARD
FOR SELECT
[ReportDate], [Source], [RawRowId],
[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePrice], [OptionSide], [OptionMultiplier],
[UnderlyingSymbol], [UnderlyingIdentity], [UnderlyingIdentitySource],
[UnderlyingName], [UnderlyingClosingPrice], [UnderlyingDividendDate],
[UnderlyingDividendPrice]
FROM
vNormalizedClearingPosition_Sage
WHERE
[ReportDate] = @reportDate
ELSE
IF UPPER(@clearingFirm) = 'PAX'
DECLARE cPosition CURSOR FAST_FORWARD
FOR SELECT
[ReportDate], [Source], [RawRowId],
[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePrice], [OptionSide], [OptionMultiplier],
[UnderlyingSymbol], [UnderlyingIdentity], [UnderlyingIdentitySource],
[UnderlyingName], [UnderlyingClosingPrice], [UnderlyingDividendDate],
[UnderlyingDividendPrice]
FROM
vNormalizedClearingPosition_Pax
WHERE
[ReportDate] = @reportDate
-- ************************************************** ***********
-- Process cusor and insert into historical table
-- ************************************************** ***********
-- open cursor and fetch first row
OPEN cPosition
FETCH cPosition INTO @reportDate, @source, @rawRowId,
@tradeDate, @symbol, @identity, @identitySource, @exchange,
@account, @name, @securityType, @position, @closingPrice,
@expiry, @optionStrikePrice, @optionSide, @optionMultiplier,
@underlyingSymbol, @underlyingIdentity, @underlyingIdentitySource,
@underlyingName, @underlyingClosingPrice, @underlyingDividendDate,
@underlyingDividendPrice
-- loop until no more rows
WHILE @@Fetch_Status = 0
BEGIN
-- insert row into normalized table
INSERT INTO Historical.dbo.ClearingPosition ( [ReportDate],
[Source], [RawRowId],
[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePrice], [OptionSide], [OptionMultiplier],
[UnderlyingSymbol], [UnderlyingIdentity],
[UnderlyingIdentitySource], [UnderlyingName], [UnderlyingClosingPrice],
[UnderlyingDividendDate], [UnderlyingDividendPrice]
)
VALUES ( @reportDate, @source, @rawRowId,
@tradeDate, @symbol, @identity, @identitySource, @exchange,
@account, @name, @securityType, @position, @closingPrice,
@expiry, @optionStrikePrice, @optionSide, @optionMultiplier,
@underlyingSymbol, @underlyingIdentity, @underlyingIdentitySource,
@underlyingName, @underlyingClosingPrice, @underlyingDividendDate,
@underlyingDividendPrice
)
-- check for error message
SET @err = @@Error
IF @err <> 0
BEGIN
-- create error message
IF @err = 2627
SET @errMsg = '2627 - PRIMARY KEY violation.'
ELSE
SET @errMsg = 'Unexpected error : ' + LTRIM(RTRIM(STR(@err)))
-- build description message
SET @descMsg = 'Source: ' + COALESCE(@source, 'NULL') + ', Symbol: '
+ COALESCE(@symbol, 'NULL') + ', Identity: ' + COALESCE(@identity,
'NULL') + ', Account: ' + COALESCE(@account, 'NULL') + ', Position: ' +
COALESCE(LTRIM(RTRIM(STR(@position))), 'NULL')
IF @securityType = 'Future' OR @securityType = 'Option' OR
@securityType = 'Future Option'
SET @descMsg = @descMsg + ', Expiry: ' +
COALESCE(CONVERT(VARCHAR(10), @expiry, 101), 'NULL')
IF @securityType = 'Future' OR @securityType = 'Option' OR
@securityType = 'Future Option'
SET @descMsg = @descMsg + ', Strike: ' +
COALESCE(LTRIM(RTRIM(STR(@optionStrikePrice))), 'NULL') + ', OptionSide:
' + COALESCE(@optionSide, 'NULL')
-- log error in exception table
INSERT INTO ExportException ([ReportDate], [ErrorMessage],
[RowDescription], [TableName], [RawRowId])
VALUES (@reportDate, @errMsg, @descMsg, 'Clearing.dbo.' +
@clearingFirm + 'Position', @rawRowId)
END
-- get next row
FETCH cPosition INTO @reportDate, @source, @rawRowId,
@tradeDate, @symbol, @identity, @identitySource, @exchange,
@account, @name, @securityType, @position, @closingPrice,
@expiry, @optionStrikePrice, @optionSide, @optionMultiplier,
@underlyingSymbol, @underlyingIdentity, @underlyingIdentitySource,
@underlyingName, @underlyingClosingPrice, @underlyingDividendDate,
@underlyingDividendPrice
END
-- clean up
CLOSE cPosition
DEALLOCATE cPosition
-- return everything good
RETURN 0
Job Output
----------
Job 'Morning Batch Raw Export' : Step 3, 'Export Sage Positions' : Began
Executing 2003-10-24 09:09:30
Msg 2627, Sev 14: Violation of PRIMARY KEY constraint
'PK_ClearingPosition'. Cannot insert duplicate key in object
'ClearingPosition'. [SQLSTATE 23000]
Msg 3621, Sev 14: The statement has been terminated. [SQLSTATE 01000]
Msg 0, Sev 0: Associated statement is not prepared [SQLSTATE HY007]
Msg 2627, Sev 14: Violation of PRIMARY KEY constraint
'PK_ClearingPosition'. Cannot insert duplicate key in object
'ClearingPosition'. [SQLSTATE 23000]
Msg 3621, Sev 14: The statement has been terminated. [SQLSTATE 01000]
Manual Output
-------------
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClearingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPosition'. Cannot insert
duplicate key in object 'ClearingPosition'.
The statement has been terminated.
and so on...
(about 50+ PRIMARY KEY violations)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!