RODBC query returns results from Windows, but not from Linux

Question

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?

why do you have two connection strings? The second one just overwrites the first. Is one of these strings what you're using on windows and the other one what you're using on linux? — David Marx, Oct 14 '14 at 18:10
@DavidMarxyes that's correct. I use FreeTDS on Linux and the Microsoft driver on Windows. — Justin Dearing, Oct 15 '14 at 13:25

score 1 · Answer 1 · answered Mar 21 '16 at 19:01

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...

score 0 · Answer 2 · answered Oct 15 '14 at 13:30

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.

RODBC query returns results from Windows, but not from Linux

2 Answers2