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.
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)).
The native data types can be consulted:
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:
odbc::odbcConnectionColumns(con, "table_name") (with
con the connection to the database and
table_name the name of the table)data_type, these ID’s can be consulted for a specific query
using the following code:
rs <- dbSendQuery(con, query)
odbc::dbColumnInfo(rs)