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.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Any advice will be very helpful 🙂