I am trying to teach myself the basics of FileStream in SQL. I have been using articles published by Nisarg Upadhyay titled Storing files in SQL Database Using FILESTREAM Parts 1 and 2.
https://codingsight.com/storing-files-in-sql-database-using-filestream-part-2/
I have successfully completed Part 1. Enabled the FireStream feature, created and configured FileStream file groups and containers, and created a Firestream Database and a table.
While working on part 2, ‘Inserting multiple files using SQL script’, I created a temporary table for storing the details of the files to be added to the database but when I tried to create a second table to physically store the files in the table, I ran into an error. Here is the query and error:
CREATE TABLE [dbo].[Document_Content ]
(
[ID] [uniqueidentifier] ROWGUIDCOLNOT NULL,
[RootDirectory] [varchar](max) NULL,
[FileName] [varchar](max) NULL,
[FileAttribute] [varchar](150) NULL,
[FileCreateDate] [datetime] NULL,
[FileSize] [numeric](10, 5) NULL,
[FileStreamCol] [varbinary](max) FILESTREAMNULL,
UNIQUE NONCLUSTERED
(
[ID] ASC
)
WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] FILESTREAM_ON [Dummy-Documents]
GO
Error msg:
Msg 1921, Level 16, State 3, Line 5
Invalid filegroup 'Dummy Documents' specified.
When I ran a query to show the Filegroup name, I get the correct Filegroup name (Dummy-Documents). I am not sure what the extra ON[Primary] is for on line 20, but it does not make a difference if I remove it.
Server Name Filegroup Name Filegroup Type
DESKTOP-UAB5C74 FileStream-Demo ROWS
DESKTOP-UAB5C74 FileStream-Demo_Log LOG
DESKTOP-UAB5C74 Dummy Documents FILESTREAM
I am stumped to say the least. The code looks right, the FileGroup Name is ‘Dummy Documents’ but, there is something I’m missing, and I cannot find it.
Anybody have any ideas what I am missing, I have checked and double checked the query but can not find the problem.
