i have a problem
-
i am having 2 systems, 1 is onlinemanager.com, and another is ipts.com
both are of the same company,
onlinemanager is using sqlserver as the database, and ipts is using oracle as the database.
our customer has account on both , customer of onlinemanger has account on ipts, they both are the same co. and same work, ipts is just a portfolio tracking system,
the problem is
the data entered for a particular customer in online manager is not currently shown in ipts, what we want that it should "Policy" data entered in onlinemanager should also get reflected along with "policy " data entered in ipts. I MEAN WHEN THE CUSTOMER IS BROWSING IPTS.COM HE SHOULD BE ABLE TO SEE THE POLICIES ENTERD IN ONLINEMANAGER ALONG WITH THE POLICIES ENTERED IN IPTS. key is there customercode,grpcompanyid, which is same in both the databases.
should i use 2 recordset for this, and show records combining the recordset data, or should i dump the sql data into oracle, and show it,
which way will be more better way?.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
hmmm,
Don't really know which is "better".
If there is a valid reason for entering information for the same customers in 2 different systems that may have conflicting data then:
I would go with 2 recordset approach. This way you are pulling potentially disparate data from 2 different systems and displaying it.
IF on the other hand the two systems should match. You will need to figure out how to perform some sort of x-platform merge replication and that would update both SQL and Oracle. Then you could run a single recordset and produce the data.
I think this boils down more to a "how does my company want to do this" instead of "which way is more efficient".
Either way I would not want to be in your shoes when it comes time to update the information that is being displayed back .....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
This sounds kind of like a good occasion for a linked server in SQL server or Oracle's equivalent should it have one (I would guess so). You can then code a cross server/database query that UNIONs the two data tables and performs any data filtering/massaging necessary.
-
union and all these things are not possible, becoz 2 different servers,oracle & sql, 2 diff tables with different data structures.
the possible solution i can think of is to make a empty recordset with column names
and then first fire a query on oracle, like "select columnnames from policy"
then bind those columns data to an empty recordset
like by doing this
do while not rssql.eof
rsempty("columnname")=rssql("columnname")
and loop
then fire the query "select columnnames from policy " on oracle server
then populate those records in an empty recordset, but the thing is it will
make my page very slow, which is alreay slow.
any better to do this. ?
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
Ultimately I expect your program would prefer a single, comprehensive set of data. This can be accomplished using a UNION if you use CONVERT and AS on the columns to make sure one type and name is used for each version of the column. For those columns in one database that do not have a corresponding column in the other database (regardless of type, name, or table) you can simply return a dummy value such as empty string, zero, or NULL for the database that does not have the corresponding column. Should your program logic on occasion actually need to know which database the row originated from you can add a column with a constant value that differs between the two SELECT statements being UNIONed.
-
but dear, ur forgetting 1 thing, its 2 different data server, one is oracle and another is sql server
i dont know how to attach sqlserver to oracle,sp_addlinkedserver in oracle
becoz the final thing will be used is a oracle, we just need few records which are there in sql server2000.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
If you need to originate from Oracle then that would be a problem. You could try an Oracle forum for something analagous to sp_addlinkedserver unless you already know there isn't such a thing. Alternatively, if acceptable, you could establish another connection from your screen code directly to SQL Server and use it to obtain the cross server data. Barring all that, I would personally tend to run along the line of replicating the data from SQL Server to Oracle rather than create really nasty to maintain and really poor performing screen code. Granted I come from more of a programmer position than a DBA position, but sometimes data replication is just prudent. The replication could be done explicitely by the application code that INSERTS/UPDATES/DELETES from the SQL Server table, as a regularly scheduled batch job (although I don't think batch is OK for you), or, I'm assuming, using triggers. I'm not a trigger expert so I can't say that cross server SQL from a trigger is definately a good or bad idea, but I don't know of any reason it wouldn't work well for this sort of a situation.
Obviously option 1 in my mind is to establish a cross server SELECT, if your environment allows you to do so.
-
OPTION 1 Solution looks very complicated solution to me, becoz i will have to find a way to transfer data from sql to oracle, i will need to create ajob right
but what about when some records in sql server has been edited or deleted, i need even to check that. and so.
all these things may create some new problem
uff
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
Actually Option 1 was to simply leave the data where it is and create a cross server SELECT statement. You can do that if you open a connection to the SQL Server machine instead of Oracle for the purpose of that one query or if you find out how to perform cross server SQL from an Oracle connection. Frankly, I don't see why you wouldn't be able to connect directly to SQL Server in this situation since both servers are internal to your company.
Also, if you use triggers to replicate the data (option 2), you would create INSERT, UPDATE, and DELETE triggers. There are very few statements that would bypass the triggers. TRUNCATE TABLE is one and I assume there may be some bulk insert methods that may bypass triggers as well, but I'm not an expert on the topic.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply