Invoke-Sqlcmd changes cases of uniqueidentifier

Question

I'm trying to develop a script to routinely export correctly formatted CSV files as per RFC 4180. I'm using a PowerShell script to do this which invokes Sqlcmd. However, the output appears to change the case of some values in the database. For example, a uniqueidentifier value gets changed from fully upper case to

92adbee2-adbf-de11-90b0-005056b325c4

The script I'm running to just output to the powershell terminal is below.

Invoke-Sqlcmd -Query "SELECT top 10 * FROM 
dbo.Account;" `
-Database db_name `
-Server server-name `
-QueryTimeout 65535

Is there any way to stop this tool changing the case of values that are of type uniqueidentifier?

Thanks

A UniquIdentifier is not a string, it's a 16-byte hexa-decimal number. therefor, the letter case is irrelevant. `a` and `A` are both equal to a decimal `10`, or a binary `1010`. — Zohar Peled, Jun 22 '17 at 15:00
@ZoharPeled but I believe it gets converted to a string when output to a csv or PowerShell window? My question is can it just export it as an upper case string? When I run the same sql query in management studio the uniqueidentifier values displayed in the results grid are in upper case but using the sqlcmd cmdlet they are returned in lowercase. — JimS, Jun 22 '17 at 15:06
That's just the string representation. It doesn't change the value of the guid. — Zohar Peled, Jun 22 '17 at 15:11
The string representation is going to be used in a downstream system so I need the string representation of the guid to be consistent on export and preferably in upper case. — JimS, Jun 22 '17 at 15:17
If you need it to be upper case then just assign it to a variable and use the `.ToUpper()` command. — Nick, Jun 22 '17 at 17:27
Trouble is I'm just doing a select * to dump the full table out. I'd rather not have to write out the name of each variable if I can avoid it. Not being too lazy..... there are quite a few. — JimS, Jun 23 '17 at 16:10

Rachan Terrell · Answer 1 · 2018-05-24T18:04:13.377

Used in 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

The first variable ($SQL_Command) store sqlcmd command The Invoke-Expression cmdlet evaluates or runs a specified string as a command

$TSQL_File variable is the file name contains sql statements (example: SELECT GUID FROM TableName....uniqueidentifier data type in sql server or you could use SELECT * FROM TableName)

The output will not changes cases of uniqueidentifier data type in SQL Server...Hope this help.

Syntax for sqlcmd
-a packet_size
-A (dedicated administrator connection)
-b (terminate batch job if there is an error)
-c batch_terminator
-C (trust the server certificate)
-d db_name
-e (echo input)
-E (use trusted connection)
-f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage] -g (enable column encryption) -G (use Azure Active Directory for authentication) -h rows_per_header
-H workstation_name
-i input_file
-I (enable quoted identifiers)
-j (Print raw error messages) -k[1 | 2] (remove or replace control characters)
-K application_intent
-l login_timeout
-L[c] (list servers, optional clean output)
-m error_level
-M multisubnet_failover
-N (encrypt connection)
-o output_file
-p[1] (print statistics, optional colon format)
-P password
-q "cmdline query"
-Q "cmdline query" (and exit)
-r[0 | 1] (msgs to stderr)
-R (use client regional settings)
-s col_separator
-S [protocol:]server[instance_name][,port]
-t query_timeout
-u (unicode output file)
-U login_id
-v var = "value"
-V error_severity_level
-w column_width
-W (remove trailing spaces)
-x (disable variable substitution)
-X[1] (disable commands, startup script, environment variables, optional exit)
-y variable_length_type_display_width
-Y fixed_length_type_display_width
-z new_password
-Z new_password (and exit)
-? (usage)

An explanation of what you are doing here would be helpful for other people. — Rhys Jones, May 23 '18 at 23:09
Thank you for your suggestion. I was in the hurry yesterday didn't have time to explain. — Rachan Terrell, May 24 '18 at 19:19

score 0 · Answer 2 · answered May 23 '18 at 23:06

As some of the comments point out a uniqueidentifier is really a binary value, xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx is merely one of a number of different visual representations of the binary value.

In SQL Server Management Studio the query results window formats unique identifiers using the xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx layout with uppercase hex digits.

The uniqueidentifier type in SQL Server maps to the Guid type in PowerShell (i.e. .Net). The default .Net Guid.ToString method also uses the the xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx layout but with lowercase hex digits.

If you could override the Guid.ToString method in PowerShell then you could change the format used, but you can't do this. If you already have a custom table formatter for handling RFC 4180 special cases (quotes, delimiters in values etc) then you could add Guid formatting in there.

score 0 · Answer 3 · answered May 25 '18 at 15:29

#----------------------------------------------------------------------------------------------------------   
#--- 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++
    }
  }
}`enter code here`

Invoke-Sqlcmd changes cases of uniqueidentifier

3 Answers3