Please post here using the plain text option in your email software because the
HTML is not coming through undamaged.
The first argument to the odbcConnect function is never SQL syntax... for SQL
Server it is always a "Data Source Name" (DSN) that has been predefined in the
ODBC administration tool (control panel on Windows).
If the databases you are trying to access are managed by the same server and
your authentication gives you access to all the databases and tables that you
need access to, then you can construct SQL queries that refer to other tables
in other databases on that server using the "dot" notation mentioned by Peter,
and pass all those queries through the same DSN.
Something like
library(RODBC)
con <- odbcConnect("SQLServer2012")
orders1 <- sqlFetch(con,"sportsDB.dbo.sports")
odbcClose(con)
---------------------------------------------------------------------------
Jeff Newmiller The ..... ..... Go Live...
DCN:<jdnew...@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 June 30, 2014 7:39:25 AM PDT, Ira Sharenow <irasharenow...@yahoo.com> wrote:
>Thanks for everyone���s feedback.
>
>library(RODBC)
>
>con = odbcConnect("SQLServer2012")
>
>orders1 = sqlFetch(con,"dbo.orders")
>
>odbcClose(con)
>
>Allowed me to close the connection properly. Thanks.
>
>However, I still cannot figure out how to connect to the second
>database
>and table.
>
>library(RODBC)
>
>>con2 = odbcConnect("[sportsDB].dbo.sports")
>
>Warning messages:
>
>1: In odbcDriverConnect("DSN=[sportsDB].dbo.sports") :
>
>[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver
>Manager] Data source name not found and no default driver specified
>
>2: In odbcDriverConnect("DSN=[sportsDB].dbo.sports") :
>
> ODBC connection failed
>
>>con2 = odbcConnect("[sportsDB].[dbo].sports")
>
>Warning messages:
>
>1: In odbcDriverConnect("DSN=[sportsDB].[dbo].sports") :
>
>[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver
>Manager] Data source name not found and no default driver specified
>
>2: In odbcDriverConnect("DSN=[sportsDB].[dbo].sports") :
>
> ODBC connection failed
>
>>con2 = odbcConnect("[sportsDB].[dbo].[sports]")
>
>Warning messages:
>
>1: In odbcDriverConnect("DSN=[sportsDB].[dbo].[sports]") :
>
>[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver
>Manager] Data source name not found and no default driver specified
>
>2: In odbcDriverConnect("DSN=[sportsDB].[dbo].[sports]") :
>
> ODBC connection failed
>
>>con3 = odbcConnect("SQLServer2012")
>
>>orders3 = sqlFetch(con3, "sportsDB.dbo.sports")
>
>Error in odbcTableExists(channel, sqtable) :
>
> ���sportsDB.dbo.sports���: table not found on channel
>
>On 6/30/2014 1:34 AM, Peter Crowther wrote:
>> On 30 June 2014 02:44, Ira Sharenow <irasharenow...@yahoo.com> wrote:
>>> I wish to query tables that are NOT in the default SQL Server 2012
>database.
>>> Now for the problem. I also want to read in the table dbo.sports.
>That
>>> table is in the database sportsDB. I did not see any way to do so
>from
>>> within R.
>> Can you not use sportsDB.dbo.sports to reference the table?
>>
>> In general, table reference syntax is [ [ [ serverName '.' ]
>> databaseName '.' ] [schema ] '.' ] tableName, where the names need
>> only be surrounded by [...] if they are not valid SQL Server
>> identifiers. Many people may suggest you reference
>> [sportsDB].[dbo].[sports]; this is unnecessary verbiage.
>>
>> Cheers,
>>
>> - Peter
>>
>
>
> [[alternative HTML version deleted]]
>
>
>
>------------------------------------------------------------------------
>
>______________________________________________
>R-help@r-project.org mailing list
>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.
______________________________________________
R-help@r-project.org mailing list
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.
