Possible bug: @PV variable declaration.

Compares and synchronizes SQL database content.

Moderators: Chris Auckland, David Atkinson, richardjm, david connell

Possible bug: @PV variable declaration.

Postby lysp » Mon Mar 23, 2009 3:16 am

Hi.

Unfortunately i'm not able to provide too much information regarding this possible bug. This is occurring on a clients machine in which i do not have access to, and am unable to get a copy of the data locally to test due to our agreement.

I have generated a script locally and it works fine. When generating on the clients database which is the same structurally, just more data, the following error is occurring:

Msg 137, Level 15, State 1, Line 1506
Must declare the scalar variable "@pv".


On basic inspection of the script the DECLARE @pv is at the top of the script, however the error is possibly due to the declaration out of scope.

It is a basic comparison with no default options changed, between a database and an another database which is the same but is empty.

The error emailed by the client is on the following lines:

Code: Select all
EXEC(N'INSERT INTO [dbo].[doc_DocumentData] ([DocumentID], [DocumentText], [DocumentDescription], [DocumentTextPrivate], [FileExt], [ModifiedDate], [DocumentSiteID]) VALUES (1595, 0x1111111111111111111111111111111111, '''', 0x11111111111111111111111111111111111111111111111111111111111'
+N'111111111111111111111, ''.htm'', ''2008-12-16 15:47:11.410'', NULL)')
SELECT @pv=TEXTPTR([DocumentTextPrivate]) FROM [dbo].[doc_DocumentData] WHERE [DocumentID]=1595
UPDATETEXT [dbo].[doc_DocumentData].[DocumentTextPrivate] @pv NULL NULL 0x11111111111111111111111111
EXEC(N'INSERT INTO [dbo].[doc_DocumentData] ([DocumentID], [DocumentText], [DocumentDescription], [DocumentTextPrivate], [FileExt], [ModifiedDate], [DocumentSiteID]) VALUES (1596, 0x111111111111111111111111111111111111, '''', 0x111111111111111111111, ''.htm'', ''2008-12-17 14:06:41.520'', NULL)')



The solution was to add a 2nd declaration of @PV just above where the error was occurring.

Sorry i am unable to provide any further details.
lysp
 
Posts: 32
Joined: Thu Aug 17, 2006 2:11 am

Postby Chris Auckland » Wed Mar 25, 2009 9:35 pm

Thanks for your post.

It does sound like it could be due to the scope of the query, but I don't see how that could have happened.

Was your client executing the script through SQL Data Compare, or were they manaully running the script? Is there a chance it could have been split up into smaller chunks causing the query to go out of scope?
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby lysp » Thu Mar 26, 2009 1:43 am

They were running it manually from a text file and not through the application.

Also they were running it in it's entirety not in segments through management studio.

In terms of the size, it was 160mb .sql file (ascii encoding), 500 tables with 100mb of the data in 2 tables.
lysp
 
Posts: 32
Joined: Thu Aug 17, 2006 2:11 am

Postby Chris Auckland » Wed Apr 08, 2009 5:01 pm

Sorry about the delay in this response.

We have been trying to reproduce this issue in house, but have so far been unsuccessful, so unfortunatly we do not have any aditional information to help resolve the problem.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby vasper » Fri Mar 30, 2012 1:21 pm

We are having the same problem with our upgrade scripts. We run them through the exes created by SQL Packager V6 ( 6.4.0.8 ).

It seems to be related with large volume of binary data being updated and for some reason the variable looses scope in the next statement.

Please find a fix because it takes 2 days to check and fix the amount of scripts we need, by trial and error.

Also as smarter aproach for deletes would be to delete consecutive rows that have integer primary index by range and not one by one.
vasper
 
Posts: 6
Joined: Fri Mar 30, 2012 1:16 pm


Return to SQL Data Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 1 guest