SQL-Server Dynamic SQL failing

Question

I have a stored procedure that dynamically generates a table to hold staging data for imports. This routine was running fine until Wednesday. I have tracked this down to one particular area. Code that's causing an issue is:

DECLARE @strSQL NVARCHAR(MAX) = N'';

SELECT  @strSQL = @strSQL + N',[' + CustomerField + N'] NVARCHAR(MAX)' + CHAR(10)
  FROM  dbo.WebServices
 WHERE  CallType = 'customer'
   AND  IsPrimaryTable = 1
   AND  Source = 'clientName'
 ORDER BY TagOrder;

SET @strSQL = STUFF(@strSQL, 1, 1, 'CREATE TABLE ' + 'TableName' + CHAR(10) + '(')+ N')';

PRINT @strSQL

NB. Table its pulling from holds a list of columns and the data hasn't changed either. There are about 34 rows of data with no special characters, and it isn't exposed to the clients.

This gives me an output of:

CREATE TABLE TableName
([CUSACNR2] NVARCHAR(MAX)
)

Running this on exactly the same data, even in the same window on SSMS, with an additional TOP filter gives me the results I was expecting i.e.

DECLARE @strSQL NVARCHAR(MAX) = N'';

SELECT  TOP (99999) @strSQL = @strSQL + N',[' + CustomerField + N'] NVARCHAR(MAX)' + CHAR(10)
  FROM  dbo.WebServices
 WHERE  CallType = 'customer'
   AND  IsPrimaryTable = 1
   AND  Source = 'clientName'
 ORDER BY TagOrder;

SET @strSQL = STUFF(@strSQL, 1, 1, 'CREATE TABLE ' + 'TableName' + CHAR(10) + '(')+ N')';

PRINT @strSQL

Generates the dynamic SQL:

CREATE TABLE TableName
([TAG] NVARCHAR(MAX)
,[CUSACNR] NVARCHAR(MAX)
.
.
.
,[SHIPPING_POSTAL_CODE] NVARCHAR(MAX)
,[CUSACNR2] NVARCHAR(MAX)
)

• Specific column names removed for security

While I have tracked this down in one specific stored proc, we use this technique for building dynamic SQL a lot and I am seeing errors popping up in multiple places. I'm assuming that something has changed on the server as this has started happening in multiple databases at one time.

Does anyone know of anything that may have caused this issue? Its a legacy system that has been running fine for a long time and re-coding every instance of this type of activity is not an option.

Answer 1

I have found the answer. Sort of. I now know what had changed, so I've been able to correct it, but it should not have made any difference.

There was an index on the table that was causing the issue. Before you say that shouldn't make any difference, and you're right it shouldn't, I've managed to reliably solve and reproduce the problem by dropping and recreating the index.

So this isn't a fragmentation issue, it's not a clustered index, and the execution plan doesn't search by this index anyway.

CREATE NONCLUSTERED INDEX [idx_ntindex] ON [dbo].[WebServices] ([Source], [CallType], [SecurityKey])

GO

DECLARE @strSQL NVARCHAR(MAX) = N'';

SELECT  @strSQL = @strSQL + N',[' + ISNULL(CustomerField,'') + N'] NVARCHAR(MAX)' + CHAR(10)
  FROM  dbo.WebServices
 WHERE  CallType = 'customer'
   AND  IsPrimaryTable = 1
   AND  Source = 'clientName'
  ORDER BY TagOrder;

SET @strSQL = STUFF(@strSQL, 1, 1, 'CREATE TABLE ' + 'TableName' + CHAR(10) + '(') + N')';

PRINT @strSQL;

GO

DROP INDEX idx_ntindex ON dbo.WebServices

DECLARE @strSQL NVARCHAR(MAX) = N'';

SELECT  @strSQL = @strSQL + N',[' + ISNULL(CustomerField,'') + N'] NVARCHAR(MAX)' + CHAR(10)
  FROM  dbo.WebServices
 WHERE  CallType = 'customer'
   AND  IsPrimaryTable = 1
   AND  Source = 'clientName'
  ORDER BY TagOrder;

SET @strSQL = STUFF(@strSQL, 1, 1, 'CREATE TABLE ' + 'TableName' + CHAR(10) + '(') + N')';

PRINT @strSQL;

Gives me the results

CREATE TABLE TableName
([CUSACNR2] NVARCHAR(MAX)
)

CREATE TABLE TableName
([TAG] NVARCHAR(MAX)
,[CUSACNR] NVARCHAR(MAX)
,[DDAYS] NVARCHAR(MAX)
.
.
.
,[SHIPPING_POSTAL_CODE] NVARCHAR(MAX)
,[CUSACNR2] NVARCHAR(MAX)
)

If anyone knows WHY this is the case then please let me know. I found it but it doesn't make any sense to me.

Thanks for the help all. +1's for all the support, each suggestion got me a little closer.