Red Gate forums :: View topic - Comparison of source control & database not refreshing views
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

Comparison of source control & database not refreshing views

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



Joined: 07 Sep 2011
Posts: 14
Location: Australia

PostPosted: Fri May 04, 2012 6:24 am    Post subject: Comparison of source control & database not refreshing views Reply with quote

I've just recently switched from script vs database comparison to source control vs database comparison (in order to support migration scripts) and noticed that views are no longer being refreshed when objects they are dependent on are modifed (i.e. a table is changed and views that use it don't get refreshed).

Is this a bug or is there a setting to fix this behaviour? My options are currently 'if,ifg,iw,ie,iq,nt,iu,iup,ip,nc' and I've tried adding 'incd' (include dependencies) but this works the reverse way (modify a function that uses a view and the view gets refreshed).
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6577

PostPosted: Mon May 07, 2012 11:56 am    Post subject: Reply with quote

SQL Compare is supposed to include an sp_refreshview for every underlying table that changes - can you post the whole command you are using?
Back to top
View user's profile Send private message
matt_hosking



Joined: 07 Sep 2011
Posts: 14
Location: Australia

PostPosted: Tue May 08, 2012 12:10 am    Post subject: Reply with quote

Here it is:
"C:\Program Files (x86)\Red Gate\SQL Compare 10\SQLCompare.exe" /sourcecontrol1 /revision1:Latest /scriptsfolderxml:"%teamcity.build.checkoutDir%\Build\compare.xml" /migrationfolderxml:"%teamcity.build.checkoutDir%\Build\migration.xml"
/scripts2:"%teamcity.build.checkoutDir%\sourcedb" /options:if,ifg,iw,ie,iq,nt,iu,iup,ip,nc /scriptfile:"%teamcity.build.checkoutDir%\Build\Scripts\%Version%b.sql"

Where %Version% = 4.0.0 (at the moment), check out dir is agent-side default check out dir and the sourcedb folder is the source-controlled (SQL Source Control) schema folder for the version to compare against (couldn't see how to supply 'scriptsfolderxml' for the second source control).

Compare.xml
<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<!--
SQL Compare 10
SQL Compare
Version:10.1.0.102-->
<ISOCCompareLocation version="1" type="SvnLocation">
<RepositoryUrl>http://[removed]/trunk/database/Schema/</RepositoryUrl>
</ISOCCompareLocation>

Migration.xml
<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<!--
SQL Compare 10
SQL Compare
Version:10.1.0.102-->
<ISOCCompareLocation version="1" type="SvnLocation">
<RepositoryUrl>http://[removed]/trunk/database/Migration Scripts/</RepositoryUrl>
</ISOCCompareLocation>
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6577

PostPosted: Tue May 08, 2012 9:34 am    Post subject: Reply with quote

You will need to add the incd option for the refreshview to be scripted. Ommitting that is the only way I can reproduce the problem.

If this doesn't work, then your database in particular must have a dependency chain that is confusing to SQL Compare. We'll probably need schema snapshots to reproduce the issue in house so we can try to fix it.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6577

PostPosted: Tue May 08, 2012 9:37 am    Post subject: Reply with quote

Also, do you get refreshviews when you don't use your deployment scripts? The deployment script is going to replace whatever SQL Compare would have generated so if someone removed the refreshview from the deployment script, that could also have an effect, I think...
Back to top
View user's profile Send private message
matt_hosking



Joined: 07 Sep 2011
Posts: 14
Location: Australia

PostPosted: Wed May 09, 2012 12:11 am    Post subject: Reply with quote

Hi Brian,
Thanks for the reply, but as I mentioned, I've already tried the 'incd' option, which appears to do the reverse - updates only objects that the current object is dependent on (e.g. function depends on a view), rather than the other way - update the objects that depend on the current object (e.g. views that depend on a table), which I want.

Could this have something to do with migration scripts? Have you tested using the exact command line parameter set? I've found that they do seem to interact somewhat in expected ways when used differently from the norm.
Back to top
View user's profile Send private message
matt_hosking



Joined: 07 Sep 2011
Posts: 14
Location: Australia

PostPosted: Wed May 09, 2012 2:17 am    Post subject: Reply with quote

Solved it - it appears that without the 'Default' option included, the 'sp_refreshview' calls are never included (at least for me on 10.1). So my new set of options is:

Default,ie,iq,nt,iu,ip,nc

and everything is working. This should have been equivalent to:

if,ifg,iw,ie,iq,nt,iu,iup,ip,nc,incd

shouldn't it? This set was only missing 'DecryptPost2KEncryptedObjects', 'IgnoreWithElementOrder' and 'IgnoreDatabaseAndServerName' but none of those should have affected the dependencies I would think. Is there something undocumented in the 'Default' option?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6577

PostPosted: Wed May 09, 2012 9:39 am    Post subject: Reply with quote

There is a description of the default options when you run the program using the /? /v arguments:
Quote:
Default (Alias: d) -
The default options for a sensible comparison: IgnoreFillFactor,
IgnoreWhiteSpace, IncludeDependencies, IgnoreFileGroups,
IgnoreUserProperties, IgnoreWithElementOrder,
IgnoreDatabaseAndServerName, DecryptPost2kEncryptedObjects.
Back to top
View user's profile Send private message
matt_hosking



Joined: 07 Sep 2011
Posts: 14
Location: Australia

PostPosted: Thu May 10, 2012 12:08 am    Post subject: Reply with quote

I read that part (which is how I deduced the options from default I was missing), but as I mentioned, these missing options shouldn't have caused the omission of sp_refreshview or constraints handling. Is there any reason why the default option would be required in order to get this to work? Could it be specific to comparing source control to scripts and / or with migration scripts enabled?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6577

PostPosted: Thu May 10, 2012 9:55 am    Post subject: Reply with quote

I checked the source code, and the only condition around scripting the refresh was whether or not the dependencies option was listed.

I'll have to check the command-line parser code.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6577

PostPosted: Thu May 10, 2012 11:12 am    Post subject: Reply with quote

I don't see a problem, so far. And if I use your option set on the command line with verbose on it says include dependencies is set...
Quote:
SQL Compare running with option: IgnoreFillFactor (OK).
SQL Compare running with option: IgnoreFileGroups (OK).
SQL Compare running with option: IgnoreWhiteSpace (OK).
SQL Compare running with option: IgnoreExtendedProperties (OK).
SQL Compare running with option: IgnoreQuotedIdentifiersAndAnsiNullSettings (OK)
.
SQL Compare running with option: NoTransactions (OK).
SQL Compare running with option: IgnoreUsersPermissionsAndRoleMemberships (OK).
SQL Compare running with option: IgnoreUserProperties (OK).
SQL Compare running with option: IgnorePermissions (OK).
SQL Compare running with option: DoNotOutputCommentHeader (OK).
SQL Compare running with option: IncludeDependencies (OK).
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