[R] help with RODBC

[Jeff Newmiller]

Using HTML email is, as usual, obscuring what you are doing on this end of the communication. The error message indicates that you have single quotes around the BOS term, but they are not visible in the code you have presented. In addition, there is a syntax error in having the word 'from' before the keyword 'select' in your code. It is hard to believe the error and presented code are related.

The actual error given below is different than the one you originally posted. This one suggests that you cannot see a table. This is not an R problem. It could be that that table name is misspelled, or it could mean that the login permissions you are using from R don't allow access to that data table. Or something else... but not related to R. 

Note that there is a mailing list where database use with R is on topic... R-sig-db.
---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
--------------------------------------------------------------------------- 
Sent from my phone. Please excuse my brevity.

On March 5, 2015 7:57:51 AM PST, Alemu Tadesse <alemu.tadesse at gmail.com> wrote:
>Hi Jeff,
>
>Thank you very much for your prompt reply and help.
>
>I have also tried without quote and it did not work for me. I was told
>that
>the query works on the  microsoft sql management studio as shown below.
>[image: Inline image 1]
>
>in my R script what I did is :
>
>ch1 <- odbcConnect(dsn="COLOMSQLQC01", uid="Data_Pull",
>pwd="Pull_Data")
>> ch1
>RODBC Connection 8
>Details:
>  case=nochange
>  DSN=COLOMSQLQC01
>  Description=COLOMSQLQC01
>  UID=DataPull
>  PWD=******
>  APP=RStudio
>  WSID=R9V7LFH
>
>P<-sqlQuery(ch1,'from select DD.WBAN,
>      TimeStamp_Local,
>      ref_density,
>      ref_dewpoint,
>      ref_dir,
>      ref_precip,
>      ref_press,
>      ref_rh,
>      ref_snowfall,
>      ref_snowdepth,
>      ref_temperature_avg,
>      ref_temperature_max,
>      ref_temperature_min,
>      ref_ws_avg,
>      ref_ws_max,
>      ref_wetbulb
>  FROM ASOS.dbo.DailyData DD
>  left outer join ASOS.dbo.ASOS_MetaData MD on MD.WBAN = DD.WBAN
>where CallSign = BOS order by TimeStamp_Local ASC')
>
>As you can see below, there is no data in P
>
>
>> P
>[1] "42S02 208 [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid
>object name 'ASOS.dbo.DailyData'."
>
>
>
>
>
>
>[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT DD.WBAN\n
> ,TimeStamp_Local\n      ,ref_density\n      ,ref_dewpoint\n
> ,ref_dir\n      ,ref_precip\n      ,ref_press\n      ,ref_rh\n
> ,ref_snowfall\n      ,ref_snowdepth\n      ,ref_temperature_avg\n
> ,ref_temperature_max\n      ,ref_temperature_min\n      ,ref_ws_avg\n
> ,ref_ws_max\n      ,ref_wetbulb\n  FROM ASOS.dbo.DailyData DD\n  left
>outer join ASOS.dbo.ASOS_MetaData MD on MD.WBAN = DD.WBAN\nwhere
>CallSign =
>'BOS' order by TimeStamp_Local ASC'"
>
>I did not understand my problem.
>
>Cheers,
>
>Alemu
>
>
>
>
>On Wed, Mar 4, 2015 at 7:23 PM, Jeff Newmiller
><jdnewmil at dcn.davis.ca.us>
>wrote:
>
>> Why do you have single quotes inside your single quotes?
>>
>---------------------------------------------------------------------------
>> Jeff Newmiller                        The     .....       .....  Go
>Live...
>> DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live
>> Go...
>>                                       Live:   OO#.. Dead: OO#.. 
>Playing
>> Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
>> /Software/Embedded Controllers)               .OO#.       .OO#. 
>rocks...1k
>>
>---------------------------------------------------------------------------
>> Sent from my phone. Please excuse my brevity.
>>
>> On March 4, 2015 5:46:23 PM PST, Alemu Tadesse
><alemu.tadesse at gmail.com>
>> wrote:
>> >I want to apply  the following query to my database.
>> >
>> >P2<-sqlQuery(ch1,'select *,
>> >      TimeStamp_Local,
>> >      ref_density,
>> >      ref_dewpoint,
>> >      ref_dir,
>> >      ref_precip,
>> >      ref_press,
>> >      ref_rh,
>> >      ref_snowfall,
>> >      ref_snowdepth,
>> >      ref_temperature_avg,
>> >      ref_temperature_max,
>> >      ref_temperature_min,
>> >      ref_ws_avg,
>> >      ref_ws_max,
>> >      ref_wetbulb
>> >  FROM ASOS.dbo.DailyData DD
>> >  left outer join ASOS.dbo.ASOS_MetaData MD on MD.WBAN = DD.WBAN
>> >where CallSign = 'BOS' order by TimeStamp_Local ASC')
>> >
>> >where ch1 <- odbcConnect(dsn="SQLBI01", uid="DataPull",
>pwd="PullData")
>> >
>> >in R (RODBC). I am running into the following error
>> >
>> >Error: unexpected symbol in:
>> >"  left outer join ASOS.dbo.ASOS_MetaData MD on MD.WBAN = DD.WBAN,
>> >where CallSign = 'BOS"
>> >
>> >I don't know why
>> >
>> >Thanks,
>> >
>> >Alemu
>> >
>> >       [[alternative HTML version deleted]]
>> >
>> >______________________________________________
>> >R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> >https://stat.ethz.ch/mailman/listinfo/r-help
>> >PLEASE do read the posting guide
>> >http://www.R-project.org/posting-guide.html
>> >and provide commented, minimal, self-contained, reproducible code.
>>
>>