SQL Server error message truncated

Question

This is not an error message about truncation. Rather, the error message itself (from SSMS) appears to have been truncated. Any idea what it was trying to tell me here? Is there a way to recover the full error message?

Msg 203, Level 16, State 2, Line 80
The name 'SELECT
SUM(CASE WHEN #actual_types.typ = 'AA' THEN #actual_types.qty ELSE 0 END) AS 'AA_Qty',
SUM(CASE WHEN #actual_types.typ = 'AB' THEN #actual_types.qty ELSE 0 END) AS 'AB_Qty',
SUM(CASE WHEN #actual_types.typ = 'AC' THEN #actual_types.qty ELSE 0 END) AS 'AC_Qty',
SUM(CASE WHEN #actual_types.typ = 'BA' THEN #actual_types.qty ELSE 0 END) AS 'BA_Qty',
SUM(CASE WHEN #actual_types.typ...

I'm using SSMS 2016, connected to a SQL 2000 server. For context - I was trying to create a more generic version of one of my current scripts (works fine against production data), using temp tables and sample data, to save as a reference in case I should need something similar in the future.

This was the code responsible for the error:

CREATE TABLE #possible_types
    (typ    varchar(2))

CREATE TABLE #actual_types
    (typ    varchar(2),
     dt     smalldatetime,
     qty    int)

INSERT  #possible_types
SELECT       'AA'
UNION SELECT 'AB'
UNION SELECT 'AC'
UNION SELECT 'BA'
UNION SELECT 'BB'
UNION SELECT 'BC'

INSERT  #actual_types
SELECT       'AA', '2015-01-01', 123
UNION SELECT 'AA', '2016-01-01', 321
UNION SELECT 'AA', '2017-01-01', 222
UNION SELECT 'BA', '2016-01-01', 777
UNION SELECT 'BC', '2017-01-01', 456

DECLARE @qry varchar(8000)
SELECT  @qry = 'SELECT' + CHAR(13)
SELECT  @qry = @qry + s.sql_gen
    FROM
    (SELECT DISTINCT 'SUM(CASE WHEN #actual_types.typ = '''+ typ + ''' THEN #actual_types.qty ELSE 0 END) AS ''' + typ + '_Qty'',' + char(13) AS [sql_gen]
     FROM #possible_types) s
SELECT  @qry = LEFT(@qry, LEN(@qry) - 2) -- gets rid of last comma and carriage return
SELECT  @qry = @qry + '
                      FROM #actual_types
                      WHERE #actual_types.dt >= ''2017-01-01''
                      '
--PRINT @qry
EXEC    @qry;

DROP TABLE  #possible_types
DROP TABLE  #actual_types

Answer 1

I had the same error. Now it works for me.

I removed the ' for the name of the fields _Qty. (Actually it's not necessary for made your query working).

Moreover I edited:

DECLARE @qry nvarchar(4000)

and

EXECUTE sp_executesql @qry;

Full code edited:

CREATE TABLE #possible_types
    (typ    varchar(2))

CREATE TABLE #actual_types
    (typ    varchar(2),
     dt     smalldatetime,
     qty    int)

INSERT  #possible_types
SELECT       'AA'
UNION SELECT 'AB'
UNION SELECT 'AC'
UNION SELECT 'BA'
UNION SELECT 'BB'
UNION SELECT 'BC'

INSERT  #actual_types
SELECT       'AA', '2015-01-01', 123
UNION SELECT 'AA', '2016-01-01', 321
UNION SELECT 'AA', '2017-01-01', 222
UNION SELECT 'BA', '2016-01-01', 777
UNION SELECT 'BC', '2017-01-01', 456

DECLARE @qry nvarchar(4000)
SELECT  @qry = 'SELECT' + CHAR(13)
SELECT  @qry = @qry + s.sql_gen
    FROM
    (SELECT DISTINCT 'SUM(CASE WHEN #actual_types.typ = '''+ typ + ''' THEN #actual_types.qty ELSE 0 END) AS ' + typ + '_Qty,' + char(13) AS [sql_gen]
     FROM #possible_types) s
SELECT  @qry = LEFT(@qry, LEN(@qry) - 2) -- gets rid of last comma and carriage return
SELECT  @qry = @qry + '
                      FROM #actual_types
                      WHERE dt >= ''2017-01-01'' 
                      '
PRINT @qry
EXECUTE sp_executesql @qry;

DROP TABLE  #possible_types
DROP TABLE  #actual_types

Answer 2

Error messages in sql server are stored in sys.messages. You can query the messages table using the information provided.

select * from sys.messages
where message_id = 203
    and language_id = 1033 --1033 is English (assumed you can read English)

This returns "The name '%.*ls' is not a valid identifier."

You obviously have something else wonky in your code. Can you share your query and we can probably help determine the actual cause of the issue.