Red Gate forums :: View topic - Possible bug: @PV variable declaration.
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Compare Previous Versions
SQL Data Compare Previous Versions forum

Possible bug: @PV variable declaration.

Search in SQL Data Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
lysp



Joined: 17 Aug 2006
Posts: 32

PostPosted: Mon Mar 23, 2009 3:16 am    Post subject: Possible bug: @PV variable declaration. Reply with quote

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:

Quote:

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:
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.
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Wed Mar 25, 2009 9:35 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
lysp



Joined: 17 Aug 2006
Posts: 32

PostPosted: Thu Mar 26, 2009 1:43 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Wed Apr 08, 2009 5:01 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
vasper



Joined: 30 Mar 2012
Posts: 6

PostPosted: Fri Mar 30, 2012 1:21 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group