Red Gate forums :: View topic - Suggestions on diagnosing SQL command batching error
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK 10
SQL Comparison SDK 10 forum

Suggestions on diagnosing SQL command batching error

Search in SQL Comparison SDK 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
SQLAdminCJ



Joined: 28 Dec 2011
Posts: 10

PostPosted: Tue Dec 04, 2012 7:00 pm    Post subject: Suggestions on diagnosing SQL command batching error Reply with quote

Hello,

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



Joined: 23 Aug 2004
Posts: 6671

PostPosted: Tue Jan 15, 2013 11:02 am    Post subject: Reply with quote

There is a MaxByteSizeOftransactions option on the SqlProvider class. Hopefully that does what you want.
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