March 18, 2011 at 5:28 am
Hi,
I have a problem related to to passing in and out a pair of ROWVERSION values from an Execute SQL Task in SSIS.
I have build a Stored Procedure that when called with a "@lastrowversion binary(8)" parameter will internally set "@activerowversion binary(8) OUTPUT" to MIN_ACTIVE_ROWVERSION() for the current database and return a resultset containing changed rows from a specific table together with the current active row version for the database.
CREATE PROCEDURE [dbo].[udsp_Export_Unica_KundeResponsHist]
@remotedbname [sysname],
@lastrowversion [binary](8),
@activerowversion [binary](8) OUTPUT
WITH EXECUTE AS CALLER
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
BEGIN TRY-- Error handling
-- Prepare and begin named transaction
DECLARE @procName [sysname] = OBJECT_NAME(@@PROCID);
BEGIN TRANSACTION @procName;
--It's important to capture min_active_rowversion() into a variable, because
--it could change while the query is running, which could lead to lost
--updates.
DECLARE @sql nvarchar(1024) = N'USE ' + @remotedbname + '; SELECT @rowid = MIN_ACTIVE_ROWVERSION()';-- connect to Unica database to get active rowversion
DECLARE @parmdefinition nvarchar(512) = '@rowid binary(8) OUTPUT';
-- get current row version for remote database - we use it to avoid concurrency issues for rows changed during snapshot
EXEC sp_executesql
,@parmdefinition
,@rowid = @activerowversion OUTPUT
;
SELECT
[CustomerID]
,[ContactDate]
,[ContactType]
FROM [ContactHistory]-- view in local database with underlying tables in remote database
WHERE
ContactTimestamp >= @lastrowversion AND ContactTimestamp < @activerowversion)-- changed rows since last snapshot, but NOT during snapshot
ORDER BY
[CustomerID]
,[ContactDate]
;
COMMIT TRANSACTION @procName;
END TRY
BEGIN CATCH;
-- On error rollback any changes
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION @procName;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
END
GO
The stored procedure works without any problems when called from SSMS (SQL Server Management Studio) but when I try to perform the same query in an Execute SQL Task in SSIS I get errors.
I am convinced that my errors are related to the data type of the ROWVERSION parameters in SSIS and are in doubt what kind of data type I should use in SSIS for storing that values of my "@lastrowversion binary(8)" and "@activerowversion binary(8) OUTPUT" parameters.
Can anyone help me please?
Claus Thorning Madsen
March 18, 2011 at 7:47 am
I have implemented a "workaround" passing my binary as hex formatted string.
This includes:
- converting my row version input paramter from a hex formatted string (nvarchar(128)) to binary(8) to be used in the query
- converting the active row version for the remote database from binary(8) to a hex formatted string (nvarchar(128))
Credits go to the following post: http://www.sqlservercentral.com/Forums/Topic447185-338-1.aspx
My resulting procedure looks like:
CREATE PROCEDURE [dbo].[udsp_Export_Unica_KundeResponsHist]
@remotedbname [sysname],
@lastrowversionhexstr [nvarchar](128),
@activerowversionhexstr [nvarchar](128) OUTPUT
WITH EXECUTE AS CALLER
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
BEGIN TRY-- Error handling
-- Prepare and begin named transaction
DECLARE @procName [sysname] = OBJECT_NAME(@@PROCID);
BEGIN TRANSACTION @procName;
--Convert recorded row version from hex string to binary(8)
DECLARE @lastrowversion binary(8);
DECLARE @sql_hextovarbin nvarchar(1024)= 'SET @rowid = CAST(CAST(' + @lastrowversionhexstr + ' as bigint) as binary(8))';
DECLARE @parmdef_hextovarbin nvarchar(512) = '@rowid binary(8) OUTPUT';
EXEC sp_executesql
@sql_hextovarbin
,@parmdef_hextovarbin
,@rowid = @lastrowversion OUTPUT
;
--It's important to capture min_active_rowversion() into a variable, because
--it could change while the query is running, which could lead to lost
--updates.
DECLARE @activerowversion binary(8)
DECLARE @sql nvarchar(1024) = N'USE ' + @remotedbname + '; SELECT @rowid = MIN_ACTIVE_ROWVERSION()';-- connect to Unica database to get active rowversion
DECLARE @parmdefinition nvarchar(512) = '@rowid binary(8) OUTPUT';
-- get current row version for Unica database - we use it to avoid concurrency issues for rows changed during snapshot
EXEC sp_executesql
,@parmdefinition
,@rowid = @activerowversion OUTPUT
;
SELECT
[CustomerID]
,[ContactDate]
,[ContactType]
FROM [ContactHistory] -- view in local database with underlying tables in remote database
WHERE
(ContactTimestamp >= @lastrowversion AND ContactTimestamp < @activerowversion) -- changed rows since last snapshot, but NOT during snapshot
ORDER BY
[CustomerID]
,[ContactDate]
;
-- convert captured active row version to hex string using undocumented fsystem function for easier parameter handling in SSIS
SELECT @activerowversionhexstr = master.sys.fn_varbintohexstr(@activerowversion);
COMMIT TRANSACTION @procName;
END TRY
BEGIN CATCH;
-- On error rollback any changes
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION @procName;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
END
GO
The "row version" parameter values in and out of the procedure has the following format: 0x00000000435a25c1 (no quotes!)
March 18, 2011 at 8:15 am
Well, I found another problem executing the dynamic SQL inside my procedure.
Then I came across another excellent tip regarding using sp_executesql in another database from the current. The tip has to do with something completely different from my issues but the sp_executesql method works: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/eaf522b3-069d-4dfc-b594-6308f9c966e3
So my final (and succesfully tested) procedure looks like:
CREATE PROCEDURE [dbo].[udsp_Export_Unica_KundeKontaktHist]
@remotedbname [sysname],
@lastrowversionhexstr [nvarchar](128),
@activerowversionhexstr [nvarchar](128) OUTPUT
WITH EXECUTE AS CALLER
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
BEGIN TRY-- Error handling
---- Prepare and begin named transaction
DECLARE @procName [sysname] = OBJECT_NAME(@@PROCID);
BEGIN TRANSACTION @procName;
--Convert recorded row version from hex string to binary(8)
DECLARE @lastrowversion binary(8);
DECLARE @sql_hextovarbin nvarchar(1024)= 'SET @rowid = CAST(CAST(' + @lastrowversionhexstr + ' as bigint) as binary(8))';
DECLARE @parmdef_hextovarbin nvarchar(512) = '@rowid binary(8) OUTPUT';
EXEC sp_executesql
@sql_hextovarbin
,@parmdef_hextovarbin
,@rowid = @lastrowversion OUTPUT
;
--It's important to capture min_active_rowversion() into a variable, because
--it could change while the query is running, which could lead to lost
--updates.
DECLARE @activerowversion binary(8);
DECLARE @sp_executesql nvarchar(1024) = @remotedbname + '.sys.sp_executesql';
DECLARE @sql nvarchar(1024) = N'SELECT @rowid = MIN_ACTIVE_ROWVERSION()';-- connect to Unica database to get active rowversion
DECLARE @parmdefinition nvarchar(512) = '@rowid binary(8) OUTPUT';
-- get current row version for Unica database - we use it to avoid concurrency issues for rows changed during snapshot
EXEC @sp_executesql
,@parmdefinition
,@rowid = @activerowversion OUTPUT
;
SELECT
[CustomerID]
,[ContactDate]
,[ContactType]
FROM [ContactHistory] -- view in local database with underlying tables in remote database
WHERE
(ContactTimestamp >= @lastrowversion AND ContactTimestamp < @activerowversion) -- changed rows since last snapshot, but NOT during snapshot
ORDER BY
[CustomerID]
,[ContactDate]
;
-- convert captured active row version to hex string using undocumented fsystem function for easier parameter handling in SSIS
SELECT @activerowversionhexstr = master.sys.fn_varbintohexstr(@activerowversion);
COMMIT TRANSACTION @procName;
END TRY
BEGIN CATCH;
-- On error rollback any changes
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION @procName;
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
END
GO
Phew - took me a few hours already.
Claus
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply