Can't use NEWID() with odbc

[stlouiso]

So I'm switching a SQL script from RODBC to odbc that uses the NEWID() function. In RODBC the script runs without error but when I run it with odbc, I get this error:

nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]'NEWID' is not a recognized built-in function name.

This error is happening for my team and isn't isolated to one computer. Is this a known issue?

[cderv]

Do you have an example of code your are using ?

This type of errors means it comes from the database driver where it says that the NEWID() function is not one recognized by the driver as an available function.

Are you using the same driver ? only the connection change between RODBC and odbc ?

I can't test anything on SQL server, so I am just trying to help dig into this.

[stlouiso]

Using the same driver as seen below. Here is some code that produces the error for my team.

library(odbc)
con <- dbConnect(odbc(), 
                 Driver = "ODBC Driver 17 for SQL Server", 
                 Server = "Server",
                 Database = "DB",
                 Authentication = "ActiveDirectoryIntegrated")
dbGetQuery(con,
           "SELECT TOP 5 *
            FROM TABLE
            ORDER BY NEWID()"
)

library(RODBC)
con2 <- odbcDriverConnect("Driver={ODBC Driver 17 for SQL Server};
                           Server=Server;
                           Database=DB;
                           Authentication=ActiveDirectoryIntegrated;")

sqlQuery(channel = con2, stringsAsFactors = FALSE, query =
           "SELECT TOP 5 *
            FROM TABLE
            ORDER BY NEWID()"
)

Let me know if anything else needs to be provided.