4

I try and run update scripts for my software in this format:

osql.exe -i "path\to\script" -U "user" -P "Password" -S "Location of sqlserver" -d "Database name" -n -b

Most of the scripts are in the same format and all end in GO. A lot of them run just fine, but all the time random scripts return an error and won't run. The error is "Incorrect syntax near '∩'. on line 1. The script might be as simple as just an INSERT, but it is always this error. I can't seem to find anything online that has been able to help me. Can anyone provide any insight?

The scripts run just fine manually. Also something interesting is if I create a new text document and paste the script in the new file and change it to the .sql and run that file then it works just fine. I'd just do this for all the 'broken' scripts, but it continues to happen to new ones and will happen on changed ones as well eventually.

ReddShepherd
  • 467
  • 1
  • 11
  • 24

2 Answers2

5

Most likely because the file is encoded as Unicode instead of UTF-8. You can check this out in Notepad++ among other free utilities. Try converting it to UTF-8 and see if that helps.

UPDATE

Correction: As the article linked in comments explains, osql can parse text files encoded as UTF-16 (Unicode 1200) or 'ANSI' (Windows-1252), but it cannot parse UTF-8 encoded files.

PinnyM
  • 35,165
  • 3
  • 73
  • 81
  • I opened it in Notepad ++ and under 'Encoding' 'Encode in UTF-8' is marked which I'm assuming means that it is already in that format. To make sure I also opened it in firefox and under View->Encoding it says it is 'Unicode (UTF-8)' – ReddShepherd Aug 29 '12 at 19:57
  • Perhaps try converting it to 'ANSI' then (an unfortunate psuedonym for Windows-1252). Just a guess... – PinnyM Aug 29 '12 at 20:04
  • This looks interesting: http://kiquenet.wordpress.com/2010/07/29/osql-exe-y-encoding-utf8/ – ReddShepherd Aug 29 '12 at 20:18
  • 1
    According to the above article, Unicode (1200) or Win-1252 work fine with osql, while UTF-8 does not. And that would certainly explain what you're seeing. – PinnyM Aug 29 '12 at 20:36
  • I changed a script that was returning the error to ANSI and the script was able to run. I just need to figure out at what point in time its getting saved at UTF-8 since it goes through a few different things before getting sent out. – ReddShepherd Aug 29 '12 at 20:52
  • I'll figure out what to do about the encoding and, while it's not because it is in UTF-8, you were right that it is an encoding issue. Thanks for the help. – ReddShepherd Aug 30 '12 at 16:00
1

It does sound like a Unicode issue (particularly since copy/paste to a new document works). To test that, you can use type and redirect to a temporary file which will force it to ANSI like so:

type \path\to\script.sql > %TEMP%\newscriptname.sql & osql.exe -i "%TEMP%\newscriptname.sql" -U "user" -P "Password" -S "Location of sqlserver" -d "Database name" -n -b
Mark
  • 3,609
  • 1
  • 22
  • 33