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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy