From Command Line : unrequired sp_refreshview statements

Spud

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

Eddie D

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

Spud

Hi Eddie.
The output states
SQL Compare Command Line V10.3.8.406

Spud

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

Eddie D

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

Brian Donahue

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.

Spud

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

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

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

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...

Mark J

Hi there

I think this behaviour is improved in our latest Frequent Updates release (11.1.7.47) - Compare should now refresh views less often; it will no longer refresh views unrelated to the deployment and will refresh no views if the 'Include Dependencies' option is disabled