'EXEC' is not processing all of submitted SQL string [closed]

Question

Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers.

This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers.

Closed 5 days ago.

Improve this question

I am creating a varchar(max) string for submission to 'EXEC' and it is less than 1000 bytes long, yet the 'exec' errors out - seems to not get the entire string.

Below is the code that errors out:

create or alter procedure usp_utility_ListFullTextCatalog_items @Database varchar(100)

as

declare @sql varchar(max), @DBName varchar(max)

set @DBName = @Database

set @sql =  cast('' as varchar(max))
             + 'SELECT  t.name as TableName, 
                    c.name as FTCatalogName,
                    i.name as UniqueIdxName,
                    cl.name as ColumnName,
                    cdt.name as DataTypeColumnName '
             + 'from    ' + @DBName + '.sys.tables t inner join '
             + '        ' + @DBName + '.sys.fulltext_indexes fi on '
             + '        ' + 't.[object_id] = fi.[object_id] inner join '
             + '        ' + @DBName + '.sys.fulltext_index_columns ic on '
             + '        ic.[object_id] = t.[object_id] inner join '
             + '        ' + @DBName + '.sys.columns cl on '
             + '            ic.column_id = cl.column_id and '
             + '            ic.[object_id] = cl.[object_id] inner join '
             + '        ' + @DBName + '.sys.fulltext_catalogs c on '
             + '        fi.fulltext_catalog_id = c.fulltext_catalog_id inner join '
             + '        ' + @DBName + '.sys.indexes i on '
             + '            fi.unique_index_id = i.index_id and '
             + '            fi.[object_id] = i.[object_id] left join ' 
             + '        ' + @DBName + '.sys.columns cdt on '
             + '        ic.type_column_id = cdt.column_id and '
             + '        fi.object_id = cdt.object_id'
select len(@sql)
select right(@sql,50)
execute @sql

go

execute usp_utility_ListFullTextCatalog_items 'AdventureWorks2022'

When I run the above, the first 'select' at the bottom of the procedure shows the length of @sql to be 900.

The 'execute @sql' statement at the end of the stored procedure produces the below error:

Msg 203, Level 16, State 2, Procedure usp_utility_ListFullTextCatalog_items, Line 33 [Batch Start Line 34]
The name 'SELECT    t.name as TableName, 
                    c.name as FTCatalogName,
                    i.name as UniqueIdxName,
                    cl.name as ColumnName,
                    cdt.name as DataTypeColumnName from AdventureWorks2022.sys.tables t           inner join        AdventureWorks2022.sys.fulltext_indexes fi on       t.[object_id] = fi.[object_id] inner join         AdventureWorks2022.sys.fulltext_index_columns ic on       ic.[object_id] = t.[object_id] inner join         AdventureWorks2022.sys.columns cl on          ic.column_id = cl.column_id and             ic.[object_id] = cl.[object_id] inner join         AdventureWorks2022.sys.fulltext_catalogs c on        fi.fulltext_catalog_id = c.fulltext_catalog_id inne' **_is not a valid identifier._**

The error message does not show the entire value of @sql, yet the second 'select' at the bottom of the procedure shows the last 50 bytes of @sql to be "= cdt.column_id and fi.object_id = cdt.object_id" - which is what they are supposed to be.

Well for a start when executing a string you need brackets e.g. execute (@sql); Think thats your complete issue tbh dbfiddle.uk/n3NChKxD — Dale K
– Dale K, Commented Nov 24 at 4:00
This is all it needed. The error message called my string a 'name' which is a clue I ignored. — JackRay
– JackRay, Commented Nov 24 at 18:59
The suggestions on the other answer should all be taken into account too. — Dale K
– Dale K, Commented Nov 24 at 19:05
If I were you I would accept that answer given ifs far more comprehensive. — Dale K
– Dale K, Commented Nov 24 at 21:12

Charlieface · Accepted Answer · 2025-11-25 09:30:49Z

There are a number of issues with your code:

Firstly as mentioned by others: use either EXEC(@sql); or EXEC sp_executesql @sql;. The latter is better as you can also pass parameters.
Dynamic SQL should be in nvarchar(max) not varchar(max).
Database/object names should be stored in sysname which is an alias for nvarchar(128).
@DBName should be escaped using QUOTENAME.
You don't need all those + signs, just use a multi-line string.
object_id does not need escaping.

create or alter procedure usp_utility_ListFullTextCatalog_items
    @Database sysname
as

declare @sql nvarchar(max), @DBName nvarchar(max);

set @DBName = QUOTENAME(@Database);

set @sql = cast(N'' as nvarchar(max)) + '
SELECT
  t.name as TableName, 
  c.name as FTCatalogName,
  i.name as UniqueIdxName,
  cl.name as ColumnName,
  cdt.name as DataTypeColumnName
from ' + @DBName + '.sys.tables t
inner join ' + @DBName + '.sys.fulltext_indexes fi
    on t.object_id = fi.object_id
inner join ' + @DBName + '.sys.fulltext_index_columns ic
    on ic.object_id = t.object_id
inner join ' + @DBName + '.sys.columns cl
    on ic.column_id = cl.column_id
    and ic.object_id = cl.object_id
inner join ' + @DBName + '.sys.fulltext_catalogs c
    on fi.fulltext_catalog_id = c.fulltext_catalog_id
inner join ' + @DBName + '.sys.indexes i
    on fi.unique_index_id = i.index_id
    and fi.object_id = i.object_id
left join ' + @DBName + '.sys.columns cdt
    on ic.type_column_id = cdt.column_id
    and fi.object_id = cdt.object_id;
';

exec sp_executesql @sql;

However, a much better idea is to just use EXEC @proc @sql; where @proc is DatabaseName.sys.sp_executesql, which will execute the dynamic SQL in the context of that database. That simplifies the whole dynamic part as you don't need to use three-part naming.

create or alter procedure usp_utility_ListFullTextCatalog_items
    @Database sysname
as

declare @proc nvarchar(1000), @sql nvarchar(max);

set @proc = QUOTENAME(@Database) + '.sys.sp_executesql';

set @sql = N'
SELECT
  t.name as TableName, 
  c.name as FTCatalogName,
  i.name as UniqueIdxName,
  cl.name as ColumnName,
  cdt.name as DataTypeColumnName
from sys.tables t
inner join sys.fulltext_indexes fi
    on t.object_id = fi.object_id
inner join sys.fulltext_index_columns ic
    on ic.object_id = t.object_id
inner join sys.columns cl
    on ic.column_id = cl.column_id
    and ic.object_id = cl.object_id
inner join sys.fulltext_catalogs c
    on fi.fulltext_catalog_id = c.fulltext_catalog_id
inner join sys.indexes i
    on fi.unique_index_id = i.index_id
    and fi.object_id = i.object_id
left join sys.columns cdt
    on ic.type_column_id = cdt.column_id
    and fi.object_id = cdt.object_id;
';

exec @proc @sql;

If final join is 'inner join' then I get one row output. If 'left outer join' I get 4 rows output - 3 of which have 'null' in DataTypeColumnName.
Ah sorry missed it was joining on ic.type_column_id not ic.column_id because you write your join conditions backwards
Your code is much cleaner and simpler than mine. I was surprised to see that using DatabaseName.sys.sp_executesql meant that the system procedures executed (those in @sql) were those residing on DatabaseName even though the stored procedure was on my 'Utility' database.
After further research I'm no longer surprised... Thanks again.

Dale K · Accepted Answer · 2025-11-24 18:42:31Z

3

You are calling EXECUTE @sql instead of EXEC (@sql).

edited Nov 24 at 18:42

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Nov 24 at 7:27

Dev

4204 silver badges11 bronze badges

2 Comments

Dale K Nov 24 at 18:42

Should be a duplicate

Dale K Nov 24 at 21:49

Or even EXECUTE (@sql); exec is just shorthand for execute.

Collectives™ on Stack Overflow

'EXEC' is not processing all of submitted SQL string [closed]

2 Answers 2

4 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Related