473,738 Members | 4,831 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 vNormalizedClea ringPosition_Sa ge
10407 ClearingPositio n
51 Will cause PK violation

SQL Command
-----------
EXEC spExportToClear ingPosition 'Sage'

Code
----

CREATE PROCEDURE spExportToClear ingPosition (
@clearingFirm VARCHAR(10),
@reportDate DATETIME = NULL
)
AS
SET NOCOUNT ON

-- If report date is not specified use todays date.
SET @reportDate = COALESCE(@repor tDate, 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,
@optionStrikePr ice DECIMAL(18, 6),
@optionSide VARCHAR(1),
@optionMultipli er INT,
@underlyingSymb ol VARCHAR(15),
@underlyingIden tity VARCHAR(15),
@underlyingIden titySource VARCHAR(10),
@underlyingName VARCHAR(75),
@underlyingClos ingPrice DECIMAL(18, 6),
@underlyingDivi dendDate DATETIME,
@underlyingDivi dendPrice 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(@clearing Firm) = 'MERRILL'
SET @source = 'Merrill'
ELSE
IF UPPER(@clearing Firm) = 'SAGE'
SET @source = 'Sage'
ELSE
IF UPPER(@clearing Firm) = 'PAX'
SET @source = 'Pax'
ELSE
BEGIN
-- invalid clearing firm
RAISERROR('Inva lid clearing firm "%s" was passed in.', 16, 1,
@clearingFirm)
RETURN -100
END

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

-- *************** *************** *************** *************** *
-- Populate cursor based on clearing firm.
-- *************** *************** *************** *************** *
IF UPPER(@clearing Firm) = 'MERRILL'
DECLARE cPosition CURSOR FAST_FORWARD
FOR SELECT
[ReportDate], [Source], [RawRowId],

[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePri ce], [OptionSide], [OptionMultiplie r],
[UnderlyingSymbo l], [UnderlyingIdent ity], [UnderlyingIdent itySource],
[UnderlyingName], [UnderlyingClosi ngPrice], [UnderlyingDivid endDate],
[UnderlyingDivid endPrice]
FROM
vNormalizedClea ringPosition_Me rrill
WHERE
[ReportDate] = @reportDate
ELSE
IF UPPER(@clearing Firm) = 'SAGE'
DECLARE cPosition CURSOR FAST_FORWARD
FOR SELECT
[ReportDate], [Source], [RawRowId],

[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePri ce], [OptionSide], [OptionMultiplie r],
[UnderlyingSymbo l], [UnderlyingIdent ity], [UnderlyingIdent itySource],
[UnderlyingName], [UnderlyingClosi ngPrice], [UnderlyingDivid endDate],
[UnderlyingDivid endPrice]
FROM
vNormalizedClea ringPosition_Sa ge
WHERE
[ReportDate] = @reportDate
ELSE
IF UPPER(@clearing Firm) = 'PAX'
DECLARE cPosition CURSOR FAST_FORWARD
FOR SELECT
[ReportDate], [Source], [RawRowId],

[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePri ce], [OptionSide], [OptionMultiplie r],
[UnderlyingSymbo l], [UnderlyingIdent ity], [UnderlyingIdent itySource],
[UnderlyingName], [UnderlyingClosi ngPrice], [UnderlyingDivid endDate],
[UnderlyingDivid endPrice]
FROM
vNormalizedClea ringPosition_Pa x
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, @optionStrikePr ice, @optionSide, @optionMultipli er,
@underlyingSymb ol, @underlyingIden tity, @underlyingIden titySource,
@underlyingName , @underlyingClos ingPrice, @underlyingDivi dendDate,
@underlyingDivi dendPrice

-- loop until no more rows
WHILE @@Fetch_Status = 0
BEGIN
-- insert row into normalized table
INSERT INTO Historical.dbo. ClearingPositio n ( [ReportDate],
[Source], [RawRowId],
[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePri ce], [OptionSide], [OptionMultiplie r],
[UnderlyingSymbo l], [UnderlyingIdent ity],
[UnderlyingIdent itySource], [UnderlyingName], [UnderlyingClosi ngPrice],
[UnderlyingDivid endDate], [UnderlyingDivid endPrice]
)
VALUES ( @reportDate, @source, @rawRowId,
@tradeDate, @symbol, @identity, @identitySource , @exchange,
@account, @name, @securityType, @position, @closingPrice,
@expiry, @optionStrikePr ice, @optionSide, @optionMultipli er,
@underlyingSymb ol, @underlyingIden tity, @underlyingIden titySource,
@underlyingName , @underlyingClos ingPrice, @underlyingDivi dendDate,
@underlyingDivi dendPrice
)

-- 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(@sourc e, 'NULL') + ', Symbol: '
+ COALESCE(@symbo l, 'NULL') + ', Identity: ' + COALESCE(@ident ity,
'NULL') + ', Account: ' + COALESCE(@accou nt, 'NULL') + ', Position: ' +
COALESCE(LTRIM( RTRIM(STR(@posi tion))), 'NULL')

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

IF @securityType = 'Future' OR @securityType = 'Option' OR
@securityType = 'Future Option'
SET @descMsg = @descMsg + ', Strike: ' +
COALESCE(LTRIM( RTRIM(STR(@opti onStrikePrice)) ), 'NULL') + ', OptionSide:
' + COALESCE(@optio nSide, '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, @optionStrikePr ice, @optionSide, @optionMultipli er,
@underlyingSymb ol, @underlyingIden tity, @underlyingIden titySource,
@underlyingName , @underlyingClos ingPrice, @underlyingDivi dendDate,
@underlyingDivi dendPrice
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_ClearingPos ition'. Cannot insert duplicate key in object
'ClearingPositi on'. [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_ClearingPos ition'. Cannot insert duplicate key in object
'ClearingPositi on'. [SQLSTATE 23000]
Msg 3621, Sev 14: The statement has been terminated. [SQLSTATE 01000]

Manual Output
-------------

Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
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!
Jul 20 '05 #1
3 4369
Apparently my post was too long. Posting code and error messages again.

Code
------
CREATE PROCEDURE spExportToClear ingPosition (
@clearingFirm VARCHAR(10),
@reportDate DATETIME = NULL
)
AS
SET NOCOUNT ON

-- If report date is not specified use todays date.
SET @reportDate = COALESCE(@repor tDate, 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,
@optionStrikePr ice DECIMAL(18, 6),
@optionSide VARCHAR(1),
@optionMultipli er INT,
@underlyingSymb ol VARCHAR(15),
@underlyingIden tity VARCHAR(15),
@underlyingIden titySource VARCHAR(10),
@underlyingName VARCHAR(75),
@underlyingClos ingPrice DECIMAL(18, 6),
@underlyingDivi dendDate DATETIME,
@underlyingDivi dendPrice 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(@clearing Firm) = 'MERRILL'
SET @source = 'Merrill'
ELSE
IF UPPER(@clearing Firm) = 'SAGE'
SET @source = 'Sage'
ELSE
IF UPPER(@clearing Firm) = 'PAX'
SET @source = 'Pax'
ELSE
BEGIN
-- invalid clearing firm
RAISERROR('Inva lid clearing firm "%s" was passed in.', 16, 1,
@clearingFirm)
RETURN -100
END

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

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

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

[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePri ce], [OptionSide], [OptionMultiplie r],
[UnderlyingSymbo l], [UnderlyingIdent ity], [UnderlyingIdent itySource],
[UnderlyingName], [UnderlyingClosi ngPrice], [UnderlyingDivid endDate],
[UnderlyingDivid endPrice]
FROM
vNormalizedClea ringPosition_Sa ge
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, @optionStrikePr ice, @optionSide, @optionMultipli er,
@underlyingSymb ol, @underlyingIden tity, @underlyingIden titySource,
@underlyingName , @underlyingClos ingPrice, @underlyingDivi dendDate,
@underlyingDivi dendPrice

-- loop until no more rows
WHILE @@Fetch_Status = 0
BEGIN
-- insert row into normalized table
INSERT INTO Historical.dbo. ClearingPositio n ( [ReportDate],
[Source], [RawRowId],
[TradeDate], [Symbol], [Identity], [IdentitySource], [Exchange],
[Account], [Name], [SecurityType], [Position], [ClosingPrice],
[Expiry], [OptionStrikePri ce], [OptionSide], [OptionMultiplie r],
[UnderlyingSymbo l], [UnderlyingIdent ity],
[UnderlyingIdent itySource], [UnderlyingName], [UnderlyingClosi ngPrice],
[UnderlyingDivid endDate], [UnderlyingDivid endPrice]
)
VALUES ( @reportDate, @source, @rawRowId,
@tradeDate, @symbol, @identity, @identitySource , @exchange,
@account, @name, @securityType, @position, @closingPrice,
@expiry, @optionStrikePr ice, @optionSide, @optionMultipli er,
@underlyingSymb ol, @underlyingIden tity, @underlyingIden titySource,
@underlyingName , @underlyingClos ingPrice, @underlyingDivi dendDate,
@underlyingDivi dendPrice
)

-- 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(@sourc e, 'NULL') + ', Symbol: '
+ COALESCE(@symbo l, 'NULL') + ', Identity: ' + COALESCE(@ident ity,
'NULL') + ', Account: ' + COALESCE(@accou nt, 'NULL') + ', Position: ' +
COALESCE(LTRIM( RTRIM(STR(@posi tion))), 'NULL')

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

IF @securityType = 'Future' OR @securityType = 'Option' OR
@securityType = 'Future Option'
SET @descMsg = @descMsg + ', Strike: ' +
COALESCE(LTRIM( RTRIM(STR(@opti onStrikePrice)) ), 'NULL') + ', OptionSide:
' + COALESCE(@optio nSide, '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, @optionStrikePr ice, @optionSide, @optionMultipli er,
@underlyingSymb ol, @underlyingIden tity, @underlyingIden titySource,
@underlyingName , @underlyingClos ingPrice, @underlyingDivi dendDate,
@underlyingDivi dendPrice
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_ClearingPos ition'. Cannot insert duplicate key in object
'ClearingPositi on'. [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_ClearingPos ition'. Cannot insert duplicate key in object
'ClearingPositi on'. [SQLSTATE 23000]
Msg 3621, Sev 14: The statement has been terminated. [SQLSTATE 01000]

Manual Error Output
-------------------
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Procedure
spExportToClear ingPosition, Line 127
Violation of PRIMARY KEY constraint 'PK_ClearingPos ition'. Cannot insert
duplicate key in object 'ClearingPositi on'.
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!
Jul 20 '05 #2
Jason Callas (ja*******@hotm ail.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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* **************@ 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?
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
4775
by: Bart Simpson | last post by:
Hi, I want to control 'Scheduled Tasks' in Windows 2003 by python program. But I couldn't find any Python module about win32 'Scheduled Tasks'. I could find only Perl module about it. (see http://taskscheduler.sourceforge.net) But, I want to control 'Scheduled Tasks' by python :) Is there any python module about win32 'Scheduled Tasks'?
4
14697
by: Colin Steadman | last post by:
We have a number of scheduled tasks on our IIS server that run daily at some point during the early morning. These tasks run as a specific user that has the correct permissions to perform whatever task (processing SQL so I'm told) I have been asked if I can setup a page within ASP that a user could logon to with their own credentials (this I can do). And once logged on be presented with a list of scheduled tasks which they can then...
6
8250
by: John Bowman | last post by:
Hi, I have a C# app that needs to launch the "Add Scheduled Tasks" wizard found in the control panel "Scheduled Tasks" applet. I realize that this "applet" really just opens the tasks folder, but I need to launch the add tasks wizard inside the folder. Does anyone have any ideas of how to do this? I can't find anything in the MSDN regarding this. All it mentions is the Task Scheduler API and I can't seem to find it in there either. Did...
6
4594
by: David | last post by:
Hi all, In my code I have a bottleneck which consists of a cpu-intensive loop of size LENGTH. What I did was to divide that loop into two threads, Thread1 doing the 0 to LENGTH/2 part, and Thread2 doing the LENGTH/2 to LENGTH part. There is no synchronization between threads, so they should work pretty freely. My machine has two processors (dual P3, Windows 2003 server). Now comes the problem: when running the program, both threads...
1
1844
by: satelite | last post by:
Hello, I am writing an exe that is intended to be run via a scheduled task. However, I also need the flexibility to have users run the scheduled task manually (right click task and select run). In the event a user runs the task manually I need to verify their identity for logging purposes. So I have two questions: 1. Is there a way to tell if a scheduled task was run because of its scheduled time or if a user manually initiated it?...
8
574
by: Dan Pavel | last post by:
Hi, I did not used Mutex before and I need now. I have an application that control some workflows on different machines. I use SNMP for that. The problem is that the thread used for starting an workflow need to write in some OID. When I start 2 workflows on the same machine the second thread need to wait for the first one to finish and after that continue work. This works OK. The problem is when I have to start 2 workflows on different...
1
5123
by: sherifbk | last post by:
Problem description ============== - I have 4 clients and 1 server (SQL server) - 3 clients are Monitoring console 1 client is operation console - Monitoring console collects some data from the control unit and store them into the Sql server - The operation console then retrieve this data from the sql for reporting and statistics purposes - I am using ODBC connection - The problem is that the operation console is not able to...
4
1945
by: pokerboy801 | last post by:
OK, I will try to explain this as clearly and as concise as possible. I am using Access, which has three MS Excel Linked tables, to store call center metrics for reps. My Excel workbook has three separate worksheets, all linked to Access. I have a query that performs some minor calculations, not to hard, and will be ued to drive a report. Here is my problem, I only have about 14 rows of data in each table as we only have three reps. ...
0
1981
by: Paulson | last post by:
Dear Freinds I want to make a program that acts as a reminder for the users.I need to open up the Scheduled task wizard programmatically.If you type Tasks in the run command the Tasks folder(ie. Scheduled Taks folder) is opened,but what I want is to open the Add Scheduled Task wizard in it.Is there any run command to do that? Also if I am able to open the Scheduled Task wizard like this is there any way by which I can...
3
1946
by: kite54surfer | last post by:
Hi all, I apologize if not posting correctly, my first time. I am having problem with the field "Scheduled" (7901-D'Acunha-A-5) getting: " Syntax error (missing operator) in query expression "7901-D'Acunha-A-5', " , 'Done', 'Update', #4/6/2009 2:21:17 PM#)' I know is related to the apostrophe and I searched and found results on (single quotes), I tried to make changes myself, with no luck. I wasn't sure how much I should post....
0
8968
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8787
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9473
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9334
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9208
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8208
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6750
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4569
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3279
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.