Collation-based issues specifically when using Invoke-SqlCmd

• Jimmy M

  Default port

  Points: 1483

  More actions

  August 15, 2011 at 3:33 am

  #246352

  Hi all

  I am using Invoke-SqlCmd to run a batch of .sql files as part of a SQL release process.

  It has recently come to my attention that .sql files which INSERT specific text into tables are being affected by a collation-type issue: the pound sign (£) is being converted to a question mark (?) before being sent to sql server.

  In other words, when i use Invoke-SqlCmd to run this T-SQL which is saved in a .sql file and then query the results, I see a ? instead of £.

  insert jm_test select 'Invoke-SqlCmd direct', '£'

  go

  This does not happen if I use SQLCMD directly, and it does not happen if a use a Powershell script to read the .sql manually and then pass the query to ADO.NET directly.

  The database and relevant column's collation is SQL_Latin1_General_CP1_CI_AS, but i don't believe this is the issue based on the tests i have done mentioned in the previous paragraph.

  If anyone knows where the problem lies and how i can fix this i would be very grateful. My main concern is any other character conversions that may have happened.

  Many thanks

  James

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  August 17, 2011 at 2:46 pm

  #1369862

  What character encoding is used in the file? If the file is UCS-2 (i.e. "Unicode" in Windows-speak) the character may be the Unicode version (UCS-2 0x20A4) and not the Windows-1252 version (ANSI 0xA3) causing the escape issue.

  A ? mark is used when SQL Server escapes an ANSI character but I am not sure if other pieces in the stack can do that too.

  For the record this worked without any escaping in SSMS for me:

  CREATE TABLE jm_test

  (

  data VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS

  )

  INSERT jm_test

  SELECT CHAR(163)

  INSERT jm_test

  SELECT NCHAR(0x20a4)

  SELECT data,

  ASCII(data)

  FROM dbo.jm_test

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Jimmy M

  Default port

  Points: 1483

  More actions

  September 1, 2011 at 8:43 am

  #1376001

  Thanks for your reply.

  Excuse my ignorance in this area but how do i check the character encoding in the file? I checked the properties of the file but it didn't seem to show me this info.

  Thanks again.

  J

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  September 1, 2011 at 10:34 am

  #1376061

  I use TextPad as my default text editor. It's usable after changing the "Keyboard Compatibility" under "Editor" in Configure > Preferences from "TextPad" to "Microsoft Applications".

  After opening the file in TextPad go to View > Document Properties (or Alt + Enter). It does a good job of determining the "Code set" properly. It also reads the EOL markers and determines if it's a Mac, Unix or PC "File type".

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato