0

When executing the query below, if I use PRINT it prints correctly. I can copy and paste the printed code and execute it. However, if I use EXEC I get the following error:

error on exec

Is there a way of simplifying what I am doing? Why do SQL PRINT and SQL EXEC deliver these two very different result sets?

DECLARE @TableName as NVARCHAR(250), @SQL as VARCHAR(MAX);

DECLARE @TableCursor as CURSOR;
SET @TableCursor = CURSOR FOR
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'
AND name like 'HISTORY_MasterList_%'
ORDER BY sobjects.name

OPEN @TableCursor;
FETCH NEXT FROM @TableCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL ='select '''+ @TableName +''', 0
 Union All
select All ''Server Count'',count(1) from ['+ @TableName +'] 
 Union All
select All ''Server Cores'',sum(convert(decimal(18,0),cores)) from ['+ @TableName +'] 
 Union All
select ''Production Servers'',count(1) from ['+ @TableName +'] where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
Union All
select ''Production Cores'', sum(convert(decimal(18,0),cores)) from ['+ @TableName +']
 where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
 Union All
select ''Production Server Count after filtering out passive/failover servers'', count(1) from 
(select  distinct m.ServerName
       from ['+ @TableName +']  m
       inner join [SQLEnv].[dbo].[vwManView] v on m.ServerName = v.ServerName
       where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
       and unit <> 0) aa
 Union All
select ''Production Server Cores after filtering out passive/failover servers'', sum(convert(decimal(18,0),cores)) from(
       select  distinct m.ServerName, m.Cores
       from ['+ @TableName +']  m
       inner join [SQLEnv].[dbo].[vwManView] v on m.ServerName = v.ServerName
       where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
       and unit <> 0) aa
 Union All
select ''Non-Prod SQL Instances downgraded to Developer Edition'',count(1) from ['+ @TableName +'] where ''InstanceStatus'' like ''Downgrade%''
 Union All
select ''Non-Prod SQL Instance Core Count downgraded to Developer Edition'',sum(convert(decimal(18,0),cores)) from ['+ @TableName +']  where ''InstanceStatus'' like ''Downgrade%''
 Union All
select ''Non-Prod VMs moved from Prod Environments'', count(1) from ['+ @TableName +']  where ServerStatus like ''VM Moved/right sized from Prod Env to NonProd''
 Union All
select ''Non-Prod VMs TO BE moved from Prod Environments'', count(1) from ['+ @TableName +']  where ServerStatus like ''Sent for V2V - non prod split'' 
'

EXEC @SQL

FETCH NEXT FROM @TableCursor INTO @TableName;
END
CLOSE @TableCursor;
DEALLOCATE @TableCursor;
5

The error is saying that ...it is not a valid identifier. When you pass EXEC @SQL , it is expecting that @sql to represent the name of a stored procedure (or scalar function). Hence the error message: ...it is not a valid identifier.

If you want to execute a text, to pass dynamically , you should encapsulate that text inside ( ), like EXEC (@SQL).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.