3

I have searched quite a bit and read the package documentation, but can't find a solution for this.

I'm using RODBC 1.3-12 against Microsoft SQL Server with the below details.

DBMS_Name       "Microsoft SQL Server"    
DBMS_Ver        "10.50.6220"           
Driver_ODBC_Ver "03.52"            
Driver_Name     "SQLSRV32.DLL"             
Driver_Ver      "06.01.7601"         
ODBC_Ver        "03.80.0000"

I'm not sure how to give a reproducable example, since you'd need a DB, but below is code similar to what I'm using. 

myConnection<- odbcDriverConnect(connection = "Driver={SQL Server};server=myServerName;database=myDBName;trusted_connection=yes")

myDataFrame <- sqlQuery(myConnection, "select top 100 * from myTable", as.is=TRUE)

Per the documentation, doubles are coming back as numeric, as I'd expect, but float and money come back as character.

Is anyone aware of a way around this?

John Tarr
  • 717
  • 1
  • 9
  • 21
  • Cast them to `DECIMAL` in your SQL expression, or cast them to `numeric` within R? – nrussell Oct 22 '15 at 14:35
  • @nrussell Thanks, but I often find myself having to explore tables, with anywhere from 30-180 columns. When I'm just pulling up the table to have a look around, I don't want to have to specify all the columns. – John Tarr Oct 22 '15 at 14:40

1 Answers1

7

It looks like the as.is argument is causing this (not exactly sure why; I've never set that argument): 

R> str(sqlQuery(tcon, qry))
'data.frame':   1 obs. of  4 variables:
 $ DecimalCol: num 9.5
 $ VarcharCol: chr "some text"
 $ FloatCol  : num 8.55
 $ MoneyCol  : num 100
##
R> str(sqlQuery(tcon, qry, as.is = TRUE))
'data.frame':   1 obs. of  4 variables:
 $ DecimalCol: chr "9.50"
 $ VarcharCol: chr "some text"
 $ FloatCol  : chr "8.5540000000000003"
 $ MoneyCol  : chr "99.9500"
nrussell
  • 18,382
  • 4
  • 47
  • 60
  • 2
    Wow, thank you. I think I started using that a while back to fix a particular issue (or maybe just trying to fix an issue), and then it kept getting carried over into my new code without even thinking about it. – John Tarr Oct 22 '15 at 15:06