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', '£'
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.