none
Syntax Error RRS feed

  • Question

  • I am getting Syntax error for  XML Path  this. Not understanding what needs to be changed..

    DECLARE @sql nvarchar(MAX)
     SELECT @sql = (SELECT 'UPDATE STATISTICS ' + QUOTENAME(T.TABLE_NAME)) + ' (' + QUOTENAME(T.INDEX_NAME) + ')  WITH FULLSCAN, INCREMENTAL = ON' +
     char(13) + char(10)
     FROM #TEMP1 T  FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
     PRINT @sql
     --EXEC(@sql)


    sami

    Wednesday, June 26, 2019 1:54 PM

Answers

  • Try this..

    DECLARE @sql nvarchar(MAX)
     SELECT @sql = (SELECT 'UPDATE STATISTICS ' + QUOTENAME(T.TABLE_NAME) + ' (' + QUOTENAME(T.INDEX_NAME) + ')  WITH FULLSCAN, INCREMENTAL = ON' +
     char(13) + char(10)
     FROM #TEMP1 T  FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
     PRINT @sql
     --EXEC(@sql)
    • Marked as answer by arc2013 Wednesday, June 26, 2019 2:28 PM
    Wednesday, June 26, 2019 2:01 PM
  • create table #TEMP1(
     
    TABLE_NAME  varchar(50),
    INDEX_NAME  varchar(50)
    )
    
    go
    
    insert into #TEMP1 values('tab1','idx1')
     
     DECLARE @sql nvarchar(MAX)
     SELECT @sql=(SELECT N'UPDATE STATISTICS ' + QUOTENAME(T.TABLE_NAME) + ' (' + QUOTENAME(T.INDEX_NAME) + ')  WITH FULLSCAN, INCREMENTAL = ON' +
     char(13) + char(10)   FROM #TEMP1 T   FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
     PRINT @sql
    
    drop table #TEMP1

    • Marked as answer by arc2013 Wednesday, June 26, 2019 2:28 PM
    Wednesday, June 26, 2019 2:12 PM
    Moderator
  • Print has a length limit on screen. if your code runs for two, it should work for all.
    • Marked as answer by arc2013 Wednesday, June 26, 2019 5:59 PM
    Wednesday, June 26, 2019 2:34 PM
    Moderator

All replies

  • Try this..

    DECLARE @sql nvarchar(MAX)
     SELECT @sql = (SELECT 'UPDATE STATISTICS ' + QUOTENAME(T.TABLE_NAME) + ' (' + QUOTENAME(T.INDEX_NAME) + ')  WITH FULLSCAN, INCREMENTAL = ON' +
     char(13) + char(10)
     FROM #TEMP1 T  FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
     PRINT @sql
     --EXEC(@sql)
    • Marked as answer by arc2013 Wednesday, June 26, 2019 2:28 PM
    Wednesday, June 26, 2019 2:01 PM
  • create table #TEMP1(
     
    TABLE_NAME  varchar(50),
    INDEX_NAME  varchar(50)
    )
    
    go
    
    insert into #TEMP1 values('tab1','idx1')
     
     DECLARE @sql nvarchar(MAX)
     SELECT @sql=(SELECT N'UPDATE STATISTICS ' + QUOTENAME(T.TABLE_NAME) + ' (' + QUOTENAME(T.INDEX_NAME) + ')  WITH FULLSCAN, INCREMENTAL = ON' +
     char(13) + char(10)   FROM #TEMP1 T   FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
     PRINT @sql
    
    drop table #TEMP1

    • Marked as answer by arc2013 Wednesday, June 26, 2019 2:28 PM
    Wednesday, June 26, 2019 2:12 PM
    Moderator
  • quick question.. this works good. This generates 25 Update statements. But I have noticed when I have more than 100 Update statements Print SQL only shows me partial. Does this mean it will execute only partial SQL when there is more data ?

    Should I use anything other than nvarchar(max) ?


    sami

    Wednesday, June 26, 2019 2:28 PM
  • Print has a length limit on screen. if your code runs for two, it should work for all.
    • Marked as answer by arc2013 Wednesday, June 26, 2019 5:59 PM
    Wednesday, June 26, 2019 2:34 PM
    Moderator