Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Location
    Switzerland
    Posts
    9

    Unanswered: Sending data from MSSQL to Sybase

    I've made 2 ways of sending it:
    1) In a stored procedure (SP-A) I start another SP-B where is a call of extended SP-C. It itself starts a SP-D on a Sybase and saves the data there.

    IT RUNS!

    2) In an Update Trigger on a table I start SP-B where is a call of extended SP-C. It itself starts a SP-D on a Sybase and ...
    now the data should be also saved! But it doesn't work.

    WHY?

    I've tried to do it between begin and commit transaction - but - no results!

    QUESTION?
    ========
    Does anybody know why it doesn't work?
    What should I do? Is it not allowed to do such things in triggers?

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Im principal this should work. You might want to post the code from SP-A and your trigger.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jul 2002
    Location
    Switzerland
    Posts
    9
    Originally posted by Paul Young
    Im principal this should work. You might want to post the code from SP-A and your trigger.
    Thank you for your answer.
    Well, the problem is somehow strange...

    I) The test with the good results is as follows:

    a) SP-Start has:

    ........
    exec (command) -- in that command is exec SP-A dynamic)
    ........

    b) SP-A has:
    ........
    begin transaction
    insert into table1...
    select @PK = @@identity
    insert into table2... set ... = @PK
    exec SP-B @PK
    commit transaction
    ........

    c) SP-B has:
    select @1 = 1,
    ........
    from table1
    where ... @PK
    exec master..XSP-C 'database_in_sybase', 'SP-D', @1, @2,...
    update table2 set.... where ... = @PK
    exec master..XSP-C 'database_in_sybase', 'SP-D1, @PK
    -- another XSP in Sybase for making some updates
    ........

    d) XSP-C is a C++ programm, like "pseudo ODBC" for transferring data

    e) SP-D has:
    create proc...
    @1..,
    @2..
    as
    ........
    insert into table3....
    ........

    f) SP-D1 has:
    ........
    begin transaction
    update table4... where ... in (select ... from table3 where ...= @PK)
    commit transaction
    ........

    ============================================
    Now, it works:
    1) if I start it in a ISQL session,
    2) if I start "SP-Start " as a step 1 in a MSSQL JOB - so should be!
    ============================================
    II) The test with the bad results is as follows:

    a) SP-1 has:
    ........
    exec (command) -- in that command is exec SP-2 dynamic)
    ........

    b) SP-2 has:
    ........
    update tableA...

    c) on that tableA I've made an update trigger, in which I make:
    ........
    insert into table1... select ... from table B
    select @PK = @@identity
    insert into table2... set ... = @PK
    exec SP-B @PK
    ........

    ============================================
    Now, it works:
    1) if I start it in a ISQL session,

    but it doesn't
    1) if I start "SP-1 " as a step 1 in a MSSQL JOB - and so should be!!!!
    ============================================


    Paul, I think that should be clear now for you. If you have any question, pls don't hesitate and ask me directly:
    zbigniew.wedlechowicz@juliusbaer.com

    MANY THANKS

Posting Permissions

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