0

I have 2 separate environments that both execute the same TSQL Code on identical databases but the second environment produces an erroneous response. The script makes use of Dynamic SQL to get a list of column names from a specific table ( Questions ) and then produces a CSV string.

Environment 1 Produces :

Q1,Q2,Q3,Q4,Q5,Q6,Q7,Q8,Q9,Q10,Q11,Q12,Q13,Q14,Q15,Q16,Q17,Q18,Q19,

Environment 2 Produces :

Q9,

DECLARE @Questions NVARCHAR(2000) = '',                                             
        @SQLTemplateQuery NVARCHAR(2000) = '',
        @Exclude NVARCHAR(2000) ='',
        @ExecSQL NVARCHAR(2000)='';                 

 --This is just a template that gets executed on more than one table in the DB
 SET @SQLTemplateQuery = 'SELECT @Cols = COALESCE( @Cols , '''' ) + c.Name + '','' FROM sys.columns c INNER JOIN sys.tables t ON t.object_id = c.object_id WHERE t.Name = @Table AND c.Name NOT IN ( {0} ) ORDER BY c.column_id;';

 SET @Exclude = '''Id'''; -- Specify columns to exclude.
 SET @ExecSQL = REPLACE( @SQLTemplateQuery, '{0}', @Exclude ); --Replace the marker with columns to exclude.
 SET @ExecSQL = REPLACE( @ExecSQL, '@Cols', '@Questions' ); --Replace the template variable with actual one used    
 EXEC sp_executesql @ExecSQL, N'@Questions NVARCHAR(2000) OUT, @Table NVARCHAR(50)', @Questions OUT, N'Questions';
 SELECT @Questions

CREATE TABLE [dbo].[Questions]
(
    [Id] [uniqueidentifier] NOT NULL,
    [Q1] [varchar](128) NULL,
    [Q2] [varchar](128) NULL,
    [Q3] [varchar](128) NULL,
    [Q4] [varchar](128) NULL,
    [Q5] [varchar](128) NULL,
    [Q6] [varchar](128) NULL,
    [Q7] [varchar](128) NULL,
    [Q8] [varchar](128) NULL,
    [Q9] [varchar](128) NULL,
    [Q10] [varchar](128) NULL,
    [Q11] [varchar](128) NULL,
    [Q12] [varchar](128) NULL,
    [Q13] [varchar](128) NULL,
    [Q14] [varchar](128) NULL,
    [Q15] [varchar](128) NULL,
    [Q16] [varchar](128) NULL,
    [Q17] [varchar](128) NULL,
    [Q18] [varchar](128) NULL,
    [Q19] [varchar](128) NULL
)

Environment 2 Though is running on SQL2014 where as Environment 1 is SQL2012 Is there some deprecated functionality here that I am dealing with? Is there just a bug in my code? Is there a server setting here that could be dealing with the dynamic SQL differently?

Interestingly the same code is run across 4 other tables and they all produce the same result of returning only 1 column back but it does not through an error. ( Top 1? but I don't code that )

Thanks in advance

Clint
  • 1
  • The problem is `SELECT @Cols = COALESCE( @Cols , '''' ) + c.Name + '','' FROM sys.columns c`. The point is very simple you should not concatenate using `+` and use `@Cols = @Cols + ...`. Use STUFF + XML instead. – Lukasz Szozda Apr 05 '16 at 07:14
  • Possible duplicate of [nvarchar concatenation / index / nvarchar(max) inexplicable behavior](http://stackoverflow.com/questions/15138593/nvarchar-concatenation-index-nvarcharmax-inexplicable-behavior) – Lukasz Szozda Apr 05 '16 at 07:15
  • And related [Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location](https://support.microsoft.com/en-us/kb/287515) – Lukasz Szozda Apr 05 '16 at 07:17
  • Thanks Lad2025. Sorry for the delayed feedback but your solution worked perfectly. – Clint Apr 26 '16 at 03:19

0 Answers0