Query works in SQL Server but not in R-CodePudding

I wrote a query in SQL Server and it ran without a problem. Here's the query with the names changed for privacy reasons.

SELECT *
FROM table1 (nolock)
LEFT JOIN table2 (nolock)
ON table1.ID = table2.ID
WHERE table1.Date = '2021-03-05' AND table1.ID = '120';

This works fine and pulls 30k rows. I have created an ODBC connection using the DBI and odbc packages to the server in R. I can run queries from R just fine. I've run many without an issue. For example, this runs with no errors:

DBI::dbGetQuery(conn, believeNRows = FALSE, "
    SELECT TOP 10 *
    FROM table1 (nolock);
")

But when I include a LEFT JOIN, then the query in R fails and returns an error.

Here's the same query in R:

DBI::dbGetQuery(conn, believeNRows = FALSE, "
    SELECT *
    FROM table1 (nolock)
    LEFT JOIN table2 (nolock)
    ON table1.ID = table2.ID
    WHERE table1.Date = '2021-03-05' AND table1.ID = '120';
")

I'm working in VSCode so the error message isn't very informative:

Error in app$vspace(new_style$margin-top %||% 0) : attempt to apply non-function

Based on some other answers, I included a couple of extra options in the query, but they didn't help:

DBI::dbGetQuery(conn, believeNRows = FALSE, "
    SET ANSI_WARNINGS OFF;
    SET NOCOUNT ON;

    SELECT *
    FROM table1 (nolock)
    LEFT JOIN table2 (nolock)
    ON table1.ID = table2.ID
    WHERE table1.Date = '2021-03-05' AND table1.ID = '120';
")

Does anyone have any idea why this perfectly good query isn't working when passed to SQL Server from within R?

CodePudding user response：

In case any one else has this problem in the future, there were two hurdles in my way. First, relative to @r2evans comment, my error messages were being masked. Per a readr github issue, I reinstalled the cli package and started getting unmasked error messages. Reran my code and received this error:

Error:
! Column names `ID1` and `Col1` must not be duplicated.
Use .name_repair to specify repair.
Caused by error in `stop_vctrs()`:
! Names must be unique.
x These names are duplicated:
  * "ID1" at locations 3 and 72.
  * "Col1" at locations 45 and 75.
Run `rlang::last_error()` to see where the error occurred.

Turns out, the SQL call brings back all columns and SSMS removes the duplicate columns automatically. R, on the other hand, does not. So I changed my SELECT clause to choose the specific columns I wanted and the code worked.