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

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Thom Thom 4 weeks, 1 day ago.

  • Author
    Posts
  • #67012
    Profile photo of Thom
    Thom
    Participant

    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
    
                          }
    
                  }
    
           }     
           }
    }
    
  • #67156
    Profile photo of Fredrik Kacsmarck
    Fredrik Kacsmarck
    Participant

    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
    }
    
    • #67191
      Profile photo of Thom
      Thom
      Participant

      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
      }
      
  • #67192
    Profile photo of Thom
    Thom
    Participant

    I then call the function as:

    $ObjName = Replace-SpecialChars $ObjName 
    

You must be logged in to reply to this topic.