Hi Guys,

Does anyone get a syntax issue on the below piece of code?

IF OBJECT_ID('tempdb..#tempSubscribedArticles') IS NOT NULL

DROP TABLE #tempSubscribedArticles

CREATE TABLE #tempSubscribedArticles

(

subscriber_srv VARCHAR(255),

subscriber_db VARCHAR(255),

destination_owner VARCHAR(255),

destination_object VARCHAR(255),

object_type VARCHAR(255),

rowcount_subscriber INT

)

DECLARE @sub_srv VARCHAR(255),

@sub_db VARCHAR(255),

@strSQL_S VARCHAR(4000)

DECLARE db_cursor_s CURSOR

FOR SELECT DISTINCT

subscriber_srv,

subscriber_db

FROM #tempTransReplication

OPEN db_cursor_s

FETCH NEXT FROM db_cursor_s INTO @sub_srv, @sub_db

WHILE @@FETCH_STATUS = 0

BEGIN

SET @strSQL_S = 'SELECT ' + '''' + @sub_srv + ''''

+ ' AS subscriber_srv, ' + '''' + @sub_db + ''''

+ ' AS subscriber_db, '

+ 's.name AS destination_owner, o.name AS destination_object, o.Type_Desc AS object_type, i.rowcnt AS rowcount_subscriber

FROM ' + @sub_srv + '.' + @sub_db + '.sys.objects AS o

INNER JOIN ' + @sub_srv + '.' + @sub_db

+ '.sys.schemas AS s on o.schema_id = s.schema_id

LEFT OUTER JOIN ' + @sub_srv + '.' + @sub_db

+ '.dbo.sysindexes AS i on o.object_id = i.id

WHERE ' + '''' + @sub_srv + '.' + @sub_db + ''''

+ ' + ' + '''' + '.' + ''''

+ ' + s.name' + ' + ' + '''' + '.' + '''' + ' + o.name'

+ ' IN (SELECT subscriber_srv + ' + '''' + '.' + ''''

+ ' + subscriber_db + ' + '''' + '.' + ''''

+ ' + destination_owner + ' + '''' + '.' + ''''

+ ' + destination_object FROM #tempTransReplication)

AND ISNULL(i.indid, 0) IN (0, 1)

ORDER BY i.rowcnt DESC'

-- heap (indid=0); clustered index (indix=1)

INSERT INTO #tempSubscribedArticles

EXEC ( @strSQL_S

)

FETCH NEXT FROM db_cursor_s INTO @sub_srv, @sub_db

END

CLOSE db_cursor_s

DEALLOCATE db_cursor_s

I get this error when I am running it.

Any advice will be very helpful 🙂