CODEWITHSUNDEEP
gitgithub
Alan2
Alan2

Reputation: 24602

Why does git think my .sql file is a binary file?

I have some .sql files that I just for the first time pushed to github. However when I look at the commit it is saying:

BIN  WebRole/Sql/Database.sql View
Binary file not shown

Can someone tell me why it's saying "Binary file not shown"

Upvotes: 114

Views: 60075

Answers (8)

Michael Freidgeim
Michael Freidgeim

Reputation: 28511

I’ve edit .sql file in GitHub and it showed the message

We’ve detected the file encoding as UTF-16LE. When you commit changes we will transcode it to UTF-8.

After commit the file in GitHub was shown correctly, but PR compare with previous version still showed BIN(just because previous version had wrong encoding)

Upvotes: 0

BU0
BU0

Reputation: 762

I was exporting SQL commands from my database using the Microsoft SQL Server Management Studio 19.1 Generate Scripts wizard and committing the generated .sql file to Github.

By default, the wizard writes the .sql file in Unicode Text. This caused Github to think that the file was a binary format.

The simple fix in my case was to change the exported format in the wizard to ANSI Text instead of Unicode Text in the Save as selection. Now Github is able to recognize the file as plaintext and offer diffs on the content.

No PowerShell commands needed, no post-batch file to run. Just export the text as ANSI and Github will be happy.

enter image description here

Upvotes: 5

Rusi
Rusi

Reputation: 1182

From version 2.18, git has an option working-tree-encoding precisely for these reasons. See gitattributes docs.
[Make sure your git version (and all who'll use the repo) is at least as recent as 2.18]

Find out the encoding of the sql file eg with file

If (say) its utf-16 without bom on windows machine then add to your gitattributes file

*.sql text working-tree-encoding=UTF-16LE eol=CRLF

If utf-16 little endinan (with bom) make it

*.sql text working-tree-encoding=UTF-16 eol=CRLF

Upvotes: 21

VonC
VonC

Reputation: 1329122

The extension alone isn't enough to GitHub to see if it is a text file.
So it has to look at its content.

And as mentioned in "Why does Git treat this text file as a binary file?", its content might not include enough ascii character to guess it is text file.

You can use a .gitattributes file to explicitly specify a .sql should be a text, not a binary.

*.sql diff

Update 2018: as I mention in "Utf-8 encoding not working on utf-8 encoded document", Git 2.18 .gitattributes has a new working-tree-encoding attribute.
So, as shown in Rusi's answer:

*.sql text working-tree-encoding=UTF-16LE eol=CRLF

As kostix adds in the comments:

if these files are generated by the Microsoft SQL Management Studio (or whatever it's called in the version of MS SQL Server's management tools you're using), the files it saves are encoded in UCS-2 (or UTF-16) -- a two-byte encoding, which is indeed not text in the eyes of Git

You can see an example in "Git says “Binary files a… and b… differ” on for *.reg files"

As mentioned in "Set file as non-binary in git":

"Why is Git marking my file as binary?" The answer is because it's seeing a NUL (0) byte somewhere within the first 8000 characters of the file.
Typically, that happens because the file is being saved as something other than UTF-8. So, it's likely being saved as UCS-2, UCS-4, UTF-16, or UTF-32. All of those have embedded NUL characters when using ASCII characters

As Neo mentions in the comments (and in Why does Git treat this text file as a binary file?):

You can change the encoding of a saved file in SSMS to UTF-8 by selecting encoding 'UTF-8 with signature' from the 'Advanced Save Options' menu item in the File menu.

Upvotes: 119

Carl
Carl

Reputation: 1832

Using the accepted answer from the linked question and a few other comments I came up with this as a solution to the issue, which is working and runs on Win10

$Utf8NoBomEncoding = New-Object System.Text.UTF8Encoding $False
Get-ChildItem -Recurse *.sql | foreach {
    $MyPath = $_.FullName;
    $Contents = Get-Content $MyPath
    [System.IO.File]::WriteAllLines($MyPath, $Contents, $Utf8NoBomEncoding)
}

Upvotes: 12

John Smith
John Smith

Reputation: 7407

Here is a quick workaround that worked for me, using SSMS 2012. Under tools => options => environment => international settings, if you change the language from "English" to "Same as Microsoft Windows" (it may prompt you to restart SSMS for the changes to take effect), it will not use UTF-16 as the default encoding for new files anymore- all new files I create have Codepage 1252 (file => advanced save options) now, which is an 8 bit encoding scheme and seems to have no problems with Git Diff

Upvotes: 6

Sam H
Sam H

Reputation: 889

The way to resolve this issue is to force the file to use 8-bit encoding. You could run this PowerShell script to change the encoding of all .SQL files in the current directory and its subdirectories.

Get-ChildItem -Recurse *.sql | foreach {
  $FileName = $_.FullName;
  [System.Io.File]::ReadAllText($FileName) | Out-File -FilePath $FileName -Encoding UTF8;
}

Upvotes: 1

Resource
Resource

Reputation: 544

For those struggling with this issue in SSMS for 2008 R2 (yes, still!), you can set the default encoding as follows:

  • Locate directory C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql

Locations may vary. This is the directory used by the default installation on Windows 7 64-bit.

  • In this location, add (or edit) empty SQL file SQLFile.sql.

This is used as a template for new .SQL files. Save it using the encoding you require (in my case, Windows-1252 with Windows line endings). The arrow to the right of the 'Save' button gives you a choice of encodings.

You need to co-ordinate encodings with your development team to avoid git and SSMS hassle.

Upvotes: 5

Related Questions