Error while writing data from Linux server to SQL Server using dbWriteTable

0
votes

I'm trying to write data to a table in SQL Server using dbWriteTable from odbc package. And, getting this error:

dbWriteTable(con1, 'mtcars', mtcars[1:5, ])

Error: 'CREATE TABLE "mtcars" ( "row_names" varchar(255), "mpg" FLOAT, "cyl" "disp" FLOAT, "hp" FLOAT, "drat" FLOAT, "wt" FLOAT, "qsec" FLOAT, "vs" FLOAT, "am" FLOAT, "gear" FLOAT, "carb" FLOAT)' nanodbc/nanodbc.cpp:1587: 42000: [FreeTDS][SQL Server]Incorrect syntax near 'mtcars'.

It's working fine from a windows machine. Any help would be appreciated..

1
rsql-serverlinuxodbc

1 Answers

2
votes

The QUOTED_IDENTIFIER option is OFF for that session. Probably because of the FreeTDS driver.

The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties. The default for SET QUOTED_IDENTIFIER is OFF for connections from DB-Library applications.

You can try setting QUOTED_IDENTIFER ON, or switch to the Microsoft ODBC Driver for Linux.