So I’m busy working on my new dbaSecurityScan module, busily trying to write all the pester tests for the appveyor pipeline

Part of the testing for the module involves building a number of test scenarios, each of which of which needs a database spinning up for it.

On the Windows builds, that’s nice and easy as I just use this in the test script to loop through all the scenarios and run the .sql files :

ForEach ($file in (Get-ChildItem "$PSScriptRoot\scenarios" -File -Filter "*.sql" -recurse)){
(& sqlcmd -S "$sqlInstance" -U "sa" -P "Password1!$£$" -b -i "$($file.fullname)" -d "master")
}
view raw gistfile1.txt hosted with ❤ by GitHub

However, I’m developing on my Macbook Pro as I want this module to be nicely platform agnostic. SQL Server is running nicely in docker and dbatools can connect to it happily. But I just can’t get sqlcmd to work, lots of TCP Provider: Error code 0x102 and TCP Provider: Error code 0x2AF9 messages no matter what I try. So being short on time I though I’d try something else, and hopefully this will work when I get Linux build running as well

The scripts I want to run create databases, users, schemas and whole lot more, so there’s lots of batch separators (; and GO) in them.Invoke-DbaQuery is a great function, but it doesn’t like working with batch separators (which is not through want of trying, it’s just really tricky). So time to drop back to some raw SMO to run the scripts in with ExecuteNonQuery()

if ($script:IgnoreSQLCMD) {
$srv = Connect-DbaInstance -SqlInstance $script:appvSqlInstance -SqlCredential $script:appvSqlCredential
ForEach ($file in (Get-ChildItem "$PSScriptRoot\scenarios" -File -Filter "*.sql" -recurse)) {
$c = Get-Content $file -Raw
$srv.Databases['master'].ExecuteNonQuery($c)
}
} else {
ForEach ($file in (Get-ChildItem "$PSScriptRoot\scenarios" -File -Filter "*.sql" -recurse)){
(& sqlcmd -S "$script:appvsqlInstance" -U "sa" -P "Password12!" -b -i "$($file.fullname)" -d "master")
}
}
view raw gistfile1.txt hosted with ❤ by GitHub

I’ve added a variable to my builds that lets me pick when I want to run via SQLCMD or via SMO. At the minute I’m just using it on my machine, but if I run into problems later, it’s nice to now I can just toggle by looking at which platform it’s running.