26

I have tried adding this to the .gitattributes:

* text=auto
*.sql diff

But it still shows like this:

BIN  WebRole/Sql/Objects/dbo.Content.Table.sql →
WebRole/Sql/dbo.Content.Table.sql Binary file not shown

Would appreciate help with this.

Samantha J T Star
  • 30,952
  • 84
  • 245
  • 427

4 Answers4

15

The only method I found that worked was to change the encoding of .sql files from the default UTF16 encoding to UTF8. The below outlines three approaches based on your circumstances:

For a small number of files

  1. Within Visual Studio or SSMS choose Save As
  2. Choose the drop down arrow beside the save button enter image description here
  3. Choose "Save with encoding..."
  4. Select Yes to the Confirm Save As dialog

enter image description here

  1. Choose Unicode (UTF-8 with signature) - CodePage 65001 from the resulting dialog box and press OK. Note that the UTF-8 option is usually above the currently selected encoding (Unicode - Codepage 1200)

enter image description here

For a large number of files

If you have many files to change and are comfortable with running Powershell scripts you may want to try the following:

  1. Download the powershell script created by Microsoft MCC, MVP Stefan Roth from this link
  2. The powershell script is not digitally signed so you will need to open powershell as an administrator and then downgrade your execution policy (temporarily) outlined here. Choose either the bypass or unrestricted policy.
  3. How to run powershell scripts is detailed here. The path to the script will need quotes around it. The script then requires source and destination folders which must not be surrounded in quotes. The Encoding requested is simply utf8.

For any future files created

In order to prevent having to convert files in the future you can alter the template for the new queries created in SSMS by using the approach outlined in this link

mrduncle1
  • 589
  • 7
  • 11
  • +1 for this bit "Note that the UTF-8 option is usually above the currently selected encoding" - I hate it when "most common" items aren't also put in their alphabetical placement – Red Jun 08 '21 at 15:33
9

Git usually guesses correctly whether a blob contains text or binary data by examining the beginning of the contents. In your case, however, git is getting confused and treating the file as binary, possibly due to binary data somewhere in the file.

From the git-diff manpage:

   -a, --text
       Treat all files as text.

So you can still get the text diff quite easily as follows:

git diff -a WebRole/Sql/Objects/dbo.Content.Table.sql

To override git incorrect guess add the following to .gitattributes in the same directory as the file:

*.sql diff

and commit this file. This will force git to treat every .sql file as text from now on, wether or not it contains binary data.

nunogt
  • 158
  • 5
  • This is only a partial answer. See below answers to save file as UTF-8 encoding also. – Mike Aug 16 '19 at 02:43
7

GitHub can very well decide how it manages its diff (for example, they have custom diffs for 3D renderings, maps or images).

GitHub support will be able to say exactly how .sql files are treated and diffed.

But check also the encoding of your sql files in your GitHub repo.
As I mentioned in "Why does git think my cs file is binary?", UTF-16 files could be problematic.
Make sure at least the sql files are in UTF-8.

Community
  • 1
  • 1
VonC
  • 1,262,500
  • 529
  • 4,410
  • 5,250
2

It looks like a file encoding problem, what encoding are you using?

cf. following SO questions: Why does Git treat this text file as a binary file? and Can I make git recognize a UTF-16 file as text?.

You are able to force your local git to permit textual diffs on the file, but not GitHub.

Check your encoding and replace your file with UTF-8 should fix the situation.

Community
  • 1
  • 1
Loic
  • 1,088
  • 7
  • 19