Invoke-Sqlcmd更改Invoke标识符的情况
Invoke-Sqlcmd更改Invoke标识符的情况
提问于 2017-06-22 14:44:28
我正在尝试开发一个脚本，以便按照RFC 4180定期导出格式正确的CSV文件。我使用一个PowerShell脚本来执行此操作，它调用了Sqlcmd。但是，输出似乎改变了数据库中某些值的情况。例如，统一标识符值将从完全大写改为
92adbee2-adbf-de11-90b0-005056b325c4
我正在运行的脚本只输出到powershell终端，如下所示。
Invoke-Sqlcmd -Query "SELECT top 10 * FROM
dbo.Account;" `
-Database db_name `
-Server server-name `
-QueryTimeout 65535是否有任何方法阻止此工具更改类型为unique标识符的值的情况？
谢谢
回答 3
Stack Overflow用户
发布于 2018-05-23 20:03:59
用于Powershell：$SQL_Command = "sqlcmd -S $DbServer -U $SQL_User -P $SQL_Pass -i $TSQL_File -o$CSV_OutPut_FileName -s“，”-w 65530“
调用表达式$SQL_Command
第一个变量($SQL_Command)存储sqlcmd命令，调用表达式cmdlet作为命令计算或运行指定的字符串。
$TSQL_File变量是包含sql语句的文件名(例如:在server中从TableName....uniqueidentifier数据类型选择GUID，或者可以使用SELECT * FROM TableName)
输出不会更改SQL Server...Hope中的output标识符数据类型的情况。
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)Stack Overflow用户
发布于 2018-05-23 23:06:33
正如一些注释指出的那样，unique标识符实际上是一个二进制值，xxxxxxxx xxxxxxxx xxxxxxxx只是二进制值的许多不同视觉表示形式之一。
在中，查询结果窗口使用xxxxxxxx-xxxxxxxxxxxx布局设置唯一标识符，并使用大写十六进制数字。
Server中的unique标识符类型映射到PowerShell中的Guid类型(即.Net)。默认的.Net Guid.ToString方法还使用xxxxxxxx-xxxx-xxxxxxxxxxxx布局，但使用小写十六进制数字。
如果可以在Guid.ToString中重写PowerShell方法，则可以更改所使用的格式，但不能这样做。如果您已经有了一个用于处理RFC 4180特殊情况的自定义表格式化程序(引号、值分隔符等)，那么可以在其中添加Guid格式。
Stack Overflow用户
发布于 2018-05-25 15:29:23
#----------------------------------------------------------------------------------------------------------
#--- 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`页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44702683
复制相关文章
相似问题
腾讯云开发者
