Filestream column

Question

Hello,

On a Visual Studio 2010 database project I created the following table:

CREATE TABLE [dbo].[Resources] (
  [Id]     INT            IDENTITY (1, 1) NOT NULL,
  [Content]   VARBINARY (MAX) FILESTREAM NULL,
  [Description] NVARCHAR (800)       NULL,
  [Key]     UNIQUEIDENTIFIER      ROWGUIDCOL NOT NULL,
  [Locked]   BIT            NOT NULL,
  [Name]    NVARCHAR (100)       NOT NULL
);

I keep having the error:

SQL04141: A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

But I do have such a column: Key

What am I doing wrong?

Thanks,

Miguel

 

Answer 1

Are all your settings correct on the database level? Try the following

CREATE TABLE [dbo].[Resources] (
 [Id]   INT      IDENTITY (1, 1) NOT NULL,
 [Content]  VARBINARY (MAX) FILESTREAM NULL,
 [Description] NVARCHAR (800)    NULL,
 [Key]   UNIQUEIDENTIFIER   ROWGUIDCOL NOT NULL UNIQUE,
 [Locked]  BIT      NOT NULL,
 [Name]  NVARCHAR (100)    NOT NULL
);

---

Abdallah El-Chal, PMP, ITIL, MCTS

Answer 2

If you attempt to create the table from SQL Server Management Studio, does that work?

If it does, then Visual Studio is pulling your legs, and you would need to ask in a VS forum what it's up to.

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Answer 3

Yes,

Abdshall suggestion work. But let me explain this better:

I have a full T-SQL code in a single file working with no problems in SSMS.

But now I am trying the new VS 2010 Database Projects which, I think, is better integrated with Testing and Deployment ... Even if it takes a little bit more work.

I had an Unique constraint in a separate script:

 

ALTER TABLE [dbo].[Resources]
  ADD CONSTRAINT [Resources_Key_U]
  UNIQUE [Key]

But somehow is not being accepted. I get the following error:

SQL02010: Incorrect syntax near [Key].

SQL80001: Incorrect syntax near 'End Of File'.  Expecting ID, QUOTED_ID, STRING, or TEXT_LEX.

What is wrong? I have Key inside [] as it is a reserved word.

Am I missing something?

Thanks,

Miguel

 

Answer 4

Generally, if you have problems with syntax, the best place to get them resolved is to look in Books Online. This is probably a faster way to get the answer than waiting for someone responding in a forum.

The relevant topic in this case is ALTER TABLE, and in Books Online 2008, you will need to proceed to continue to "Table_constraint".

The relevant part is:

[code]
[ CONSTRAINT constraint_name ]
{
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
                (column [ ASC | DESC ] [ ,...n ] )
[/sql]

That is, you to have parentheses around the list of key columns.

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Answer 5

My code was correct ... I just posted the wrong version by mistake. I have the following table:

CREATE TABLE [dbo].[Resources] 
(
 Id int identity (1, 1) NOT NULL,
 Content varbinary (max) filestream NULL,
 [Key] uniqueidentifier rowguidcol NOT NULL
)

And the following constraint:

ALTER TABLE [dbo].[Resources]
 ADD CONSTRAINT [Resources_Key]
 UNIQUE ([Key])

And I get the error, only when building:

SQL04141: A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

It is solved only if I add Unique to the field in the table code.

But isn't the Alter Table part doing just that?

I usually do this in a different way (all in table's code) but using the VS 2010 Database Project things are made in steps.

To me the code seems ok ... But maybe I am missing something ... Really.

Answer 6

You need to have the UNIQUE constraint at the time you create the table because of the conditions of having FILESTREAM is to have a unique row ID, which is introduced by the UNIQUE constraint.

---

Abdallah El-Chal, PMP, ITIL, MCTS

Answer 7

Related thread:

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/3b5a984b-9c2c-4bd7-81d0-bd7bbee2c895

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM