Updating rowguidcol error
One of the Junior SQL Server DBAs in my company approached me yesterday with a dilemma.He wanted to know how he can create a FILESTREAM enabled database and how to use different DML statements such as INSERT, UPDATE, DELETE and SELECT against a SQL Server 2008 FILESTREAM enabled database.Error: 5538, Severity: 16, Partial updates are not supported on columns that have a FILESTREAM as a source.Error: 5539, Severity: 16, The ROWGUIDCOL column associated with the FILESTREAM being used is not visible where method %ls is called.
However, in order to enable the FILESTREAM feature you need to be a member of SYSADMIN or SERVERADMIN fixed server role.
Use File Stream DBGOCREATE TABLE [File Stream Data Storage]( [ID] [INT] IDENTITY(1,1) NOT NULL, [File Stream Data] VARBINARY(MAX) FILESTREAM NULL, [File Stream Data GUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),[Date Time] DATETIME DEFAULT GETDATE())ON [PRIMARY]FILESTREAM_ON File Stream Group GO Inserting FILESTREAM Data to File Stream Data Storage Table Let's add a row to the File Stream Data Storage table by execute the T-SQL below. JPG which is stored in the "C:\Sample Files" folder.
Before you run the below code, make sure you have changed the name of the file (C:\Sample Files\Image1.
The old FILESTREAM data will be removed from FILESTREAM data container once the next CHECKPOINT occurs and the garbage collector process has completed successfully.
If you want to trigger the FILESTREAM Garbage Collector thread EXPLICIT, then execute the CHECKPOINT command manually.
Error: 5540, Severity: 16, The FILESTREAM column cannot be used with method %ls because the associated ROWGUIDCOL of the base table is nullable or does not have a unique constraint.