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
@OdysseyJobIDINT,
@BOUserVARCHAR(50),
@SpotsExtractUpdatePermFilesReturnBIGINT 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.CaseIDAS CaseID,
REPLACE(w.CaseNumber, '.', '')AS CaseNumber,
w.NodeIDAS NodeID,
w.CourtNameAS CourtName ,
w.WarrantIDAS WarrantID ,
w.WarrantNumberAS WarrantNumber,
w.IssueDateAS IssueDate,
w.WarrantStatusCodeAS WarrantStatusCode,
w.WarrantStatusDescAS WarrantStatusDesc,
w.WarrantStatusDateAS WarrantStatusDate,
REPLACE(w.ORINumber, '.', '')AS ORINumber,
OdysseyNH.dbo.fnSpotsInvalidCharacters(w.FullName, 'B')
AS FullName,
REPLACE(w.GenderKy, '.', '')AS GenderKy,
w.GenderDescAS 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.RaceDescAS RaceDesc,
w.DtDOBAS DtDOB,
w.DtDOBFormattedAS DtDOBFormatted,
w.HtInchesAS HtInches,
w.HeightAS Height,
w.WtLbsAS WtLbs,
REPLACE(w.EyeKy, '.', '')AS EyeKy,
w.EyeDescAS EyeDesc,
REPLACE(w.HairKy, '.', '')AS HairKy,
w.HairDescAS HairDesc,
REPLACE(w.Statute, '.', '')AS Statute,
w.DegreeAS Degree,
w.ChargeOffenseDescAS ChargeOffenseDesc,
w.BondAmountAS BondAmount,
REPLACE(w.BondTypeCode, '.', '')AS BondTypeCode,
w.BondTypeDescAS BondTypeDesc,
REPLACE(w.WarrantTypeCode, '.', '')AS WarrantTypeCode,
w.WarrantTypeDescAS WarrantTypeDesc,
w.DateOfWarrantAS DateOfWarrant,
w.DateOfWarrantFormattedAS DateOfWarrantFormatted,
REPLACE(w.Miscellaneous, '.', '')AS Miscellaneous,
@BOUserAS CreatedBy
FROM #SpotsReportDataw
WHERERecordType = 1
ANDErrorCount= 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]
34120900msmith2008-08-13 10:52:49.273update2
35120900msmith2008-08-13 10:52:49.663update2
36120905msmith2008-08-13 11:06:35.593update9
37120901msmith2008-08-13 11:07:03.417update2
38120901msmith2008-08-13 11:07:03.853update2
39120952msmith2008-08-13 14:21:03.920update5
40120940msmith2008-08-13 14:21:35.430update2
41120940msmith2008-08-13 14:21:35.837update2
42120979msmith2008-08-13 14:43:02.040update1
43120979msmith2008-08-13 14:43:02.383update1
44120953msmith2008-08-13 14:43:41.223update6
45120973msmith2008-08-13 14:44:19.643update1
46120973msmith2008-08-13 14:44:19.987update1
47121048msmith2008-08-13 15:50:40.217update4
48121068msmith2008-08-13 15:55:26.427update2
49121068msmith2008-08-13 15:55:26.817update2
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
@DateStartDATETIME,
@DateEndDATETIME,
@CourtCodeINT,
@OdysseyJobIDINT,
@BOUserVARCHAR(50)
AS
SET NOCOUNT ON
DECLARE @SpotsExtractUpdatePermFilesReturnBIGINT
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),
DegreeCodeIDINT,
Degree VARCHAR(20),
DegreeDescVARCHAR(100),
ChargeOffenseDesc VARCHAR(100),
CaseNumber VARCHAR(30),
BondAmount INT,
BondTypeCodeIDINT,
BondTypeCode VARCHAR(20),
BondTypeDesc VARCHAR(100),
WarrantTypeCodeIDINT,
WarrantTypeCode VARCHAR(20),
WarrantTypeDesc VARCHAR(100),
DateOfWarrant DATETIME,
OdysseyJobID INT )
CREATE TABLE #SpotsExceptions(
WarrantIDINT,
WarrantNumberVARCHAR(30),
CaseIDINT,
CaseNumberVARCHAR(30),
NodeIDINT,
ExceptionNumberINT,
ExceptionTypeCHAR(1),
ExceptionMessageVARCHAR(300))
CREATE TABLE #SpotsWarrantExceptionCounts(
WarrantIDINT,
WarrantExceptionCountINT,
WarrantErrorCountINT,
WarrantWarningCountINT)
CREATE TABLE #SpotsWarrantErrorCounts(
WarrantIDINT,
WarrantErrorCountINT)
CREATE TABLE #SpotsWarrantWarningCounts(
WarrantIDINT,
WarrantWarningCountINT)
CREATE TABLE #SpotsReportData(
--common to all records
RecordTypeSMALLINT ,
WarrantIDINT,
WarrantNumberVARCHAR(30),
CaseIDINT,
CaseNumberVARCHAR(30),
NodeIDINT,
CourtNameVARCHAR(35),
ExceptionCountINT,
ErrorCountINT,
WarningCountINT,
DateStartDATETIME,
DateEndDATETIME,
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),
DegreeCodeIDINT,
Degree VARCHAR(20),
DegreeDescVARCHAR(100),
ChargeOffenseDesc VARCHAR(100),
BondAmount INT,
BondTypeCodeIDINT,
BondTypeCode VARCHAR(20),
BondTypeDesc VARCHAR(100),
WarrantTypeCodeIDINT,
WarrantTypeCode VARCHAR(20),
WarrantTypeDesc VARCHAR(100),
DateOfWarrant DATETIME,
DtDOBFormattedVARCHAR(8),
DateOfWarrantFormattedVARCHAR(8),
MiscellaneousVARCHAR(150),
--exception data
ExceptionNumberINT,
ExceptionTypeCHAR(1),
ExceptionMessageVARCHAR(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
FROMSpotsWarrants
WHEREOdysseyJobID = @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