File tables are not scripted properly

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

File tables are not scripted properly

Postby JosvanDuijn » Thu May 10, 2012 4:24 pm

When you compare a database with file tables (new SQL 2012 feature) they are not scripted properly!

I tested this with version 10.1.10.102.

How it is scripted now:

CREATE TABLE [dbo].[BOOKING_INVOICE_FILE]
(
[stream_id] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT [DF__BOOKING_I__strea__2C3393D0] DEFAULT (newsequentialid()),
[file_stream] [varbinary] (max) FILESTREAM NULL,
[name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[path_locator] [sys].[hierarchyid] NOT NULL CONSTRAINT [DF__BOOKING_I__path___2D27B809] DEFAULT (convert(hierarchyid, '/' + convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 1, 6))) + '.' + convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 7, 6))) + '.' + convert(varchar(20), convert(bigint, substring(convert(binary(16), newid()), 13, 4))) + '/')),
[parent_path_locator] AS (case when [path_locator].[GetLevel]()=(1) then NULL else [path_locator].[GetAncestor]((1)) end) PERSISTED,
[file_type] AS (getfileextension([name])) PERSISTED,
[cached_file_size] AS (datalength(file_stream)) PERSISTED,
[creation_time] [datetimeoffset] NOT NULL CONSTRAINT [DF__BOOKING_I__creat__2E1BDC42] DEFAULT (sysdatetimeoffset()),
[last_write_time] [datetimeoffset] NOT NULL CONSTRAINT [DF__BOOKING_I__last___2F10007B] DEFAULT (sysdatetimeoffset()),
[last_access_time] [datetimeoffset] NULL CONSTRAINT [DF__BOOKING_I__last___300424B4] DEFAULT (sysdatetimeoffset()),
[is_directory] [bit] NOT NULL CONSTRAINT [DF__BOOKING_I__is_di__30F848ED] DEFAULT ((0)),
[is_offline] [bit] NOT NULL CONSTRAINT [DF__BOOKING_I__is_of__31EC6D26] DEFAULT ((0)),
[is_hidden] [bit] NOT NULL CONSTRAINT [DF__BOOKING_I__is_hi__32E0915F] DEFAULT ((0)),
[is_readonly] [bit] NOT NULL CONSTRAINT [DF__BOOKING_I__is_re__33D4B598] DEFAULT ((0)),
[is_archive] [bit] NOT NULL CONSTRAINT [DF__BOOKING_I__is_ar__34C8D9D1] DEFAULT ((1)),
[is_system] [bit] NOT NULL CONSTRAINT [DF__BOOKING_I__is_sy__35BCFE0A] DEFAULT ((0)),
[is_temporary] [bit] NOT NULL CONSTRAINT [DF__BOOKING_I__is_te__36B12243] DEFAULT ((0))
CONSTRAINT [UQ__BOOKING___9DD95BAF514888B4] UNIQUE NONCLUSTERED ([stream_id])
)
GO

How it should be scripted:


CREATE TABLE [dbo].[BOOKING_INVOICE_FILE] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [Supose_Documents]
WITH
(
FILETABLE_DIRECTORY = N'BOOKING_INVOICE_FILE', FILETABLE_COLLATE_FILENAME = SQL_Latin1_General_CP1_CI_AS
)

GO
JosvanDuijn
 
Posts: 9
Joined: Tue Jan 25, 2011 10:36 am

Postby james.billings » Thu May 10, 2012 11:22 pm

Thanks for your post. Unfortunately SQL Compare doesn't yet work with many of the new keywords and functions in SQL 2012.

I've logged a change-request under ref SC-5769 for this one.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests