ODBC Connection to SPDS from R - no data or tables.

[burchill]

We have recently setup and ODBC connection to our SPDS server to allow more products to access data directly.  It took a bit of work but we have both 32bit and 64bit connections working to our 64bit SPDS 5.1 server.  We can access files from MS Access and Excel, along with STATA (sort of).   In all cases access is not quite as expected but at least we can get data pulled.

The problem that we have run into is accessing data from R.  I am hoping someone has done this with SPDS and can provide some example code that pulls data - I don't know if this is a a problem with RODBC or with the SPDS ODBC connection.

Getting a connection with ch <- odbcConnect("spdsserv") works fine.

Issuing a sqlTables(ch) returns 0 tables (I know tables exist because I can load them from Access/Excel/STATA)

If I use sqlQuery(ch,"select * from pccf_1986)  all of the fields are pulled but no records (table is found but no data loaded)

if I use sqlFetch(ch,"pccf_1986") the table is not found.

I don't use ODBC very much but there might be a direct execute command that will load data rather than the interpretation.

Thanks for any advice or direction.

I realize that this may be an R problem so I will go to the R boards but I am trying to figure out if it is R or SPDS.

[Ksharp]

It seems a Schema problem.
In SAS you can define it as
libname xx odbc dsn=xxxx schema=dbo ;

I don't know if R function odbcConnect() has an option to define Schema. Or you could check ODBC connection if it could define it .

[burchill · Accepted solution]

Thanks for the reply - yes RODBC (odbcConnect) allows you to define a schema.  I had tried this without success.

I had a reply from the r-help list suggesting :
ch <- odbcConnect('tmp', believeNRows=FALSE, colQuote=NULL)

This is the connection used when connecting via odbc to base SAS tables.  It worked with SPDS as well - the colQuote=NULL doesn't make a difference but the believeNRows does.

CB

[ShelleySessoms]

Hi, 

This is Shelley Sessoms, the community manager for the Admin community. Since it looks like this question has been answered, I'm going to mark it as "Accept as Solution" so that it may help others with a similar question.

Thanks,

Shelley