执行SQL查询时，显示结果表中的NULL值

Question

我有一个脚本，用于执行SQL文件并在控制台中显示结果（暂时如此）。问题是我需要区分在结果表中返回的Null值和空字符串。

这就是查询在Management Studio中返回的内容：

您可以看到它包含字符串、空字符串和NULL值。

这是在PowerShell IDE中查询返回的内容：

NULL和空值之间没有区别。

同时，最后几列被截断，只打印了前10列。

我该如何解决这个问题？

以下是我的代码：

$ConnectionString = "Data Source=...;Initial Catalog=...;User Id=..;Password=.."
$FolderToSQLFiles = "C:\SQLFilesTestFolder";

#function that executes sql queries and return result tables
function GetSQLresults {
    Param(
        [Parameter(Mandatory=$True, ValueFromPipelineByPropertyName=$True, Position=0)] $SQLqueryText, # sql query text returned from file
    )

    $objConnection = New-Object System.Data.SqlClient.SqlConnection;
    $objConnection.ConnectionString = $ConnectionString

    $objConnection.Open();

    $ObjCmd = New-Object System.Data.SqlClient.SqlCommand;
    $ObjCmd.CommandText = $SQLqueryText;
    $ObjCmd.Connection = $objConnection;
    $ObjCmd.CommandTimeout = 0;

    $objAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
    $objAdapter.SelectCommand = $ObjCmd;
    $objDataSet = New-Object System.Data.DataSet;

    $objAdapter.Fill($objDataSet);

    $ResultSets = @(); #adding all result tables to array

    for ($i=0; $i -lt $objDataSet.Tables.Count; $i++) {
        $tmpResultTable = $objDataSet.Tables[$i] | Format-Table | Out-String;
        $ResultSets += $tmpResultTable;
    }

    return $ResultSets 

    $query = $null;

    $objDataSet = $null;

    $objConnection.Close();
    $objConnection = $null;
}

Get-ChildItem $FolderToSQLFiles -Filter *.sql | Foreach-Object {
    $tmpSQLfilePath = $_.FullName #getting the sql file full path
    $tmpSQLfileContent = (Get-Content $tmpSQLfilePath) -join "`n"  #getting the content of the sql

    GetSQLresults -SQLqueryText $tmpSQLfileContent  #passing the sql query to the executing funciton
}

- nyagolova

Answer 1

在Powershell中使用：$SQL_Command = "sqlcmd -S $DbServer -U $SQL_User -P $SQL_Pass -i $TSQL_File -o$CSV_OutPut_FileName -s ',' -w 65530"

Invoke-Expression $SQL_Command

第一个变量($SQL_Command)存储了sqlcmd命令。Invoke-Expression cmdlet评估或运行指定的字符串作为命令。

$TSQL_File变量是包含sql语句的文件名(例如：SELECT GUID FROM TableName....sql server中的uniqueidentifier数据类型，或者您可以使用SELECT * FROM TableName)

输出将按照您想要的方式在SQL Server中显示(NULL和空字符串)...希望这有所帮助。

- Rachan Terrell

Answer 2

PowerShell会自动将null值转换为空字符串进行输出。您需要检查字段中的值类型并相应地调整输出/值。

类似以下代码应该可以解决问题：

GetSQLresults ... |
    Select-Object -Property *,@{n='Col006',e={
        if ($_.Col006 -is [DBNull]) {'NULL'} else {$_.Col006}
    }} -Exclude Col006

- Ansgar Wiechers

---

问题在于我也不知道每个表的列名。我执行了许多查询，它们都有不同的结果表。此外，一个查询可能有多个结果表。 : / - nyagolova

然后您需要枚举表，枚举列，并替换每个列中的值类型为 [DBNull] 的值。我不会为此提供现成的解决方案。 - Ansgar Wiechers

Answer 3

#----------------------------------------------------------------------------------------------------------   
#--- This will generate the result that you want 
#----------------------------------------------------------------------------------------------------------   
#--- Each line of TSQL_FileName.TXT ($ScriptsArray) contains a file name ($TSQL_File) of the TSQL statement that need to be execute
#----------------------------------------------------------------------------------------------------------   
FOREACH ($TSQL_File in $ScriptsArray)
{ $Num_TSQL_File++
  $Result_MSG = ''
  #----------------------------------------------------------------------------------------------------------   
  #--- GENERATE OUT PUT FILE NAME USE FOR CSV FILE WHEN WE RUN sqlcmd 
  #----------------------------------------------------------------------------------------------------------   
  $OutPut_File = (Get-Item $TSQL_File).Basename+'_'+$OutPut_DateTime
  $OutPut_File = $OutPut_File.replace(' ','_')
  $OutPut_File = $OutPut_File.replace('.','_')
  $OutPut_File = $OutPut_File+'.CSV'
  $OutPut_Error = ''
  $SQL_Command = "sqlcmd -S $DbServer -U $SQL_User -P $SQL_Pass -i $TSQL_File -o $OutPut_File -s ',' -w 65530"
  Invoke-Expression $SQL_Command 
  $OutPut_Array = Get-Content $OutPut_File
  $ThirdLine = "|"+$OutPut_Array[3]+"|"
  #----------------------------------------------------------------------------------------------------------
  #--- Determine if an error had occured, if so send email notification
  #----------------------------------------------------------------------------------------------------------
  IF (($OutPut_Array -like "Error*") -or ($OutPut_Array -like "Msg*") -or ($OutPut_Array -like "Invalid*")  -or ($OutPut_Array -like "Sqlcmd: Error*") )
  { $OutPut_Error = (Get-Item $TSQL_File).Basename+'_'+$OutPut_DateTime+'.ERR'
    $OutPut_Array | out-file ".\$OutPut_Error"
    $Result_MSG = 'Error!'
    $OutPut_File = $OutPut_Error
    $OutPut_FileSizeKb = $NumOfRow_Send = 0
    $global:DbErr++
    "{0,-16} {1,-40} {2,-60} {3,-10} {4,-4} {5,-4}" -f $DbServer, $TSQL_File, $OutPut_File, [int]$OutPut_FileSizeKb, $NumOfRow_Send, $Result_MSG
    $Results_Array += ,@($Results_Array_Index, $DbServer, $TSQL_File, $OutPut_File, $OutPut_FileSizeKb, $NumOfRow_Send, $Result_MSG)  
    $Results_Array_Index++
  }
  ELSE
  { #----------------------------------------------------------------------------------------------------------
    #--- Determine if zero row return from query, if so modify the array so that it will send only header info
    #----------------------------------------------------------------------------------------------------------
    IF ( ($OutPut_Array.Length -EQ 3) -and ($ThirdLine -EQ '||') )
    { $NeedFix_Array = Get-Content $OutPut_File
      #-------------------------------------------------------------
      #--- MODIFY THE ARRAY SO THAT IT WILL SEND ONLY THE HEADER
      #-------------------------------------------------------------
      $NeedFix_Array = $NeedFix_Array[1]
      #-----------------------------------------------------------
      #--- REMOVE BLANK SPACE FROM THE LINE
      #-----------------------------------------------------------
      $NeedFix_Array = $NeedFix_Array -replace '\s+', ' '
      $NeedFix_Array = $NeedFix_Array -replace ' ,', ','
      $NeedFix_Array = $NeedFix_Array -replace ', ', ','
      #-----------------------------------------------------------
      $NeedFix_Array | out-file $OutPut_File -Encoding ASCII
      $OutPut_FileSizeKb = (Get-Item $OutPut_File).length/1KB
      $OutPut_FileSizeKb = [int][Math]::Ceiling($OutPut_FileSizeKb)
      $NumOfRow_Send = 1
      $Num_CSV_File_Created++
      "{0,-16} {1,-40} {2,-60} {3,-10} {4,-4} {5,-4}" -f $DbServer, $TSQL_File, $OutPut_File, [int]$OutPut_FileSizeKb, $NumOfRow_Send, $Result_MSG
      $Results_Array += ,@($Results_Array_Index, $DbServer, $TSQL_File, $OutPut_File, $OutPut_FileSizeKb, $NumOfRow_Send, $Result_MSG)  
      $Results_Array_Index++
    }
    #----------------------------------------------------------------------------------------------------------
    #--- REMOVE 1st LINE AND '-----' IN 3rd LINE AND FIX THE SPACES BETWEEN COMMA
    #----------------------------------------------------------------------------------------------------------
    ELSE
    { $NeedFix_Array = Get-Content $OutPut_File
      #-------------------------------------------------------------
      #--- REMOVE THE FIRST LINE 
      #-------------------------------------------------------------
      $NeedFix_Array = $NeedFix_Array[1..($NeedFix_Array.Length-1)]
      #-------------------------------------------------------------
      #--- REMOVE THE SECOND LINE AFTER THE FIRST LINE WERE REMOVE
      #-------------------------------------------------------------
      $NeedFix_Array = $NeedFix_Array[0,0+2..($NeedFix_Array.length - 1)]
      $NeedFix_Array = $NeedFix_Array[1..($NeedFix_Array.Length-1)]
      #-----------------------------------------------------------
      #--- REMOVE BLANK SPACE FROM THE LINE
      #-----------------------------------------------------------
      $NeedFix_Array = $NeedFix_Array -replace '\s+', ' '
      $NeedFix_Array = $NeedFix_Array -replace ' ,', ','
      $NeedFix_Array = $NeedFix_Array -replace ', ', ','
      #-----------------------------------------------------------
      $NeedFix_Array | out-file $OutPut_File -Encoding ASCII
      $OutPut_FileSizeKb = (Get-Item $OutPut_File).length/1KB
      $OutPut_FileSizeKb = [int][Math]::Ceiling($OutPut_FileSizeKb)
      $NumOfRow_Send = $NeedFix_Array.Length
      $Num_CSV_File_Created++
      "{0,-16} {1,-40} {2,-60} {3,-10} {4,-4} {5,-4}" -f $DbServer, $TSQL_File, $OutPut_File, [int]$OutPut_FileSizeKb, $NumOfRow_Send, $Result_MSG
      $Results_Array += ,@($Results_Array_Index, $DbServer, $TSQL_File, $OutPut_File, $OutPut_FileSizeKb, $NumOfRow_Send, $Result_MSG)  
      $Results_Array_Index++
    }
  }
}

- Rachan Terrell