SQLTeam.com | Weblogs | Forums

After migration Oracle database into SQL Server 2016 facing problem in VB 6.0 application

After Migration Oracle database into SQL Server 2016 facing problem in VB 6.0 application i.e. Some kind of error occurs in back end stored procedure that will not showing in front end application is this problem of driver which is use connection or else

In sql server & VB 6.0 application using : Provider='sqloledb'

In oracle & VB 6.0 application using : Provider=MSDASQL' DSN Connection

If i execute procedure in SSMS it's showing result like

Msg 8114, Level 16, State 5, Procedure p1no, Line 14 [Batch Start Line 45]
Error converting data type varchar to numeric.

but in vb application showing like given below and not showing main procedure or sub procedure name and line no.

Run-time error -2147xxxxxxx
Error converting data type varchar to numeric.

Please help

Can you get the SQL scripts the VB code passes to the database?
Execute each SQL script one by one to find the SQL script that gives an error.

This has nothing to do with Oracle vs SQL Server drivers in my opinion.
The SQL script presumably contains a bug.
The script is using the wrong column or does not convert it in the way SQL Server needs.
Oracle may handle (automatic typecast) this situation differently.

If any error occur oracle stored procedure it will show in vb application like given below

ORA-00907: missing right parenthesis
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "EST.D_SQL", line 13
ORA-06512: at "EST.P_VAL_C", line 512
ORA-06512: at "EST.P_EST_MAIN", line 29
ORA-06512: at "EST.P_EST_C", line 31
ORA-06512: at line 2

But after migration with sql server if any error occur at database site it is not showing in vb application and passing control to next line

You probably need to:

SET XACT_ABORT ON;

at the start of the SQL Server stored procedure.

Hi Ifor,

SET XACT_ABORT ON was new for me. I Googled it; This is what I found

Your stored procedures should always include this statement in the beginning:

SET XACT_ABORT, NOCOUNT ON

This turns on two session options that are off by default for legacy reasons, but experience has proven that best practice is to always have them on. The default behaviour in SQL Server when there is no surrounding TRY-CATCH is that some errors abort execution and roll back any open transaction, whereas with other errors execution continues on the next statement. When you activate XACT_ABORT ON, almost all errors have the same effect: any open transaction is rolled back and execution is aborted. There are a few exceptions of which the most prominent is the RAISERROR statement.

The option XACT_ABORT is essential for a more reliable error and transaction handling. Particularly, with the default behaviour there are several situations where execution can be aborted without any open transaction being rolled back, even if you have TRY-CATCH. We saw one such example in the previous section where we learnt that TRY-CATCH does not catch compilations errors in the same scope. An open transaction which is not rolled back in case of an error can cause major problems if the application jogs along without committing or rolling back.

For good error handling in SQL Server, you need both TRY-CATCH and SET XACT_ABORT ON. Of these two, SET XACT_ABORT ON is the most important. For production-grade code it's not really sufficient to rely on XACT_ABORT, but for quick and simple stuff it can do.

Rolling back a transaction is a good thing to preserve data integrity. No discussion about that.
Yet at the end nothing has happened. How will this solve Tushark's problem?

It might not but:

  1. The OP mentioned an Oracle stored procedure so has probably translated it to a SQL stored procedure.
  2. The OP may not be that familiar with SQL Server so may not have XACT_ABORT at the top of the sp.
  3. Without XACT_ABORT the sp could have thrown an error and then continued processing the sp in such a way that the sp as a whole did not return an exception.

You have to use a branded tool to remove such errors.

Tushark,

First, your error seems like it in the Stored Procedure and needs a CAST.

Second, as for your connection, you may want to try SQL Server Native since it is the newer OLEDB and has more features. You should google the difference of Pros/Cons between the two.

This is SQLOLEDB:
https://www.connectionstrings.com/ole-db-driver-for-sql-server

This is SQL Server Native:
https://www.connectionstrings.com/sql-server-native-client-11-0-oledb-provider

I am using OLEDB provider

My main problem is if i execute any main stored procedure through vb application and if any error occur in sub procedure that error not reflect at front end message box vb application but if i execute same procedure via SSMS then it will show.

So my worry is if back end error occur user will not known and he assume all procedures execute successfully

which branded tool you are talking

Please understand my problem my vb application not able show errors in front end if occur while executing sql stored procedure

Such SQL Database Recovery Tool from databasefilerecovey