@pv variable declared outside of query batches

mcdrewski

Hi,

I'm trying to do a large and complex synchronisation, and generate a script. When I do so i get something looking like this (the other 30Mb or so is snipped)

/*
Run this script on:
(local).DEST    -  This database will be modified
to synchronize it with:
(local).SOURCE
You are recommended to back up your database before running this script
Script created by SQL Data Compare version 10.7.0 from Red Gate Software Ltd at 19/06/2014 2:09:49 PM
*/
		
/*...snip...*/

BEGIN TRANSACTION
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)

PRINT(N'Drop constraints from [dbo].[TABLEA]')
GO
ALTER TABLE [dbo].[TABLEA] DROP CONSTRAINT [FK_TABLEA_TABLEB]

/*...snip...*/

EXEC(N'INSERT INTO [dbo].[TABLEX] ([BAR], [FOO]) VALUES (231, ''...firstpart...'')')
SELECT @pv=TEXTPTR([FOO]) FROM [dbo].[TABLEX] WHERE [BAR]=231
UPDATETEXT [dbo].[TABLEX].[FOO] @pv NULL NULL N'...secondpart...'

/*...snip...*/

the problem is that @pv is declared before the "GO" is issued (actually, there are multiple "GO" commands), and as such is out of scope when referred to later. This seems like a fairly clearcut bug to me. Any ideas on workarounds other than manually editing the script?

I'm using SQLServer 2012 FYI

Brian Donahue

The variable @pv used for BLOB handles is scoped to batches in T-SQL, so it goes out of scope when a batch ends with a GO. The problem was identified a few times and fixed, and it looks like the particular root cause of this in v10 (SDC-1682) will be fixed in v11 when it comes out. I can let you know when it's out or there is a beta.