Rodbc Temporary Table Issue When Connecting to Ms SQL Server

R and MSSQL - communication with temp tables (table not found on channel)

I finally found that the cause of this error was in the settings of ODBC Data Source. It seems that communication with temp tables using R functions such as sqlSave or sqlUpdate requires the default database in ODBC data source to be set to 'tempdb'.
So, now I'm able to use sqlSave() function to insert values in temp table. This function in fact has much better performance than using sqlQuery function with the direct 'INSERT' query as a parameter.

RODBC command 'sqlQuery' has problems with table variables in t-SQL

Try toggling NOCOUNT as below:

old_qry <- "
DECLARE @tbl_IDs TABLE 
(
    Country nvarchar(30),
    CID nvarchar(5),
    PriceID int,
    WindID int
)

INSERT INTO @tbl_IDs
VALUES 
    ('Germany', 'DE', 112000001, 256000002);

SELECT * FROM @tbl_Ids
"
##
new_qry <- "
SET NOCOUNT ON;
DECLARE @tbl_IDs TABLE 
(
    Country nvarchar(30),
    CID nvarchar(5),
    PriceID int,
    WindID int
);

INSERT INTO @tbl_IDs
VALUES 
    ('Germany', 'DE', 112000001, 256000002);
SET NOCOUNT OFF;
SELECT * FROM @tbl_Ids
"

---

R> sqlQuery(tcon, gsub("\\n", " ", old_qry))
#character(0)
R> sqlQuery(tcon, gsub("\\n", " ", new_qry))
#  Country CID   PriceID    WindID
#1 Germany  DE 112000001 256000002

---

Basically you want to SET NOCOUNT ON at the beginning of your code, and SET NOCOUNT OFF just before the final SELECT statement.

sql temporary tables in rstudio notebook's sql chunks?

Like this question, it will work if you put

set nocount on

at the top of your chunk. R seems to get confused when it's handed back the rowcount for the temp table.

SQL Server Query failing when executed from R

Okay, so I think I've figured out what is going wrong here. The subquery

select
    obj_id,
    max(obj_name) as obj_name,
    max(obj_dt) as obj_dt
    from #A
group by obj_id

produces a hidden warning. You don't see the warning if you just run the code as is, but if you store the output in a temporary table then the warning message is produced:

select
    obj_id,
    max(obj_name) as obj_name,
    max(obj_dt) as obj_dt
    into #C
    from #A
group by obj_id

Warning: Null value is eliminated by an aggregate or other SET
operation.

The warning is hidden when this is run as part of the subquery in the original SQL code in the question. I believe this message is somehow part of output that R is "seeing", and once R sees that output it terminates the query. But since no results have been returned yet the output in R is empty (i.e. character(0)).

To solve this issue I coalesced the variables that I'm computing the max of to some minimal values (I'm not sure about what the minimal character is in sql server collation but '0' worked for my purposes). The idea is to remove NULL values before aggregation so no warning is generated. The final working SQL code is below:

set nocount on;
if OBJECT_ID('tempdb..#A') IS NOT NULL DROP TABLE #A;

set nocount on;
create table #A
( obj_id int,
  obj_name varchar(50),
  obj_dt   datetime);

  insert into #A (
  obj_id,
  obj_name,
  obj_dt)
  values
  ( 1
   ,'name'
   ,'2019-01-01 00:00:00'
  ),
  ( 2
   ,NULL
   ,NULL
  ),
  ( 2
   ,'alias'
   ,'2019-02-01 00:00:00'
  );

set nocount on;
if OBJECT_ID('tempdb..#B') IS NOT NULL DROP TABLE #B;

set nocount on;
select 
 #A.obj_id
,subq.obj_name
,subq.obj_dt
into #B 
from #A
join
(select
        obj_id,
        max(isnull(obj_name,'0')) as obj_name,
        max(isnull(obj_dt,cast(-1 as datetime))) as obj_dt
      from #A
      group by obj_id) as subq 
on #A.obj_id = subq.obj_id;

set nocount on;
select * from #B; 

I believe this behavior should be addressed in the RODBC package as it is quite likely to trip up others and can be a bit tricky to track down the root cause and troubleshoot.

Insert R dataframe into SQL (RODBC) - error table not found

To avoid the error, you could specify the database in the connection string:

Driver=ODBC Driver 17 for SQL Server; Server = someserveraddress; database = some_db; Uid = user_login; Pwd = some_password

and avoid using brackets:

sqlSave(myconn, mydf, tablename = 'some_schema.my_table',  append = F, rownames = F,  verbose=TRUE)

SQL Server RODBC Connection

library(RODBC)
dbhandle <- odbcDriverConnect('driver={SQL Server};server=mysqlhost;database=mydbname;trusted_connection=true')
res <- sqlQuery(dbhandle, 'select * from information_schema.tables')

---

Related Topics