Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70

    Unanswered: Capturing Data Type Mismatch

    Hi,
    Create Table tb_mismatch
    (x int)
    Create Procedure proc_mismatch
    as
    begin
    insert into tb_mismatch values('s')
    if @@error<>0
    begin
    print ' entered error loop'
    end
    print 'successfully exited'
    end
    exec proc_mismatch --executing the proc
    Now, when i try to capture the above error its not getting trapped..its directly going to the final end statement.
    I have even tried calling subprocedures so that it comes out of the inner procedure and by some means i can move forward in the outer proc,but even that failed.
    The proc. is able to capture all the other errors like primary key violation,binary data truncated etc but not the datatype mismatch error (mainly int with varchar...)
    any ideas are highly appreciated.
    Thanks & regards,
    Pavan.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    It looks like a little data checking is needed somewhere. Here is one example:

    Code:
     Create Table dbo.tb_mismatch
    (x int) 
     
     
    Create Procedure dbo.proc_mismatch @var varchar(50), @err int OUTPUT
    as
     
    if isnumeric(@var) = 1
    BEGIN
    insert into dbo.tb_mismatch (x) values(@Var)
    END
    ELSE
    BEGIN
    set @err = -1
    END
     
     
    if @err<>0
    begin
    print 'encountered error'
    end
    else
    begin
    print 'successfully exited'
    end
     
     
     
    declare @var varchar(50), @errreturn int
    set @var = 's'
    set @errreturn = 0
     
    exec dbo.proc_mismatch @var, @errreturn OUTPUT --executing the proc
    select @errreturn
     
    drop proc dbo.proc_mismatch
    drop table dbo.tb_mismatch

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    Thanks for your quick response but my requirement is:
    I have several update and insert statements in my actual procedure which fetches the data from an oracle DB and updates the sql database.. during these updates and inserts Business wants me to capture all the system related errors and when i am trying to capture the data mismatch error(manually placing a varchar value in a float field) the cursor is directly moving to the end of procedure,instead of populating the log file.
    I dont think placing isnumeric for all int and float fields is the feasible solution,
    any other ways??

    Many Thanks
    Pavan.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No

    And this sounds like a batch process...

    I woul unload the data from oracle, bcp the data in to sql server, perform my audits, then load the data in
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    Sounds good but it doesn't help my requirement as i have lots of validations to be done before performing any transactions and even need to Rollback transactions in some cases..
    Do we have any exception handling mechanism to handle this ..other than raiseerror as it didn't worked out.. or is this a bug in sqlserver?? like we have when VALUE_ERROR exception in oracle

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SQL Server error handling is kludgey in 2000. SQL 2005, takes for steps to address that, but I haven't looked in to it.

    So why can't you do basic aduitng in batches in a set based manner? What's the difficulty. You will need some staging tables, bit so what?

    You need to divorce yourself from sequential cursor processing that you're accostomed too in Oracle....even in Oracle, it is over used a lot of times.

    Good Luck.

    If you continue to do it this way, create a second stored procedure that gets called...like a nested stored procedure....when the nested proc fails, it will rais out to the calling stored procedure, and the driver can then handle the error...but that's the long way around the mountain
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    Brett,
    thanks for ur concern.
    I have tried the second option but it hasn't helped me out.
    My code goes something like this..
    gets the jobnumber and its related info from the oracle job master table..checks for its existance in sql db and then creating 2 cursors for diff tables checks and then lots of if's and else's,calculations..and once it goes through all the validations we will start inserting the details into some tables,move data to history and then update the main job table..if it fails in any of the case just rollback the whole operations..now the turn of next job comes into picture..
    As of now it works fine until we dont get varied data from oracledb which has the similar db structure of sql server.
    My scope is till its developed.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I still don't know why you can't do something like this

    Code:
    USE Northwind
    GO
    
    
    -- Set up the situation
    SET NOCOUNT ON
    CREATE TABLE ORACLE_TABLE(Col1 varchar(10))
    CREATE TABLE SQL_TABLE(Col1 int)
    GO
    
    -- Create some sample Data
    
    INSERT INTO ORACLE_TABLE(Col1)
    SELECT '1' UNION ALL
    SELECT '2' UNION ALL
    SELECT '3' UNION ALL
    SELECT 'a' UNION ALL
    SELECT 'b' UNION ALL
    SELECT 'd'
    GO
    
    -- Report On Bad Data
    
    SELECT * FROM ORACLE_TABLE WHERE ISNUMERIC(Col1) = 0
    
    -- Place the good data in to SQL
    INSERT INTO SQL_TABLE(Col1)
    SELECT (Col1) FROM ORACLE_TABLE WHERE ISNUMERIC(Col1) = 1
    GO
    
    SELECT * FROM SQL_TABLE
    GO
    
    SET NOCOUNT OFF
    DROP TABLE ORACLE_TABLE, SQL_TABLE
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    Try doing the same with a small change,changing the datatype from varchar to int,as this is my current structure,without using isnumeric option as my table has lots of columns and there are lots of insert and update statements.
    CREATE TABLE ORACLE_TABLE(Col1 int)

    Came to know that this error cannot be captured by sqlserver 2000 which is resolved in the next version sqlserver 2005 using the try catch block.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •