Hello,

I am creating a table on a SQL 2008 database as follows:

create table Slides (
ID uniqueidentifier not null
constraint PK_Slide primary key clustered,
[File] varbinary(max) filestream default(0x),
[Name] nvarchar(100) null,
) -- Slides

I get the error:
Default FILESTREAM filegroup is not available in database "MyDatabase"

How can I solve this problem?

Thanks,
Miguel

A least one FILESTREAM filegroup file is needed in order to use filestream
storage. Also, one of the filestream filegroups needs to be marked as the
default if you don't specify FILESTREAM ON clause in your CREATE TABLE.

You can add a FILESTREAM filegroup to you database from SQL Server
Management Studio or script. In the SSMS objet explorer, right-click the
database node and select Properties-->Filegroups. Add a filestream
filegroup with the default box checked. Then add a folder for that
filesteam storage using Properties-->Files.

Script example (the first FILESTREAM filegroup is the default one):

ADD FILEGROUP Bar CONTAINS FILESTREAM;

ALTER DATABASE Foo
ADD FILE ( NAME = N'Bar',
FILENAME = N'C:\Bar' ) TO FILEGROUP [Bar];

Note that you also need a ROWGUIDCOL to your uniqueidentifier definition.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"shapper" <mdmoura (AT) gmail (DOT) com> wrote

On Aug 1, 2:37*pm, "Dan Guzman" <guzma... (AT) nospam-online (DOT) sbcglobal.net>
wrote:
I added the following:
add filegroup Slides contains filestream;

So Slides is the default file group.

I am using Script

I don't understand this. FileGroup will be a folder where the data is
saved, correct?

I have two tables. Posts and Slides, where both ones have a column
named File of type file stream.
How can I associated a file group named Posts to table Posts and
another one named Slides to table Slides?
Shouldn't I do this?

And do I need to specify the path?
Can I define the path as the same as the database file is located?
If not then I will need to change this either using the database on my
computer or on my hosting server.

Should I add this to all my other tables? And shoulc I have clustered
on my IDs?

And don't I also need to do the following for File Stream?

exec [sp_filestream_configure] @enable_level = 3;
go

See replies inline <TK>:

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"shapper" <mdmoura (AT) gmail (DOT) com> wrote

On Aug 1, 2:37 pm, "Dan Guzman" <guzma... (AT) nospam-online (DOT) sbcglobal.net>
wrote:
I added the following:
add filegroup Slides contains filestream;

So Slides is the default file group.

I am using Script

I don't understand this. FileGroup will be a folder where the data is
saved, correct?

<TK> Correct.


I have two tables. Posts and Slides, where both ones have a column
named File of type file stream.

<TK> You don't associate a filegroup to a table. You assoiate a
table's filestream data to a filegroup (defined as filestream). The
filegroup in turn need at least one "file", which is really a folder.
You specify where the filestream data for a table will be using the
FILESTREAM_ON option for the CREATE TABLE command.</TK>


How can I associated a file group named Posts to table Posts and
another one named Slides to table Slides?
Shouldn't I do this?

And do I need to specify the path?
Can I define the path as the same as the database file is located?
If not then I will need to change this either using the database on my
computer or on my hosting server.

Should I add this to all my other tables? And shoulc I have clustered
on my IDs?

And don't I also need to do the following for File Stream?

exec [sp_filestream_configure] @enable_level = 3;
go

shapper (mdmoura (AT) gmail (DOT) com) writes:
Once you have create a FILESTREAM filegroup in your database, you don't
need do to more than say just FILESTREAM. But of course you can have
more than one FILSTREAM filegroup, and put different columns in different
places.

The filestream can be a directory in that folder, yes. Depending on
what you plan to use FILESTREAM for, this may or may not be a good idea.

You have a database at a web host? In that case you need to discuss
with them whether they support filstream, and particularly at what
access level.

The ROWGUIDCOL is need in tables with FILESTREAM columns. Clustering
has nothing to do with it.

sp_filestream_configure is deprecated. It appeared in the first beta of
SQL 2008, but was quickly phased out. There are two places where you
configure FILSTREAM: "sp_configure 'filestream access level'" and in
SQL Server Configuration Manager. With sp_configure you configure the
SQL Server part of it, and in SQL Server Configuration Manager you
control the OS part of it.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

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/prodinf...ons/books.mspx

I think Tibor has answered most your questions.

You can either use the same filegroup or separate ones for the filestream
data in both tables. Personally, I would use the same (default) filegroup
unless you have a reason to do otherwise. It might be appropriate to
segregate the filegroup storage if you have different recovery (read-only
vs. read-write) or performance (fast vs slow disks) requirements.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"shapper" <mdmoura (AT) gmail (DOT) com> wrote

On Aug 1, 2:37 pm, "Dan Guzman" <guzma... (AT) nospam-online (DOT) sbcglobal.net>
wrote:
I added the following:
add filegroup Slides contains filestream;

So Slides is the default file group.

I am using Script

I don't understand this. FileGroup will be a folder where the data is
saved, correct?

I have two tables. Posts and Slides, where both ones have a column
named File of type file stream.
How can I associated a file group named Posts to table Posts and
another one named Slides to table Slides?
Shouldn't I do this?

And do I need to specify the path?
Can I define the path as the same as the database file is located?
If not then I will need to change this either using the database on my
computer or on my hosting server.

Should I add this to all my other tables? And shoulc I have clustered
on my IDs?

And don't I also need to do the following for File Stream?

exec [sp_filestream_configure] @enable_level = 3;
go

On Aug 1, 6:23*pm, "Dan Guzman" <guzma... (AT) nospam-online (DOT) sbcglobal.net>
wrote:
Hello,

I am using the following:

add filegroup Files contains filestream;

alter database Badb
add file ( name = N'Files', filename = N'c:\Program Files\Microsoft
SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA' )
to filegroup [Files];

create table Slides (
ID uniqueidentifier not null rowguidcol
constraint PK_Slide primary key clustered,
Active bit not null default 0,
[File] varbinary(max) filestream default(0x),
[Name] nvarchar(100) null,
Url nvarchar(400) null
) -- Slides

And I always get the error:
Incorrect syntax near 'filegroup'.

What am I missing?
I checked my code but I can't find the error.

Thank You,
Miguel

On Aug 1, 11:15*pm, shapper <mdmo... (AT) gmail (DOT) com> wrote:
Do you have an 'ALTER DATABASE dbname' statement 'ADD FILEGROUP'?

On Aug 1, 7:31*pm, "jacob.relianc... (AT) gmail (DOT) com"
<jacob.relianc... (AT) gmail (DOT) com> wrote:
Yes I also used that:

alter database Badb
add filegroup Files contains filestream;

alter database Badb
add file ( name = N'Files', filename = N'c:\Program Files\Microsoft
SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA' )
to filegroup [Files];

And I get another error:
FILESTREAM feature is disabled.
Invalid filegroup 'Files' specified.
Default FILESTREAM filegroup is not available in database 'Badb'.
No rows affected.

I would expect those messages from your script if file FILESTREAM feature is
not enabled for the instance. Ensure FILESTREAM is enabled using the SQL
Configuration Manager tool.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"shapper" <mdmoura (AT) gmail (DOT) com> wrote

On Aug 1, 7:31 pm, "jacob.relianc... (AT) gmail (DOT) com"
<jacob.relianc... (AT) gmail (DOT) com> wrote:
Yes I also used that:

alter database Badb
add filegroup Files contains filestream;

alter database Badb
add file ( name = N'Files', filename = N'c:\Program Files\Microsoft
SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA' )
to filegroup [Files];

And I get another error:
FILESTREAM feature is disabled.
Invalid filegroup 'Files' specified.
Default FILESTREAM filegroup is not available in database 'Badb'.
No rows affected.