title: dynamically monitor stored procedures executions and cpu - error on dynamic sql
body: <p>I am developing a script to monitor stored procedures executions and cpu utilisation, however, running the script below dynamically it does not work. It is working directly in SQL Server Mgmt Studio. Can someone spot something that should be changed?</p>
  
  <p>The error message is:</p>
  
  <blockquote>
    <p>Msg 156, Level 15, State 1, Line 43<br>
    Incorrect syntax near the keyword 'proc'.</p>
  </blockquote>
  
  <p>This is the script:</p>
  
  <pre><code>DECLARE @SQL NVARCHAR(MAX)
  --SELECT @SQL ='SELECT * FROM OPENQUERY('+ QUOTENAME('servername')    + ','''+ '
  SELECT @SQL = 'SELECT dbID
                        ,dbname
                        ,StoredProcedure
                        ,last_execution_time
                        ,total_cpu_time
                        ,executionCounts
                        ,Total_Logical_Reads
                        ,Total_Logical_Writes 
                 FROM OPENQUERY('+ QUOTENAME('servername') + ','''+ '
  
  SET FMTONLY OFF   
  SET NOCOUNT ON
  SET DATEFORMAT DMY
  SET DEADLOCK_PRIORITY LOW
  
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  
  ;with r0 as 
  (
     SELECT 
        st.dbID
        ,StoredProcedure = OBJECT_NAME(st.objectid,st.dbid)
        ,last_execution_time = MAX(QS.last_execution_time)
        ,total_cpu_time = sum(qs.total_worker_time) 
        ,executionCounts = sum(cp.usecounts)
        ,Total_Logical_Reads=sum(qs.Total_Logical_Reads)
        ,Total_Logical_Writes=sum(qs.Total_Logical_Writes)
     FROM 
        sys.dm_exec_query_stats qs 
     INNER JOIN 
        sys.dm_exec_cached_plans cp ON cp.plan_handle = qs.plan_handle
     CROSS APPLY 
        sys.dm_exec_sql_text(cp.plan_handle) st
     WHERE
        cp.objtype = ''proc''--&gt; most of our problems are related to stored procedures 
        --  removing this filter would increase the cost of the query in the monitored servers
        AND OBJECT_NAME(st.objectid, st.dbid) IS NOT NULL
   GROUP BY
       st.dbid, st.objectid
  )
  SELECT 
      r0.dbID
      ,DBName = CASE WHEN r0.dbid = 32767 
                       THEN ''Resource''
                       ELSE COALESCE(DB_NAME(r0.dbid), ''No3 Name'') -- keep the coalesce for the database name to be used in a primary key constraint
                END 
      ,r0.StoredProcedure
      ,r0.last_execution_time
      ,r0.total_cpu_time
      ,r0.executionCounts
  ,r0.Total_Logical_Reads
  ,r0.Total_Logical_Writes
  FROM  
      r0
  ORDER BY 
      total_cpu_time DESC' + ''')'
  
  --print @sql
  EXEC SP_EXECUTESQL @SQL
  </code></pre>