updating text datatype: Problem with a scheduled job

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],

-- ************************************************** ***********
-- 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!Apparently my post was too long. Posting code and error messages again.

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)

DELETE FROM Historical.dbo.ClearingPosition
WHERE
[ReportDate] = @.reportDate
AND [Source] = @.source

-- ************************************************** ***********
-- Populate cursor based on clearing firm.
-- ************************************************** ***********
-- do Merrill SELECT (similiar to Sage except for view name)

IF UPPER(@.clearingFirm) = 'SAGE'
DECLARE cPosition CURSOR FAST_FORWARD
FOR SELECT
[ReportDate], [Source], [RawRowId],

-- do Pax SELECT (similiar to Sage except for view name)

-- ************************************************** ***********
-- Process cusor and insert into historical table
-- ************************************************** ***********

IF @.securityType = 'Future' OR @.securityType = 'Option' OR
@.securityType = 'Future Option'
SET @.descMsg = @.descMsg + ', Expiry: ' +
COALESCE(CONVERT(VARCHAR(10), @.expiry, 101), 'NULL')

-- clean up
CLOSE cPosition
DEALLOCATE cPosition

-- return everything good
RETURN 0

Job Error Output
------
Job 'Morning Batch Raw Export' : Step 3, 'Export Sage Positions' : Began
Executing 2003-10-24 09:09:30

Manual Error 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.

(and so on for 51 times...)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Jason Callas (jaycallas@.hotmail.com) writes:
> 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.

So write it as:

INSERT tbl (keycol, ...)
SELECT keycol, ...
FROM src
WHERE NOT EXISTS (SELECT *
FROM tbl
WHERE tbl.keycol = src.keycol)

A SELECT ... WHERE EXISTS before that can be good to list the duplicates
if you like.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94202AE268E2Yazorman@.127.0.0.1>...
> So write it as:
> INSERT tbl (keycol, ...)
> SELECT keycol, ...
> FROM src
> WHERE NOT EXISTS (SELECT *
> FROM tbl
> WHERE tbl.keycol = src.keycol)
> A SELECT ... WHERE EXISTS before that can be good to list the duplicates
> if you like.

I thought about that but I have several problems with it.

1) First and foremost is that I need to log any row that does not get
inserted into historical table. That way I can go and manually deal
with those rows (find out what the conflict was, fix it, and then
insert them).

I guess I could do a COUNT(*) with a HAVING > 1 statement but that
would catch any conflicts from the current normalized view. I would
have do it twice - once to compare the view to the historical table
and once to compare it to itself. Then log any results I get.

2) That would only deal with FK violations but it not with any other
issues. As an example would be a column in the normalized view that is
null but should not be. I do my best to clean up the data in the
normalized view but I cannot control the data that is given to me. For
the historical table I did identify and mark those columns that cannot
have nulls.

3) This solution does not really deal with my underlying problem. Is
it a common (or maybe uncommon but something I need to watch out for)
issue that a particular stored procedure could not work (or at least
different results) in a scheduled job (SQLs scheduler) compare to
within a SQL Query window?

Wednesday, March 21, 2012

Problem with a scheduled job

No comments:

Post a Comment

updating text datatype

Blog Archive

About Me