Collation-based issues specifically when using Invoke-SqlCmd

  • Jimmy M

    Default port

    Points: 1481

    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: 182268

    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: 1481

    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: 182268

    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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply