rindexingrebuild

What is the syntax to rebuild indexes in R?

I have a SQL table with 6 indexes on it. I am trying to rebuild them using dbGetQuery, or dbSendQuery using this syntax

buildIndexes <- dbSendStatement(conn,str_interp("USE ${database_}
ALTER INDEX [NonClusteredIndex - 20220304 - 083604] ON [dbo].${
toTableName_
} REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)
;"))

The weird thing is that the syntax runs fine in R, but fails to rebuild the index. When I run the same syntax in SSMS, it does rebuild the index.

Any ideas of what else to try? I am going to put all the statements in a stored procedure, if I can't get them to work in R, but I prefer to do all of the work in R. Any help is much appreciated.

Solution

  • After many failed attempts, I wrote this syntax that worked fine for rebuilding SQL Server indexes:

    rebuildIndexs <- dbExecute(COVID19_Connection,str_interp("USE [${database_}];

ALTER INDEX [NonClusteredIndex-20220304-083604] ON [dbo].[${toTableName1_}] REBUILD;
ALTER INDEX [NonClusteredIndex-20220309-130244] ON [dbo].[${toTableName1_}] REBUILD;
ALTER INDEX [NonClusteredIndex-20220314-140000] ON [dbo].[${toTableName1_}] REBUILD;
ALTER INDEX [NonClusteredIndex-20220502-160842] ON [dbo].[${toTableName1_}] REBUILD;
"))