Connection to multiple database by a single user at the same time.

hi mateen,
 
as said by u ...u r connected to one database through dsn by the code given above....

now to connect to another database on the same server what i do is as follows....

create a standard class userobject of type transaction.....
write a function in the uo to connect to the other database and use this uo as a transaction object.....

i am putting in the exported user object ...import it and use it....

$PBExportHeader$uo_transaction.sru
$PBExportComments$This is a default transaction object. Developers are suppose to use

this object instead of using SQLCA which is a default object for all the data manipulations

done.
forward
global type uo_transaction from transaction
end type
end forward

global type uo_transaction from transaction
end type
global uo_transaction uo_transaction

forward prototypes
public function integer uf_connect (transaction arg_transaction, boolean arg_showerror)
end prototypes

public function integer uf_connect (transaction arg_transaction, boolean

arg_showerror);Transaction lt_trans

This.DBMS                   =       "MSS Microsoft SQL Server 6.x"
This.DataBase             =       "dbname"
This.ServerName       =  "servername"
This.LogID                   =       "sa"
This.LogPass             =       ""
This.AutoCommit       =  False
This.DBParm             =       ""

lt_trans                        =      This

Connect using lt_trans ;

If This.SQLCODE = -1 Then
      If arg_showerror Then
            MessageBox( "[GES] - Connect", "Could not connect to the database :

~r~n" + &
                                                     This.SQLERRTEXT )
      End If
End If

Return This.SQLCODE

end function
on uo_transaction.create
call super::create
TriggerEvent( this, "constructor" )
end on

on uo_transaction.destroy
TriggerEvent( this, "destructor" )
call super::destroy
end on

//ends here...

now u have a uo with the name uo_transaction and a function in it with the name uf_connect...it has 2 arguments...


in ur window where u want to retrieve these 2 dws of different databases just declare an instance variable as follows..
uo_transaction it_trans

now write the following code in ur open event of the window...

Long   li_success
/* Instantiate the new transaction object */
it_trans = Create uo_transaction

/* Set up the new transaction and log in */
li_Success = it_trans.uf_connect(SQLCA, FALSE)

// dw retrieved from one database using SQLCA
dw_2.SetTransObject(SQLCA)
dw_2.Retrieve()

// dw retrieved from another database using SQLCA
dw_1.SetTransObject(it_trans)
dw_1.Retrieve()

similarly u can make more transaction objects to connect to more databases...

...do ask for more assistance....

Cheers,
Rosh

Hi Rosh:
Thanks 4 your answer.
I am afraid this will not solve my real problem. I want to create a consolidate report from three databases.Therefore,
suppose a dw contain following sql
select a.*,b.*,c.*
from srv1.db1.dbo.tablex a, srv1.db2.dbo.tablex b, srv1.db3.dbo.tablex c
then will data be retrieved in that dw. If so , then my problem is solved.

Mateen

mateen,

just remebered ur previous post on trusted connection and dsn creation...
so what u can do is the code to set into registry and connect using dsn can be written in this user object (uo_transaction)...

i tried it and it works fine....
...so now u can connect to as many databases at the same time thru PB using user object of transaction type...

Cheers,
Rosh

well mateen ur question was a bit misleading,,,,u said......

I want  user1 to be connected to all databases (db1,db2,db3) at the same time so
that dw1.retrieve() or dw2.retrieve() or dw3.retrieve() is executed successfully.


...but now if i'm not mistaken u want to retrieve dw_1 which has all three databases...right....

enlighten me...

Cheers,
Rosh

mateen,
i remember a similar requirement a long time back...
..what i did was i created a table in my primary database and wrote a stored procedure ... from this stored procedure i inserted into my new table....as u must be aware that thru the stored procedure u can access all dbs on the same server...

my consolidated dw was of this new table... u might use it as a solution...

Cheers,
Rosh

Hi rosh:
I am sorry not to narrate my point clearly.

Yes I want to retrieve data from three databases.
....as u must be aware that thru the stored procedure u can access all dbs on the same server.
   as long as  I am in query analyzer i am able to access all dbs on the same server but from within pb I don't know how to do.

Hi Rosh:
I am unable to understand this

I made a dw out of this
select top 1 ca_acc_code from accsrv.shma_d53.dbo.gl_chartofacc
union all
select top 1 ca_acc_code from accsrv.shma_spin.dbo.gl_chartofacc
union all
select top 1 ca_acc_code from accsrv.shma_b26.dbo.gl_chartofacc
and it is working whereas I have connected to only shma_d53. Why

Mateen