I’m receiving a truly bizarre response from a query in SSMS this afternoon, and I’m not sure how to begin troubleshooting.
I have a very wide table loaded into my data warehouse from a pipe delimited file. ALL of the values are loaded as variously sized varchar columns.
The query I’m running is this:
select 14C_CLINIC_VISIT_NUMBER, 13D_CLINIC_COPAY_AMOUNT from [Table]
The results I’m getting back makes no sense:
| C_CLINIC_VISIT_NUMBER | D_CLINIC_COPAY_AMOUNT |
|---|---|
| 14 | 13 |
| 14 | 13 |
| 14 | 13 |
| 14 | 13 |
...and so on.
The expected result is a text string for each column. In this particular case they should all read ‘N/A’. Also note that the headers being returned are not the correct column names. The numeric characters at the head of each column name have been removed and seem to be returned in the result set.
I’ve confirmed that they are N/A in the pipe delimited file. They are set to D_STR with a length of 9 in SSIS, where they’re loaded in from. The table itself has the field as a varchar(9) datatype. I don’t have DBA level access and I’m unsure of how I could audit the values in the resulting SQL table outside of simply querying, which is what I’m doing.
Has anyone ever seen anything like this before? I’m unsure of where to even troubleshoot.
CodePudding user response：
It is not bizzarre. The string 14C_CLINIC_VISIT_NUMBER is parsed as two tokens - a number and an identifier*. So your query is processed as:
SELECT 14 AS C_CLINIC_VISIT_NUMBER, 13 AS D_CLINIC_COPAY_AMOUNT
FROM ...
You must quote the identifiers for correct parsing:
SELECT [14C_CLINIC_VISIT_NUMBER], [13D_CLINIC_COPAY_AMOUNT]
FROM ...
* In SQL Server the identifiers begin with a-z, _, @ and # (ref). The parser sees digits so assumes a number literal followed by an identifier.
CodePudding user response：
Resolved.
I enclosed the column names in brackets, and then received the correct resultset.
Still a very weird return from SSMS. How is it justified?