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.
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
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?
The OP mentioned an Oracle stored procedure so has probably translated it to a SQL stored procedure.
The OP may not be that familiar with SQL Server so may not have XACT_ABORT at the top of the sp.
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.
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.
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