December 31, 2013 at 8:23 am
Hi. I have a stored procedure with two output parameters. I am receiving the following error when I execute the stored procedure:
Msg 245, Level 16, State 1, Procedure xsp_insertJobSetup, Line 63
Conversion failed when converting the nvarchar value 'xsp_insertJobSetup' to data type int.
However, when I execute the SELECT statement within the stored procedure, I see the *REAL* error:
Msg 241, Level 16, State 1, Line 5
Conversion failed when converting date and/or time from character string.
I am unable to catch the error in a TRY/CATCH block, and so I am unsure how to deal with this problem. This stored procedure is one of many that are called in a main driver stored procedure so I need to be able to determine that an error has been encountered and capture that error so I can then move onto the next record set for processing.
My stored procedure code:
CREATE PROCEDURE [dbo].[xsp_insertJobSetup]
@xmlStagingID INT,
@jobID INT OUTPUT,
@errorNum INT OUTPUT
AS
DECLARE @severity INT
DECLARE @state INT
DECLARE @procedure INT
DECLARE @line INT
DECLARE @message VARCHAR(1000)
DECLARE @tool VARCHAR(250)
DECLARE @rc INT
SET NOCOUNT ON;
BEGIN TRY
INSERT [dbo].[jobSetup]
([jobName],
[branchID],
[branchCodeID],
[runDate],
[hostOS],
[hostName],
[userName],
[productCode],
[doublePrecision],
[object],
[mpMode],
[parallel],
[standardParallel],
[snapshot])
SELECT
c3.value('@jobName[1]','varchar(750)') as jobName,
branchID,
branchCodeID,
c3.value('@runDate[1]','datetime') as runDate,
c3.value('@hostOS[1]','varchar(100)') as hostOS,
c3.value('@hostName[1]','varchar(150)') as hostName,
c3.value('@userName[1]','varchar(100)') as userName,
c3.value('@productCode[1]','varchar(25)') as productCode,
c3.value('@doublePrecision[1]','varchar(100)') as dblPrecision,
c3.value('@object[1]','varchar(100)') as codeObject,
c3.value('@mpMode[1]','varchar(100)') as mpMode,
c3.value('@parallel[1]','varchar(100)') as parallel,
c3.value('@standardParallel[1]','varchar(100)') as stdParallel,
c3.value('@snapshot[1]','int') as [snapshot]
FROM xmlResults xmlR
CROSS APPLY xmlR.xmlContent.nodes('//DEV/job') AS T3(c3)
JOIN dbo.branchMaster brM
ON c3.value('@codeBranch[1]','varchar(100)') = brM.branchName
JOIN dbo.branchCodeMaster brCM
ON c3.value('@codeVersion[1]','varchar(100)') = brCM.branchCodeName
where xmlStagingID = @xmlStagingID
SET @jobID = SCOPE_IDENTITY();
END TRY
BEGIN CATCH
SET @errorNum = ERROR_NUMBER()
SET @severity = ERROR_SEVERITY()
SET @state = ERROR_STATE()
SET @procedure = ERROR_PROCEDURE()
SET @line = ERROR_LINE()
SET @message = ERROR_MESSAGE()
SET @tool = 'devPerfResults.dbo.xsp_insertJobSetup'
EXECUTE @rc = [Maintenance].[dbo].[ErrorDetails_withMail]
@tool
,@errorNum
,@severity
,@state
,@procedure
,@line
,@message
END CATCH
GO
The call to the stored procedure:
EXEC dbo.xsp_insertJobSetup 2, @jobID_LOCAL OUTPUT, @errorNum_LOCAL OUTPUT
Any help would be greatly appreciated!
Cathy
December 31, 2013 at 8:44 am
Just a guess, but your runDate in the XML might not have the correct format or might be an invalid date.
December 31, 2013 at 8:47 am
Luis,
Yes, the problem is with the rundate. However, I want to be able to capture that. Right now, my stored procedure is erroring with the message and the main driver stored procedure is dying. I want to catch the error and move on, if possible, rather than die there because the format of the rundate is incorrect. Do you have any suggestions with regard to how to catch the error and move on? Thank you for your help.
Cathy
December 31, 2013 at 10:08 am
It's hard to give an answer as I don't know your table or xml structure. Could you give a sample of values for the following query?
SELECT c3.value('@runDate[1]','varchar(100)') as runDate
FROM xmlResults xmlR
CROSS APPLY xmlR.xmlContent.nodes('//DEV/job') AS T3(c3)
where xmlStagingID = @xmlStagingID
And DDL for [jobSetup].
December 31, 2013 at 10:18 am
Sure. The table (without foreign keys or other indexes for simplicity's sake)
USE [devPerfResults]
GO
/****** Object: Table [dbo].[jobSetup] Script Date: 12/31/2013 12:10:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[jobSetup](
[jobID] [bigint] IDENTITY(1,1) NOT NULL,
[jobName] [varchar](750) NOT NULL,
[branchID] [int] NOT NULL,
[branchCodeID] [int] NULL,
[runDate] [datetime2](7) NOT NULL,
[hostOS] [varchar](100) NOT NULL,
[hostName] [varchar](150) NOT NULL,
[userName] [varchar](100) NOT NULL,
[productCode] [varchar](25) NOT NULL,
[doublePrecision] [varchar](100) NULL,
[object] [varchar](100) NULL,
[mpMode] [varchar](100) NULL,
[parallel] [varchar](100) NULL,
[standardParallel] [varchar](100) NULL,
[snapshot] [int] NOT NULL,
CONSTRAINT [PK__jobSetup__164AA18822751F6C] PRIMARY KEY CLUSTERED
(
[jobID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
sample rundates:
2013-11-181 3:59:59.997Z
2013-11-18T13:59:59.997Z
sample xml (bad rundate):
<DEV xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="devPerfResults_schema.xsd">
<job id="0" jobName="jobRun" codeBranch="branch" codeVersion="1.23" runDate="2013-11-18T1 3:59:59.997Z" hostName="xxx" hostOS="windowsOS" userName="abcdef" productCode="PRODUCT" doublePrecision="OFF" object="nodebug" mpMode="MPI" parallel="DOMAIN" standardParallel="ALL" snapshot="131300" numDomains="" numElements="" numFlops="" numberOfRanks="2" cores="6" ppn="" dof="" numIncrements="" numIterations="">
<process rankNumber="1234">
<phase name="flatten" userTime="0.3" systemTime="0.1" cpuTime="0.1" wallTime="0.3" maxRSS="1.5" VmMaxRSS="0.75" parent="" />
<phase name="Elgen" userTime="0.3" systemTime="0.1" cpuTime="0.1" wallTime="0.3" maxRSS="2" VmMaxRSS="1" parent="" />
<phase name="CallSolver" userTime="0.3" systemTime="0.1" cpuTime="0.1" wallTime="0.3" maxRSS="32" VmMaxRSS="32" parent="" />
</process>
<process rankNumber="4321">
<phase name="flatten" userTime="0.3" systemTime="0.1" cpuTime="0.1" wallTime="0.3" maxRSS="5" VmMaxRSS="3.25" parent="" />
<phase name="Elgen" userTime="0.3" systemTime="0.1" cpuTime="0.1" wallTime="0.3" maxRSS="32" VmMaxRSS="10" parent="" />
<phase name="CallSolver" userTime="0.3" systemTime="0.1" cpuTime="0.1" wallTime="0.3" maxRSS="2.5" VmMaxRSS="3" parent="" />
</process>
</job>
</DEV>
December 31, 2013 at 12:01 pm
Maybe this will avoid errors.
CAST( CASE WHEN CHARINDEX('T', c3.value('@runDate[1]','varchar(30)')) = 0
THEN STUFF( REPLACE(c3.value('@runDate[1]','varchar(30)'), ' ', ''), 11, 0, 'T')
ELSE REPLACE(c3.value('@runDate[1]','varchar(30)'), ' ', '') END AS datetime2)
But the best option is to clean the data from the source. You shouldn't have this wrong formats for date strings.
December 31, 2013 at 12:08 pm
Alright, that makes sense. I am using an XSD, but SQL Server kept erroring out on the xsd:datetime data type, so I gave up trying to get it to work and set it to a string data type upon insert. I will go back to the XSD and see if I can figure out what the problem is at that level. Thank you for your help.
Cathy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply