SQL Passthrough creates character vars of length 32767

[jlh368]

Hello, 

Support on an external server for the SQL driver ended.  We are using in our ODBC connection with the driver SQL Server.  We recreated the connection with ODBC Driver 17 for SQL Server. This reestablished our connection but the data returned for some character vars is returned at a length of 32767.  I can cast the vars in the pass thru to get smaller numbers cast(var as varchar(9)) as var_name1.  This seems like an extra step.  What am I missing? Should I use the SAS/Access driver?  

[SASKiwi]

What type are these columns in SQL Server? You can define variable lengths in SAS for these also. Also we have SAS option COMPRESS = YES set to avoid disk space blowing out. 

[jlh368]

Thanks that is a good direction to question.  The SQL column is varchar(max).  It is used to store multiple types of data with an associated key field.  It looks like the field that is being returned was created as an integer when it should have been a character field of length 10.  Instead it was stored as an integer hence the max length standard integer being returned.  Thanks!

[Tom]

---

@jlh368 wrote:

Thanks that is a good direction to question.  The SQL column is varchar(max).  It is used to store multiple types of data with an associated key field.  It looks like the field that is being returned was created as an integer when it should have been a character field of length 10.  Instead it was stored as an integer hence the max length standard integer being returned.  Thanks!

---

So the cause seems to be the way the variable is defined in the remote database.  The variable is begin defined as length $32767 in SAS because that is the maximum length that SAS allows for a character variable.

If you are using implicit passthru you can control the SAS type created by using the DBSASTYPE= dataset option.

The second part of your message does not make any sense to me.  If the variable is defined as an INTEGER in the remote database then SAS will transfer it to a numeric variable (SAS stores all numbers as 8 byte floating point numbers).