R help - Jul 2009 - Help with RODBC connection to multiple MS SQL Sever databases

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. I'm currently getting the following
error:
region
[1] "[RODBC] ERROR: Could not SQLExecDirect"
[2] "42000 102 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect
syntax near 'Completed'."

When I look at the tables from the contab<-sqlTables(con), it is only getting
information from one of the databases.

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")
 contab<-sqlTables(con) 
  
 region<-sqlQuery(con,'SELECT DISTINCT dbo_PATIENT.PATIENT,
dbo_CONTRACT.CONTRACT, dbo_PATIENT.PATIENT_EPISODE, dbo_PATIENT.LAST_NAME,
dbo_PATIENT.FIRST_NAME, dbo_CONTRACT.CON_STATUS, dbo_CONTRACT.CON_NUMBER,
dbo_PATIENT.STATE, dbo_REGION_TERR.OPS_REGION, dbo_REGION_TERR.SPG_TERR,
Sum(dbo_Line.total_ln_paid_amount) AS SumOftotal_ln_paid_amount
                       FROM dbo_CONTRACT 
                          INNER JOIN dbo_REGION_TERR
                          RIGHT JOIN dbo_PATIENT 
                              ON dbo_REGION_TERR.STATE = dbo_PATIENT.STATE
                              ON dbo_CONTRACT.PATIENT = dbo_PATIENT.PATIENT 
                              AND dbo_CONTRACT.PatientEpisodeID =
dbo_PATIENT.PATIENT_EPISODE
                          INNER JOIN dbo_Line 
                            ON dbo_PATIENT.PATIENT = dbo_Line.claim
                         WHERE dbo_PATIENT.DIAGNOSIS_TYPE="ABI" 
                               AND dbo_CONTRACT.CON_STATUS In
"Completed"
                               AND dbo_CONTRACT.CON_NUMBER Like "OP*"
                          GROUP BY dbo_PATIENT.PATIENT, dbo_CONTRACT.CONTRACT,
dbo_PATIENT.PATIENT_EPISODE, dbo_PATIENT.LAST_NAME
                                , dbo_PATIENT.FIRST_NAME,
dbo_CONTRACT.CON_STATUS, dbo_CONTRACT.CON_NUMBER, dbo_PATIENT.STATE
                                , dbo_REGION_TERR.OPS_REGION,
dbo_REGION_TERR.SPG_TERR
               ')
 odbcCloseAll()
 
region
[1] "[RODBC] ERROR: Could not SQLExecDirect"
[2] "42000 102 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect
syntax near 'Completed'."
> 
 


Chris Anderson

http://www.seocodebreaker.com/?thankyou-page=429
____________________________________________________________
The easiest way to create a blog. Click now!
http://thirdpartyoffers.netzero.net/TGL2241/fc/BLSrjpYRxiYGYCuyKabTb5JapHgbWqUjvZ4P8AguC0ACizbuKJCNdYf7SL2/
	[[alternative HTML version deleted]]

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.