There are several issues with your code but the easiest way to find them is to put a
PRINT @OpenQuerySql
in place of the
EXEC
- that way you can use the built-in error reporting in SSMS to help you locate the errors.
In this case, you have missed a
SELECT
before the first
UNION ALL
and you are not handling the insert
into #t
correctly for a
UNION
query.
Try this
SET @OpenQuerySql = 'IF (' + cast(@FirstTableMonth as varchar) + '+99) = CONVERT(nvarchar(6), dateadd(month,-1,GETDATE()), 112)
BEGIN
SELECT * INTO #t FROM
(
SELECT *
FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL + ''' )
UNION ALL
SELECT * FROM #temp
) as tmp
END
else
begin
SELECT * INTO #t FROM
(
SELECT ACCT_NO, *
FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL + ''' )
UNION ALL
SELECT ACCT_NO, *
FROM OPENQUERY(['+ @LinkedServerName +'], '''+ @DynamicSQL2 + ''' )
) as tmp
end
'