Yes, i was wondering about that too. You have the select statement embedded too, but do not close the select statement either. Since the SELECT statement is embedded in a string, the single-quoted items in the where have to "double-quoted" twice.

Here is the final version of your SQL. Note the 4 single-quotes on the where clause items.

SET @SQL = N'INSERT dbo.SERVER_LEVEL_Access_cmpr

SELECT *

From OPENQUERY ([' + @linkedServer + ',' + @port_nbr + '],

''SELECT

@@servername

,name

,type_desc

,convert(varchar(10),create_date,121)

,convert(varchar(10),getdate(),121)

,null

,null

FROM master.sys.server_principals

WHERE type_desc not in (''''SERVER_ROLE'''' , ''''CERTIFICATE_MAPPED_LOGIN'''') '') '

Another helpful trick I usually do in these cases is actually PRINT the variable during tests without performing the "EXEC(@SQL)" command. This way you can copy the output and try it to see if all the single-quoted items are terminated correctly with the correct number of single-quotes. It can be very tricky to get them right sometimes.

I hope this helps.

EDIT: Also notice the terminated "SELECT" clause with the additional quotes prior to the end parantheses { '') ' }