1

I am using a SQL Project in Visual Studio (SSDT) and I have a column defined as:

BinaryDocument VARBINARY(MAX) FILESTREAM NOT NULL

I need to make a change to allow NULL, so I changed the definition to:

BinaryDocument VARBINARY(MAX) FILESTREAM NULL

But when publish the project, the script generated shows:

ALTER TABLE [Docs] ALTER COLUMN [BinaryDocument] VARBINARY (MAX) NULL;

Which, as you would expect because you can't remove FILESTREAM from a column, gives the following error:

Cannot alter column 'BinaryDocument' in table 'Docs' to add or remove the FILESTREAM column attribute.

Does anyone know if this is supposed to work or is it just me? Is there any workaround?

EDIT

It's not just restricted to making NULLable. Tried setting a default and same problem:

BinaryDocument VARBINARY(MAX) FILESTREAM NOT NULL DEFAULT(0x00)

Yields the same output in the publish script as above:

ALTER TABLE [Docs] ALTER COLUMN [BinaryDocument] VARBINARY (MAX) NOT NULL;

So it seems to honour the NULL/NOT NULL changes, but fails to carry the FILESTREAM keyword into the output script :(

oatsoda
  • 2,088
  • 2
  • 26
  • 49
  • 1
    Logged it as a Connect bug https://connect.microsoft.com/VisualStudio/feedback/details/1268660 – oatsoda Apr 22 '15 at 07:52
  • I get the same error when publishing via the sqlpackage.exe command line tool. However, when I publish via Visual Studio, it works for me. I'm wondering if I'm using a different version of SSDT within VS than you. In VS Help about, it is reporting SSDT version 12.0.50730.0 – Simon Green Nov 23 '15 at 16:50
  • BTW, I asked a similar question before seeing yours. Sadly no one has replied to mine either yet but in case I get a response and you don't the link is http://stackoverflow.com/questions/33869927/sqlpackage-breaks-when-trying-to-alter-a-filestream-column – Simon Green Nov 23 '15 at 16:52
  • @SimonGreen See connect bug in my comment above. They have confirmed it: "Thank you for your feedback. The issue has been confirmed as a product defect. We have a fix and will deliver the fix in a SSDT release soon." – oatsoda Nov 23 '15 at 17:48
  • Yes - thanks. I was using a slightly older sqlpackage.exe. Getting the latest from the VS SSDT package (sqlspackage.exe version 12.0.3021.0) fixed the issue – Simon Green Nov 23 '15 at 20:10

0 Answers0