SQL Server FILESTREAM not working properly

[sphe_g]

Hello hi.

I am seeing flames with using FILESTREAM in SQL Server 2008!

I have a table (Products), in which I wanna store a link to an image stored on disk, under the ProdPicture column.
This is what the script looks like:

CREATE TABLE PRODUCTS
(
ProdID INT PRIMARY KEY,
ProdName VARCHAR(40),
ProdDescription VARCHAR(50),
ProdCategory VARCHAR(20),
StockOnHand INT,
ProdPrice MONEY,
ProdPicture VARBINARY(MAX) FILESTREAM
)

I have looked up using FILESTREAM online, and came across a number of resources. I followed all the available instructions, even running the script for enabling filestream from within SQL Server, which looks like this:

EXEC SP_CONFIGURE FILESTREAM_ACCESS_LEVEL, 2
           RECONFIGURE.

I tried to set the access level to 3 (which is what we need since we want to allow remote client access) which doesn't work.
I always seem to get the error 'Default FILESTREAM filegroup is not available in database'

Please help!!

[BitBlt]

Well, you have several things going on here, but I have questions as well. First things first.

Does your database have a FileStream filegroup added to it? If you look at the DatabaseProperties, under the Filegroups page, you should see it in the bottom list of filegroups. The top set is labeled "Rows" and the bottom is labeled "Filestream". If there isn't one, you need to add it, then go to the Files page and add a file of type "Filestream Data", and point it to a path.

If you are all set with that, then we can get to your specific issues. To begin with, there is no access level 3. It is 0, 1 or 2. You probably want 2.

Next, your table definition requires a unique rowguid before you can have a Filestream column. Use this snippet as a sample (borrowed shamelessly from your code above):

CREATE TABLE PRODUCTS
(
ProdID INT PRIMARY KEY,
[rowguid] [uniqueidentifier] ROWGUIDCOL unique NOT NULL CONSTRAINT [Products_rowguid] DEFAULT (newid()),
ProdName VARCHAR(40),
ProdDescription VARCHAR(50),
ProdCategory VARCHAR(20),
StockOnHand INT,
ProdPrice MONEY,
ProdPicture VARBINARY(MAX) FILESTREAM,
)

That should get your table properly defined. You should now be able to insert rows and stream stuff into your ProdPicture column.

Hope this helps! Good luck!