Forcing DB Changes to commit in SQL CE !!!

Posted: Jan/22/2011 By: nikhil Points:15 Category: .NET - ASP.Net Views:205 Vote Up (0) Vote Down (0)

This is a really wide spread - more than a time discussed - on SQL CE MSDN Forums - Issue !!!

Is there any way i can commit changes which happens during runtime (when i am developing the application) such as inserts/updates and deletes to the .sdf DB on the machine ?????

View Complete Post

Comments:

Be the first to comment this post.

Post Comment

Please login to post your comment

More Related Resources

Excel 2010 OLAP PivotTable does not start new transaction after commit

I have observed that after committing data, excel does not start a new transaction, resulting in an error when excel tries to perform a rollback afterwards.

I have created a Microsoft Connect bug for this problem: https://connect.microsoft.com/SQLServer/feedback/details/631402/excel-2010-olap-pivottable-does-not-start-new-transaction-after-commit

The following steps reproduce the error. I also listed some queries observed using the profiler:

1) Create a pivottable connected to an SSAS OLAP Cube that has writeback enabled.

2) Add an editable measure to the pivot table.

3) Enable what-if analysis on the pivot table.

Query: BEGIN TRANSACTION

4) Switch-off "automatically calculate changes"

5) Change a value

6) Publish Changes

Query: UPDATE CUBE ....

Query: COMMIT TRANSACTION

(Note that no new transaction was started)

7) Disable What-If analysis

Query: ROLLBACK TRANSACTION

Error: XML for Analysis parser: The commit or rollback request cannot be completed because there is no active transaction in this session.

Variations:

- If after step 6, we change another value, but do not publish this value, and then disable What-If Analysis, excel does not fire a ROLLBACK TRANSACTION and thus there is no error.

- If after step 6, we change another value and choose to calculate, but not publish the changes, excel does an update cube. When then diabling What-If excel fires a ROLLBACK TRANSACTION which succeeds.

Digital Signatures forcing documents to check-out/check-in in document library

Hello,

We have a document library that utilizes digital signatures in MOSS. This document library does not have document check-in/check-out enabled. However users are still being prompted to check in and check out. Is this because of digital signatures ?

Thanks!

Forcing a parallel query plan when doing an insert into x select...

I have a large stored procedure that does a number of insert into table select ...

When I run the select statements by themselves they run very fast and consume large amounts of CPU. The query plans show efficient use of parallelism. When I add in the insert into table, the query plan all the sudden stops using parallelism. I have tried to include the maxdop query hint, but SQL Server just seems to ignore it. How can I force SQL Server to use parallelism to generate the query results (I know that I can't do parallel DML with the insert)?

SSIS - Loading Excel - Forcing Incorrect Data Type

Hi,

I am trying to load excel file using SSIS. One of the column defined as Varchar and has alphanumeric values. In source excel sheet, it had values as numbers although stored in text (in excel format). When I tried to import, it's importing as number and throwing the error.

I can;t change this in excel as excel is generated by users.

Please advice.

Regards,

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

ALTER proc [dbo].[uspDeleteChannel]
@ChannelId bigint
as
begin
    declare @value int,@retval int
    set @retval=0

    begin transaction
    if((select count(1) from tblchannelBranch where ChannelId=@ChannelId)>0)
    begin
    return @retval
    end
    else
    begin
    update [tblChannel] set [status]='I' where [ChannelId]=@channelId
    set @retval=1
    end

    if(@@error=0)
    begin
        commit transaction
        return @retval
    end
    else
    begin
        rollback transaction
    end

Hi My senario is to update the status of Channel rows, IF no child date(channelbranch) data present
If child data exists no need to update the status of Channel

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing error code 266

Hi,

We have Sharepoint 2007 SP1 databases on SQL Server 2005 SP3 x64. Users are uploading and viewing the documents with NO issues but we are frequently getting the below error in share point logs:

08/01/2010 08:21:33.11 w3wp.exe (0x0DA4)                        0x2168 Windows SharePoint Services    Database                       6f8g Unexpected Unexpected query execution failure, error code 266. Additional error information from SQL Server is included below. "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." Query text (if available): "DECLARE @@S uniqueidentifier; DECLARE @@DocId uniqueidentifier; DECLARE @@Level tinyint; DECLARE @@DocUIVersion int; DECLARE @@DoclibRowId int; DECLARE @@iRet int;DECLARE @DN nvarchar(256); DECLARE @LN nvarchar(128); DECLARE @FU nvarchar(260); SET @@Level=2 SET @@DocUIVersion =1 SET @@DocId='9FA36FDF-00FF-4810-92CE-A5ED5671A0C9' SET @@S='6780CE0A-2C92-463D-B86D-D8ACBB72E065'; EXEC @@DoclibRowId = proc_GenerateNextId 'C145E0B3-9F7C-4E0F-9644-7DB0AEE53EDF','1E9F697F-4032-4AD0-BC2B-0B0D0921B6F9'; SET @DN=N'DocR...
08/01/2010 08:21:33.11* w3wp.exe (0x0DA4)                        0x2168 Windows SharePoint Services    Database                       6f8g Unexpected ...epository'; SET @LN=N'Re DSMHP Sp 45 pal''s_M-1190-1212090_08012010_182047.msg';BEGIN TRAN; EXEC @@iRet = proc_AddDocument '6780CE0A-2C92-463D-B86D-D8ACBB72E065','C145E0B3-9F7C-4E0F-9644-7DB0AEE53EDF',143,NULL,@DN, @LN OUTPUT, @@Level, @@DocUIVersion, @@DocId,'1E9F697F-4032-4AD0-BC2B-0B0D0921B6F9',@@DoclibRowId,?,?,6369792,457,1,0,332,NULL,NULL,0,1,0,0,0,-2,NULL,0,NULL,NULL,NULL,NULL,NULL,? OUTPUT,0,?,? OUTPUT; SET @FU = dbo.fn_GetFullUrl(@DN, @LN); IF @@iRet <> 0 GOTO done; EXEC proc_DirtyDependents @@S,1,@FU;IF @DN IS NULL SET @DN=N'DocRepository'; IF @LN IS NULL SET @LN=N'Re DSMHP Sp 45 pal''s_M-1190-1212090_08012010_182047.msg'; EXEC @@iRet =proc_AddListItem @WebId='C145E0B3-9F7C-4E0F-9644-7DB0AEE53EDF',@SiteId='6780CE0A-2C92-463D-B86D-D8ACBB72E065',@ListID='1E9F697F-4032-...
08/01/2010 08:21:33.11* w3wp.exe (0x0DA4)                        0x2168 Windows SharePoint Services    Database                       6f8g Unexpected ...4AD0-BC2B-0B0D0921B6F9',@ItemId=@@DoclibRowId,@UIVersion=@@DocUIVersion,@RowOrdinal=0,@DocIdAdded='9FA36FDF-00FF-4810-92CE-A5ED5671A0C9',@Size=0,@ItemName=N'Re DSMHP Sp 45 pal''s_M-1190-1212090_08012010_182047.msg',@ItemDirName=@DN OUTPUT,@ItemLeafName=@LN OUTPUT,@ItemDocType=0,@UserId=143,@Level=@@Level, @TimeNow='20100802 16:40:47', @tp_ContentTypeId = ?,@nvarchar1=?,@nvarchar2=?,@nvarchar3=?,@tp_ContentType=?,@tp_ModerationStatus=?,@acl=0xF3FE000001000000000000004000000003000000FFFFFFFFFFFFFF7F0400000061104308B000000005000000E7000300B000000006000000001001083000000007000000FF1B5F3CB001000008000000EF1BE77EF001004009000000FF13433CB00100000A00000021100308300000000B00000000100108300000000E00000041104308B000000010000000E7000300B000000011000000E7000300B000000012000000E7000300B00000001300...
08/01/2010 08:21:33.11* w3wp.exe (0x0DA4)                        0x2168 Windows SharePoint Services    Database                       6f8g Unexpected ...0000E7000300B000000014000000E7000300B000000015000000E7000300B000000016000000E7000300B000000017000000E7000300B000000018000000E7000300B000000019000000E7000300B00000001A000000E7000300B00000001B000000E7000300B00000001C000000E7000300B00000001D000000E7000300B00000001E000000E7000300B00000001F000000E7000300B000000020000000E7000300B000000021000000E7000300B000000022000000E7000300B000000023000000E7000300B000000024000000E7000300B000000025000000E7000300B000000026000000E7000300B000000027000000E7000300B000000028000000E7000300B000000029000000E7000300B00000002A000000E7000300B00000002B000000E7000300B00000002C000000E7000300B00000002D000000E7000300B00000002E000000E7000300B000

Commit on Query?

I've got several functions that simply run a query and return the results -- SELECT

Here is an example function:

Public Shared Function CountAny(ByVal FROM, ByVal WHERE)
        Dim count As Integer = 0

        Dim conn As New OracleConnection(Connection.GetConn())
        Dim comm As New OracleCommand
        Dim SQL As String = ""
        Dim dr As OracleDataReader
     
        Using conn
            Try
                SQL = String.Format("SELECT COUNT(*) AS ANSWER FROM {0} {1}", FROM, WHERE)

                conn.Open()

                comm.Connection = conn
                comm.CommandType = Data.CommandType.Text
                comm.CommandText = SQL

                dr = comm.ExecuteReader

                Do While dr.Read
                    count = dr("ANSWER")
                Loop

                dr.Close()
            Catch ex As Exception
                HttpContext.Current.Session("ERROR") = ex.Message
            Finally
                conn.Close()
            End Try
        End Using

        Return count
    End Function

I got this from another forum:

Why does it seem that a SELECT over a db_link requires a commit after execution ?

Because it does! When Oracle performs a distributed SQL statement Oracle reserves an entry in the rollback segment area for the two-phase commit processing. This entry is held until the SQL statement is committed even if the SQL statement is a query.

If the application code fails to issue a commit after the remote or distributed select statement then the rollback segment entry is not released. If the program stays connected to Oracle but goes inactive for a significant period of time (such as a daemon, wait for alert, wait for mailbox entry, etc?) then when Oracle needs to wrap around and reuse the extent, Oracle has to extend the rollback segment because the remote transaction is still holding its extent. This can result in the rollback segments extending to either their maximum extent limit or consuming all free space in the rbs tablespace even where there are no large transactions in the application. When the rollback segment tablespace is created using extendable files then the files can end up growing well beyond any reasonable size necessary to support the transaction load of the database. Developers are often unaware of the need to commit distributed queries and as a result often create distributed applications that cause, experience, or contribute to rollback segment related problems like ORA-01650 (unable to extend rollback). The requirement to commit distributed SQL exists even with automated undo management available with version 9 and newer. If the segment is busy with an uncommitted distributed transaction Oracle will either have to create a new undo segment to hold new transactions or extend an existing one. Eventually undo space could be exhausted, but prior to this it is likely that data would have to be discarded before the undo_retention period has expired.

Note that per the Distributed manual that a remote SQL statement is one that references all its objects at a remote database so that the statement is sent to this site to be processed and only the result is returned to the submitting instance, while a distributed transaction is one that references objects at multiple databases. For the purposes of this FAQ there is no difference, as both need to commit after issuing any form of distributed query.

Because it does! When Oracle performs a distributed SQL statement Oracle reserves an entry in the rollback segment area for the two-phase commit processing. This entry is held until the SQL statement is committed even if the SQL statement is a query. If the application code fails to issue a commit after the remote or distributed select statement then the rollback segment entry is not released. If the program stays connected to Oracle but goes inactive for a significant period of time (such as a daemon, wait for alert, wait for mailbox entry, etc) then when Oracle needs to wrap around and reuse the extent, Oracle has to extend the rollback segment because the remote transaction is still holding its extent. This can result in the rollback segments extending to either their maximum extent limit or consuming all free space in the rbs tablespace even where there are no large transac

Forcing a user to LogOut on closing the browser

I need help with writing code for Session_Start() and Session_End() such that whenever a user closes the browser without logging out he is automatically forcely logged out......And too when a user logs out normally he is re-directed to the LogOn page.....Can anybody help...I am kinda of new to MVC.