August 19, 2020 at 12:37 pm
Forum,
I'm trying to build a clustered index for the all the heap tables moving to a different FG i have almost 1k statements so running them all as a batch
CREATE CLUSTERED INDEX [PK_Create_my_index1]
ON [dbo].[tab1] ([ID] ) WITH (DROP_EXISTING = OFF , PAD_INDEX = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , IGNORE_DUP_KEY = OFF , DATA_COMPRESSION = PAGE)
ON [FG_1] ;
CREATE CLUSTERED INDEX [PK_Create_my_index2]
ON [dbo].[tab2] ([ID] ) WITH (DROP_EXISTING = OFF , PAD_INDEX = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , IGNORE_DUP_KEY = OFF , DATA_COMPRESSION = PAGE)
ON [FG_1] ;
CREATE CLUSTERED INDEX [PK_Create_my_index3]
ON [dbo].[tab3] ([ID] ) WITH (DROP_EXISTING = OFF , PAD_INDEX = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , IGNORE_DUP_KEY = OFF , DATA_COMPRESSION = PAGE)
ON [FG_1] ;
some of the index names are very long almost 60 characters length for example (this_is_a_new_index_im_trying_to_create_migrate_all_tables_from_primary_to_individual_File_Group)
But when i execute them i get the following error when run all of them at once or in a batch
"Msg 319, Level 15, State 1, Line 2152 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."
thanks in advance
August 19, 2020 at 2:18 pm
Those statements look fine. There must be one or more CREATE statements with some other syntax or name error which causes SQL to then misinterpret some SQL later on.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 19, 2020 at 3:33 pm
This is exactly im not sure what im doing wrong in all the 1k+ create statements ,
all the create stmts are separated by "--------------------------------------------------------------------------------------------------------------------------" will that makes a difference?
CREATE CLUSTERED INDEX [PK_Create_my_index1]
ON [dbo].[tab1] ([ID] ) WITH (DROP_EXISTING = OFF , PAD_INDEX = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , IGNORE_DUP_KEY = OFF , DATA_COMPRESSION = PAGE)
ON [FG_1] ;
CREATE CLUSTERED INDEX [PK_Create_my_index2]
ON [dbo].[tab2] ([ID] ) WITH (DROP_EXISTING = OFF , PAD_INDEX = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , IGNORE_DUP_KEY = OFF , DATA_COMPRESSION = PAGE)
ON [FG_1] ;
--------------------------------------------------------------------------------------------------------------------------
CREATE CLUSTERED INDEX [PK_Create_my_index3]
ON [dbo].[tab3] ([ID] ) WITH (DROP_EXISTING = OFF , PAD_INDEX = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , IGNORE_DUP_KEY = OFF , DATA_COMPRESSION = PAGE)
ON [FG_1] ;
--------------------------------------------------------------------------------------------------------------------------
August 19, 2020 at 6:43 pm
No, that should do it, it must be something else.
Can you copy out the statements around where the error occurs and parse those directly and see if you can spot an error?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 19, 2020 at 6:46 pm
Tried that too, based on the line# below, i check the script and run that script seperately and it works but not all of them as a batch
Ln1 ->CREATE CLUSTERED INDEX [PK_Create_my_index1]
ON [dbo].[tab1] ([ID] ) WITH (DROP_EXISTING = OFF , PAD_INDEX = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , IGNORE_DUP_KEY = OFF , DATA_COMPRESSION = PAGE)
ON [FG_1] ;
--------------------------------------------------------------------------------------------------------------------------
LN-25>CREATE CLUSTERED INDEX [PK_Create_my_index2]
ON [dbo].[tab2] ([ID] ) WITH (DROP_EXISTING = OFF , PAD_INDEX = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , IGNORE_DUP_KEY = OFF , DATA_COMPRESSION = PAGE)
ON [FG_1] ;
--------------------------------------------------------------------------------------------------------------------------
Ln-392>CREATE CLUSTERED INDEX [PK_Create_my_index3]
ON [dbo].[tab3] ([ID] ) WITH (DROP_EXISTING = OFF , PAD_INDEX = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , IGNORE_DUP_KEY = OFF , DATA_COMPRESSION = PAGE)
ON [FG_1] ;
August 19, 2020 at 10:39 pm
Have tried separating each create index statement with GO (batch separator)?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply