From Command Line : unrequired sp_refreshview statements
Spud
Posts: 23
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
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
Comments
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
The output states
SQL Compare Command Line V10.3.8.406
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
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
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.
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
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...
Great. Back to the drawing board / banging my head against the wall...
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...
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