1

I wrote an R script a few months ago that uses RODBC to pull data from SQL Server 2008 R2. Last week, I re-ran the script and noticed some inconsistencies.

Upon investigation, it turns out that running the SQL in management studio now returns a divide by zero error, due to some bad data entry. However, when running the same SQL statement through R/RODBC, it returns all rows up to the one which results in a divide by zero error.

I set errors = TRUE on my sqlQuery call, but that had no effect, nor did adding rows_at_time = 1.

Has anyone seen this behavior, or have any ideas as to how to correct it?

mySQLServer <- odbcDriverConnect(connection = "Driver={SQL Server};server=<server-name>;database=<db>;trusted_connection=yes")

sqlResult <- sqlQuery(mySQLServer, <sqlStatement>, errors = TRUE, rows_at_time = 1)
John Tarr
  • 717
  • 1
  • 9
  • 21
  • Usually you can use `NULLIF(column, 0)` to eliminate the divide by zero error, and filter missing values afterwards, not sure if that is what you needed. – Psidom Dec 28 '16 at 20:14
  • Thanks for the comment, but it isn't what I need. I'm aware of workarounds for the error, but am more concerned with understanding why RODBC is passing back a partial result set as opposed to the error message. Seems like a dangerous behavior. – John Tarr Dec 28 '16 at 20:18
  • This may be a helpful post: [http://stackoverflow.com/questions/3501528/rodbc-functions-and-errors-warnings?rq=1](http://stackoverflow.com/questions/3501528/rodbc-functions-and-errors-warnings?rq=1) – Ben Jacobson Dec 28 '16 at 21:15
  • @BenJacobson Thanks for the post. Unfortunately, I get the same result whether I'm using errors = TRUE or errors = FALSE. sqlQuery returns a dataframe with partial results, with no indication that there was a SQL error stopping it from returning all results. – John Tarr Dec 28 '16 at 21:26

0 Answers0