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.

I am seeing errors popping up in multiple places - what errors and in which places? — GSerg
– GSerg, Commented Feb 15, 2019 at 9:42
do you have any specific error message? could the permission of the user accessing the db/stored proc or the stored proc itself have changed? what work was done on/just before wednesday? can you check back to see if anything looks like it could have caused this? do you have a DB backup from Tuesday you could restore to another instance to run this code against? — scgough
– scgough, Commented Feb 15, 2019 at 9:42
On a side note, N',[' + CustomerField + N'] is utterly wrong and should be replaced with N',' + quotename(CustomerField, '['). — GSerg
– GSerg, Commented Feb 15, 2019 at 9:43
Have you tried running SELECT CustomerField FROM dbo.WebServices WHERE CallType = 'customer' AND IsPrimaryTable = 1 AND Source = 'clientName' ORDER BY TagOrder;? — GSerg
– GSerg, Commented Feb 15, 2019 at 9:50
This routine was running fine until Wednesday. - Wednesday is 13 Feb. Date conversion related problems? :) — Alexander Volok
– Alexander Volok, Commented Feb 15, 2019 at 10:04

Matthew Baker · Accepted Answer · 2019-02-15 13:26:35Z

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.

Collectives™ on Stack Overflow

SQL-Server Dynamic SQL failing

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related