Ed
Wed Sep 28 15:07:11 CDT 2005
Thanks for your rapid response. Option 2 looks like where I need to go. I
was afraid it would not be easy. You have provided a good roadmap.
Again Thanks,
Ed Warren.
"AMDRIT" <amdrit@hotmail.com> wrote in message
news:udCeuQGxFHA.3720@TK2MSFTNGP11.phx.gbl...
> You can solve this in at least two manners.
>
> 1. Let SQL server bridge the connection between the two databases. If
> they are on two seperate servers, us a linked server. Pick one database
> to host as director for data operations. Using stored procedures
> manipulate the data as you need. You can use views as long as both
> primary keys are present, scripting the database can become tedious
> though.
>
> Local Server 2 Databases
>
> SELECT Property.PropertyDescription, People.MasterName,
> People.MasterAddress
> FROM database1.dbo.Property Property INNER JOIN Database2.dbo.People
> People ON Property.MasterID = People.MasterID;
>
> 2 SQL Servers 2 Databases (Server1 contains the People table in Database1
> and links to Server 2)
>
> --Server2 is a linked server
> -- to grant access to Database 2's Property Table
> --refer to sp_addlinkedserver in help docs
>
> SELECT Property.PropertyDescription, People.MasterName,
> People.MasterAddress
> FROM server2.database1.dbo.Property Property INNER JOIN
> Database2.dbo.People People ON
> Property.MasterID = People.MasterID;
>
>
> 2. Make a call a call to each database, and then populate a datatable
> accordingly. All DML will be manual.
>
>
> adp1.fill(peopletable)
> adp2.fill(propertytable)
>
> for each dr1 as datarow in peopletable.rows
> for each dr2 as datarow in propertytable.rows
> set drMixed = mydataset.mixedtable.newmixedrow
> with drmixed
> .MasterName = dr1.MasterName
> .PropertyDescription = dr2.PropertyDescription
> end with
> mydataset.mixedtable.addrow(drmixed)
> next dr2
> next
>
> 'Accept the changes so that they are not flagged when evaluating for
> later updates
> mydataset.mixedtable.acceptchanges
>
> dim dt as dataset.MixedDatatable
>
> dt = mydataset.MixedTable.Getchanges(Updated or Inserted or Deleted)
>
> for each dr as datarow in dt.row
> if dr.rowstate = Updated then
> 'Im sure we could test what was changed
> ssql = "Update people set.... where.."
> cmdPeople.execute ssql
> ssql = "Update properties set... where"
> cmdProperties.execute ssql
> end if
> next dr
>
> HTH
>
> "Ed Warren" <eowarren@fakeaddress.zzz> wrote in message
> news:uVjNdCGxFHA.2656@TK2MSFTNGP09.phx.gbl...
>> Database1 contains a table of people with addresses (sql Server)
>> Table: People
>> MasterID MasterName MasterAddress1 etc.
>>
>>
>> DataBase2 Contains a table of people and the property they own.
>> (msAccess -> imported from the sqlServer but detached)
>> Table Property
>> PropertyID MasterID PropertyDescription, etc.
>>
>>
>> I can easily put together oledbdataadaptors to fill a common dataset
>> (dataset1) with:
>>
>> People Table
>> and
>> Property Table
>>
>> My task is to generate a table/view something like
>>
>> SELECT Property.PropertyDescription, People.MasterName,
>> People.MasterAddress
>> FROM Property INNER JOIN People ON Property.MasterID = People.MasterID;
>>
>> I'm at a loss as to what Ado.net widget, and/or process to use to do
>> this. It would be easy if they came from the same database (they sorta do
>> but we can't go there) ;>.
>>
>> The only approach I can dream up is to create a temp table in one of the
>> databases of the data from the other then do a query, but for several
>> reasons, I would like to find a more 'elegant' way to do this.
>>
>> Ed Warren.
>>
>
>