Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Post Unanswered: control transaction in sybase database using sql-server 2000

    Can anyone help me with this scenario!!!!
    I have a sybase database and a sqlserver 2000 database.
    I want to insert data into sybase database table thru sql-server 2000 using distributed queries
    When i execute the following the transaction


    Create procedure myCurrentDataBaseProcedure
    as
    begin

    begin tran
    insert into mytable values(1)
    if @@error <>0
    begin
    rollback transaction
    return
    end
    insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
    if @@error <>0
    begin
    rollback transaction
    return
    end
    commit transaction
    end

    The procedure is created in sql server database
    trying to execute this procedure..shows error
    The first part of the procedure is executed.

    But the error is here
    insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
    The data is succesfully inserted in the local database
    I am unable to insert data into the remote database
    Can anyone suggest me wht shd i do in this scenario
    Are there any drivers to be loaded to commit this transactions

    Pl.Help
    Last edited by RajiniKasturi; 04-20-04 at 06:25.

  2. #2
    Join Date
    Apr 2004
    Posts
    4
    can anyone help me regarding this topic

  3. #3
    Join Date
    Mar 2004
    Posts
    80

    Re: control transaction in sybase database using sql-server 2000

    what was the error?
    go thru books on line (BOL) about
    sp_addlinkedserver and sp_addlinkedsrvlogin.

    Originally posted by RajiniKasturi
    Can anyone help me with this scenario!!!!
    I have a sybase database and a sqlserver 2000 database.
    I want to insert data into sybase database table thru sql-server 2000 using distributed queries
    When i execute the following the transaction


    Create procedure myCurrentDataBaseProcedure
    as
    begin

    begin tran
    insert into mytable values(1)
    if @@error <>0
    begin
    rollback transaction
    return
    end
    insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
    if @@error <>0
    begin
    rollback transaction
    return
    end
    commit transaction
    end

    The procedure is created in sql server database
    trying to execute this procedure..shows error
    The first part of the procedure is executed.

    But the error is here
    insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
    The data is succesfully inserted in the local database
    I am unable to insert data into the remote database
    Can anyone suggest me wht shd i do in this scenario
    Are there any drivers to be loaded to commit this transactions

    Pl.Help

  4. #4
    Join Date
    Apr 2004
    Posts
    4
    Thanks for the reply

    See, actually I dont have any problem connecting to the remote nor executing the individual statement which i mentioned in the code.

    But if i have all the sql statements as a single stored procedure
    i am unable to control the transaction

    That is I shd first insert some values into the local server database.table then the same data shd get inserted in the remote server. If both are correct then the transaction shd be committed else both shd rollback

    when i execute the second statement
    insert into sybasedatabaseserver.databasename.dbo.tablename values(1)

    the error says remote server not found.

    but if i execute the single statement it is working perfectly

    Wht could be wrong in the transaction.

  5. #5
    Join Date
    Mar 2004
    Posts
    80
    I guess it should be BEGIN DISTRIBUTED TRANSACTION
    instead of begin tran.
    let me check it out.

    Originally posted by RajiniKasturi
    Thanks for the reply

    See, actually I dont have any problem connecting to the remote nor executing the individual statement which i mentioned in the code.

    But if i have all the sql statements as a single stored procedure
    i am unable to control the transaction

    That is I shd first insert some values into the local server database.table then the same data shd get inserted in the remote server. If both are correct then the transaction shd be committed else both shd rollback

    when i execute the second statement
    insert into sybasedatabaseserver.databasename.dbo.tablename values(1)

    the error says remote server not found.

    but if i execute the single statement it is working perfectly

    Wht could be wrong in the transaction.

  6. #6
    Join Date
    Apr 2004
    Posts
    4
    Thanks again for the prompt reply

    It is not working with that option too
    Is there is anyway I can achieve this????
    Can u pl.help me

Posting Permissions

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