User profile for bhopenw

David, You are right on when you say: Do you need a mechanism to parameterise these database names and replace with values on synchronization, depending on what the target database is? That is what I am looking for, I know I can write this pretty easily in powershell using a config xml that holds the linked servers for each test region. Currently, we exclude any stored proc that uses a linked server, this scares me b/c if any of these procs need to be changed it will be a manual action in each test region. (FYI we have 7 test regions here) Ideally, there should not be a need for manual actions once our automation is set up. By the way I am very impressed with power of the cmd-line for SQLCompare, combo it with powershell and you got a very powerful Database migration automation platform. I believe I will start coding up the search and replace pre-deployment scripts b/c I think this will be best way to achieve my goal of total automation. I am still open to any and all ideas out there please don't be shy BJHop / comments

David, You are right on when you say: Do you need a mechanism to parameterise these database names and replace with values on synchronization, depending on what the target database is? That is w...

0 votes

Okay this some what pieced together b/c my scripts run on a platform of scripts we have developed and would not make a whole lot o sense if I posted them stright up Below is the TeamBuild targets that could be used we use team build to set up the workspace where the scripts folder is we also have argument xml checked and this is what we call from the cmd line, makes it easy and we have a record of what is being called <Target Name="AfterBuildDrop" Condition="$(SyncDB) == 'true'"> <AddBuildStep BuildUri="$(BuildUri)" BuildStepName="DB_Sync" BuildStepText="Search DB_ Dev for latest HRLYCLDL Scripts" /> <Exec Command="powershell -c ".\BuildScripts\DB_Sync.ps1 '$(SolutionRoot)' '$(DropLocation)'"" WorkingDirectory="$(PowerShellScripts)" IgnoreExitCode="true"> <Output TaskParameter="ExitCode" PropertyName="DB_ExitCode" /> </Exec> <Error Condition="$(DB_ExitCode)==2" Text="There was an unhandle exception in the DB Sync script " /> <Error Condition="$(DB_ExitCode)==1" Text="General error code." /> <Error Condition="$(DB_ExitCode)==8" Text="Unsatisfied argument dependency or violated exclusion when user runs command line. E.g. /arg2 depends on /arg1 but you have specified /arg2 without specifying /arg1, or alternatively /arg2 cannot be used with /arg1 but you have tried to use them both." /> <!-- should have all the Red Gate error msg catches here <UpdateBuildStep BuildUri="$(BuildUri)" BuildStepName="DB_Sync" BuildStepResult="true" /> <OnError ExecuteTargets="OnErrorDB_Sync"/> </Target> <Target Name="OnErrorDB_Sync" > <UpdateBuildStep BuildUri="$(BuildUri)" BuildStepName="DB_Sync" BuildStepResult="false" /> </Target> Now here is the DB_Sync.ps1 param($slnRoot = $(throw 'The snl root path is needed'), $dropLoc = $(throw 'The Build Drop location is required')) trap { $host.SetShouldExit(2) return $false } function get-ErrorMsg ([int] $exitCode) { # These messages were copied from the SQLCompare.exe -help section #if SQL Compare is updated need to ensure that the below holds true [string] $errMsg = "" switch ($exitCode) { 1 { $errMsg = "General error code." } 3 { $errMsg = "Illegal argument duplication. Some arguments may not appear more than once in a command-line. If such arguments appear more than once this exit code will be returned." } 8 { $errMsg = "Unsatisfied argument dependency or violated exclusion when user runs command line. E.g. /arg2 depends on /arg1 but you have specified /arg2 without specifying /arg1, or alternatively /arg2 cannot be used with /arg1 but you have tried to use them both." } 32 { $errMsg = "Value out of range. Numeric value supplied for an argument that is outside the range of valid values for that argument." } 33 { $errMsg = "Value overflow. The magnitude of a value supplied for an argument is too large and causes an overflow." } 34 { $errMsg = "Invalid value. The value supplied for an argument is invalid." } 35 { $errMsg = "No / invalid software license or trial period has expired." } 63 { $errMsg = "The databases being compared are identical with respect to any constraints on objects being compared." } 64 { $errMsg = "General command-line usage error." } 65 { $errMsg = "Data error. Some input data required by the tool is invalid or corrupt." } 69 { $errMsg = "A resource or service required to run the tool is unavailable." } 73 { $errMsg = "Failed to create report" } 74 { $errMsg = "IO error occurred. Generally returned if the program attempts to write to a file that already exists without the user having specified the /force option." } 77 { $errMsg = "Action cannot be completed because the user does not have permission." } 126 { $errMsg = "Execution failed because of an error." } 130 { $errMsg = "Execution stopped because Ctrl+Break." } } return $errMsg } ###MAIN### #set XML args paths $sqlCompareArgsPath_sync = Join-Path $slnRoot "\XmlArgs\Sync.xml" #set output area $outputResults = Join-Path $dropLoc "SyncResults.log" pushd "C:\Program Files\Red Gate\SQL Compare 8" #run sync job $rs = .\sqlcompare.exe /argfile:"$($sqlCompareArgsPath_Sync)" #catch exit code [int] $sqlExitCode = $lastExitCode $rs | Out-File -FilePath $outputResults -Force -Append if ($sqlExitCode -ne 0) { [string] $errMsg = get-ErrorMsg $sqlExitCode write-error $errMsg $host.SetShouldExit($sqlExitCode) } popd This has been simplified but is should work I hope this helps Thanks, BJHop / comments

Okay this some what pieced together b/c my scripts run on a platform of scripts we have developed and would not make a whole lot o sense if I posted them stright up Below is the TeamBuild targets t...

0 votes

I am unaware of the articles you talk about but I would believe that the easiest way to call SQL Compare via MSBuild would be to use the <EXEC> task (http://msdn.microsoft.com/en-us/library/x8zx72cd.aspx) to call the SQLCompare.exe (need SQL Compare Pro). Furthermore, I would utilize the arguments xml (http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Compare&c=SQL_Compare/help/7.0/sc_cl_usingxmltospecifyclarguments.htm&toc=SQL_Compare/help/7.0/toc356448.htm) when calling SQLCompare.exe With that said, I would take it one step further and call powershell via EXEC task to make the call to SQLCompare.exe, this way you have power of powershell to basically do what ever you want. Powerhsell and MSBuild work great together and are much quicker to get working than using the ITask class via C# to write a custom task. Plus your scripts are not coupled with MSBuild when using powershell as they are when using C# and ITask. Let me know if this help or if you have any other questions I can even post the powershell script I use to call SQLCompare.exe BJHop MCTS-TFS / comments

I am unaware of the articles you talk about but I would believe that the easiest way to call SQL Compare via MSBuild would be to use the <EXEC> task (http://msdn.microsoft.com/en-us/library/x8zx72c...

0 votes

Eddie, Thank you for your response I guess I forgot to mention that we looked into the registry at all the keys for both Red Gate, MSSCCI and TFS All seemed to correct including the below path you ...

0 votes

1. Is the registry key for RoboSource Control in HKEY_LOCAL_MACHINE\software\SourceCodeControlProvider pointing to a valid key? Yes 2. Does your user have permissions set to access the registry? User is local Admin of Box I appreciate all the help and do want to resolve this issue but this is only PC that we are having issues with from about 10 that we have installed it on. We do plan on installing on many more machines (40+) but that work is a couple weeks off. With that said I am willing to right this off as local to this particular server and that it is not a Red Gate issue but some configuration issue. Thanks for you help I will continue to work to see if I can fix if I do i will post the fix. bjhop / comments

1. Is the registry key for RoboSource Control in HKEY_LOCAL_MACHINE\software\SourceCodeControlProvider pointing to a valid key? Yes 2. Does your user have permissions set to access the registry? U...

0 votes

I just wanted to let everyone know that Version 7.1 of SQL Compare Pro does indeed fix the issue we were having with our Stored Proc The only catch we had was that all of the files were added to TFVC meaning spName.sql, spName1.sql, and so forth. Which caused the compare to fail b/c of multiple stored Proc w/ same name where found. Once we deleted the dups from TFVC all was well. Thanks, BJHop / comments

I just wanted to let everyone know that Version 7.1 of SQL Compare Pro does indeed fix the issue we were having with our Stored Proc The only catch we had was that all of the files were added to TF...

0 votes

FYI Below is the response I got back from RedGate after they examined the Stored Proc in question. Brian, Iâ€™ve had a look at the stored proc michelle sent me, and it seems the parser within SQL Compare does not like variables as the last clause of a CONTAINSTABLE expression, which this stored procedure has several examples of. This will be fixed in version 7.1, which will be released within the next couple of weeks. Simon Cooper / comments

FYI Below is the response I got back from RedGate after they examined the Stored Proc in question. Brian, Iâ€™ve had a look at the stored proc michelle sent me, and it seems the parser within SQL ...

0 votes

Michelle, The Syntax checked out w/o error. This stored proc is our largest by far, some 600 lines, furthermore, it is our only proc that uses Full text search. I do not believe that these would have anything to do with it but I thought I throw it out there nonetheless. Thanks, BJHop / comments

Michelle, The Syntax checked out w/o error. This stored proc is our largest by far, some 600 lines, furthermore, it is our only proc that uses Full text search. I do not believe that these would ha...

0 votes

Michelle Thanks for you reply and I am sorry it have taken me so long to respond I was on a vacation, last week I. I will have my DBA guys look at the Syntax, today. Hopefully they find something, if not I will send you the file. Thanks a million BJHop / comments

Michelle Thanks for you reply and I am sorry it have taken me so long to respond I was on a vacation, last week I. I will have my DBA guys look at the Syntax, today. Hopefully they find something,...

0 votes

Okay Question: Will SQL Compare be able to view this source as a Scripts Folder? I see a RedGate.scc file where with SQLCompre/SQLChangeSet its RedGateDatabaseInfo.xml. Meaning, can I call SQL Compare from the cmd line after the source has been downloaded to a local workspace? thanks, BJHop / comments

Okay Question: Will SQL Compare be able to view this source as a Scripts Folder? I see a RedGate.scc file where with SQLCompre/SQLChangeSet its RedGateDatabaseInfo.xml. Meaning, can I call SQL Comp...

0 votes

How can we help you today?

bhopenw

Comments