Collation-based issues specifically when using Invoke-SqlCmd

Question

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

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

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

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 · Answer 2

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: 182276 More actions · Answer 3

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