[R] Help with RODBC connection to multiple MS SQL Sever databases

[Allan Engelhardt]

On 16/07/09 00:13, Chris Anderson wrote:
> I'm trying to pull data from multiple MS SQL Sever databse in R. I can access the databases one at a time, but the tables are to large to pull the entire tables then join then in R. So I need to do a SQL join that will join the tables from the each of the databases. How do I combine the connection so that I can implement it in my sql query below.

If you can't do it in R, don't do it in R.  If it hurts when you bang 
your head against a wall, stop banging your head against the wall.

You don't "combine connections", you ask your RDBMS to join across 
databases.  Ask your DBA to give you an account with read access to all 
the databases you need.  (This assumes they are all on a single RDBMS 
instance, if not ask your DBA to link them first.)  Then open a single 
connection with those user credentials and do the cross-database join on 
the database server.

The MS SQL Server syntax becomes something like

SELECT ...
FROM [database].[schema].[table_or_view]

(or, for joining several linked database servers

SELECT ...
FROM [linked_server].[catalog].[schema].[object_name]

but you'd often try to use just one RDBMS.)

> [...]
> library(RODBC)
> dwparadigm<-odbcConnect(dsn="dwParadigm", uid = "XXXXX", pwd = "XXXXXX", case = "nochange", believeNRows = TRUE)
> rptparadigm<-odbcConnect(dsn="Rpt_Paradigm", uid = "XXXXX", pwd = "XXXX", case = "nochange", believeNRows = TRUE)
> wcrpt<-odbcConnect(dsn="WC_Reporting", uid = "XXXXX", pwd = "XXXXX", case = "nochange", believeNRows = TRUE)
> con<-odbcConnect(dsn=c("dwParadigm","Rpt_Paradigm","WC_Reporting")
>    

Two problems with this:

1. It has a syntax error (missing ')')
2. odbcConnect takes a single string, not a vector of strings.

If you must do it in R, use each of the three connections to grab the 
data and then use merge() and friends to join the data in R.  But you 
already said you can't do that.

Hope this helps a little.

Allan.