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

What Monitored Objects can be assigned to a group?

I've always just assigned servers to groups, but I read in the documentation that you can't assign instances to groups but you can assign a cluster. you can, of course assign a group. What other r...

1 follower 1 comment 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

Get-SQLMonitorGroup doesn't do what one would expect.

I have three groups, Staging, Development and ProductionYour documentation says this.... <div>  -------------------------- EXAMPLE 1 --------------------------</div><div>    </...

1 follower 1 comment 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

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

@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

(Later) reinstalled the SCA in SSMS in its latest version. This time it worked when one hit 'refresh'. and produced a scripts directory just like the good old days. I generally use SQL Compare for this, but this could be handy. / comments

(Later) reinstalled the SCA in SSMS in its latest version. This time it worked when one hit 'refresh'. and produced a scripts directory just like the good old days. I generally use SQL Compare for...

0 votes

I tried clicking the link but that is talking about Visual Studio, which I don't use. I have SQL Change Automation in SSMS. Does this do the same thing? I've tried starting a workspace and putting a development database in it but I can't find a way of generating the object-level scripts (I think you call this the offline schema model) / comments

I tried clicking the link but that is talking about Visual Studio, which I don't use. I have SQL Change Automation in SSMS. Does this do the same thing? I've tried starting a workspace and putting...

0 votes

I'd still be grateful to know whether SCA will remain eclectic, and support conventional object-level scripts as well as migration scripts? / comments

I'd still be grateful to know whether SCA will remain eclectic, and support conventional object-level scripts as well as migration scripts?

0 votes

How can we help you today?

Phil_Fact0r

Activity overview