August 13, 2008 at 2:23 pm
I am beyond baffled. I was having issues with records not being inserted into the SpotsWarrants table, so I added some code to write some results to a debugging table. Well, I solved the original problem (badly formed transaction), but the way records are being written to the debugging table has me totally confused. This stored procedure is called from a master stored procedure.
ALTER PROCEDURE stp_SpotsExtractUpdatePermFiles
@OdysseyJobID INT,
@BOUser VARCHAR(50),
@SpotsExtractUpdatePermFilesReturn BIGINT OUTPUT
AS
SET NOCOUNT ON
SET @SpotsExtractUpdatePermFilesReturn = 0
INSERT INTO SpotsWarrants (
OdysseyJobID,
CaseID,
CaseNumber,
NodeID,
CourtName,
WarrantID,
WarrantNumber,
IssueDate,
WarrantStatusCode,
WarrantStatusDesc,
WarrantStatusDate,
ORINumber,
FullName,
GenderKy,
GenderDesc,
Address,
City,
State,
RaceKy,
RaceDesc,
DtDOB,
DtDOBFormatted,
HtInches,
Height,
WtLbs,
EyeKy,
EyeDesc,
HairKy,
HairDesc,
Statute,
Degree,
ChargeOffenseDesc,
BondAmount,
BondTypeCode,
BondTypeDesc,
WarrantTypeCode,
WarrantTypeDesc,
DateOfWarrant,
DateOfWarrantFormatted,
Miscellaneous,
CreatedBy )
SELECT
@OdysseyJobID AS OdysseyJobID,
w.CaseID AS CaseID,
REPLACE(w.CaseNumber, '.', '') AS CaseNumber,
w.NodeID AS NodeID,
w.CourtName AS CourtName ,
w.WarrantID AS WarrantID ,
w.WarrantNumber AS WarrantNumber,
w.IssueDate AS IssueDate,
w.WarrantStatusCode AS WarrantStatusCode,
w.WarrantStatusDesc AS WarrantStatusDesc,
w.WarrantStatusDate AS WarrantStatusDate,
REPLACE(w.ORINumber, '.', '') AS ORINumber,
OdysseyNH.dbo.fnSpotsInvalidCharacters(w.FullName, 'B')
AS FullName,
REPLACE(w.GenderKy, '.', '') AS GenderKy,
w.GenderDesc AS GenderDesc,
LEFT(REPLACE(w.Address, '.', ''), 23) AS Address,
LEFT(REPLACE(w.City, '.', ''), 15) AS City,
REPLACE(w.State, '.', '') AS State,
REPLACE(w.RaceKy, '.', '') AS RaceKy,
w.RaceDesc AS RaceDesc,
w.DtDOB AS DtDOB,
w.DtDOBFormatted AS DtDOBFormatted,
w.HtInches AS HtInches,
w.Height AS Height,
w.WtLbs AS WtLbs,
REPLACE(w.EyeKy, '.', '') AS EyeKy,
w.EyeDesc AS EyeDesc,
REPLACE(w.HairKy, '.', '') AS HairKy,
w.HairDesc AS HairDesc,
REPLACE(w.Statute, '.', '') AS Statute,
w.Degree AS Degree,
w.ChargeOffenseDesc AS ChargeOffenseDesc,
w.BondAmount AS BondAmount,
REPLACE(w.BondTypeCode, '.', '') AS BondTypeCode,
w.BondTypeDesc AS BondTypeDesc,
REPLACE(w.WarrantTypeCode, '.', '') AS WarrantTypeCode,
w.WarrantTypeDesc AS WarrantTypeDesc,
w.DateOfWarrant AS DateOfWarrant,
w.DateOfWarrantFormatted AS DateOfWarrantFormatted,
REPLACE(w.Miscellaneous, '.', '') AS Miscellaneous,
@BOUser AS CreatedBy
FROM #SpotsReportData w
WHERE RecordType = 1
AND ErrorCount = 0
SET @SpotsExtractUpdatePermFilesReturn = @@ROWCOUNT
--debugging
INSERT INTO SpotsWarrantsErrors (
OdysseyJobID,
RunBy,
UnsuccessfulProc,
ErrorCode )
VALUES (
@OdysseyJobID,
@BOUser,
'update',
@SpotsExtractUpdatePermFilesReturn )
--debugging
All I'm trying to do is write a debugging record that shows some parameters and a record count. The problem is, sometimes the code causes multiple records to be written to the SpotsWarrantsErrors table. Here's the output from today's runs:
[UID] [OdysseyJobID] [RunBy] [RunDate] [UnsuccessfulProc] [ErrorCode]
34 120900 msmith 2008-08-13 10:52:49.273 update 2
35 120900 msmith 2008-08-13 10:52:49.663 update 2
36 120905 msmith 2008-08-13 11:06:35.593 update 9
37 120901 msmith 2008-08-13 11:07:03.417 update 2
38 120901 msmith 2008-08-13 11:07:03.853 update 2
39 120952 msmith 2008-08-13 14:21:03.920 update 5
40 120940 msmith 2008-08-13 14:21:35.430 update 2
41 120940 msmith 2008-08-13 14:21:35.837 update 2
42 120979 msmith 2008-08-13 14:43:02.040 update 1
43 120979 msmith 2008-08-13 14:43:02.383 update 1
44 120953 msmith 2008-08-13 14:43:41.223 update 6
45 120973 msmith 2008-08-13 14:44:19.643 update 1
46 120973 msmith 2008-08-13 14:44:19.987 update 1
47 121048 msmith 2008-08-13 15:50:40.217 update 4
48 121068 msmith 2008-08-13 15:55:26.427 update 2
49 121068 msmith 2008-08-13 15:55:26.817 update 2
If you look closely, you'll see that when I'm writing more than a couple of records to the table, the debugging code only inserts one record into the SpotsWarrantsErrors table. But when there's only one or two records, I get two debugging records. I've recompiled the stored procedure, and it makes no difference. Since I've solved my original problem, I should probably just put this aside and declare success, but I'd feel a lot more comfortable about this if I knew what might be causing this.
Here's what the SpotsWarrantsErrors table looks like:
CREATE TABLE [SpotsWarrantsErrors] (
[UID] [int] IDENTITY (1, 1) NOT NULL ,
[OdysseyJobID] [int] NULL ,
[RunBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RunDate] [datetime] NULL CONSTRAINT [DF_SpotsWarrantsErrors_RunDate] DEFAULT (getdate()),
[UnsuccessfulProc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ErrorCode] [int] NULL ,
CONSTRAINT [PK_SpotsWarrantsErrors] PRIMARY KEY CLUSTERED
(
[UID]
) ON [PRIMARY]
) ON [PRIMARY]
Any insight into what might be causing this would be greatly appreciated.
Thanks,
Mattie
August 14, 2008 at 6:45 am
Hi Mattie,
From the code you've posted, each execution of stp_SpotsExtractUpdatePermFiles will only perfom one insert into SpotsWarrantsErrors.
Are you sure the stp sproc isn't being executed more than once and that you haven't got a trigger configured on SpotsWarrants table?
Adam
August 14, 2008 at 8:06 am
Adam,
Thanks for responding. No triggers anywhere; not in SpotsWarrants, not in SpotsWarrantsErrors. I've posted the create statements for both tables below. And my first thought was 'I must be calling the SP twice', but I'd be getting a duplicate key error, and I don't. I've scripted out all the stored procedures in the database and there's only one execution of stp_SpotsExtractUpdatePermFiles.
In continuing my debugging, I decided to look at the CreatedOn date of the SpotsWarrants records. When an extra SpotsWarrantsErrors record is created, the first record actually has a getdate() value prior to the getdate() value on the last SpotsWarrants record inserted. I even put a WAITFOR 10 seconds command between the SpotsWarrants insert and the SpotsWarrantsErrors insert to make sure I had a getdate-significant interval. Here's what's happened so far today: one run had two SpotsWarrants records inserted, and one run had nine. The run with the two SpotsWarrants records had an extra SpotsWarrantsErrors record. The sequence was
1. First SpotsWarrantsErrors record created at 2008-08-14 09:21:31.427
2. First SpotsWarrants record created at 2008-08-14 09:21:31.803
3. Second SpotsWarrants record created at 2008-08-14 09:21:31.803
4. Second SpotsWarrantsErrors record created at 2008-08-14 09:21:41.807 (this time reflects the 10 second delay I added)
The run that did not create an extra SpotsWarrantsErrors record had a date of 2008-08-14 09:28:22.993, and its last SpotsWarrants record created at 2008-08-14 09:28:12.977.
Because I had additional steps in the stored procedure at one point that included inserting into SpotsWarrantsErrors, I decided to just drop the sp and create it again. If that doesn't fix this, I am completely out of solutions.
Thanks for taking a look at this.
Mattie
CREATE TABLE [dbo].[SpotsWarrantsErrors] (
[UID] [int] IDENTITY (1, 1) NOT NULL ,
[OdysseyJobID] [int] NULL ,
[RunBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RunDate] [datetime] NULL ,
[UnsuccessfulProc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ErrorCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SpotsWarrantsErrors] WITH NOCHECK ADD
CONSTRAINT [PK_SpotsWarrantsErrors] PRIMARY KEY CLUSTERED
(
[UID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SpotsWarrantsErrors] ADD
CONSTRAINT [DF_SpotsWarrantsErrors_RunDate] DEFAULT (getdate()) FOR [RunDate]
GO
CREATE TABLE [dbo].[SpotsWarrants] (
[OdysseyJobID] [int] NOT NULL ,
[CaseID] [int] NULL ,
[CaseNumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NodeID] [int] NULL ,
[CourtName] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WarrantID] [int] NOT NULL ,
[WarrantNumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IssueDate] [datetime] NULL ,
[WarrantStatusCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WarrantStatusDesc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WarrantStatusDate] [datetime] NULL ,
[ORINumber] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullName] [varchar] (164) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GenderKy] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GenderDesc] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [varchar] (23) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RaceKy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RaceDesc] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DtDOB] [datetime] NULL ,
[DtDOBFormatted] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HtInches] [smallint] NULL ,
[Height] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WtLbs] [smallint] NULL ,
[EyeKy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EyeDesc] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HairKy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HairDesc] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Statute] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Degree] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ChargeOffenseDesc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BondAmount] [int] NULL ,
[BondTypeCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BondTypeDesc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WarrantTypeCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WarrantTypeDesc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOfWarrant] [datetime] NULL ,
[DateOfWarrantFormatted] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Miscellaneous] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedOn] [datetime] NOT NULL ,
[CreatedBy] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TransmittedOn] [datetime] NULL ,
[TransmittedBy] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SpotsWarrants] WITH NOCHECK ADD
CONSTRAINT [PK_SpotsWarrants] PRIMARY KEY CLUSTERED
(
[OdysseyJobID],
[WarrantID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SpotsWarrants] ADD
CONSTRAINT [DF_SpotsWarrants_CreatedOn] DEFAULT (getdate()) FOR [CreatedOn],
CONSTRAINT [DF_SpotsWarrants_CreatedBy] DEFAULT (host_name()) FOR [CreatedBy]
GO
CREATE INDEX [IX_SpotsWarrants] ON [dbo].[SpotsWarrants]([OdysseyJobID]) ON [PRIMARY]
GO
August 14, 2008 at 8:27 am
I would suggest checking your master procedure.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 14, 2008 at 10:25 am
Barry,
Thanks for responding. I have looked at that sp a dozen times, there's nothing in there. I even dropped and recreated it. I've attached the code for it just in case another set of eyes helps. But before you do, you should know that I removed the debugging insert into SpotsWarrantsErrors from stp_SpotsExtractUpdatePermFiles, and now NO records are being inserted into the table. So the only place that insert was happening was in stp_SpotsExtractUpdatePermFiles.
Don't spend a lot of time on this. The debugging table helped me resolve the original problem, I don't need it anymore, and I've wasted enough time on this already. Obviously the only solution is to take up martial arts (see today's newsletter editorial). I just wish I knew what was causing this.
Thanks again to both of you for taking a look at this.
Mattie
ALTER PROCEDURE stp_SpotsExtractMain
@DateStart DATETIME,
@DateEnd DATETIME,
@CourtCode INT,
@OdysseyJobID INT,
@BOUser VARCHAR(50)
AS
SET NOCOUNT ON
DECLARE @SpotsExtractUpdatePermFilesReturn BIGINT
CREATE TABLE #SpotsWarrants (
CaseID INT,
CaseCategoryKey VARCHAR(10),
CaseTypeID INT,
CaseTypeDesc VARCHAR(60),
CaseCategoryDesc VARCHAR(60),
WarrantID INT ,
IssueDate DATETIME,
WarrantNumber VARCHAR(30),
NodeID INT,
WarrantStatusCode VARCHAR(20),
WarrantStatusDesc VARCHAR(100),
WarrantStatusDate DATETIME,
CourtName VARCHAR(35),
ORINumber CHAR(9),
DefendantID INT,
FullName VARCHAR(164),
GenderKy CHAR(2),
GenderDesc VARCHAR(60),
AddrIDHmCur INT,
AddressID INT,
Address VARCHAR(120),
City VARCHAR(40),
State CHAR(2),
RaceKy VARCHAR(10),
RaceDesc VARCHAR(60),
DOBIDCur INT,
DtDOB DATETIME,
PartyExtPartyID INT ,
HtInches SMALLINT,
Height VARCHAR(4),
WtLbs SMALLINT ,
EyeKy CHAR(3),
EyeDesc VARCHAR(60),
HairKy CHAR(3),
HairDesc VARCHAR(60),
OffenseHistoryID INT,
Statute VARCHAR(40),
DegreeCodeID INT,
Degree VARCHAR(20),
DegreeDesc VARCHAR(100),
ChargeOffenseDesc VARCHAR(100),
CaseNumber VARCHAR(30),
BondAmount INT,
BondTypeCodeID INT,
BondTypeCode VARCHAR(20),
BondTypeDesc VARCHAR(100),
WarrantTypeCodeID INT,
WarrantTypeCode VARCHAR(20),
WarrantTypeDesc VARCHAR(100),
DateOfWarrant DATETIME,
OdysseyJobID INT )
CREATE TABLE #SpotsExceptions (
WarrantID INT,
WarrantNumber VARCHAR(30),
CaseID INT,
CaseNumber VARCHAR(30),
NodeID INT,
ExceptionNumber INT,
ExceptionType CHAR(1),
ExceptionMessage VARCHAR(300))
CREATE TABLE #SpotsWarrantExceptionCounts (
WarrantID INT,
WarrantExceptionCount INT,
WarrantErrorCount INT,
WarrantWarningCount INT)
CREATE TABLE #SpotsWarrantErrorCounts (
WarrantID INT,
WarrantErrorCount INT)
CREATE TABLE #SpotsWarrantWarningCounts (
WarrantID INT,
WarrantWarningCount INT)
CREATE TABLE #SpotsReportData (
--common to all records
RecordType SMALLINT ,
WarrantID INT ,
WarrantNumber VARCHAR(30),
CaseID INT ,
CaseNumber VARCHAR(30) ,
NodeID INT ,
CourtName VARCHAR(35) ,
ExceptionCount INT ,
ErrorCount INT ,
WarningCount INT ,
DateStart DATETIME ,
DateEnd DATETIME ,
OdysseyJobID INT ,
--warrant data
CaseCategoryKey VARCHAR(10) ,
CaseTypeID INT ,
CaseTypeDesc VARCHAR(60) ,
CaseCategoryDesc VARCHAR(60) ,
IssueDate DATETIME ,
WarrantStatusCode VARCHAR(20) ,
WarrantStatusDesc VARCHAR(100),
WarrantStatusDate DATETIME ,
ORINumber CHAR(9) ,
DefendantID INT ,
FullName VARCHAR(164),
GenderKy CHAR(2) ,
GenderDesc VARCHAR(60) ,
AddrIDHmCur INT ,
AddressID INT ,
Address VARCHAR(120),
City VARCHAR(40) ,
State CHAR(2) ,
RaceKy VARCHAR(10) ,
RaceDesc VARCHAR(60) ,
DOBIDCur INT ,
DtDOB DATETIME ,
PartyExtPartyID INT ,
HtInches SMALLINT ,
Height VARCHAR(4) ,
WtLbs SMALLINT ,
EyeKy CHAR(3) ,
EyeDesc VARCHAR(60) ,
HairKy CHAR(3) ,
HairDesc VARCHAR(60) ,
OffenseHistoryID INT ,
Statute VARCHAR(40) ,
DegreeCodeID INT ,
Degree VARCHAR(20) ,
DegreeDesc VARCHAR(100),
ChargeOffenseDesc VARCHAR(100),
BondAmount INT ,
BondTypeCodeID INT ,
BondTypeCode VARCHAR(20) ,
BondTypeDesc VARCHAR(100),
WarrantTypeCodeID INT ,
WarrantTypeCode VARCHAR(20) ,
WarrantTypeDesc VARCHAR(100),
DateOfWarrant DATETIME ,
DtDOBFormatted VARCHAR(8) ,
DateOfWarrantFormatted VARCHAR(8) ,
Miscellaneous VARCHAR(150),
--exception data
ExceptionNumber INT ,
ExceptionType CHAR(1) ,
ExceptionMessage VARCHAR(300))
IF @DateStart IS NULL
BEGIN
SET @DateStart = CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS DATETIME)
END
IF @DateEnd IS NULL
BEGIN
SET @DateEnd = CAST(CONVERT(VARCHAR(10), @DateStart, 101) AS DATETIME)
END
--!!Remove records for this OdysseyJobID
DELETE
FROM SpotsWarrants
WHERE OdysseyJobID = @OdysseyJobID
--!!Build #SpotsWarrants
EXEC stp_SpotsExtractBuildTempFiles
@DateStart = @DateStart,
@DateEnd = @DateEnd,
@OdysseyJobID = @OdysseyJobID,
@CourtCode = @CourtCode,
@BOUser = @BOUser
--!!Build #SpotsExceptions, #SpotsWarrantExceptionCounts
EXEC stp_SpotsExtractIdentifyExceptions
--!!Build #SpotsReportData
EXEC stp_SpotsExtractBuildReportRecordset
@DateStart = @DateStart,
@DateEnd = @DateEnd,
@OdysseyJobID = @OdysseyJobID
--!!Update perm table SpotsWarrants
EXEC stp_SpotsExtractUpdatePermFiles @OdysseyJobID = @OdysseyJobID,
@BOUser = @BOUser,
@SpotsExtractUpdatePermFilesReturn = @SpotsExtractUpdatePermFilesReturn OUTPUT
DROP TABLE #SpotsWarrants
DROP TABLE #SpotsExceptions
DROP TABLE #SpotsWarrantExceptionCounts
DROP TABLE #SpotsWarrantErrorCounts
DROP TABLE #SpotsWarrantWarningCounts
DROP TABLE #SpotsReportData
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply