exec failed because the name not a valid identifier

Question

I have a query I need run it as a dynamic query to output a meaningful column name  As an example  if I run the query directly  it returns data correctly  However  if I use below code  it shows   The name               SELECT  CASE WHEN A Domain IS NOT NULL THEN A Domain ELSE B Domain END  AS  Domain                         CASE WHEN A Email IS NOT NULL THEN A Email ELSE B Email END  AS  Email                        A  Sender Size  AS  Sender Size 1   A  Sender Count  AS  Sender Count 1                           A  Receiver Size  AS  Receiver Size 1   A  Receiver Count  AS  Receiver Count 1                       A  Sender Size 2  AS  Sender Size 2   A  Sender Count 2  AS  Sender Count 2                           A  Receiver Size 2  AS  Receiver Size 2   A  Receiver Count 2  AS  Receiver Count 2                       B SenderSize AS  Sender Size Average   B SenderCount AS  Sender Count Average                           B ReceiverSize AS  Receiv  is not a valid identifier    Below it is the code   DECLARE  query NVARCHAR 4000  SET  query   N SELECT               FROM                               SELECT  CASE WHEN A Domain IS NOT NULL THEN A Domain ELSE B Domain END  AS  Domain                             CASE WHEN A Email IS NOT NULL THEN A Email ELSE B Email END  AS  Email                            A  Sender Size  AS  Sender Size 1   A  Sender Count  AS  Sender Count 1                               A  Receiver Size  AS  Receiver Size 1   A  Receiver Count  AS  Receiver Count 1                           A  Sender Size 2  AS  Sender Size 2   A  Sender Count 2  AS  Sender Count 2                               A  Receiver Size 2  AS  Receiver Size 2   A  Receiver Count 2  AS  Receiver Count 2                           B SenderSize AS  Sender Size Average   B SenderCount AS  Sender Count Average                               B ReceiverSize AS  Receiver Size Average   B ReceiverCount AS  Receiver Count Average                  FROM                                           SELECT  CASE WHEN tf Domain IS NOT NULL THEN tf Domain ELSE tf2 Domain END  AS Domain                                CASE WHEN tf Email IS NOT NULL THEN tf Email ELSE tf2 Email END  AS Email                            ISNULL tf SenderSize 0  AS  Sender Size    ISNULL tf SenderCount 0  AS  Sender Count   ISNULL tf ReceiverSize 0  AS  Receiver Size   ISNULL tf ReceiverCount 0  AS  Receiver Count                             ISNULL tf2 SenderSize 0  AS  Sender Size 2   ISNULL tf2 SenderCount 0  AS  Sender Count 2   ISNULL tf2 ReceiverSize 0  AS  Receiver Size 2   ISNULL tf2 ReceiverCount 0  AS  Receiver Count 2                       FROM  TrafficFinal tf FULL JOIN  TrafficFinal2 tf2 ON  tf Email   tf2 Email AND tf Domain   tf2 Domain                        A FULL JOIN  TrafficFinal3 B ON  A Email   B Email AND A Domain   B Domain                C             ORDER BY Domain  Email        PRINT  query   -- run it exec  query    Is it because of the full join

User · Answer

As was in my case if your sql is generated by concatenating or uses converts then sql at execute need to be prefixed with letter N as below

e.g.

Exec N'Select bla..'

the N defines string literal is unicode.

User · Answer

Try this instead in the end   exec   query    If you do not have the brackets  SQL Server assumes the value of the variable to be a stored procedure name   OR  EXECUTE sp executesql  query   And it should not be because of FULL JOIN  But I hope you have already created the temp tables   TrafficFinal   TrafficFinal2   TrafficFinal3 before this     Please note that there are performance considerations between using EXEC and sp executesql  Because sp executesql uses forced statement caching like an sp  More details here      On another note  is there a reason why you are using dynamic sql for this case  when you can use the query as is  considering you are not doing any query manipulations and executing it the way it is

[sql] exec failed because the name not a valid identifier?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008