Possible bug: @PV variable declaration.
lysp
Posts: 47
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:
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:
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.
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:
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.
Comments
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?
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.
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.
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.