Migrating Oracle to Sqlserver 2000 
Author Message
 Migrating Oracle to Sqlserver 2000

Hello All,
                While migrating oracle to sqlserver 2000 we converted
strored procedures and packages to Sqlserver. Can you clarify my following
doubts?.
                        1. Can i use Query hints for FIRST_ROWS ?.
                        2. Can i use INSTEAD OF trigger for BEFORE TRIGGER?
                        3. For EACH ROW in Oracle, We planned to convert the
trigger in the following ways. Please let me know which is the optimum
solution for the three different concepts given below or let me know if you
have some other best solution.

(a).  First way, Can i use the cursors for virtual tables and calling stored
procedures. I know it will take more times to execute the query  b'cos the
stored procedure has more DML statements.For example.

CREATE TRIGGER TRG_INS_EMP
ON  EMP
FOR DELETE AS
DECLARE


declare curDeleted cursor for
SELECT  empno, ename from deleted;
begin
       OPEN curDeleted;


       BEGIN


      END;
      CLOSE curDeleted;
    DEALLOCATE curDeleted;
END;

                                (b) second way, Can i use dynamic sql
statement for virtutal tables inside the stored procedures. I didn't worked
arround this concept. B'cos we can write  a correlated sub queries with
these virtual tables.
                                (c) Third way, Can i use all the stored
procedure statments in the trigger. But it will recompile every time. So it
will time to execute the queries.

            If you have any other idea give me TIPS. The MS library is less
information for Migarting Oracle to Sqlserver 2000.

regards,
sivababu



Fri, 28 May 2004 14:28:42 GMT
 Migrating Oracle to Sqlserver 2000

sivababu,

Quote:
>  1. Can i use Query hints for FIRST_ROWS ?.

Look at OPTION (FAST n) for select statements.

Quote:
>  2. Can i use INSTEAD OF trigger for BEFORE TRIGGER?

Yes you can. The INSTEAD OF trigger does not actually carry out the action,
such as a delete, so you have to 'do it again for real' in the body of the
trigger.

Quote:
> 3. For EACH ROW in Oracle, We planned to convert the
> trigger in the following ways. Please let me know which is the optimum
> solution for the three different concepts given below or let me know if
you
> have some other best solution.

> (c) Third way, Can i use all the stored
> procedure statments in the trigger. But it will recompile every time. So
it
> will time to execute the queries.

Try this approach first. However, try to avoid cursors all together. For
example, if you use the UPDATE ... FROM <table> syntax, you can usually do
away with cursors in favor of set operations.

Why do you think the trigger will recompile each time it fires? If this is a
problem, post some code so people can look at it.

Quote:
>             If you have any other idea give me TIPS. The MS library is
less
> information for Migarting Oracle to Sqlserver 2000.

The following document titled "Migrating Oracle Databases to SQL Server
2000"
is a reprint from the SQL Server 2000 Resource Kit. It summarizes
differences
between Oracle and SQL Server.

http://www.microsoft.com/SQL/techinfo/deployment/2000/migrateoracle.asp

Another good resource is "Oracle8i and Microsoft SQL Server 2000
Integration" by Stephen Chelack, ISBN 0-7645-4699-6.

Linda



Fri, 28 May 2004 14:50:09 GMT
 Migrating Oracle to Sqlserver 2000
Hello linda and all,

   Thanks for immediate reply. It is very usefull for us.
  1. Can i use INSTEAD OF trigger for BEFORE TRIGGER?
 Yes you can. The INSTEAD OF trigger does not actually carry out the action,
 such as a delete, so you have to 'do it again for real' in the body of the
 trigger.

       ** Suppose i have three tables, Say A , B & C before deleting from C
i have to update A & B tables. The Pk column name of C is ID. It is
referenced in two tables A & B. Can i use UPDATE CASECADE for this case?.

  2. For EACH ROW in Oracle,

 Try this approach first. However, try to avoid cursors all together. For
 example, if you use the UPDATE ... FROM <table> syntax, you can usually do
 away with cursors in favor of set operations.

 Why do you think the trigger will recompile each time it fires? If this is
a
 problem, post some code so people can look at it.

** Yes. In ORACLE, It will recompile whenever it will call. So we created
stored procedure. We are also calling same procedure in different triggers.
If i wil write it in the trigger i will get more redundancy code. We have
more than 200 procedures and triggers. How can i solve it with out
redundancy?. Can you have any sample code for triggers, ie.. multiple rows
updation in a single operation?. I need insert , Delete and update statement
for different tables inside the trigger. Will it make any problem if i will
use chaining trigger for the tables?.

 Another good resource is "Oracle8i and Microsoft SQL Server 2000
 Integration" by Stephen Chelack, ISBN 0-7645-4699-6.

   ** We have this book. But less explanation about the triggers. ie with
out examples.

regards,
sivababu



Fri, 28 May 2004 21:16:54 GMT
 Migrating Oracle to Sqlserver 2000
sivababu,

Quote:
>        ** Suppose i have three tables, Say A , B & C before deleting from
C
> i have to update A & B tables. The Pk column name of C is ID. It is
> referenced in two tables A & B. Can i use UPDATE CASECADE for this case?.

Yes, you can. Have you tried it?

A good rule of thumb is, if something is documented in Books Online, then it
is a supported feature that you can use. :)

Quote:
>   2. For EACH ROW in Oracle,

>  Try this approach first. However, try to avoid cursors all together. For
>  example, if you use the UPDATE ... FROM <table> syntax, you can usually
do
>  away with cursors in favor of set operations.

>  Why do you think the trigger will recompile each time it fires? If this
is
>  a problem, post some code so people can look at it.

> ** Yes. In ORACLE, It will recompile whenever it will call. So we created
> stored procedure. We are also calling same procedure in different
triggers.
> If i wil write it in the trigger i will get more redundancy code. We have
> more than 200 procedures and triggers. How can i solve it with out
> redundancy?. Can you have any sample code for triggers, ie.. multiple rows
> updation in a single operation?. I need insert , Delete and update
statement
> for different tables inside the trigger. Will it make any problem if i
will
> use chaining trigger for the tables?.

Do some performance testing to see if your triggers are recomiling. If so
you can make changes to your code to avoid this.

Executing stored procedures within a trigger is not a problem. However, the
inserted and deleted tables are not visible to the procedures. You should
eliminate the cursors for performance reasons. They are significantly slower
than set operations. If you write your triggers without cursors you may end
up with less code that is easier to maintin because you do not need the loop
control structures and collateral code to support then.

'Chaining' triggers can be a problem because you are holding locks on tables
thoughout the entire operation, starting from the original triggering
statement. This can lead to concurrency problems and deadlocks if the
triggers are slow.

Refer to Books Online, CREATE TRIGGER, Example E to see how to update
multiple rows in a trigger. Refer to the UPDATE statement for examples of
updating multiple rows from another table.

If you need assistance writing a trigger, please post DDL (CREATE TABLE
statements) and sample data in the form of INSERT statements so that we have
a better idea of what you need.

Linda



Fri, 28 May 2004 22:58:50 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. is there any tools for oracle migrate to SQLserver 2000 or 7

2. migrating from sqlserver 7 to 2000

3. Migrating 2 SQLServer 2000 from Access 97 (passthrough)

4. Migrating SqlServer 6.5 database to SqlServer 7.0

5. Migrate Unicode Data from SqlServer to Oracle.

6. migrating oracle -sqlserver

7. Migrate Unicode Data from SqlServer to Oracle.

8. I am migrating my system VB with Oracle for VB with SQLServer

9. I am migrating my system VB with Oracle for VB with SQLServer

10. I am migrating my system VB with Oracle for VB with SQLServer

11. how to Migrate oracle database to sqlserver ??

12. Migrate from Oracle to Sqlserver


 
Powered by phpBB® Forum Software