Red Gate forums :: View topic - From Command Line : unrequired sp_refreshview statements
Return to www.red-gate.com RSS Feed Available

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

From Command Line : unrequired sp_refreshview statements

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



Joined: 21 Mar 2013
Posts: 16
Location: United Kingdom

PostPosted: Thu Mar 28, 2013 1:27 pm    Post subject: From Command Line : unrequired sp_refreshview statements Reply with quote

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
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 938
Location: Red Gate Software

PostPosted: Mon Apr 01, 2013 12:26 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Spud



Joined: 21 Mar 2013
Posts: 16
Location: United Kingdom

PostPosted: Tue Apr 02, 2013 9:47 am    Post subject: Reply with quote

Hi Eddie.
The output states
SQL Compare Command Line V10.3.8.406
Back to top
View user's profile Send private message
Spud



Joined: 21 Mar 2013
Posts: 16
Location: United Kingdom

PostPosted: Tue Apr 09, 2013 10:35 am    Post subject: Reply with quote

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 Smile
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 938
Location: Red Gate Software

PostPosted: Tue Apr 09, 2013 4:18 pm    Post subject: Reply with quote

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



Joined: 23 Aug 2004
Posts: 6673

PostPosted: Mon Apr 22, 2013 2:23 pm    Post subject: Reply with quote

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



Joined: 21 Mar 2013
Posts: 16
Location: United Kingdom

PostPosted: Wed Apr 24, 2013 2:32 pm    Post subject: Re: Reply with quote

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



Joined: 21 Mar 2013
Posts: 16
Location: United Kingdom

PostPosted: Wed May 01, 2013 9:50 am    Post subject: workaround... Reply with quote

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



Joined: 21 Mar 2013
Posts: 16
Location: United Kingdom

PostPosted: Wed May 01, 2013 10:20 am    Post subject: ignore previous post Reply with quote

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



Joined: 21 Mar 2013
Posts: 16
Location: United Kingdom

PostPosted: Tue May 07, 2013 2:00 pm    Post subject: ? Reply with quote

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