What are the challenges you face when working across database platforms? Take the survey

Suggestions on diagnosing SQL command batching error


I am using SQL Comparison SDK to synchronize two databases. The queries generated to synchronize a certain table are very large (usually between 15 MB and 25 MB) and cannot be run as a single block. As such, we break them down into smaller batches of approximately 500 lines and execute each in a transaction. We have some logic to ensure that the batches aren't cut off mid-command, and that each batch has the required configuration commands to be run independently of the entire query.

However, I am getting a SQL exception: "The variable name '@pv' has already been declared. Variable names must be unique within a query batch or stored procedure."

This likely means that there is a problem with our batching logic that is putting

DECLARE @pv binary(16)

twice in one batch.

Debugging this issue is proving problematic. It takes a very long time to generate this error in the IDE (perhaps around 4 hours). Moreover, if the exception is left for too long after it is raised, the batch times out and the IDE cannot rewind to the point of the exception.

I'm trying to find a way to get the error to happen faster. I tried to only diff rows for which the primary key contains the number 8, but I can't get it to happen that way. Deleting data to reduce the diff in a test environment is tedious as there are constraints and triggers that have to be disabled to do this.

Does anyone have any advice on how to debug this exception? Perhaps there is some tool in the SDK or tactic to use? I'd appreciate any advice anyone can provide.


  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    There is a MaxByteSizeOftransactions option on the SqlProvider class. Hopefully that does what you want.
  • Options
    i upgraded recently the SDK to version 10.5 and i faced some issues with the spitted transactions.

    In the comparison i have set the option MaxByteSizeOfTransactions to 10*1024 but the produced script is not always correct (please check below a sample of the produced statement):
    -- Pointer used for text / image updates. This might not be needed, but is declared here just in case
    DECLARE @pv binary(16)
    -- here we have the data suncronization script
    PRINT(N'Add 2 rows to XXX)
    --here we have the data syncronization script whitout the declaration of the @pv variable (in this case this is not an issue since the statements does not need the variable)
    PRINT(N'Add 2 rows to YYY)
    --here we have the data syncronization script whitout the declaration of the @pv variable. But the variable will be used in the syncronization statement and the missing declaration is an issue.
    SELECT @pv=TEXTPTR([ZZZ]) FROM [dbo].[TableXXX] WHERE [KEY_CODE]=4985
    UPDATETEXT [dbo].[TableXXX].[IMAGE_FIELD] @pv NULL NULL [i][the binary data][/i]

    Does is needed to change something for the 10.5 version (this was worked ok in the previous version) ?
  • Options
    Yeah, MaxByteSizeOftransactions, of course.
Sign In or Register to comment.