In yesterday’s post I described debugging some encoding problems I encountered while running SqlCmd via a custom MSBuild task. Here are some additional details:
.SQL script files encoded in any codepage will run just fine and dandy from the command line.
When introducing MSBuild into the equation, which shells out to SQLCMD via the .NET Process class, it works if I save the .sql file in codepage 1200 or 1252. It does NOT work if the file is saved in codepage 65001 (UTF-8). Which sucks, because that’s the default codepage VS 2008 uses, and there’s no apparent way to change it.
Forcing SQLCMD to use codepage 65001 via the -f 65001 or -f i:65001 switches doesn’t work.
I verified the BOMs using XVI32. The BOM for the .sql file that doesn’t work is EF BB BF, which matches what I’d expect from this MSDN article. It’s UTF-8. UTF-16 (codepage 1200) works; the BOM is FF FE when VS 2008 saves it.
I briefly wondered if MSBuild was doing something funny to the file encoding; but that wouldn’t make sense. The ItemGroups are just handles to files, not the actual files themselves. The Process.Start() stuff is printing out the correct OS command-line call. At that point it’s past MSBuild’s control.
Now I’m wondering if SQLCMD thinks that CodePage 65001 is something different than what I think it is. I’ll have to check in on that.
UPDATE: Just for shits and grins, I tried -f i:unicode and -f i:utf-8, both with no luck. SQLCMD didn’t barf, either, which is a bad design flaw IMHO. If you pass a codepage it can’t support, it should tell you.
UPDATE 2: I ran into this post from Matthias Denkmaier who seemed to indicate exactly the opposite of what I’ve been experiencing. Then I thought *ding!* maybe it has to do with the SQL SERVER code page. My server is using codepage 1252 for collation (LATIN1_GENERAL_blah blah blah). But just as fast I though nah, that’s not it, because the exact same SQLCMD call from the command prompt (not from within MSBuild) works just fine.