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'")(withconthe connection to the database andtable_namethe 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")(withconthe connection to the database andtable_namethe 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)