Reputation: 2136
I am creating a script to create a table. When the table has a large object column such as varchar(max)
the table needs the text TEXTIMAGE_ON [PRIMARY]
.
To determine the name of the file group, I use the system table, sys.filegroups
and the column name
. This returns the name of the file group. In the above example it is PRIMARY
.
However, one of the tables that I am scripting has this value of PRIMARY
when there are no columns in the table that are text, ntext, varchar(max), etc. So the script is failing when it gets to this table because TEXTIMAGE_ON
is not allowed unless you have a large object column.
Why in the system table of sys.filegroups
is the name
not NULL
because the TEXTIMAGE_ON should not be set?
This is the query I use to get the attribute of TextImageOnFileGroup:
SELECT [Table].name AS "TableName", fg.name AS "TextImageOnFileGroup"
FROM sys.tables as [Table] left outer join sys.filegroups as fg on [Table].lob_data_space_id = fg.data_space_id
WHERE [Table].name in ('ProviderSpecialty', 'ProviderState', 'ProviderExclusion')
This returns
TableName TextImageOnFileGroup
ProviderSpecialty PRIMARY
ProviderState NULL
ProviderAttribute PRIMARY
The tables ProviderState does not have any columns that have a large object so TextImage_On is NULL. This is expected.
CREATE TABLE [dbo].[ProviderState](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProviderId] [numeric](19, 0) NOT NULL,
[State] [nvarchar](2) NOT NULL,
CONSTRAINT [PK_dbo.ProviderState] PRIMARY KEY CLUSTERED
(
[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]
The table ProviderAttribute has a varchar(max)
column so the TextImage_on property should be set:
CREATE TABLE [dbo].[ProductAttribute](
[ProductAttributeId] [int] IDENTITY(1,1) NOT NULL,
[Description] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_dbo.ProductAttribute] PRIMARY KEY CLUSTERED
(
[ProductAttributeId] 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]
The table ProviderSpecialty is the problem. It has the TextImage_on property set yet there is no column to warrant this.
CREATE TABLE [dbo].[ProviderSpecialty](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProviderId] [numeric](19, 0) NOT NULL,
[SpecialtyId] [int] NOT NULL,
CONSTRAINT [PK_dbo.ProviderSpecialty] PRIMARY KEY CLUSTERED
(
[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]
I am using the system tables to create a script that creates these tables. If the property name
in the sys.filegroups
table is set to PRIMARY
I add to the table script 'TEXTIMAGE_ON [PRIMARY]`.
When I run the script for this table, it errors because the TEXTIMAGE_ON property is not allowed unless there is a column of text, varchar(max), etc.
Why is the property set in the system table if it is not needed?
Upvotes: 0
Views: 214