Comments
Sort by recent activity
Any update on this? I posted in another thread about this (for varchar, not XML). Ideally even on case insensitive collated databases, changed-case string could be committed and transferred to other devs via SSC. I've even tried tricking it by updating a second field (like a datestamp). The behavior is that the other dev gets the new date value but the string value is left unchanged.
But the INSERT statements stored in the static data script in svn are picking up the correct case. / comments
Any update on this? I posted in another thread about this (for varchar, not XML). Ideally even on case insensitive collated databases, changed-case string could be committed and transferred to ot...
Here are the property values from my local box (linked to svn) and the dev server we push to:
Local:
<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<ISOCCompareLocation version="1" type="SvnLocation">
<RepositoryUrl>http://tco-dev-scm.xxx.internal/svn/code/trunk/Database/MigrationScripts/Intranet/</RepositoryUrl>
</ISOCCompareLocation>
Dev:
<?xml version="1.0" encoding="utf-16" standalone="yes"?> <ISOCCompareLocation version="1" type="SvnLocation"> <RepositoryUrl>http://tco-dev-scm.xxx.internal/svn/code/trunk/Database/MigrationScripts/Intranet/</RepositoryUrl> </ISOCCompareLocation>
They seem the same. Is it possible the way I call the command line, or the exclude filters, is causing a problem? Here are the command line params:
/exclude:role /exclude:user /exclude:assembly /sourcecontrol1 /Server2:TCO-DEV-SQL-02 /Database2:Intranet /sync /migrationfolder:C:\TeamCity\buildAgent\work\946b3977434c5179\database\migrationscripts\Intranet /scriptsfolderxml:C:\TeamCity\buildAgent\work\946b3977434c5179\Build\Shared\config\SQLCompareSvnConfig_Intranet_temp.xml /revision1:HEAD /versionusername1:builduser /versionpassword1:xxx
Thanks,
John / comments
Here are the property values from my local box (linked to svn) and the dev server we push to:
Local:
<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<ISOCCompareLocation version="1" type="...
It's similar actually. I have a support case open but we seem to be stuck. What happens is:
1) I choose to compare subversion HEAD to a shared database server.
2) The comparison results screen correctly shows objects that changed since the last sync with that server. In my case subversion is at 5034 and the dev SQL server is at 5018. The database extended property on the SQL server shows 5018.
3) But ... once I click Deployment Wizard, the "Review Migration Scripts" page shows pre-selected migration scripts from earlier revision. Also, the little circle-graph at the bottom says "The deployment script will be created as follows: 0 --> 4673 --> 4783 --> 5018 --> 5034" and if I click Next, it generates a script with all the old, unneeded changes.
If I uncheck the older migration scripts, it still scripts back to rev 0, just without the scripts.
It seem obvious that it's not "getting" that the target server is on rev 5018. There are no RedGate tools on the target SQL Server, but I'm not aware that they need to be there.
Any clues are appreciated ... I ma y be missing something obvious.
John / comments
It's similar actually. I have a support case open but we seem to be stuck. What happens is:
1) I choose to compare subversion HEAD to a shared database server.
2) The comparison results screen co...
I got this working eventually. Originally I was doing the compare from
script folder -> database
using the checkout folder on the build server. I used the new options in 10.1 to change this to
svn server -> database
This took a while as I had to install SQL Source Control on the build server, which meant I had to installed SQL Management Studio on the build server, etc. Currently What I have going includes a custom c# console app that wraps calls to SQL Compare and SQL Data Compare. Basically:
1) MSBuild file on build server has a list of databases to compare (17 for us).
2) MSBuild file calls our custom CompareDatabases console app once per database.
3) That app calls SQL Compare and SQL Data Compare. For SQL Compare it dynamically generates the XML config file mentioned above. For Data Compare it takes a stored list of tables to compare (static data) and dynamically builds an XML config file that specifies a destination server and either /sync or a script file name. The script file name comes from a build label set in the build server (happens to be TeamCtiy).
4) The build has a step to check in the generated SQL scripts, if applicable, for a DBA to review before we run them in staging/prod.
Thanks to everyone for the help. I'm happy to share details of how I did all this. / comments
I got this working eventually. Originally I was doing the compare from
script folder -> database
using the checkout folder on the build server. I used the new options in 10.1 to change this to
sv...
Glad you are starting a blog. this'll be really helpful. I'd be happy to write something but we are still running into issues so I will wait a bit.
The latest problem is that when comparing svn (HEAD revision) to a database, SQL Compare is trying to apply all historical changes instead of just those that differ between svn and the target database. This fails due to objects no longer existing, etc.
Command line is like this:
sqlcompare /exclude:role /exclude:user /exclude:a
ssembly /sourcecontrol1 /Server2:MYSERVER /Database2:MyDb /sync /migrationfolder:d:\code\trunk\database\migrationscripts\MyDb /scriptsfolderxml:d:
\code\trunk\Build\Shared\config\SQLCompareSvnConfig_MyDb_temp.xml /revision1
:HEAD /versionusername1:builduser /versionpassword1:mypass /verbose
There are just 3 objects that differ, but the sync is trying to run through earlier changes. If I change to script generation mode, it generates a ton of older stuff. It seemed like this was working, so I'm mystified. The target db shows svn revision 5018 in its extended properties, and the HEAD revision in svn is 5034. But changes scripted go way back before that. / comments
Glad you are starting a blog. this'll be really helpful. I'd be happy to write something but we are still running into issues so I will wait a bit.
The latest problem is that when comparing svn (...
The repository referenced in the target database's extended properties need to match the repository of the source. Can you check that this is the case?
Ah ha. No, it's not the case. We are testing this as part of a new process. The databases downstream from dev (staging, prod) have never had any changes rolled out from these tools yet, so they don't have these extended properties. How do they get into the database the first time? / comments
The repository referenced in the target database's extended properties need to match the repository of the source. Can you check that this is the case?
Ah ha. No, it's not the case. We are t...
I'm having similar problems with comparing a subversion scripts folder to a live database. I was on 10.1 (which doesn't have the options mentioned above) and was just using /Scripts1 to point to an svn checkout directory. At first it appeared to pick up migration scripts but I have not been able to get it to work again. I wish I knew more about how the two tools interact.
I also am wondering if maybe the scripts are being excluded to a perceived wrong db version .. I don't know how this is stored. Maybe db extended properties? I have a new column this exists in svn and a migration script. Staging server does not have the column. I want my command line SQL compare to grab the script and use it, but I don't see how. / comments
I'm having similar problems with comparing a subversion scripts folder to a live database. I was on 10.1 (which doesn't have the options mentioned above) and was just using /Scripts1 to point to a...
I figured it out. It looks like the /include:identical switch overrides /exclude switches, because when I took the include out it worked as expected.
This isn't the behavior I'd expect to see. I would think that
/include:identical /exclude:assembly /exclude:role
for example, would first exclude all roles and assemblies, and then produce a report of the remaining object types including identical ones. That's not how it works though ... I tried
/include:identical /exclude:role
to take assembly-related concerns out of the test, and it output a list that included roles ... / comments
I figured it out. It looks like the /include:identical switch overrides /exclude switches, because when I took the include out it worked as expected.
This isn't the behavior I'd expect to see. I ...
I've figured it out. It had to do with having UNSAFE c# CLR assemblies on the server. In that scenario the user must be in the sql admins group for the comparison to work, it seems. THat's why it worked when I ran it but not when the build system did.
We actually ended up revisiting usage of CLR functions and we going to stop making new ones, and also exclude them from sql source control. The actual C# project code is already in svn anyway.
Thanks for the suggestion.
John / comments
I've figured it out. It had to do with having UNSAFE c# CLR assemblies on the server. In that scenario the user must be in the sql admins group for the comparison to work, it seems. THat's why i...