odbc query problem

Description of the problem

If a query is fetched against a odbc based database connection, an error occurs if long data types (varchar(max), nvarchar(max), text, ntext, xml, geography, geometry, hierarchyid, binary, image or rowversion) are not put at the end of the select statement. As it is not in our power to change this shortcoming, we replaced the original cryptic warning by an informative one mentioning the columns that should be put in the end of the select statement.

This problem has been solved from odbc 1.2.3, so we recommend installing an update of this package. Shortly the adapted warning and this vignette will be removed from inbodb.

Some background on SQL Server and odbc data types

It seems that the error corresponds to the native data types in the SQL Server database (with the exception that data type xml causes an error in Windows OS but not in Unix OS). These data types are however translated to odbc data types that do not always correspond to the native types, and the latter are OS dependent. E.g. the native data type nvarchar(max) becomes odbc type ntext in Windows OS and nvarchar in Unix OS. The data types that are shown in RStudio in the Connections pane, are the odbc data types.

In Windows OS, luckily, native long data types always correspond to odbc long data types, and vice versa (despite the fact that de exact data types do not always correspond). In Unix OS, the native long data types nvarchar(max) and varchar(max) correspond to the odbc ‘short’ data types nvarchar and varchar. So in Windows the data types shown in the RStudio Connection pane can be used to determine which columns should be placed in the end of the select statement. In Unix, the native data types should be checked (or should additionally be checked for nvarchar(max) and varchar(max)).

Consulting data types

The native data types can be consulted:

• directly in SQL Server via applications such as Microsoft SQL Server Management Studio or Azure Data Studio
• in R by querying separate tables with dbGetQuery(con, "sp_columns 'table_name'") (with con the connection to the database and table_name the name of the table)

The odbc data types can be consulted:

• in the Connections pane in RStudio
• in R by querying separate tables with odbc::odbcConnectionColumns(con, "table_name") (with con the connection to the database and table_name the name of the table)
• once you know the corresponding ID’s in column data_type, these ID’s can be consulted for a specific query using the following code:

rs <- dbSendQuery(con, query)
odbc::dbColumnInfo(rs)