Red Gate forums :: View topic - Issue with declaring @pv for BLOB data in 10.4.8.62
Return to www.red-gate.com RSS Feed Available

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

Issue with declaring @pv for BLOB data in 10.4.8.62

Search in SQL Data Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
paschott



Joined: 10 May 2006
Posts: 64

PostPosted: Fri Sep 27, 2013 10:42 pm    Post subject: Issue with declaring @pv for BLOB data in 10.4.8.62 Reply with quote

I generated scripts for our databases that disable triggers and constraints before modifying data in the database. I noticed that for several of these scripts, there is a DECLARE @pv command at the beginning of the script, followed by a DISABLE TRIGGER command, followed by a "GO". I recorded the output of my script and noticed that there were several errors because the variable "@pv" was not declared.

Looking through the rest of the script, it seemed that the @pv variable was declared within each transaction/batch, but for the very first data transaction/batch, it was declared outside of that section.

Shouldn't the DECLARE @pv command be done after all of the constraint and trigger disables, just before we actually start manipulating data?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6640

PostPosted: Mon Sep 30, 2013 10:16 am    Post subject: Reply with quote

Is this issue caused by using the "split transactions into batches" option in the application options?
Back to top
View user's profile Send private message
paschott



Joined: 10 May 2006
Posts: 64

PostPosted: Mon Sep 30, 2013 4:15 pm    Post subject: Reply with quote

I believe it is related to that. I'm using Data Compare to script out entire customers from our multi-tenant database. It works great for the most part, but generates very large files. From what I can tell in one of the smaller files, the DECLARE @pv section is at the very top of the file and is followed by several commands to disable constraints. Those all have GO to separate the batches, which means that when the first actual section is run to insert data, there is no variable declared to handle the BLOB data.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6640

PostPosted: Tue Oct 01, 2013 10:38 am    Post subject: Reply with quote

I'm trying to reproduce this - what is the problematic BLOB type (image, text, binary), version of SQL Server, and the size of BLOBS going in?

It doesn't seem to use or need the pointer for nvarchar(max) on SQL 2008, but it does declare @pv again in every block of split transactions.
Back to top
View user's profile Send private message
paschott



Joined: 10 May 2006
Posts: 64

PostPosted: Tue Oct 01, 2013 1:59 pm    Post subject: Re: Reply with quote

Brian Donahue wrote:
I'm trying to reproduce this - what is the problematic BLOB type (image, text, binary), version of SQL Server, and the size of BLOBS going in?

It doesn't seem to use or need the pointer for nvarchar(max) on SQL 2008, but it does declare @pv again in every block of split transactions.


Pretty sure it's with XML or Text data. This is coming from SQL Server 2005 going to SQL 2012 (though that ideally shouldn't matter). When I pulled up one of the smaller scripts that failed, I noticed that the only DECLARE @pv prior to using it was not in the same transaction block. When I ran the Data Compare directly, it worked and I figured the script would work as well, but I can't seem to run the saved script.

I'll try another data compare to see if that makes a difference. I was just hoping to avoid that as the generated scripts are hundreds of MB and if I can't reuse the scripts, that makes the process a little less useful.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6640

PostPosted: Wed Oct 02, 2013 1:46 pm    Post subject: Reply with quote

How do you re-use the scripts? Are you running them in another tool, like sqlcmd?
Back to top
View user's profile Send private message
paschott



Joined: 10 May 2006
Posts: 64

PostPosted: Wed Oct 02, 2013 3:47 pm    Post subject: Re: Reply with quote

Brian Donahue wrote:
How do you re-use the scripts? Are you running them in another tool, like sqlcmd?


I'm using SQLCMD to run the scripts. Otherwise, what is the point of saving out the data compare to a script. I want the ability to re-create an empty set of databases and run just the scripts to populate them with a base set of data to use for testing and development work.

Obviously that data will change in small ways over time, but populating a working set is a good start. I'm almost positive that this worked properly in a prior version of Data Compare.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6640

PostPosted: Fri Oct 04, 2013 1:53 pm    Post subject: Reply with quote

My thought is, that if it works in SQL Data Compare but not from sqlcmd, perhaps there is some option you can change in sqlcmd? I'm not sure how sqlcmd deals with batches and transactions.
Back to top
View user's profile Send private message
paschott



Joined: 10 May 2006
Posts: 64

PostPosted: Fri Oct 04, 2013 5:55 pm    Post subject: Re: Reply with quote

Brian Donahue wrote:
My thought is, that if it works in SQL Data Compare but not from sqlcmd, perhaps there is some option you can change in sqlcmd? I'm not sure how sqlcmd deals with batches and transactions.


I'll regenerate the scripts soon, but SQLCMD should work as a standard SQL Script. I can't declare a variable in one batch and then use it in a different batch. If Data Compare is not appropriately declaring the variables within their batches, that's a problem that should be addressed.
Back to top
View user's profile Send private message
paschott



Joined: 10 May 2006
Posts: 64

PostPosted: Fri Oct 04, 2013 11:02 pm    Post subject: Reply with quote

And I just tried to re-use the newly created scripts. No luck. I'll try scripting without the option to split the script into multiple transactions next time around, but to me if I have the option to generate a SQL Script, I should be able to use that script to push changes to the database without needing Data Compare.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6640

PostPosted: Mon Oct 07, 2013 10:53 am    Post subject: Reply with quote

SQLCMD may be truncating the script. It has limitations both in the file size and the maximum size for a large object. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/01697325-4a6c-40fd-9d0a-13e4403aa922/sqlcmd-output-line-limit?forum=sqltools
Back to top
View user's profile Send private message
paschott



Joined: 10 May 2006
Posts: 64

PostPosted: Mon Oct 07, 2013 1:26 pm    Post subject: Reply with quote

I'm pretty sure that the SQLCMD options referenced in that article have to do with OUTPUT not INPUT. Reading through the thread and looking at the options, it doesn't seem like they'd apply.

As for the file length, SQLCMD keeps going through the file after the failures. It just doesn't have a variable @pv to process the BLOB data in those particular batches.

Today I plan to try the scripts again without the option to break the transactions apart. Hopefully that will help. However, there still seems to be something off when choosing to split the script into multiple batches and declaring the @pv variable correctly. I'm pretty sure this worked in an earlier release, but don't recall which and I tweaked my scripts since then to disable triggers as well as PK/FK constraints when generating them.
Back to top
View user's profile Send private message
paschott



Joined: 10 May 2006
Posts: 64

PostPosted: Thu Oct 10, 2013 1:51 pm    Post subject: Reply with quote

Tried the option to generate a script as one single script, no transactions. I still got the error with @pv not being declared properly.

I really think there is an issue with Data Compare not declaring the @pv variable in the correct place if the options are chosen to disable FK constraints and triggers.
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