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

This topic contains 3 replies, has 2 voices, and was last updated by  Thom 5 months ago.

  • Author
  • #67012


    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”
    $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
    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
  • #67156

    Fredrik Kacsmarck

    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.

    if(($ObjName -like '*`**') -or ($ObjName -like '*\*'))
       # Change $ObjName to something else
    • #67191


      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 {
              [string]$Replacement  = "_zzz_",
              [string]$SpecialChars = "#?()[]{}/\*:"
          $rePattern = ($SpecialChars.ToCharArray() |ForEach-Object { [regex]::Escape($_) }) -join "|"
          $InputString -replace $rePattern,$Replacement
  • #67192


    I then call the function as:

    $ObjName = Replace-SpecialChars $ObjName 

You must be logged in to reply to this topic.