Characters in SQL tables that powershell or SMO don't like

PowerShell Help
1

I found a Powershell script to script out all of my database objects. I found that certain characters such as * or \ aren’t liked. We use * to mark a table for deprecation and if you create a table without specifying schema it gets created with your network username domain\userid.

Anyway, either SMO or Powershell don’t like it. How can I filter objects with these characters so it doesn’t bomb our when running from SQL Agent?

It bombs on the outfile chunk of code, last code line.

$objs.Script($so)+”GO” | out-File $OutFile

#-power shell script
Set-Location d:
$path = “D:\ETLDeployment\D01_VISN05\ScriptObjects\”

$ServerName = “vhacdwdwhsql33”



[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName

$DateStr = Get-Date -format "yyyy-MM-dd"

$IncludeTypes = @(“tables”,”StoredProcedures”,”Views”,”UserDefinedFunctions”)

$ExcludeSchemas = @(“sys”,”Information_Schema”)





$so = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')

$so.IncludeIfNotExists = 0

$so.SchemaQualify = 1

$so.AllowSystemObjects = 0

$so.ScriptDrops = 0         #Script Drop Objects



$dbs=$serverInstance.Databases

foreach ($db in $dbs)

{

       $dbname = “$db”.replace(“[“,””).replace(“]”,””)

       $dbpath = “$path”+”$dbname” + “_” + “$DateStr” + “\”
if ($dbname -like 'D01_VISN05*') 
       {

    #if ( !(Test-Path $dbpath))

          # {$null=new-item -type directory -name “$dbname”-path “$path”}



       foreach ($Type in $IncludeTypes)

       {

              $objpath = “$dbpath” + “$Type” + “\”

         if ( !(Test-Path $objpath))

           {$null=new-item -type directory -name “$Type”-path “$dbpath”}

              foreach ($objs in $db.$Type)

              {

                     If ($ExcludeSchemas -notcontains $objs.Schema ) 

                      {

                           $ObjName = “$objs”.replace(“[“,””).replace(“]”,””)                  

                           $OutFile = “$objpath” + “$ObjName” + “.sql”

                           $objs.Script($so)+”GO” | out-File $OutFile

                      }

              }

       }     
       }
}
2

It’s probably going to help to see the error.
But I would guess that it’s $OutFile that makes it bomb if the path include * or an extra \ in the path.

So you would need to do some error handling on that.
If it’s on $ObjName the extra * or \ enters into the mix then do an if statement on that.
E.g.

if(($ObjName -like '*`**') -or ($ObjName -like '*\*'))
{
   # Change $ObjName to something else
}
3

Yes, exactly. File system doesn’t allow reserved characters in file. I don’t think I grasped exactly what the error was telling me at first. It isn’t powershell or SMO but the file system. I ended up adding a Replace function and just replaced any reserved characters with “zzz” so they would be easy to spot in file system.

function Replace-SpecialChars {
    param(
        [string]$InputString,
        [string]$Replacement  = "_zzz_",
        [string]$SpecialChars = "#?()[]{}/\*:"
    )

    $rePattern = ($SpecialChars.ToCharArray() |ForEach-Object { [regex]::Escape($_) }) -join "|"
    $InputString -replace $rePattern,$Replacement
}
4

I then call the function as:

$ObjName = Replace-SpecialChars $ObjName