October 25, 2016 at 11:21 am
I have looked all over the place on how to get this to work and assume there must be some setting that must be enabled either for the Server or the Database but I can't find it.
Using the sample table from MSDN:
CREATE TABLE Department
(
DeptID int NOT NULL PRIMARY KEY CLUSTERED
, DeptName varchar(50) NOT NULL
, ManagerID INT NULL
, ParentDeptID int NULL
, SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
, SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
, PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory)
)
;
I get the following error message:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'GENERATED'.
Msg 319, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
I assume that it doesn't recognize the "GENERATED" as well as the with statement because there is something missing.
What am I missing?
Thanks,
Tom
October 25, 2016 at 11:50 am
What's the compatibility level of the database you're working in?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 25, 2016 at 12:14 pm
Actually, I did find that I was on 2012 instead of 2016. When I put the same CREATE on 2016 it worked.
But then I tried to use my actual table (not the one from MSDN), it doesn't work.
CREATE TABLE [dbo].[data_load](
[ID] [INT] NOT NULL,
[PC] [INT] NOT NULL,
[AM_System_Type] [VARCHAR](50) NOT NULL,
[PeriodKey] [VARCHAR](50) NOT NULL,
[File_Path] [VARCHAR](4000) NOT NULL,
[Fale_Name] [VARCHAR](255) NOT NULL,
[File_Type] [VARCHAR](50) NOT NULL,
[Status] [VARCHAR](255) NULL,
[PlaceHolder1] [VARCHAR](4000) NULL,
[PlaceHolder2] [VARCHAR](4000) NULL,
[SysStartTime] [DATETIME2] GENERATED ALWAYS AS ROW START NOT NULL,
[SysEndTime] [DATETIME2] GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),
CONSTRAINT [PK_data_load] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.data_load_history) ) ON [PRIMARY]
) ON [PRIMARY]
GO
This now gives me this error;
Msg 155, Level 15, State 1, Line 20
'SYSTEM_VERSIONING' is not a recognized CREATE TABLE option.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near '('.
Obviously, SYSTEM_VERSIONING is recognized as it works with the other create.
Is the format different from the versions of SQL Server?
Thanks,
Tom
October 25, 2016 at 12:32 pm
Try getting rid of the ON PRIMARY sections ... they're probably the default option, anyway. There's probably a bracket or a comma in the wrong place.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 25, 2016 at 12:57 pm
CREATE TABLE [dbo].[data_load]
(
[ID] [INT] NOT NULL,
[PC] [INT] NOT NULL,
[AM_System_Type] [VARCHAR](50) NOT NULL,
[PeriodKey] [VARCHAR](50) NOT NULL,
[File_Path] [VARCHAR](4000) NOT NULL,
[Fale_Name] [VARCHAR](255) NOT NULL,
[File_Type] [VARCHAR](50) NOT NULL,
[Status] [VARCHAR](255) NULL,
[PlaceHolder1] [VARCHAR](4000) NULL,
[PlaceHolder2] [VARCHAR](4000) NULL,
[SysStartTime] [DATETIME2] GENERATED ALWAYS AS ROW START NOT NULL,
[SysEndTime] [DATETIME2] GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),
CONSTRAINT [PK_data_load] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) --Needed to close the column definition section
ON [PRIMARY] --This goes between the column definition section and the table options WITH section
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.data_load_history) )
GO
There are a couple issues I see. The column definition section did not have its closing parentheses, and the ON was placed incorrectly.
I've fixed those above.
Cheers!
October 25, 2016 at 1:49 pm
I did find that the format seemed to be wrong as suggested. So I just followed the same format as the original CREATE and it worked
CREATE TABLE [dbo].[data_load](
[ID] [INT] NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[PC] [INT] NOT NULL,
[AMFRS_System_Type] [VARCHAR](50) NOT NULL,
[PeriodKey] [VARCHAR](50) NOT NULL,
[File_Path] [VARCHAR](4000) NOT NULL,
[Fale_Name] [VARCHAR](255) NOT NULL,
[File_Type] [VARCHAR](50) NOT NULL,
[Status] [VARCHAR](255) NULL,
[PlaceHolder1] [VARCHAR](4000) NULL,
[PlaceHolder2] [VARCHAR](4000) NULL,
[SysStartTime] [DATETIME2] GENERATED ALWAYS AS ROW START NOT NULL,
[SysEndTime] [DATETIME2] GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.data_load_history)
)
GO
But it doesn't seem to write to the history table.
I tried two different inserts on the original table and neither wrote to the history file:
INSERT Department
( DepTID ,
DepTName ,
ManagerID ,
ParentDeptID
)
VALUES ( 1 ,
'Accounting' ,
5 ,
10
)
INSERT Department
( DepTID ,
DepTName ,
ManagerID ,
ParentDeptID,
SysStartTime,
SysEndTime
)
VALUES ( 2 ,
'Accounting' ,
5 ,
10,
DEFAULT,
DEFAULT
)
When I do a select on the Department table, it gives me the two records. But when I do a select on the DepartmentHistory table, it returns 0 rows.
Tom
October 25, 2016 at 1:55 pm
I figured it out.
Inserts do not add records to the History table. Updates and Deletes do.
Thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply