Post reply

Error Using BEGIN TRANSACTION and COMMIT from SQL 2000 with Oracle linked server

  • May 13, 2009 at 1:37 pm

    #221188

    Hi.. this is the scenario...

    Configured Linked Server from SQL2000 SP4 to Oracle 10G server... Linked server works fine. Can see tables, query, insert, etc...

    Problem is... If I create a Stored Procedure in SQL Server using BEGIN TRANSACTION and COMMIT, when it runs (even if it contains a simple select statement) I get this error

    > [Microsoft][ODBC SQL Server Driver][Shared Memory]

    > ConnectionCheckForData (CheckforData()).

    > Server: Msg 11, Level 16, State 1, Line 0

    > General network error. Check your network documentation.

    >

    > Connection Broken

    This is the SP:

    DECLARE @STR_SQL VARCHAR(200)

    BEGIN TRAN

    SET @STR_SQL = '

    select * from openquery (oracletest, ''Select * from ' + @table + ''')'

    exec(@STR_SQL)

    COMMIT

    If I remove the transaction commands (Begin Tran and COmmit) the query works fine...

    I have checked all configurations related to this in SQLServer, and everything is OK (DTC service, IN and OUT permissions for transactions, etc)...

    ANy hint???

    Thanks

    Juan P. Realini
    Buenos Aires, Argentina
    Web Developer, .Net Developer,
    Windows Applications, SQL Server 2000
    "A man's gotta do what a man's gotta do... that is, do all his wife says... "

  • May 14, 2009 at 6:38 am

    #994433

    Technically this is a distributed transaction so I would go with BEGIN DISTRIBUTED TRAN instead of BEGIN TRAN.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • May 14, 2009 at 7:28 am

    #994495

    Thanks for your reply... I tried that too, but I get the same error...

    Today I am trying to do something else... I found out that the DB version is 9i, so I am installing ODAC 9i and Oracle Client 9i in my SQL Server, in order to check if the problem is caused by my "multiversion" scenario (running Oracle Client 10G and InstantClient 11i)

    As long as I get to run some tests I will post the results...

    I anyone has any other ideas in the meantime, please post...

    THX

    Juan P. Realini
    Buenos Aires, Argentina
    Web Developer, .Net Developer,
    Windows Applications, SQL Server 2000
    "A man's gotta do what a man's gotta do... that is, do all his wife says... "

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply