Oracle-style exception handling in SQL2000

Hello!

I'm working on a project that migrates an Oracle based DB application into SQL2000 server.

The original version of the application uses the Oracle exception-handling mechanism. In SQL2000 I wasn't able to find exception handling. Of course, there is an error handling mechanism, but it is far less sophisticated.

The raised error doesn't exit the procedure, neither propagate the "error state" through the whole call-stack, which means that I have to simulate the exception handling by putting error check after virtually all the DB access statements, which is a painstaking job.

Is there any better solutions?

Thanks for your help!

BalageNope..

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 1
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

If @.Month1 = '99' Or @.Month2 = '99' Or @.Month3 = '99'
BEGIN
SELECT @.Error_Loc = 2
SELECT @.Error_Message = 'Month Not set for File name. Check System Variables. Values are: '
+ ' Proof_Year=~' + @.Proof_Year + '~'
+ ' Proof_Quarter=~' + @.Proof_Quarter + '~'
SELECT @.Error_Type = 50002
GOTO Load_Ledger_Init_sp_Error
END

One check for system error, one for business logic error|||We are a small company and we specialise in Database Migration. We have our toolbox that we have developed after long years of research and experience. However, even then, there were these hard nuts that we could never crack.

Of late, we have found a powerful utility in a product called Vaman DataServer. This product is essentially a DBMS, but what sets them apart, is their utility to migrate data from any source to any destination. I was a little sceptical to begin with, but eventually I figured that this tool is a nifty little thing that every migration company can use.

Right now it is free, but we are in talks with them for a commercial version, which they say migrates DDLs as well.

You could check out this link
http://www.vaman.net/vmndataserver.asp

You need to login for the download.

Hope this helps.

Oracle tricks

Hi there,

We have a BI application that uses SSAS 2005 cubes. This application needs to work with either an Oracle 9i or SQL Server 2000 data mart. Unfortunately, when connecting to Oracle, the Data Source Views in the SSAS project start complaining because of the way Oracle references tables (Schema.TableName etc., compared to SQL which is just TableName).

Are there any tricks to getting a single SSAS 2005 project to work against either an Oracle 9i data mart or a SQL 2000 data mart?

We would really like to avoid having to create and maintain two SSAS projects.

Thanks,

--Phil

I have not tried this, but if you could set up a "dbo" schema in oracle it might work as SQL2000 has a "default schema" of dbo, so dbo.<tablename> should work in both.

Oracle table design

Hello,

I have an oracle table from which rows are retrieved every 10 mins. Some other application puts rows into the same table. There is a timestamp field and I am planning to use that inorder to retrieve new rows. I am concerned about transactions. There might be a point where that application inserts a row when I try to retrieve and so, I will miss that row no matter I check the timestamp. Can somebody throw some light on this?

Also, when I retrieve rows and process, there might be a situation where the processing is faulty and therefore, that row has to be fetched again sometime.

I am thinking of using a new table. Please suggest some ideas regarding my design.

P.S
I am very interested in knowing such scenarios and I would like to know how to deal with such situations at enterprise level ( I wonder what would be the design in time critical applications where multiple applications put and retrieve rows on a single table using timestamps).Don't add a new table to your schema. It will only make things more complicated then they should be.

I suggest the following :

1. Add a column to your table, say : process_timestamp date not null default to_date('01.01.1901','DD.MM.YYYY'). In fact, you can assign any default value you want, but don't use NULL.

2. Your program that retrieves the rows should only read those having process_timestamp = to_date('01.01.1901','DD.MM.YYYY')

3. If the execution succeeds, update process_timestamp to sysdate. If the execution fails, leave it unchanged.

Doing so,
1. You don't have to change anything to the programs that are inserting rows into your table. The default value (to column process_timestamp) will be assigned automatically.

2. You don't have to worry about rows that are inserted while you are processing. You will pick them up later on.

3. On failure, you will be able to reprocess the rows as many times as necessary.|||Many thanks for the reply!

Oracle Application and laptops

I need advice of a powerfull laptop can bear the future extensions of
Oracle editions and implementing installations of all Oracle products
on it ?Lots of memory, AMD-64 X2 processor, as much harddisk as you can get.
I think, 2GB is the maximum on most, if not all laptops - a laptop is
not a server, most manufacturers think.|||<ehabaziz2001@.gmail.com> wrote in message
news:1141975526.522713.195560@.z34g2000cwc.googlegr oups.com...
> I need advice of a powerfull laptop can bear the future extensions of
> Oracle editions and implementing installations of all Oracle products
> on it ?

Oracle for Sun Sparc Solaris or AIX 5L will not run there, so I wonder if
you really want to do what you are asking about. I would not.|||ehabaziz2001@.gmail.com wrote:
> I need advice of a powerfull laptop can bear the future extensions of
> Oracle editions and implementing installations of all Oracle products
> on it ?

What it is appearing you wish to do is impossible. There is no laptop
capable of running these products and, in fact, I would expect that to
do so would take somewhere between 10 and 12 laptops.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@.x.washington.edu
(replace x with u to respond)