cancel
Showing results for 
Search instead for 
Did you mean: 

Cannot drop the table '#elbat_yraropmet_ymmud_a_si_siht'

Breck_Carter
Participant
8,616

[ Update: The excerpt of the MobiLink log has been expanded, and some code has been included. ]

That is not Elbonian for "Elbat is taking a siht", it is "this_is_a_dummy_temporary_table" spelled backwards.

alt text

No such table is created or used by this application.

The message appears in the following MobiLink 12.0.1.3298 server error message, which was issued after a previous (different) runtime error inside a trigger on SQL Server 2008 has caused synchronization to fail.

Please... can someone explain where this message is coming from?

And also this message: "Cannot roll back it201. No transaction or savepoint of that name was found."

[ See the annotations in [ square braces ] below. ]

I. 2011-06-14 08:19:42. SQL Anywhere MobiLink Server Version 12.0.1.3298
I. 2011-06-14 08:19:42. SQL Anywhere MobiLink Server Version 12.0.1.3298
I. 2011-06-14 08:19:42. 
I. 2011-06-14 08:19:42. Copyright © 2001-2011, iAnywhere Solutions, Inc.
I. 2011-06-14 08:19:42. Portions copyright © 1988-2011, Sybase, Inc. All rights reserved.
I. 2011-06-14 08:19:42. Use of this software is governed by the Sybase License Agreement.
I. 2011-06-14 08:19:42. Refer to http://www.sybase.com/softwarelicenses.
I. 2011-06-14 08:19:42. 
I. 2011-06-14 08:19:42. This server is licensed to:
I. 2011-06-14 08:19:42.     Developer Edition
I. 2011-06-14 08:19:42.     Restricted Use
I. 2011-06-14 08:19:42. Running Windows 7 Build 7600  on X86_64
I. 2011-06-14 08:19:42. Server built for X86_64 processor architecture
I. 2011-06-14 08:19:42. <main> Option 1: -c
I. 2011-06-14 08:19:42. <main> Option 2: DSN=myservice_consolidated;UID=sa;pwd=********;
I. 2011-06-14 08:19:42. <main> Option 3: -o
I. 2011-06-14 08:19:42. <main> Option 4: mlsrv12_log.txt
I. 2011-06-14 08:19:42. <main> Option 5: -os
I. 2011-06-14 08:19:42. <main> Option 6: 10M
I. 2011-06-14 08:19:42. <main> Option 7: -ppv
I. 2011-06-14 08:19:42. <main> Option 8: 60
I. 2011-06-14 08:19:42. <main> Option 9: -vcefhikmnopstuU
I. 2011-06-14 08:19:42. <main> Option 10: -zp
I. 2011-06-14 08:19:42. <main> Option 11: -zu+
I. 2011-06-14 08:19:42. <main> Verbose logging: show upload row values
I. 2011-06-14 08:19:42. <main> Verbose logging: show script names when invoked
I. 2011-06-14 08:19:42. <main> Verbose logging: show script contents when invoked
I. 2011-06-14 08:19:42. <main> Verbose logging: show schema for each table
I. 2011-06-14 08:19:42. <main> Verbose logging: show an error when the first read of a synchronization fails
I. 2011-06-14 08:19:42. <main> Verbose logging: show translated SQL for prepared statements
I. 2011-06-14 08:19:42. <main> Verbose logging: show rowcount values
I. 2011-06-14 08:19:42. <main> Cache size: 52428800 bytes
I. 2011-06-14 08:19:42. <main> Download cache directory size: 10485760 bytes
I. 2011-06-14 08:19:42. <main> Local file for remote synchronization logs: 'mlsrv.mle'
I. 2011-06-14 08:19:42. <main> Starting cache sizes: min of 52428800 bytes; max of 5961467904 bytes; initial size of 52064256 bytes
I. 2011-06-14 08:19:42. <main> Individual database connections will be closed after synchronization errors
I. 2011-06-14 08:19:42. <main> Maximum number of BLOB bytes to compare: 4294967295
I. 2011-06-14 08:19:42. <main> Maximum number of database connections: 5
I. 2011-06-14 08:19:42. <main> Maximum number of deadlock retries: 10
I. 2011-06-14 08:19:42. <main> Timeout for inactive database connections: 60 minutes
I. 2011-06-14 08:19:42. <main> Maximum delay between retries after deadlock: 30 seconds
I. 2011-06-14 08:19:42. <main> Rowset size: 10
I. 2011-06-14 08:19:42. <main> Number of database worker threads: 5
W. 2011-06-14 08:19:42. <main> [10064] Unknown users will be added automatically (when there is no authenticate_user script)
I. 2011-06-14 08:19:42. <main> Shared administrative connection connected
I. 2011-06-14 08:19:42. <main> ODBC DBMS Name: Microsoft SQL Server
I. 2011-06-14 08:19:42. <main> ODBC DBMS Version: 10.00.2531
I. 2011-06-14 08:19:42. <main> ODBC DBMS Driver Version: 10.50.1600
I. 2011-06-14 08:19:42. <main> ODBC Version supported by the driver: 3.52
I. 2011-06-14 08:19:42. <main> Collation sequence of the consolidated database is 'ISO 8859-1'
I. 2011-06-14 08:19:42. <main> System event on shared administrative connection:
                        select count(*) from sys.dm_os_cluster_nodes
I. 2011-06-14 08:19:42. <main> Translated SQL:
                        select count(*) from sys.dm_os_cluster_nodes
I. 2011-06-14 08:19:42. <main> ODBC DBMS Type: MICROSOFT SQL SERVER
I. 2011-06-14 08:19:42. <main> System event on shared administrative connection:
                        set xact_abort off
I. 2011-06-14 08:19:42. <main> Translated SQL:
                        set xact_abort off
I. 2011-06-14 08:19:42. <main> ODBC isolation set to: Read Committed
...
I. 2011-06-14 08:20:01. <1> (,xxxx) begin_publication <connection> (no script)
I. 2011-06-14 08:20:01. <1> (,xxxx) COMMIT Transaction: begin_synchronization
I. 2011-06-14 08:20:01. <1> (,xxxx) System event on synchronization connection:
                        { CALL ml_lock_rid( ?, ?, ? ) }
I. 2011-06-14 08:20:01. <1> (,xxxx) Translated SQL:
                         EXEC ml_lock_rid ?,?,?  
I. 2011-06-14 08:20:01. <1> (,xxxx) begin_upload <connection>
                        EXEC MLSPBeginUpload {ml s.username}
I. 2011-06-14 08:20:01. <1> (,xxxx) System variable username value: xxxx
I. 2011-06-14 08:20:01. <1> (,xxxx) Translated SQL:
                        EXEC MLSPBeginUpload  ?
I. 2011-06-14 08:20:01. <1> (,xxxx) begin_upload UUUU (no script)
I. 2011-06-14 08:20:01. <1> (,xxxx) begin_upload TTTT (no script)
I. 2011-06-14 08:20:01. <1> (,xxxx) handle_UploadData <connection> (no script)
I. 2011-06-14 08:20:01. <1> (,xxxx) begin_upload_rows UUUU (no script)
I. 2011-06-14 08:20:01. <1> (,xxxx) System event on synchronization connection:
                        save tran  it3 
I. 2011-06-14 08:20:01. <1> (,xxxx) Translated SQL:
                        save tran  it3 
I. 2011-06-14 08:20:01. <1> (,xxxx) upload_update UUUU
                        EXEC MLSPUUUUUploadUpdate {ml r.UUUU}, {ml r.LAST_SYNC_TS}, {ml r.LAST_SUCC_SYNC_TS}, {ml r.LAST_SUCC_DUR}                             , {ml r.TEMPLATE_CREATE_TS}, {ml r.UPGRADING}, {ml r.CURRENT_SCRIPT_VER}, {ml r.REFRESH_CLIENT}, {ml r.UUUU}                             , {ml o.LAST_SYNC_TS}, {ml o.LAST_SUCC_SYNC_TS}, {ml o.LAST_SUCC_DUR}, {ml o.TEMPLATE_CREATE_TS}                             , {ml o.UPGRADING}, {ml o.CURRENT_SCRIPT_VER},{ml o.REFRESH_CLIENT}
I. 2011-06-14 08:20:01. <1> (,xxxx) Translated SQL:
                        EXEC MLSPUUUUUploadUpdate  ?,  ?,  ?,  ?                             ,  ?,  ?,  ?,  ?,  ?                             ,  ?,  ?,  ?,  ?                             ,  ?,  ?, ?
I. 2011-06-14 08:20:01. <1> (,xxxx) Update row (new remote values) [UUUU]:
I. 2011-06-14 08:20:01. <1> (,xxxx)   xxxx
I. 2011-06-14 08:20:01. <1> (,xxxx)   2011-06-14 08:19:49.351000
I. 2011-06-14 08:20:01. <1> (,xxxx)   2011-06-14 07:11:13.363000
I. 2011-06-14 08:20:01. <1> (,xxxx)   62
I. 2011-06-14 08:20:01. <1> (,xxxx)   2011-06-14 06:50:36.565000
I. 2011-06-14 08:20:01. <1> (,xxxx)   N
I. 2011-06-14 08:20:01. <1> (,xxxx)   MySvc2011R2
I. 2011-06-14 08:20:01. <1> (,xxxx)   N
I. 2011-06-14 08:20:01. <1> (,xxxx) end_upload_rows UUUU (no script)
I. 2011-06-14 08:20:01. <1> (,xxxx) begin_upload_rows TTTT (no script)
I. 2011-06-14 08:20:01. <1> (,xxxx) System event on synchronization connection:
                        save tran  it201 
I. 2011-06-14 08:20:01. <1> (,xxxx) Translated SQL:
                        save tran  it201 
I. 2011-06-14 08:20:01. <1> (,xxxx) upload_insert TTTT
                        EXEC MLSPTTTTUploadInsert {ml s.username}, {ml r.cccc_GUID}, {ml r.CUST_GUID}, {ml r.dddd_GUID}, {ml r.eeee_GUID}
                            , {ml r.gggg_GUID}, {ml r.ffff_TYP_CD}, {ml r.ROW_DEL_FLG}, {ml r.ROW_STAT_IND}
                            , {ml r.CRE_DTTM}, {ml r.CRE_OPER}, {ml r.MOD_DTTM}, {ml r.MOD_OPER}
I. 2011-06-14 08:20:01. <1> (,xxxx) Translated SQL:
                        EXEC MLSPTTTTUploadInsert  ?,  ?,  ?,  ?,  ?
                            ,  ?,  ?,  ?,  ?
                            ,  ?,  ?,  ?,  ?
I. 2011-06-14 08:20:01. <1> (,xxxx) System variable username value: xxxx
I. 2011-06-14 08:20:01. <1> (,xxxx) Insert row [TTTT]:
I. 2011-06-14 08:20:01. <1> (,xxxx)   7c35c208-3c6b-476d-9d86-140cd86fbbea
I. 2011-06-14 08:20:01. <1> (,xxxx)   7c35c208-3c6b-476d-9d86-140cd86fbbea
I. 2011-06-14 08:20:01. <1> (,xxxx)   2ebd23c5-65b1-469b-93eb-57bd5e3c788a
I. 2011-06-14 08:20:01. <1> (,xxxx)   128ac84f-65fc-4bbc-a57f-f13cb5440c8b
I. 2011-06-14 08:20:01. <1> (,xxxx)   5d00a951-1f04-4d72-be3f-7c44aaaf2d8b
I. 2011-06-14 08:20:01. <1> (,xxxx)   G
I. 2011-06-14 08:20:01. <1> (,xxxx)   N
I. 2011-06-14 08:20:01. <1> (,xxxx)   A
I. 2011-06-14 08:20:01. <1> (,xxxx)   2011-05-23 14:47:06.680000
I. 2011-06-14 08:20:01. <1> (,xxxx)   XXXX             
I. 2011-06-14 08:20:01. <1> (,xxxx)   2011-05-23 15:34:22.717000
I. 2011-06-14 08:20:01. <1> (,xxxx)   XXXX

[ At this point, the following SQL error occurred in an INSERT trigger on table TTTT. ]

E. 2011-06-14 08:20:02. <1> (,xxxx) [-10002] Consolidated database server or ODBC error:  ODBC: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'Contact'. (ODBC State = 42S02, Native error code = 208)
E. 2011-06-14 08:20:02. <1> (,xxxx) [-10072] Unable to insert into table 'TTTT' using upload_insert
I. 2011-06-14 08:20:02. <1> (,xxxx) Error Context:
I. 2011-06-14 08:20:02. <1> (,xxxx) Remote ID: 9bd03b43-d283-44ac-a859-302688a67c34                                                                                           
I. 2011-06-14 08:20:02. <1> (,xxxx) User Name: xxxx
I. 2011-06-14 08:20:02. <1> (,xxxx) Modified User Name: xxxx
I. 2011-06-14 08:20:02. <1> (,xxxx) Transaction: upload
I. 2011-06-14 08:20:02. <1> (,xxxx) Table Name: TTTT
I. 2011-06-14 08:20:02. <1> (,xxxx) Insert Row:
I. 2011-06-14 08:20:02. <1> (,xxxx)   7c35c208-3c6b-476d-9d86-140cd86fbbea
I. 2011-06-14 08:20:02. <1> (,xxxx)   7c35c208-3c6b-476d-9d86-140cd86fbbea
I. 2011-06-14 08:20:02. <1> (,xxxx)   2ebd23c5-65b1-469b-93eb-57bd5e3c788a
I. 2011-06-14 08:20:02. <1> (,xxxx)   128ac84f-65fc-4bbc-a57f-f13cb5440c8b
I. 2011-06-14 08:20:02. <1> (,xxxx)   5d00a951-1f04-4d72-be3f-7c44aaaf2d8b
I. 2011-06-14 08:20:02. <1> (,xxxx)   G
I. 2011-06-14 08:20:02. <1> (,xxxx)   N
I. 2011-06-14 08:20:02. <1> (,xxxx)   A
I. 2011-06-14 08:20:02. <1> (,xxxx)   2011-05-23 14:47:06.680000
I. 2011-06-14 08:20:02. <1> (,xxxx)   XXXX             
I. 2011-06-14 08:20:02. <1> (,xxxx)   2011-05-23 15:34:22.717000
I. 2011-06-14 08:20:02. <1> (,xxxx)   XXXX             
I. 2011-06-14 08:20:02. <1> (,xxxx) Script Version: MySvc2011R2
I. 2011-06-14 08:20:02. <1> (,xxxx) Script: EXEC MLSPTTTTUploadInsert {ml s.username}, {ml r.cccc_GUID}, {ml r.CUST_GUID}, {ml r.dddd_GUID}, {ml r.eeee_GUID}
                            , {ml r.gggg_GUID}, {ml r.ffff_TYP_CD}, {ml r.ROW_DEL_FLG}, {ml r.ROW_STAT_IND}
                            , {ml r.CRE_DTTM}, {ml r.CRE_OPER}, {ml r.MOD_DTTM}, {ml r.MOD_OPER}
I. 2011-06-14 08:20:02. <1> (,xxxx) End of Error Context
I. 2011-06-14 08:20:02. <1> (,xxxx) handle_error <connection>
                        EXEC MLSPHandleError {ml s.action_code}, {ml s.error_code}, {ml s.error_message}, {ml s.username}, {ml s.table}
I. 2011-06-14 08:20:02. <1> (,xxxx) System variable action_code value: 3000
I. 2011-06-14 08:20:02. <1> (,xxxx) System variable error_code value: 208
I. 2011-06-14 08:20:02. <1> (,xxxx) System variable error_message value: ODBC: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'Contact'. (ODBC State = 42S02, Native error cod
I. 2011-06-14 08:20:02. <1> (,xxxx) System variable username value: xxxx
I. 2011-06-14 08:20:02. <1> (,xxxx) System variable table value: TTTT

[ The code for MLSPHandleError and MLSPReportError is shown at the bottom ]

I. 2011-06-14 08:20:02. <1> (,xxxx) Translated SQL:
                        EXEC MLSPHandleError  ?,  ?,  ?,  ?,  ?
I. 2011-06-14 08:20:02. <1> (,xxxx) System variable action_code value: 3000
I. 2011-06-14 08:20:02. <1> (,xxxx) The handle_error script returned 3000
I. 2011-06-14 08:20:02. <1> (,xxxx) report_error <connection>
                        EXEC MLSPReportError {ml s.action_code}, {ml s.error_code}, {ml s.error_message}, {ml s.username}, {ml s.table}
I. 2011-06-14 08:20:02. <1> (,xxxx) System variable action_code value: 3000
I. 2011-06-14 08:20:02. <1> (,xxxx) System variable error_code value: 208
I. 2011-06-14 08:20:02. <1> (,xxxx) System variable error_message value: ODBC: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'Contact'. (ODBC State = 42S02, Native error cod
I. 2011-06-14 08:20:02. <1> (,xxxx) System variable username value: xxxx
I. 2011-06-14 08:20:02. <1> (,xxxx) System variable table value: TTTT
I. 2011-06-14 08:20:02. <1> (,xxxx) Translated SQL:
                        EXEC MLSPReportError  ?,  ?,  ?,  ?,  ?
I. 2011-06-14 08:20:02. <1> (,xxxx) System variable action_code value: 3000
I. 2011-06-14 08:20:02. <1> (,xxxx) handle_odbc_error <connection> (no script)
I. 2011-06-14 08:20:02. <1> (,xxxx) report_odbc_error <connection> (no script)
E. 2011-06-14 08:20:02. <1> (,xxxx) [-10061] An error occurred while uploading an insert row into table 'TTTT'.  The inserted column values are as follows:
I. 2011-06-14 08:20:02. <1> (,xxxx)   7c35c208-3c6b-476d-9d86-140cd86fbbea
I. 2011-06-14 08:20:02. <1> (,xxxx)   7c35c208-3c6b-476d-9d86-140cd86fbbea
I. 2011-06-14 08:20:02. <1> (,xxxx)   2ebd23c5-65b1-469b-93eb-57bd5e3c788a
I. 2011-06-14 08:20:02. <1> (,xxxx)   128ac84f-65fc-4bbc-a57f-f13cb5440c8b
I. 2011-06-14 08:20:02. <1> (,xxxx)   5d00a951-1f04-4d72-be3f-7c44aaaf2d8b
I. 2011-06-14 08:20:02. <1> (,xxxx)   G
I. 2011-06-14 08:20:02. <1> (,xxxx)   N
I. 2011-06-14 08:20:02. <1> (,xxxx)   A
I. 2011-06-14 08:20:02. <1> (,xxxx)   2011-05-23 14:47:06.680000
I. 2011-06-14 08:20:02. <1> (,xxxx)   XXXX             
I. 2011-06-14 08:20:02. <1> (,xxxx)   2011-05-23 15:34:22.717000
I. 2011-06-14 08:20:02. <1> (,xxxx)   XXXX

[ This is where things start getting nasty. ]

I. 2011-06-14 08:20:02. <1> (,xxxx) System event on synchronization connection:
                        rollback tran  it201
I. 2011-06-14 08:20:02. <1> (,xxxx) Translated SQL:
                        rollback tran  it201
E. 2011-06-14 08:20:02. <1> (,xxxx) [-10002] Consolidated database server or ODBC error:  ODBC: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot roll back it201. No transaction or savepoint of that name was found. (ODBC State = 25000, Native error code = 6401)
I. 2011-06-14 08:20:02. <1> (,xxxx) end_upload_rows TTTT (no script)
E. 2011-06-14 08:20:02. <1> (,xxxx) [-10002] Consolidated database server or ODBC error:  ODBC: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the table '#elbat_yraropmet_ymmud_a_si_siht', because it does not exist or you do not have permission. (ODBC State = 42S02, Native error code = 3701)
I. 2011-06-14 08:20:02. <1> (,xxxx) end_upload UUUU (no script)
I. 2011-06-14 08:20:02. <1> (,xxxx) end_upload TTTT (no script)
I. 2011-06-14 08:20:02. <1> (,xxxx) end_upload <connection> (no script)
I. 2011-06-14 08:20:02. <1> (,xxxx) ROLLBACK Transaction: end_upload
I. 2011-06-14 08:20:02. <1> (,xxxx) System event on synchronization connection:
                        { CALL ml_lock_rid( ?, ?, ? ) }
I. 2011-06-14 08:20:02. <1> (,xxxx) Translated SQL:
                         EXEC ml_lock_rid ?,?,?  
I. 2011-06-14 08:20:02. <1> (,xxxx) end_publication <connection> (no script)
...

[ Here's the code for the error handling procedures. ]

-----------------------------------------------
-- MLSPLogging
-----------------------------------------------
IF EXISTS( SELECT 1 
             FROM INFORMATION_SCHEMA.ROUTINES
             WHERE routine_name = 'MLSPLogging' )
    DROP PROCEDURE MLSPLogging
GO
CREATE PROCEDURE MLSPLogging
    @p_syncUser         VARCHAR( 128 ),
    @p_messType         CHAR( 1 ),
    @p_messText         VARCHAR( 255 )
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO SYNC_LOG( SYNC_USER, MESSAGE_TYPE, MESSAGE_TEXT )
    VALUES( @p_syncUser, @p_messType, @p_messText )
END
GO

-----------------------------------------------
-- MLSPLogError
-----------------------------------------------
IF EXISTS( SELECT 1 
             FROM INFORMATION_SCHEMA.ROUTINES
             WHERE routine_name = 'MLSPLogError' )
    DROP PROCEDURE MLSPLogError
GO

CREATE PROCEDURE MLSPLogError
    @p_syncUser         VARCHAR( 128 ),
    @p_messText         VARCHAR( 255 )
AS
BEGIN
    SET NOCOUNT ON
    EXEC MLSPLogging @p_syncUser, 'E', @p_messText
END
GO

-----------------------------------------------
-- MLSPHandleError
-----------------------------------------------
IF EXISTS( SELECT 1 
             FROM INFORMATION_SCHEMA.ROUTINES
             WHERE routine_name = 'MLSPHandleError' )
    DROP PROCEDURE MLSPHandleError
GO
CREATE PROCEDURE MLSPHandleError
    @p_action             INTEGER OUT,
    @p_error_code         INTEGER,
    @p_error_message      VARCHAR( 255 ),
    @p_user_name          VARCHAR( 128 ),
    @p_table_name         VARCHAR( 128 )
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @lv_msg     VARCHAR( 255 )

SELECT @lv_msg = @p_error_message + ' - ' + @p_table_name
    SELECT @p_action = 3000
    EXEC MLSPLogError @p_user_name, @lv_msg
END
GO

-----------------------------------------------
-- MLSPReportError
-----------------------------------------------
IF EXISTS( SELECT 1 
             FROM INFORMATION_SCHEMA.ROUTINES
             WHERE routine_name = 'MLSPReportError' )
    DROP PROCEDURE MLSPReportError
GO
CREATE PROCEDURE MLSPReportError
    @p_action             INTEGER OUT,
    @p_error_code         INTEGER,
    @p_error_message      VARCHAR( 225 ),
    @p_user_name          VARCHAR( 128 ),
    @p_table_name         VARCHAR( 128 )
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @lv_msg     VARCHAR( 255 )

SELECT @lv_msg = @p_error_message + ' - ' + @p_table_name
    SELECT @p_action = 3000
    EXEC MLSPLogError @p_user_name, @lv_msg
END
GO

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

We are able to reproduce this problem with an insert trigger defined for a sync table and the trigger was trying to access an undefined object. The SQL Server will rollback the entire transaction when an error occurs inside a trigger. Please see the following link:

http://www.sommarskog.se/error-handling-I.html#triggercontext

To workaround this problem, please fix the error in the trigger.

We are looking to remove this temporary table requirement in the future.

@Breck: Thank you for reporting this problem and providing the MobiLink server log file.

Breck_Carter
Participant

Thanks!

That link is great, especially the subtle footnote "(Now, taste that concept: an informational error.)"

I can't do subtle... instead, I shout "SQL SERVER SUCKS BIG WAMPUM!"

Or, in Elbonian, "Mupmaw gib kcus revres lqs!"

Former Member

I like the "Because the sky is blue" quote. 🙂

VolkerBarth
Contributor
0 Kudos

But all will agree to the "SQL Server is too smart for you" quote:)

Answers (5)

Answers (5)

Former Member

The ML server creates savepoints that it can roll back to if statements in synchronization scripts fail.

Microsoft SQL Server does not allow creating a savepoint at the beginning of a transaction, so to get around that the ML server creates a dummy temporary table (called '#elbat_yraropmet_ymmud_a_si_siht') that is dropped at the end of the transaction. So you get those two errors if there is a commit or rollback (explicit or implicit) between creating the savepoint and trying to roll it back (because of an error).

VolkerBarth
Contributor
0 Kudos

Just to understand:

The temporary table is created as a dummy DDL statement because MS needs one such a statement before it can create a savepoint?

(To be honest, I wasn't aware that MS supports savepoints at all - I thought it would just support nested transactions.)

Former Member

Doesn't have to be DDL, but it seems to need something that can be rolled back before one can create a savepoint. I think we chose temp table creation because it shouldn't affect anything else or persist. I'm just guessing, but the name is probably spelled backwards to lessen the chance of a name collision (not necessarily to confound Breck). 🙂

Breck_Carter
Participant
0 Kudos

@Graham: I cannot find any commit or rollback either explicit or implicit in the error handling procedures... does SQL Server issue an implicit rollback when a trigger has an error? ...that would not make sense.

Anyway... I have included more of the MobiLink log in the original question, plus the procedures that get called by the first error.

Breck_Carter
Participant

I was not confounded... I am fluent in Elbonian! 🙂

Former Member
0 Kudos

@Breck: As Yufei has answered separately, MS SQL Server does indeed issue an implicit rollback when a trigger has an error.

MSS has an "XACT_ABORT" option which is kind of like an auto-rollback mode: if there is an error during a transaction when the option is enabled, then the transaction gets rolledback. Do the scripts turn that option on? As of 12.0.1.3089 the ML server disables the option when it opens a connection.

Former Member

This problem may happen after a deadlock occurred, because the database server would roll back everything for the deadlock victim. It would be always helpful, if you can post all the warning and error messages the MobiLink server generated this this error or the full MobiLink server log file (without user data).

Breck_Carter
Participant
0 Kudos

I have included more of the MobiLink log in the original question, plus the procedures that get called by the first error.

Former Member
0 Kudos

@Breck: Thank you for posting the MobiLink server log file. It looks like the SQL Server rolled back everything after the error occurred. We'll try to replay your scripts.

VolkerBarth
Contributor

No, I'm no expert in Elbonian nor in ML - and I don't know whether this applies here, but there's a NG thread Breck might remember...

To cite David Fishburn:

This is just an FYI for anyone reading this thread, you must never do anything that results in a commit during the upload phase of a MobiLink synchronization.
In this case, SQL Server was dropping the temporary table after the commit.

Breck_Carter
Participant
0 Kudos

Um, the "NG thread" link goes back to this question. Anyway, Dave's reply didn't make much sense back then, and it certainly doesn't now... nobody's doing a commit, and nobody created the Elbat thing.

VolkerBarth
Contributor
0 Kudos

Sorry, at least now the link works as expected... even if the thread isn't that helpful:(

Not even the slightest possibility of an implicit COMMIT...?

Former Member
0 Kudos

The last post in the old NG thread (by Eric Odum) said:

I have seen this issue if there was a cascading trigger set on the synchronized table.

Could that be the problem?

Breck_Carter
Participant
0 Kudos

Why would a cascading trigger be a problem? Here's what happened: A trigger on that table did have an error, earlier in the sync, and MobiLink issued an error message as expected. MUCH LATER in the sync, the posted messages appeared. There are TWO messages, both totally bogus, shown in the question. When the earlier error in the trigger was fixed, the sync ran OK. So... those old NG threads were completely useless then, and they are completely useless now... just WAGs in my opinion.

Breck_Carter
Participant
0 Kudos

Sigh. There are no commits, implicit or otherwise, in any of the MobiLink scripts.

There is also no temporary table with the name #elbat_yraropmet_ymmud_a_si_siht created by any of the scripts.

There is also no "rollback tran it201" issued by the MobiLink scripts. I am guessing the MobiLink server itself issued that, and SQL Server clearly doesn't like it.

Breck_Carter
Participant
0 Kudos

Oh, but wait... there are tables whose names start with the letter "T"... could that be the problem?

Some of the code was written on Wednesdays, could that be the problem? 🙂

VolkerBarth
Contributor
0 Kudos

The problem could be ... minor translation issues between your expectation and the ML implementation - possibly in the way error messages are temporarily stored?

(That's the last totally useless comment I'm gonna leave on this question, I promise.)

Breck_Carter
Participant
0 Kudos

(Good... because I was going to have to spank you 🙂

Breck_Carter
Participant
0 Kudos

Bill Somers said: "MSS has an "XACT_ABORT" option which is kind of like an auto-rollback mode: if there is an error during a transaction when the option is enabled, then the transaction gets rolledback. Do the scripts turn that option on? As of 12.0.1.3089 the ML server disables the option when it opens a connection."

@Bill: Oh, cool! I'll bet that's it... except that I'm running 12.0.1.3298.

Can you promote your comment to an answer so it's easier to discuss? It wouldn't let me post a reply comment.

Also, FWIW, I expanded the MobiLink log excerpt in the original question.

VolkerBarth
Contributor
0 Kudos

@Bill,@Breck: I just promoted the comment to an answer, and now it's gone. That was truly not my intent.

Dear administrator, can you help please?

Breck_Carter
Participant
0 Kudos

@Volker: Looks like there's more than one bug down there in deeply nested comments... I couldn't post a reply to his comment, and you couldn't promote his comment to an answer. In THIS case it doesn't matter because Mister Paranoid (me) pasted his comment into an answer.

Who needs polymorphism and inheritance when you have copy and paste? 🙂

VolkerBarth
Contributor
0 Kudos

Well, I've sent a note to Graeme, so I'm sure the comment is gonna have its comeback as an answer:)

But I agree, now and then it's very helpful to re-cite...

graeme_perrow
Advisor
Advisor
0 Kudos

I'm not sure what happened, but I've restored the answer.

graeme_perrow
Advisor
Advisor
0 Kudos

@Breck: When you say you "couldn't post a reply", what do you mean? Did it not allow you to, or did it fail, or did it not show up, or something else?