dplyr::tbl() help

[Dawie]

I am connecting to a Microsoft SQL db using DBI::dbConnect() function with odbc::odbc() and the connection works fine. I can use the RStudio connection pane to preview tables as well as use the SQL button in RStudio to preview a table with the following SQL code (pre-populated)

-- !preview conn=con3

SELECT * FROM "WEB"."a"."WEB_AGEING"

I would like to use an R Script file to extract and manipulate the data and have used the dplyr::tbl() function without any issues before.

The function below shows the function used:

dplyr::tbl(con3, '"WEB"."a"."WEB_AGEING"')

I get the following error:

Error: nanodbc/nanodbc.cpp:1617: 42000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '"WEB"."a"."WEB_AGEING"'. [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.
'SELECT *
FROM """WEB"".""a"".""WEB_AGEING""" AS "zzz51"
WHERE (0 = 1)

I will appreciate some help with constructing the query part of the tbl() function.

Dawie

[mfherman]

A little tough tell from here without knowing the structure of your database, but you may need to use dbplyr::in_schema() within tbl() to refer to a table in a non-default schema. Something like the following may work:

tbl(in_schema("WEB", "WEB_AGEING"))

[Dawie]

Thank you you are a lifesaver!!!!

It works with dbplyr::in_schema("a", "WEB_AGEING")