CODEWITHSUNDEEP
rrodbcrapache
Justin Dearing
Justin Dearing

Reputation: 14928

RODBC query returns results from Windows, but not from Linux

I have a R script that fetches results from a SQL query from R studio on windows, but not Rapache on linux. My two connection strings are:

channel = odbcDriverConnect('Driver=FreeTDS;Server=server\\instance;Database=DB;Uid=name;Pwd=password')
channel = odbcDriverConnect('Driver={SQL Server};Server=name\\instance;Database=DB;Trusted_Connection=True;')

I construct some SQL and pass it to this function:

get_sql_data <- function(query,as.is=TRUE){
  return(sqlQuery(channel, query,as.is=as.is, errors=TRUE))
}

I look in SQL profile and see a BatchStarting and BatchCompleted event for the query from Rapache. I copy and past the Query into SSDT and get rows back. In Rstudio I can View() the resulting dataframe. In rapache I head it like this:

warning('head head')
warning(head(RESULTS))
warning('head tail')

I get the following:

2: In eval(expr, envir, enclos) : head head 3: In eval(expr, envir, enclos) : 4: In eval(expr, envir, enclos) : head tail

What am I doing wrong? Do I need to use sqlGetResults() or something?

Upvotes: 0

Views: 418

Answers (2)

RyanStochastic
RyanStochastic

Reputation: 4083

I ran into a windows/linux difference in behavior and solved it with the following modification between windows and linux versions:

on windows:

dbhandle <- odbcDriverConnect(paste("driver={SQL Server};server=", ...

on linux:

dbhandle <- odbcDriverConnect(paste("driver=SQLServer;server=", ...

the difference is just in declaring the driver, with/without curly braces and a space between SQL and Server

still searching for an explanation of why this works though...

Upvotes: 1

Justin Dearing
Justin Dearing

Reputation: 14928

So the issue seemed to be that I was declaring variables before my statement like so:

DECLARE @foobar CHAR(3) 'AAA';
SELECT TOP (5) id, name, desc FROM tbl WHERE code = @fooBar;

Changing that to:

SELECT TOP (5) id, name, desc FROM tbl WHERE code = 'AAA';

Caused the result set to appear on in Linux as well as Windows. I'm going to investigate exactly why this happens.

Upvotes: 0

Related Questions