I am creating files with xp_cmdshell like this:
SELECT @command = 'echo ' + @fileContent + ' > e:\out\' + @fileName + '.csv'
exec master.dbo.xp_cmdshell 'mkdir "e:\out\"'
exec master..xp_cmdshell @command
The problem is that the file contents is not in UTF-8 and so some special characters are wrong.
Can i create the file in UTF-8 encoding?
I finally succeeded doing this by writing the output to a temp.txt file and adding the next PowerShell command to convert it to UTF-8:
-- Change encoding to UTF-8 with PowerShell
SET @command = 'powershell -Command "Get-Content '+@Path+'\temp.txt -Encoding Unicode | Set-Content -Encoding UTF8 '+@path+'\'+@filename+'"';
EXEC xp_cmdshell @command;
You can use the SQLCMD instead the old tecnique as DOS outupt redirect
sqlcmd -S ServerName -d DataBaseName -E -o "CSV File Path & Location" -Q "Your Query" -W -w 4000 -s ";" -f 65001
the switch -f 65001 indicate to use UTF8 as outfile file
SELECT @command = 'sqlcmd -S ServerName -d DataBaseName -E -o "'+ @fileName +'" -Q "Your Query" -W -w 4000 -s ";" -f 65001'
exec master.dbo.xp_cmdshell 'mkdir "e:\out\"'
exec master..xp_cmdshell @command
p.s. i cant test it so please check the SQLCMD documentation
Hope it help
I chose Jan Lenders's way but did not use that encoding type setting when reading the time.
SET @COMMAND = 'powershell -Command "Get-Content '+ @PATH + @V_FILE_NAME + ' | Set-Content -Encoding UTF8 '+ @PATH + @V_FILE_NAME +'2"';
This is worked.
Thank you^^
