User profile for Phil_Fact0r

(I'm only a FoRG, not a Redgate employee.) The Error handling system in a SQL Compare script is there to prevent the script from carrying on after an error. Generally, you really shouldn't want it to carry on. The option removes this system in the scripts that catches those errors that do not otherwise automatically stop the batch or the entire script from being executed. The idea is to ensure that even an apparently trivial error rolls the script back. Unfortunately, some SQL Server errors can't be caught this way. The only way to prevent more serious errors, up to level 20 stopping the script is to use a try...catch block for every block or batch, but even then, you won't catch all errors, and I doubt whether it would have caught the one you had. . An object name resolution error is not caught by the TRY...CATCH construct. / comments

(I'm only a FoRG, not a Redgate employee.) The Error handling system in a SQL Compare script is there to prevent the script from carrying on after an error. Generally, you really shouldn't want it ...

0 votes

I'll answer my own question about how to get a group. There is another Cmdlet called Get-SQLMonitorMainGroups that seems to do what I want. One then gets subgroups using Get-SqlMonitorSubGroups. I'm still mystified about what Get-SQLMonitorGroup does. / comments

I'll answer my own question about how to get a group. There is another Cmdlet called Get-SQLMonitorMainGroups that seems to do what I want. One then gets subgroups using Get-SqlMonitorSubGroups. I'...

0 votes

This should do it. Sorry about the quotename stuff. I hardly ever seem to use columns with illegal characters in them. I meant to add something clever that added the quotes only when necessary but never got around to it. This should work nicely /** Summary: > This is a temporary batch for creating such things as table variable, temporary tables or anything else that needs a column list Author: Phil Factor **/ Declare @TheExpressionNVARCHAR(MAX)= N'$SELECTEDTEXT$' SELECT 'DECLARE @$NameOfVariable$ table ('+ Stuff ((SELECT ', '+QuoteName(Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name)) + ' '+ System_type_name + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE ''END+' NULL' --+ CASE WHEN collation_name IS NULL THEN '' ELSE ' COLLATE '+collation_name END AS ThePath FROM sys.dm_exec_describe_first_result_set (@TheExpression, NULL, 1)AS f -- use sys.sp_describe_first_result_set for a batch LEFT OUTER JOIN (SELECT name AS name FROM sys.dm_exec_describe_first_result_set (@TheExpression, NULL, 0) WHERE is_hidden=0 GROUP BY name HAVING Count(*)>1) AS DetectDuplicateNames ON DetectDuplicateNames.name=f.name WHERE f.is_hidden=0 ORDER BY column_ordinal FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,1,'')+')' / comments

This should do it. Sorry about the quotename stuff. I hardly ever seem to use columns with illegal characters in them. I meant to add something clever that added the quotes only when necessary but...

0 votes

@pmenard I may have misunderstood the problem, but you can easily use SCA in order to create and build a whole lot of clean databases recreated from scratch every time. It is probably best not to wire this into SCA as a feature because it is so simple to do. I like the way that SCA actually allows you to do things the way you prefer. The trick is to delete the database and re-create it before you do the release. Because all your releases are identical (same source and the target is an empty database) you can use just one Release object. This saves a lot of time! Here is the whole script, including the database deletion and recreation. Please let me know if it isn't quite what you're looking for and I'll fix it. Import-Module SqlChangeAutomation import-Module sqlserver <# here you specify the source of the release, a source control directory in our case but it could be a single build script or a connection string to a source database You need to specify the project details under 'projects' and then a list of connection strings for each of the targets on which you'd like a clean install #> $Databases = @{ 'source' = 'MyPathToTheScriptDirectory'; 'Project' = @{ 'name' = 'KillnFill'; 'version' = '1.4.5'; 'description' = 'This demonstrates how to do a clean build' } 'CleanTargets' = @( <# list of connection strings for each of the targets on which you'd like a clean install #> 'Server=Philf01;Database=Thomas;User Id=PhilFactor;Password=Wouldntyouliketoknow;Persist Security Info=False', 'Server=Philf01;Database=Richard;User Id=PhilFactor;Password=Wouldntyouliketoknow;Persist Security Info=False', 'Server=Philf01;Database=Harold;User Id=PhilFactor;Password=Wouldntyouliketoknow;Persist Security Info=False', 'Server=Philf01;Database=Ebeneezer;User Id=PhilFactor;Password=Wouldntyouliketoknow;Persist Security Info=False' ) } $ReleaseArtifact = $null; #start off with a null release artifact so you know when to make one $databases.CleanTargets | foreach { $csb = New-Object System.Data.Common.DbConnectionStringBuilder $csb.set_ConnectionString($_) #we need to remove the database to connect at server level $connectionString = $csb.Remove('database') try # now we make an SMO connection to the server, using the modified connection string { $sqlConnection = new-object System.Data.SqlClient.SqlConnection $csb.ConnectionString $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection $sqlConnection $srv = new-object Microsoft.SqlServer.Management.Smo.Server $conn } catch { Write-error "Could not connect to SQL Server instance '$csb.server': $($error[0].ToString() + $error[0].InvocationInfo.PositionMessage). Script is aborted" exit -1 } $csb.set_ConnectionString($_) #restore the connection string as we need the name of the database $Db = $csb.'database' #if the database already exists, then kill it If (@($srv.Databases | % { $_.Name }) -contains $csb.'Database') # search for the name { $srv.KillDatabase($csb.'Database') } # if it is there then kill it # in one of my articles, I show how to write out any changes # Now we create the database $DestinationDatabaseObject = ` New-Object Microsoft.SqlServer.Management.Smo.Database ($srv, $csb.'Database') $DestinationDatabaseObject.Create() # double check that it all worked if ($DestinationDatabaseObject.name -ne $csb.Database) { Write-error "Could not create database $($csb.'Server').$($csb.'Database')): $($error[0].ToString() + $error[0].InvocationInfo.PositionMessage). Script is aborted" exit -1 } if ($ReleaseArtifact -eq $null) { #only do this once since all release artifiacts will be the same Write-Verbose "Now creating the build object using ( $($csb.'Server').$($csb.'Database'))" <# we create a build artifact so we can put in the package version etc#> $buildArtifact = $databases.source | New-DatabaseProjectObject | #wrap up the script and create a build artefact New-DatabaseBuildArtifact -PackageId $Databases.Project.name -PackageVersion $Databases.Project.version ` -PackageDescription $Databases.Project.Description Write-verbose "Now creating the release object using ( $($csb.'Server').$($csb.'Database'))" $ReleaseArtifact = new-DatabaseReleaseArtifact ` -Source $buildArtifact ` -Target $csb.ConnectionString ` -AbortOnWarningLevel None -SQLCompareOptions IgnoreSystemNamedConstraintNames } #at this point, we will have the release object so we can use it Use-DatabaseReleaseArtifact -InputObject $ReleaseArtifact -DeployTo $csb.ConnectionString } / comments

@pmenard I may have misunderstood the problem, but you can easily use SCA in order to create and build a whole lot of clean databases recreated from scratch every time.It is probably best not to wi...

0 votes

Yes, it is certainly good to cater for those application developers who are more comfortable with a migrations approach, and Flyway is a popular system. I hope that Redgate continues to ensure that SCA offers give first-class features and support for the 'static' approach to database deployment, which is more popular with database developers / comments

Yes, it is certainly good to cater for those application developers who are more comfortable with a migrations approach, and Flyway is a popular system. I hope that Redgate continues to ensure that...

0 votes

How can we help you today?

Phil_Fact0r

Comments