an error message from SSIS Execute SQL Task which

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

an error message from SSIS Execute SQL Task which

Author

Topic

dchu007
Starting Member

3 Posts

Posted - 2011-08-03 : 13:40:53

Hi all,

Would you please help me to find out what is really meaning of the following error… from SSIS 2008 R2...

"The data type of substitution parameter 2 does not match the expected type of the format specification.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This error message came from a SSIS Execute SQL Task which executes a Stored Procedure as
EXEC [PDSLog].[dbo].[uspBatchInfoUpdate] 289750,0,0,0,1,3,’00:00:03’
And, interesting thing is this error is not always failed only occurred randomly!!!

Thanks,

dennis

dchu007
Starting Member

3 Posts

Posted - 2011-08-03 : 17:51:12

The SP code is ...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--DROP PROCEDURE [dbo].[uspBatchInfoUpdate]
CREATE PROCEDURE [dbo].[uspBatchInfoUpdate]
(
@pBatchID BIGINT,
@pRowsInserted INT,
@pRowsUpdated INT,
@pRowsDeleted INT,
@pBatchStatusID INT,
@pRetry INT = 3, --default 3 times
@pWaitForRetry char(8)= '00:00:02' --default is 2 seconds
)
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON;

-- BEGIN STANDARD ERROR HANDLING SECTION.
DECLARE @ErrNum INT
DECLARE @ErrMsg NVARCHAR(MAX)
DECLARE @SPName NVARCHAR(128)
DECLARE @EndDate Datetime

SET @EndDate = GETDATE()

/* Init Vars */
SELECT
@ErrNum = 0,
@ErrMsg = '',
@SPName = OBJECT_NAME(@@ProcID)

BEGIN TRANSACTION
WHILE (@pRetry > 0)
BEGIN
BEGIN TRY
UPDATE dbo.Batch
SET RowsInserted = @pRowsInserted,
RowsUpdated = @pRowsUpdated,
RowsDeleted = @pRowsDeleted,
BatchStatusID = @pBatchStatusID,
BatchEndDate = @EndDate
WHERE BatchID = @pBatchID

COMMIT TRANSACTION;
SET @pRetry = -1
END TRY

BEGIN CATCH
SET @pRetry = @pRetry - 1
WAITFOR DELAY @pWaitForRetry;
END CATCH
END; --WHILE

IF (@pRetry = 0)
BEGIN
ROLLBACK TRANSACTION
SET @ErrMsg = '%s: Cannot update Batch info for the Batch ID = %i!'
RAISERROR(@ErrMsg, 16, 1, @SPName, @pBatchID)
END
END

dennis

dchu007
Starting Member

3 Posts

Posted - 2011-08-04 : 19:45:51

With 2008, for formatting the message with BIGINT should use %I64d instead of %I.
http://msdn.microsoft.com/en-us/library/ms178592.aspx

dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources