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 Virtual Private Database equivalent feature in SQL Server 2005

I have used Oracle Virtual Private Database in my earlier project. I would like to know if a similar feature is available in SQL Server or not.

Please let me know.

Thanks

Sqlserver does support row level security. You can find more info here.
http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx

Oracle pl/sql and MSSQLserver

I have a solid experience in Oracle PL/SQL but now I am involved in a new migration project under MSSQLServer 2000 Database.
Anybody knows any documentation about sql programming in MSSQLServer to create my Stored Procedures, functions, Packages, tables and so on?I would start with the BOL Books on line which comes with SQL server. I am guessing you might not have it below is a link to where it can be downloaded.

http://www.microsoft.com/sql/techinfo/productdoc/2000/default.asp

Oracle Data Source Problem: Cannot connect during deployment. Help!

I am developing an SSAS project which looks at an Oracle database but I cannot for the life of me get it to process.

This is one of those awful Oracle connectivity problems!

Now, in the DataSource, I can edit and the test connection works perfectly. Every other application can connect to the Oracel server correctly. The problem comes when I try to deploy my project. I get errors left right and centre. They even change between attempts! Most common is TNS name resolution error:

Error 1 OLE DB error: OLE DB or ODBC error: ORA-12154: TNS:could not resolve the connect identifier specified. 0 0

Yet, as I have said, when I test the connection everything is OK. And I can use the same connection details in every other application. What is so different about the deployment operation that means it cannot find the connection configuration?

I have tried all the providers, impersonation etc. FYI: the main method for getting the Oracle connection details is through LDAP.

OK.. I found a work around.. I put the connection details directly into the tnsnames.ora file and everything went smoothly again!

So, why does the deployment processing not look at sqlnet.ora when everything else does, including the UI/designer? I am just worried things might get difficult in a production environment.

The other interesting thing is that this has worked from my other machine, so their must be some Oracle config somewhere? Any Oracle connectivity experts out there?

|||

If you running 64 bit be aware of this thread as well --> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=159581&SiteID=1

Oracle Client Exception

Hi,

I've been working with MS XP OS and Oracle Client 9i (9.2) and my project was working properly. In another machine with the OS: MS Windows 2003 Server SP.1 and Oracle Client 10g I'm getting the error "System.Data.OracleClient requires Oracle client software version 8.1.7 or greater " when trying to open a connection.

Please help,

thnx

Hi,

YOu either have the Oracle client not installed / the installation is messed up and you will have to install it again or it could be based on the following issue:

http://dotnetjunkies.com/WebLog/rtgurskevik/archive/2005/01/19/45958.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I had a similar problem with Reporting Services trying to access an Oracle 9i db, this response from Robert Bruckner helped:

Another user replied to a Forums thread you started !

Re: 2005 Reporting Srv cannot connect to Oracle datasource on web server

Posted by Robert Bruckner MSFT in SQL Server Reporting Services

Most likely the RS web service and the RS windows service cannot access the Oracle client installation due to missing file system permissions. Please r! ead the following KB article: http://support.microsoft.com/default.aspx?scid=kb;en-us;870668

-- Robert

Hope this helps you as well

Richard

Oracle Client Exception

Hi,

I've been working with MS XP OS and Oracle Client 9i (9.2) and my project was working properly. In another machine with the OS: MS Windows 2003 Server SP.1 and Oracle Client 10g I'm getting the error "System.Data.OracleClient requires Oracle client software version 8.1.7 or greater " when trying to open a connection.

Please help,

thnx

Hi,

YOu either have the Oracle client not installed / the installation is messed up and you will have to install it again or it could be based on the following issue:

http://dotnetjunkies.com/WebLog/rtgurskevik/archive/2005/01/19/45958.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I had a similar problem with Reporting Services trying to access an Oracle 9i db, this response from Robert Bruckner helped:

Another user replied to a Forums thread you started !

Re: 2005 Reporting Srv cannot connect to Oracle datasource on web server

Posted by Robert Bruckner MSFT in SQL Server Reporting Services

Most likely the RS web service and the RS windows service cannot access the Oracle client installation due to missing file system permissions. Please r! ead the following KB article: http://support.microsoft.com/default.aspx?scid=kb;en-us;870668

-- Robert

Hope this helps you as well

Richard