How to remove the special Character in SQL File

Kannan AU

7y1.9kSQL

Dear Team,

I am facing very big issue, please help me. I am having "FileName.sql" file , in that file when I try to deploy to server using Powershell script (that's our company deployment tool), its always saying that there is a special character inside the file, hence deployment is getting failed, it never says on which line it has special cheater , could you please help me, how to identify the special character in SQL file. When I execute this FileName.sql fiel manually in SQL server management studio without any error it will execute, but through Powershall script I am not able to deploy, so please help me , how to identify the special character inside my sql file.

This is the error message on Pwoershall script - Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException: Incorrect syntax near '"'.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah7y ago
to test remove all records except the first 10 records see if it works
if it doesnt remove the records one by one and check

generally what is causing this can be

1 - a record is saved in multiple line
2- you have a ENTER in one of the text fields that has split the record into 2 records
3- if the delimiter is a comma , you have have an extra comma in a record that represents and extra field
4- other carecters for other languages can cause an issue like É é ...... etc....
0
Jenith Thakkar7y ago
I assume that the file is being saved as either UTF-16LE ("Unicode" in Microsoft-land) or as UTF-8, but in either case, without a byte-order mark (BOM). I suggest re-saving the file with an encoding that includes the Byte Order Mark ("save with encoding" in some programs like SSMS). Be sure to pick the encoding that specifies either "BOM" or "with signature" (like in MS products). The BOM is 2 - 4 characters (depending on the encoding being used) at the beginning of the file that indicates the encoding being used (i.e. UTF-8, UTF-16LE, UTF-16BE, UTF-32LE, or UTF-32BE). The BOM is a Unicode-only feature and does not exist for non-Unicode encodings. With it being set in the file, any program that opens the file will detect it and know what the encoding is without you needing to specify. Hopefully this holds true for Invoke-Sqlcmd.
0
Salman Beg7y ago
Hi. Which link not working. Just now I checked and all the two links are working. Thanks.
0
Kannan AU7y ago
this link is not working The page you requested was not found
0
Salman Beg7y ago
Hi.
In Sql, there is a replace function and by using which you can replace any particular special character.
You can follow these links also for more,
https://www.sqlshack.com/replace-ascii-special-characters-sql-server/
https://stackoverflow.com/questions/31050086/find-and-replace-all-special-character-in-sql

First you need to create functions, and after that you need to pass you all sql statement in it and which will replace your special character.
Thanks.
0