From Command Line : unrequired sp_refreshview statements

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

From Command Line : unrequired sp_refreshview statements

Postby Spud » Thu Mar 28, 2013 1:27 pm

I am finding that the script output by sqlcompare 10 from the command line often outputs sp_refreshview statements when seemingly none are required.

e.g. the script includes one correct object creation statement for a brand new table with no relationships to other objects at all, and yet includes a raft of sp_refreshview statements.

I've seen somewhere on the redgate site (typically can't find the link now) that hinted that these statements may be included if the view in question included "select *.." in the definition, which I could understand, but that isn't the case I'm seeing as column names are used throughout.

After trawling through the forums on this, it sounds as though the issue of creating unrequired sp_refreshview statements was resolved in SC-4542.

Is there something I'm missing?

NB Command line I'm using is..

"C:\\Program Files (x86)\\Red Gate\\SQL Compare 10\\sqlcompare.exe" /pr:"C:\\Users\\CIBuildUser\\Documents\\SQL Compare\\SharedProjects\\DBName (Head) v DBName.scp" /scriptfile:deploy_DBName.sql /include:identical /force /include:identical /Options:Default,ForceColumnOrder,IgnoreCollations,oec,IncludeDependencies
Spud
 
Posts: 16
Joined: Thu Mar 21, 2013 3:08 pm
Location: United Kingdom

Postby eddie davis » Mon Apr 01, 2013 12:26 pm

Hi Spud

Thank you for your forum post.

A support call has been created for you, the call reference is F0071264.

This problem was first reported in V8 of SQL Compare. A fix for the issue was released in V9.5. However there is the possibility that something added to V10 may have re-introduced the reported problem.

What exact version of SQL Compare V10 you are using?

Many Thanks
Eddie
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Postby Spud » Tue Apr 02, 2013 9:47 am

Hi Eddie.
The output states
SQL Compare Command Line V10.3.8.406
Spud
 
Posts: 16
Joined: Thu Mar 21, 2013 3:08 pm
Location: United Kingdom

Postby Spud » Tue Apr 09, 2013 10:35 am

Hi redgate / anybody.

Any news on this? It's becoming an issue as the scripts are far larger than they should be, and are taking 10 minutes to execute rather than a few seconds, meaning we'd have to manually alter the scripts - removing the point of having it produced as part of the build in the first place, and the validity of purchasing an automation license.

e.g if I create a brand new table "EddiesTable" with no dependencies to any other objects, it still adds sp_refreshview statements for each view in the database, where there really is no need.

For some reason it does this on 2 of our 3 databases in this project. If you have a clue as to what causes this issue to kick in / not lick in I may be able to work out a workaround.

Cheers :)
Spud
 
Posts: 16
Joined: Thu Mar 21, 2013 3:08 pm
Location: United Kingdom

Postby eddie davis » Tue Apr 09, 2013 4:18 pm

Hi Spud

It looks like a regression bug to me, I was able to recreate the symptoms you are experiencing using SQL Compare V10.3.8.406.

Sadly I do not have an ETA for a fix to the problem. Have reopened Bug Report SC-4542 and alerted the development team to the problem.

The only workarounds I can provide to you are:
    1. As you have been performing, editing the script to remove the sp_refreshview statements.

    2. Leave the sp_refresh statements in the script and allow for the additional time taken to run the script.

    3. Use the SQL Compare GUI to generate the script


I suspect none of the above workarounds will help as you are using SQL Compare Command Line as part of your build process. Until the development team fix the issue, I cannot provide no other solutions to you.

Many Thanks
Eddie
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Postby Brian Donahue » Mon Apr 22, 2013 2:23 pm

If this is the problem I am thinking of, it's not a bug and you should be able to work around it by putting /Exclude:Identical in the command. Views that have differences, or are dependencies of included objects, will still be refreshed, but using this argument, you should no longer get refreshes done on views that are identical in both databases.

SQL Compare scripts a refresh for all views that are "included" by design. However, I don't have all the information to know whether or not this will work in your case. It's worth a try, though.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Re:

Postby Spud » Wed Apr 24, 2013 2:32 pm

Brian Donahue wrote:If this is the problem I am thinking of, it's not a bug and you should be able to work around it by putting /Exclude:Identical in the command.


Thanks for the reply Brian.

Unfortunately... "Error: The /project switch cannot be used on conjunction with the /exclude switch"

Is there any other way around this?
Does this mean the "preferred" way of using the command line isn't by creating and referencing a project file?

cheers
Spud
 
Posts: 16
Joined: Thu Mar 21, 2013 3:08 pm
Location: United Kingdom

workaround...

Postby Spud » Wed May 01, 2013 9:50 am

Ok after a little investigation there are some funny shenanigans going on with the command line arguments, and what causes all these statements to be generated, but at least I have a workaround.


If I run this:
"C:\\Program Files (x86)\\Red Gate\\SQL Compare 10\\sqlcompare.exe" /project:"C:\\Users\\MyUser\\Documents\\SQL Compare\\SharedProjects\\redgateprojectfile.scp" /scriptfile:output.sql /password1:password /force /verbose /Options:Default,ForceColumnOrder,IgnoreCollations,oec,IncludeDependencies
then it doesn't create the sp_refreshview statements

But it I then append either of these to the end:
a) "/Report:output.html /reportType:Simple"
b) "/include:identical"

then in each case any change cause sqlcompare to include a sp_refreshview statement for every view in the database, even if the only change was (for example) creating a new table "hello" which has no dependant objects in the database.

Therefore if I run two statements instead, both of which start:
"C:\\Program Files (x86)\\Red Gate\\SQL Compare 10\\sqlcompare.exe" /project:"C:\\Users\\MyUser\\Documents\\SQL Compare\\SharedProjects\\redgateprojectfile.scp" /password1:password /force /verbose /Options:Default,ForceColumnOrder,IgnoreCollations,oec,IncludeDependencies

and apend this to the first:
/include:identical /Report:output.html /reportType:Simple
and this to the second
/scriptfile:output.sql

Then I've created my report and output file correctly (without the statements) but with the downside that obviously the build will take longer due to running the statement twice.

Hope this helps someone anyway.
I'll let you raise a bug if you think it needs one, as there's obviously something a bit quirky going on...
Spud
 
Posts: 16
Joined: Thu Mar 21, 2013 3:08 pm
Location: United Kingdom

ignore previous post

Postby Spud » Wed May 01, 2013 10:20 am

ignore my previous post. Obviously *without* the "/include:identical" on the statement the creates the scripts then error 63 is raised when there are no differences in the database.

Great. Back to the drawing board / banging my head against the wall...
Spud
 
Posts: 16
Joined: Thu Mar 21, 2013 3:08 pm
Location: United Kingdom

?

Postby Spud » Tue May 07, 2013 2:00 pm

Hi redgate.
Has this been reproduced in-house?

The same results occurred when using the argument xml file with sql compare as well, which after some initial response via email has all gone quiet as well.

If I get confirmation that it' a bug that has no "fix by" date then I can move on...
Spud
 
Posts: 16
Joined: Thu Mar 21, 2013 3:08 pm
Location: United Kingdom


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests