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 9i to SQL 2000 linked Server Problem..

When i execute a Query from an Oracle Linked server i got 99 rows less then
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick

Oracle 9i to SQL 2000 linked Server Problem..

When i execute a Query from an Oracle Linked server i got 99 rows less then
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick

Oracle 9i to SQL 2000 linked Server Problem..

When i execute a Query from an Oracle Linked server i got 99 rows less then
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick

Oracle 9i to SQL 2000 linked Server Problem..

When i execute a Query from an Oracle Linked server i got 99 rows less then
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick