[R-sig-DB] SQLServer and RODBC

[Craig Mundy]

Hello,

I have been having similar problems to Xavier when connecting to Sql Server 2008 using RODBC. In addition to the date problem, I have two floating point fields in SQL Server that are truncated somewhere in the process. I have had slightly better success by creating a dummy Access database, with linked tables through to SQL Server, and then using RODBC to connect to the .mdb file. But, still, It's not ideal, and the resulting data types are not faithfull to the original.

The context of my attempts is to pull time/spatial data out of SQL Server 2008 into R for furhter analyses. I understand that ODBC is a fairly basic protocol, and possibuly restricted to primitive data types and may not handle Blob fields such as WKB. Hence the ability to at least get Lat/Long or Eastings & Northings (with decimals in tact) is important.

I am currently exploring the concept of RJDBC as an alternative to RODBC, to connect to SQL Server. Has anyone had any exerience, or a perspective on the ability of RJDBC in terms of being faithfull to data types.

I have scoured all of the pdf's for packages Rgdal, DBI, RODBC, and RJDBC ... and other than working my through getting the syntax right, I'm none the wiser about what data types these packages will support.

Many thanks

Craig



	[[alternative HTML version deleted]]