Problem with Agent on 2000 server - jobs hanging

I have a server that had SQL agent working on it, and running scheduled
jobs on set interval forever. Windows Server 2003, SQL 2000, most
current service packs on both.
I installed an instance of SQL 2005 on this server.
Now, when I look at Enterprise Manager & try to see what's happening
with the jobs, I see that the jobs are sitting out there & will kick off
when they are supposed to run, or I can kick them off manually.
However, now, they will run to certain steps in a job, and it will just
"hang". The jobs that hang are the ones that are running DTS jobs:
DTSRun /~Z0xC9C........
These particular jobs haven't been changed in months, and were running
fine until the install of SQL 2005 on this server.
Any idea what's happening or how to fix it?It may be a locking. You can really easy to detect in Current Activity
window, processes view, last two fields if you scroll to the right, 'blocked
by" and "blocking" columns.
"Blasting Cap" wrote:
> I have a server that had SQL agent working on it, and running scheduled
> jobs on set interval forever. Windows Server 2003, SQL 2000, most
> current service packs on both.
> I installed an instance of SQL 2005 on this server.
> Now, when I look at Enterprise Manager & try to see what's happening
> with the jobs, I see that the jobs are sitting out there & will kick off
> when they are supposed to run, or I can kick them off manually.
> However, now, they will run to certain steps in a job, and it will just
> "hang". The jobs that hang are the ones that are running DTS jobs:
> DTSRun /~Z0xC9C........
>
> These particular jobs haven't been changed in months, and were running
> fine until the install of SQL 2005 on this server.
> Any idea what's happening or how to fix it?
>

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

[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!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)

-- 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.
-- ************************************************** ***********
-- do Merrill SELECT (similiar to Sage except for view name)

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

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

-- ************************************************** ***********
-- 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 Error 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 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?