updating text datatype: job

Showing posts with label job. Show all posts

Wednesday, March 28, 2012

Problem with Backup

Hello!
I am starting to use SQL Server in my Company and I am having problem
with DataBase Backup.
The following error appears on the History Job.
The SQL Statement is:
BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
PcyMgntProd backup, NOSKIP , STATS = 10, DESCRIPTION = Daily
backup, NOFORMAT
The error is:
Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
(WrapperRead()). [SQLSTATE 01000] (Message 258) General network
error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
- - - - -
If anybody knows what is happen.
Thanks
Juliano Horta
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Probl...ict253160.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=874693
Hi
This may be the issue:
http://support.microsoft.com/default...b;en-us;827452
John
"jhnegrao" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_874693_3539bcc3020afe5f4788b11551220d0a@.dbf orumz.com...
> Hello!
> I am starting to use SQL Server in my Company and I am having problem
> with DataBase Backup.
> The following error appears on the History Job.
> The SQL Statement is:
> BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
> 'PcyMgntProd backup', NOSKIP , STATS = 10, DESCRIPTION = 'Daily
> backup', NOFORMAT
> The error is:
> Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
> (WrapperRead()). [SQLSTATE 01000] (Message 258) General network
> error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
> 10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
> - - - - -
> If anybody knows what is happen.
> Thanks
> Juliano Horta
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Server-Probl...ict253160.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=874693

Problem with Backup

Hello!
I am starting to use SQL Server in my Company and I am having problem
with DataBase Backup.
The following error appears on the History Job.
The SQL Statement is:
BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
PcyMgntProd backup, NOSKIP , STATS = 10, DESCRIPTION = Daily
backup, NOFORMAT
The error is:
Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
(WrapperRead()). [SQLSTATE 01000] (Message 258) General network
error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
- - - - -
If anybody knows what is happen.
Thanks
Juliano Horta
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Prob...pict253160.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=874693Hi
This may be the issue:
http://support.microsoft.com/defaul...kb;en-us;827452
John
"jhnegrao" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_874693_3539bcc3020afe5f4788b11551
220d0a@.dbforumz.com...
> Hello!
> I am starting to use SQL Server in my Company and I am having problem
> with DataBase Backup.
> The following error appears on the History Job.
> The SQL Statement is:
> BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME =
> 'PcyMgntProd backup', NOSKIP , STATS = 10, DESCRIPTION = 'Daily
> backup', NOFORMAT
> The error is:
> Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
> (WrapperRead()). [SQLSTATE 01000] (Message 258) General network
> error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
> 10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
> - - - - -
> If anybody knows what is happen.
> Thanks
> Juliano Horta
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Server-Prob...pict253160.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=874693

Problem with Backup

Hello!
I am starting to use SQL Server in my Company and I am having problem
with DataBase Backup.
The following error appears on the History Job.
The SQL Statement is:
BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME = ?PcyMgntProd backup?, NOSKIP , STATS = 10, DESCRIPTION = ?Daily
backup?, NOFORMAT
The error is:
Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
(WrapperRead()). [SQLSTATE 01000] (Message 258) General network
error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
- - - - -
If anybody knows what is happen.
Thanks
Juliano Horta
--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Server-Problem-Backup-ftopict253160.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=874693Hi
This may be the issue:
http://support.microsoft.com/default.aspx?scid=kb;en-us;827452
John
"jhnegrao" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_874693_3539bcc3020afe5f4788b11551220d0a@.dbforumz.com...
> Hello!
> I am starting to use SQL Server in my Company and I am having problem
> with DataBase Backup.
> The following error appears on the History Job.
> The SQL Statement is:
> BACKUP DATABASE [PcyMgntProd] TO PMIProdBkp WITH INIT, NAME => 'PcyMgntProd backup', NOSKIP , STATS = 10, DESCRIPTION = 'Daily
> backup', NOFORMAT
> The error is:
> Executed as user: NT AUTHORITY\SYSTEM. ConnectionRead
> (WrapperRead()). [SQLSTATE 01000] (Message 258) General network
> error. Check your network documentation. [SQLSTATE 08S01] (Error 11)
> 10 percent backed up. [SQLSTATE 01000] (Error 3211). The step failed.
> - - - - -
> If anybody knows what is happen.
> Thanks
> Juliano Horta
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Server-Problem-Backup-ftopict253160.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=874693

Friday, March 23, 2012

Problem with Accessing Unix Share Drive When a SSIS Job Runs

Hi I am trying to schedule a job to copy an MDB data file from Unix server to Windows 2003 server (Accfp1_data2_server). I have created a file copy SSIS package and tested it in the SSIS Visual Studio environment where it runs ok. The package was created while logged in as a domain administrator.

I then created a job to run this package (which is stored on a folder) using the credential of the same domain administrator who has full access privilege to both of these servers. However, the job fails whenever it is run manually or scheduled? The error message displayed is given below

Message
Executed as user: FORTIES\ABCITYG.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved. Started: 14:26:07 Error: 2007-09-13 14:26:12.56 Code: 0xC001401E
Source: CommunityContact - Copy MS Access Database Connection manager "CONTACT.mdb On Accfp1_data2_server"
Description: The file name "\\Accfp1_data2_server\DATA2\Arts&rec\Apps\Contacts\CONTACT.mdb" specified in the connection was not valid.
End Error Error: 2007-09-13 14:26:12.56 Code: 0xC001401D Source: CommunityContact - Copy MS Access Database Description: Connection "CONTACT.mdb On Accfp1_data2_server" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:26:07 Finished: 14:26:12 Elapsed: 5.297 seconds. The package execution failed. The step failed.

Please note that the job runs without problem when I change the source file to a Windows 2000 server share . How bizzare? Hope this is not a Microsoft's Trick?

Can anyone help?

Just to check, the FORTIES\ABCITYG account is the domain administrator that has access to this share?

|||

Thanks for pointing to this. Apology for my dyslexic reading of the error message. This account does not have access. I will ask our team to look into this.

I overlooked that the job was running under FORTIES\ABCITYG (local) account. It is weird because when I created the credential I had entered a different domain admin account but I noticed that the identiry has been automatically reverted to FORTIES\ABCITYG account. In fact I recreated the credential with domainserver\abcityg but the identity for this account is automatically refreshed with FORTIES\ABCITYG again and again. Any guess?

|||

Just to emphasise the fact that I am unable to create a new credential that uses other domain user. (I used the option menu Security/Credential) . And this appears to be the root of the problem.

I can select a user who is not a user in the current server (forties) but is a domain admin (<domainserver>\admin) from "select User or Group" window. But when I click on OK button the Identity field displays 'forties\admin'. How bizzare? I would have expected it to be '<domainserver>\admin'. In fact whenever I use any other <user>from the domain user list the Identity field is replaced by forties\<user>.

If this is not a bug then how on earth could you create a Domain Level Credential?

Any suggestion?|||

That is weird. I can't repro this problem.

Do you have permissions (BOL says "Requires ALTER ANY CREDENTIAL permission to create or modify a credential. Requires ALTER ANY LOGIN permission to map a login to a credential.")

Anyway, I'm not an expert on Agent Proxy account, please try this question in Tools forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1

|||

Yes, I do have full permission. As suggested by (Michael) I have added a new thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2160775&SiteID=1 , but it is not going anywhere.

By the way, the sql server and agent are running under LocalSystem account. Will this be the problem? Will reinstalling the SQL Server using a window domain user resolve the issue? Come on Microsoft, Please advise.

Problem with Accessing Unix Share Drive When a SSIS Job Runs

Please note that the job runs without problem when I change the source file to a Windows 2000 server share . How bizzare? Hope this is not a Microsoft's Trick?

Can anyone help?

Just to check, the FORTIES\ABCITYG account is the domain administrator that has access to this share?

|||

Thanks for pointing to this. Apology for my dyslexic reading of the error message. This account does not have access. I will ask our team to look into this.

|||

Just to emphasise the fact that I am unable to create a new credential that uses other domain user. (I used the option menu Security/Credential) . And this appears to be the root of the problem.

If this is not a bug then how on earth could you create a Domain Level Credential?

Any suggestion?|||

That is weird. I can't repro this problem.

Do you have permissions (BOL says "Requires ALTER ANY CREDENTIAL permission to create or modify a credential. Requires ALTER ANY LOGIN permission to map a login to a credential.")

Anyway, I'm not an expert on Agent Proxy account, please try this question in Tools forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1

|||

Yes, I do have full permission. As suggested by (Michael) I have added a new thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2160775&SiteID=1 , but it is not going anywhere.

Problem with Accessing Unix Share Drive When a SSIS Job Runs

Please note that the job runs without problem when I change the source file to a Windows 2000 server share . How bizzare? Hope this is not a Microsoft's Trick?

Can anyone help?

Just to check, the FORTIES\ABCITYG account is the domain administrator that has access to this share?

|||

Thanks for pointing to this. Apology for my dyslexic reading of the error message. This account does not have access. I will ask our team to look into this.

|||

Just to emphasise the fact that I am unable to create a new credential that uses other domain user. (I used the option menu Security/Credential) . And this appears to be the root of the problem.

If this is not a bug then how on earth could you create a Domain Level Credential?

Any suggestion?|||

That is weird. I can't repro this problem.

Do you have permissions (BOL says "Requires ALTER ANY CREDENTIAL permission to create or modify a credential. Requires ALTER ANY LOGIN permission to map a login to a credential.")

Anyway, I'm not an expert on Agent Proxy account, please try this question in Tools forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1

|||

Yes, I do have full permission. As suggested by (Michael) I have added a new thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2160775&SiteID=1 , but it is not going anywhere.

sql

Wednesday, March 21, 2012

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

Problem viewing the history of the job I ran manually

Hey guys. I've a job thta I ran manually and when i tried to look for the history, it doesn't show anything. How would I see why it failed. I also tried to look at the sysjobhistory table and nothing there. The thing is, if the agent runs the job, it shows it there but if I run it manually, it doesn't. I know that's not the case in SQL 2000.

Please advice, how to check that. Thank you.

Tej

As explicitly stated in book online,

"Contains information about the execution of scheduled jobs by SQL Server Agent. This table is stored in the msdb database."

So, all sql job execution is logged into sysjobhistory. How did you invoke the job manually? Do make sure you haven't cleared the history info.

If need to, run sql profiler and see if the job is started and if history is written to sysjobhistory table.|||

Hey oj,

What I meant by manual execution is that I right clicked the job and ran it manually. When I do that and go to the Job activity Monitor, it would go to Step 0, which I think is the preparation phase and before even going to step one it finishes. And it doesn't get logged. I've captured the SP:Completed,SP:Starting,StmtCompleted,SP:StmtStarting,SQL:BatchCompleted,SQL:BatchStarting.

I see a call so sp_help_jobstep sp and then it calls the sp_sqlagent_log_jobhistory to log the failure.

I dont see it actually trying to run the jobstep.It's still failing. I had to restart the agent. When I did that, it started logging stuff but the problem is that, this job calls a SSIS package to be executed. It fails saying 'The package execution failed'. When I run the package with my account, it runs fine. When I right click the job and run the package, it runs as SQL Server service account which is also a local admin on the box. And it fails. What would the next step be in order to figure out where the problem is?

Thank you

Tej

|||

I faced this type of issue with job history view. when we tried to view the job history, it runs some scripts in back ground(we can capture thru profile), there microsoft has defined servername variable as 30, but my server name has more than 30 charecters length. I fixed this issue by reducing the server name to 30 charecters.

my solution may help u if ur server name has more length than 30.

Thanks

kiran

|||Hi Tej,

Sorry for the late reply.

Look like you're having permission/security problem. Please check out the following for some guidance.

Sqlagent security:
http://msdn2.microsoft.com/en-us/library/ms190926.aspx

Creating sqlagent proxies:
http://msdn2.microsoft.com/en-us/library/ms189064.aspx

Problem viewing the history of the job I ran manually

Please advice, how to check that. Thank you.

Tej

As explicitly stated in book online,

"Contains information about the execution of scheduled jobs by SQL Server Agent.

This table is stored in the msdb database."

So, all sql job execution is logged into sysjobhistory. How did you invoke the job manually? Do make sure you haven't cleared the history info.

If need to, run sql profiler and see if the job is started and if history is written to sysjobhistory table.|||

Hey oj,

I see a call so sp_help_jobstep sp and then it calls the sp_sqlagent_log_jobhistory to log the failure.

Thank you

Tej

|||

my solution may help u if ur server name has more length than 30.

Thanks

kiran

|||Hi Tej,

Sorry for the late reply.

Look like you're having permission/security problem. Please check out the following for some guidance.

Sqlagent security:
http://msdn2.microsoft.com/en-us/library/ms190926.aspx

Creating sqlagent proxies:
http://msdn2.microsoft.com/en-us/library/ms189064.aspx

Problem viewing the history of the job I ran manually

Please advice, how to check that. Thank you.

Tej

As explicitly stated in book online,

"Contains information about the execution of scheduled jobs by SQL Server Agent.

This table is stored in the msdb database."

So, all sql job execution is logged into sysjobhistory. How did you invoke the job manually? Do make sure you haven't cleared the history info.

If need to, run sql profiler and see if the job is started and if history is written to sysjobhistory table.|||

Hey oj,

I see a call so sp_help_jobstep sp and then it calls the sp_sqlagent_log_jobhistory to log the failure.

Thank you

Tej

|||

my solution may help u if ur server name has more length than 30.

Thanks

kiran

|||Hi Tej,

Sorry for the late reply.

Look like you're having permission/security problem. Please check out the following for some guidance.

Sqlagent security:
http://msdn2.microsoft.com/en-us/library/ms190926.aspx

Creating sqlagent proxies:
http://msdn2.microsoft.com/en-us/library/ms189064.aspx

Wednesday, March 28, 2012

Problem with Backup

Problem with Backup

Problem with Backup

Friday, March 23, 2012

Problem with Accessing Unix Share Drive When a SSIS Job Runs

Problem with Accessing Unix Share Drive When a SSIS Job Runs

Problem with Accessing Unix Share Drive When a SSIS Job Runs

Wednesday, March 21, 2012

Problem with a scheduled job

Wednesday, March 7, 2012

Problem viewing the history of the job I ran manually

Problem viewing the history of the job I ran manually

Problem viewing the history of the job I ran manually

updating text datatype

Blog Archive

About Me