Write To A Table In A Rolled Back Transaction.
Mar 4, 2008
Hi,
Is there a way to write to a log-table inside a transaction which is rolled back without rollback of this log-entry.
thanks in advance
Raimund
ADVERTISEMENT
SQL Server 2008 :: Can Second Save Transaction T2 Be Rolled Back
May 4, 2015I am confused about save transaction in the below scenario :
begin transaction
save transaction t1
delete from #t1
save transaction t2
begin try
delete from #t2
[Code] ....
If there is error after delete #t2 , transaction t1 is rolled back. But i am not able to understand why i am getting error in the statement 'rollback transaction t2' . I am getting error as 'Cannot roll back t2. No transaction or savepoint of that name was found.'. but save point t2 is mentioned in the code.
Rolled Back And Unfinish Installing MSDE2000
Oct 11, 2006I'm trying to install the MSDE2000 on window xp, sp2. During the installing, apperance the percent bar is rolling back, which is not even completed install. I had read the issue from microsoft to restart the server service, and...but no luck. Is there anyone has an ideas.
View 1 Replies View RelatedNonqualified Transactions Are Being Rolled Back. Estimated Rollback Completion: 100%.
Apr 30, 2007Hi all,
Sometimes when I do "alter database ABCD set partner failover" I get the following message: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
In 99 percent of the cases after such message the first attempt to use an open connection would also raise an error such as "Exception: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"
After the first error all subsequent queries would run perfectly.
What am I missing?
Avi
Write A Date Back To A Table In An Expression
Mar 12, 2008Help,
I have a package that maps the current getdate to a variable.
I then want to be able to write that variable back to a table using a sql task using the following expression:
"INSERT INTO CONTROL_BATCH_HEADER VALUES (
" + (DT_STR, 10, 1252) @[Control::intAdtBatchId] + ",
'" + @[Control::strSubjectArea] + "',
convert(datetime, '" + (DT_STR, 30, 1252) @[Control::dteRunDate] + "',131))"
The problem is the date is being written back in the wrong format. I've tried changing the mask to 121 but the month is being written as the day.
Help, how can I make this work regardless of the locals. In oracle I would just put a to_char(date,'dd-mm-yyyy') but not sure what to do here.
Thanks for your help
Stapsey
How Do You Roll Back Transactions For A Single Table From The Current Transaction Log?
Nov 1, 2007
Goofed up and ran an update query. It messed up all the data in a single table. I'm trying not to restore the table from a previous backup since the backup is more than 20 GB. It's going to take forever to restore it. Any advice would be much appreciated!
Thanks!
Charles.
Write DataSet Back To SQL
Sep 10, 2004Hello everybody,
I have a dataset that i read from an xml file.
I need to import this database back in to sql..
Is there a easy way.. Or do i need to loop thorugh each record in the dataset to import it.
Thanks
Chris
Transact SQL :: Insert Records And Write Back Identity Value
Jun 19, 2015I'm trying to insert records into "holding" table and write back identity column value (Entry_Key) to the original table. So my setup is I have two tables; tblEWPBulk and tbleFormsUploadEWP. Users will enter records into tblEWPBulk and use BatchID to group records, once batch entry has been completed (usually less than 30 records) user will click on UploadAll button and insert records (not all fields) into tbleFormsUploadEWP. One record in tblEWPBulk can be sent multiple times to the holding table but tblEWPBulk will need to have latest Entry_Key captured. Records are sent from holding table to DB2 z/VSE using SQL stored procedure and based on certain logic records are marked uploaded or certain error capture... that part works fine.
So for example I want to send
BatchID, AccountNumber, Period, ReceiveDate, AccountType, ReturnType, NetProfitOrLoss, TaxCredit FROM tblEWPBulk to the holding table and write back Entry_Key (identity column) back to the record in tblEWPBulk (field called UploadEntryKey). As I said one record could be sent to the holding table multiple times until uploaded or deleted and UploadEntryKey always needs to be updated so that when results are processed response from the DB2 can be inserted into table and presented to the user.
No foreign key relationship exists since records in the holding table get sent to the archive table and table is truncated and entry_key starting value reset back to 2000... just some DB2 restrictions.
Analysis :: How To Block Writing In Total Cells When Use Excel And Write Back
Nov 16, 2015I have a cube with a partition configures in write back.
Users in Excel need to see the totals of the line. Unfornately they have the bad idea to write in this cell sometimes and not in the leaf cells.
As there is some MDX code behind in the weight expression field, we got some weird values : one is negative and others ARe 10 times the initial value in the total. So it's very dangerous.
How can we block the writing in this totals cells ?
How To Back Up The Transaction Log For The Database?
Feb 8, 2001Hi all,
I'm getting the following error message in NT event view:
Error: 9002, Severity: 17, State: 2
The log file for database 'db_sys' is full. Back up the transaction log for the database to free up some log space.
But I don't know how to back up the transaction log for the database.
Do I need TRUNCTE LOG (in E.M) to free up some log space?
I only know back up all the database using ALL TASKS -> BACKUP DATABASE in E.M. Please help me. Thanks in advance.
TH
Transaction Not Rolling Back
Mar 19, 2007I have a stored procedure that calls another stored procedure with thefirst stored procedure opening a transaction:BEGINSET XACT_ABORT ONBEGIN TRANSACTIONdoes various updates/insertscalls 2nd stored procedure to proccess updates/inserts common to manyother stored proceduresdoes more various updates/insertscommitENDThe problem I'm having is that within the 2nd stored procedure is thatif it encounters an error, it does not roll back the entiretransaction and I finish up with missing records in the database. Amusing this in the 2nd stored procedure:if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendWhat could the problem be? From what I've read, it seems as thoughyou can't have an open transaction within one sp that calls another spand it maintains the same transactoin? Is this corrrect?I tired the following too, and I still couldn't get it to work. Anyideas anyone?************ sp 1 ***********Declare @AddressError char(3)SET XACT_ABORT ONBEGIN TRANSACTIONexec Sp2@AddressError OUTPUT,@variable1,@variable2,etc. etc************** sp 2 *****************@AddressError char(3) OUTPUT,if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendSET XACT_ABORT ONBEGIN TRANSACTIONprocess updates/insertsSet @AddressError = 'no'Commit******** back to sp 1************If @AddressError <'no'BEGINrollback transactionENDcontinue doing updates/insertscommit
View 1 Replies View RelatedRolling Back Transaction
Jun 16, 2007Hi,
I have a case where I read from SQL Server DB and write to a flat file.
I have one Data Flow Task inside which I have a OLEDB source component that feeds rows to a script component that writes to a flat file. I have set the txn attributes for the container to "Required" and "Read committed" . But I find that rows are written to flat file even when I throw an exception from my script component. Question is how do I prevent rows from being written to the flat file if error/exception happens. I want the whole process to be in a single transaction.
Thanks
How To Implement Transaction Roll Back
Feb 28, 2008Hello, I do not know how to implement transaction roll back in asp.net application. I am using SqlHelper class to communicate with my sql db.Thanks, junior
View 1 Replies View RelatedMy Transaction Isnt Rolling Back When It Should
Apr 9, 2008Ive got an insert statement that fails, and below that I have code like the following:
IF @@ERROR <> 0
BEGIN
-- Roll back the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error INSERT INTO Address.', 16, 1)
print 'test was here'
RETURN
END
However, there is now rollback and the inserts below it are going through.
what do i have wrong ?
Transaction Reverts Back After 10 Mins
Apr 3, 2015The following transaction runs, then reverts back after 10 mins.
begin Tran [UpdateSundays]
update ScanAssetInformation with (rowlock)
set BusinessDate = dateadd(day,-2,BusinessDate)
where datepart(weekday,BusinessDate) = 1
commit tran [UpdateSundays]
begin Tran [UpdateSaturdays]
update ScanAssetInformation with (rowlock)
set BusinessDate = dateadd(day,-1,BusinessDate)
where datepart(weekday,BusinessDate) = 7
commit tran [UpdateSaturdays]
Mirroring And Rolling Back Transaction Log
Mar 17, 2008hi,
i want to create a disaster recovery site, to which i can fail over (not automatically),
and also to have the option to return the database to a point in time?
for example,
if my principal server fails in 17:00,
i want to have the option to make the mirror server available for users from 17:00 (or at least close to that time),
and also to be able the return to the data from 16:00 (in the mirror site).
Is it possible, and what is the best way to do it?
Thanks.
Transaction Log Back Ups - Simple Question
Aug 15, 2007
When using the maintenance plan to backup t logs, should I enclude the system database logs such as master, model, etc...?
Thanks!
Back Up And Restore Using Transaction Log And Incremental Backup
Feb 1, 2001hi, I would like to know the correct reaction for a crash in both senarios.
First senario,
I made a full back up at 6 am , then scheduled sql server to make transaction log back up every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 12:30. How would I resotre the data in the first senario....Can I restore the full back up done at 6 am then restore the last transaction log backup ( which is 12 Noon ) . I am not sure If I need to resotre the whole tran from 6 am till the time it was crashed.
Second senario,
I made a full back up at 6 am, then scheduled sql server to make Incremental backup every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 3:00 pm. How would I resotre the data in the second senario. ....Do I restore the full backup at 6 am then restore each incremental backup backwords ( 2,12,10,8)
AS you can see, I am not sure how to deal with this issue, I do appreciate your feedback.
Best regards
Ahmed
Full Back Up ( Transaction Log ) Vs ( Incremental Backup)
Feb 1, 2001hi, I would like to know the correct reaction for a crash in both senarios.
First senario,
I made a full back up at 6 am , then scheduled sql server to make transaction log back up every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 12:30. How would I resotre the data in the first senario....Can I restore the full back up done at 6 am then restore the last transaction log backup ( which is 12 Noon ) . I am not sure If I need to resotre the whole tran from 6 am till the time it was crashed.
Second senario,
I made a full back up at 6 am, then scheduled sql server to make Incremental backup every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 3:00 pm. How would I resotre the data in the second senario. ....Do I restore the full backup at 6 am then restore each incremental backup backwords ( 2,12,10,8)
AS you can see, I am not sure how to deal with this issue, I do appreciate your feedback.
Best regards
Ahmed
Rolling Back Transaction Logs Database
Feb 3, 2004I have a Development database and I want to roll it back to Monday morning. I backed up the database and used the command:
RESTORE DATABASE ITTEST
FROM ITTEST20040203
WITH NORECOVERY
GO
RESTORE LOG MyNwind
FROM ITTEST20040203
WITH RECOVERY, STOPAT = 'FEBRUARY 2, 2004 09:00 AM'
GO
The transaction logs have never been truncated.
But it does not seem to have worked. It this the best way to do a roll back the database or have I missed out something.
thanks.
How To Write An Application Which Monitors Transaction Log?
Sep 3, 2007Hi,
It is possible to write an application which monitors the Microsoft SQL Server 2005 database's transaction log and captures data modified (values after modification) by transactions? Can you point me to some tutorial and API specification?
Thanks,
Rafal
How To Write An Application Which Monitors Transaction Log?
Sep 3, 2007Hi,
It is possible to write an application which monitors the Microsoft SQL Server 2005 database's transaction log and captures data modified (values after modification) by transactions? Can you point me to some tutorial and API specification?
Thanks,
Rafal
Rolling Back Transaction After .Commit() Runs Successfully
Jun 10, 2007I have a page that runs a transaction correctly after a button click.
I want to allow someone to click a button that rolls back the transaction, after the transaction runs on the first button click.
I can also successfully roll back within the first button click.
I'm getting a NullReference error when trying to access SqlTransaction.Rollback() outside the button click.
If SqlTransaction.Commit() completes without error can SqlTransaction.Rollback() be called after?
I tried making 'trans' a more global variable and it still gave me the error.
Button Click 1:
Dim trans As SqlTransaction
trans = connection.BeginTransaction()
try
'run SQL Statement
trans.Commit()
Catch e As Exception
trans.Rollback()
throw e
end try
Button Click 2:
trans.Rollback()
Transaction Scope Rollback Does Not Roll Back Action Of Activation SP
Aug 16, 2007Hi,
I followed Remus' post about not doing 'fire and forget'.
I have two queues, ProcessingSendQueue and ProcessingReceiveQueue.
Once i receive from ProcessingReceiveQueue, activation SP gets called on ProcessingSendQueue and ends conversation.
However,if I then get an exception, the action of the activation SP ( ie the ending of the conversation ) does not get rolled back... is this possible? I would have thought that the action of the activation SP would get rolled back too.
My ProcessingSendQueue activation SP is as follows:
ALTER PROCEDURE [dbo].[ProcessingSendQueue_AP]
AS
BEGIN
DECLARE @dh UNIQUEIDENTIFIER;
DECLARE @message_type SYSNAME;
DECLARE @message_body NVARCHAR(4000);
RECEIVE @dh = [conversation_handle], @message_type = [message_type_name], @message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [ProcessingSendQueue];
IF @dh IS NOT NULL
BEGIN
IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
RAISERROR (N'Received error %s from service [ProcessingReceiveQueue]', 10, 1, @message_body) WITH LOG;
END
END CONVERSATION @dh;
END
END
Transaction Report By Weekly----Plz Help To Write Query
Nov 30, 2007
Hi Guys,
I am generating Transaction Activity report,which should get data by weekly.Report shold look like this.
W1
W2
W3
W4
W5
W6
OKC
79
38
50
76
35
47
NFL
0
0
45
43
33
28
LA
5
12
10
0
0
10
Total
79
38
95
119
68
75
Iam passing 3 parameters @startdate,@enddate,@Market. when i select one Market(OKC/NFL/LA), report generating properly, but when i passing 3 markets values(OKC,NFL,LA), iam getting wrong report,report format is not correct.I used Cross tab for generating this report.Result lam getting like this.
W37
W38
W39
W40
W41
W42
W43
W44
W45
OKC
80
OKC
38
OKC
95
OKC
119
OKC
68
OKC
75
OKC
74
OKC
70
OKC
59
OKC
OKC
LA
And i wrote query like this,
select m.Market_name as Market,'W'+datename(ww,ut.creation_date) as Week,count(ut.transaction_id) as Count
from POS.DSC_TRANSACTION_STATUS_VL ts inner join POS.DSC_USER_TRANSACTION ut
on ts.transaction_status=ut.transaction_status inner join POS.RETAIL_LOCATION rl
on ut.rl_number=rl.rl_number inner join POS.BILLING_MARKETS bm
on rl.bm_code=bm.bm_code inner join dbo.Market m
on bm.market_id=m.market_id
where (ut.creation_date between @startdate and @enddate) and m.market_name IN(@Market)
group by m.Market_name,ut.creation_date
order by m.Market_name desc
Could you please some one help me to get this correctly.
Thanks in advance
Thanks
San
Transaction Replication Failed After Restoring Full Back Up Of Database In NORECOVERY
Mar 4, 2011We had a siutation last night in our production environment that forced us to revert back to an earlier version of the database (before a major code rollout that failed). After restoring the days full backup (with NORECOVERY), and then restoring a DIFF backup (FULL RECOVERY and had checked Preserve Replication Settings)...the transaction replication failed.
Message #1
The replication agent has been successfully started. See the Replication Monitor for more information.
Message #2
2011-03-04 15:07:17.566 Copyright (c) 2008 Microsoft Corporation
2011-03-04 15:07:17.566 Microsoft SQL Server Replication Agent: logread
2011-03-04 15:07:17.566
2011-03-04 15:07:17.566 The timestamps prepended to the output lines are expressed in terms of UTC time.
2011-03-04 15:07:17.566 User-specified agent parameter values:
[code]....
I've tried reinitializing the publication/subscription and while that took brand new snapshots and copied it over to the replicated data server, it did not fix the problem.I read from a different post that I could try running "sp_replrestart" but that ran for about a half an hour and didn't appear to do anything but fill up our log files...did I not wait long enough?
The only thing I know to do at this point is to drop the publication on the production server and rebuild it completely (and with all the tables we're replicating that would take quite a bit of time.
SQL Server 2014 :: Stored Proc Timing Out Halfway Through - Rolling Back Without Transaction
Jul 7, 2015We have a high volume database with 1000's of users and 1000's of procs. Our application enforces a 20 second timeout on all connections.
We can't adjust the 20 seconds - this is a business rule.
It sometimes happens that a proc does not complete within 20 seconds and then times out halfway though. This causes data inconsistency where 50% of the code was saved to the DB and 50% was not - seeing that a stored proc is not transactional and therefor does not roll back the code.
We can't put the code in a TRANSACTION in order to roll back when a time out occurs, because this causes exclusive locks on the tables.
So I guess my question is:
Is it possible to undo/rollback all the code in a proc when a timeout occurs - without using a TRANSACTION? And if a TRANSACTION is the only way - how do I avoid the exclusive lock and blocks?
Replication :: Current Transaction Cannot Be Committed And Cannot Support Operations That Write To Log File
Nov 4, 2015After I watch a video for how to create MS SQL Replication, I configure distribution and create the publication. The problem comes from subscription. If I create a "PUSH" subscription, it works fine. However, when I create a "PULL" subscription, I got the following error. Where I should look for solution for this error.
View 5 Replies View RelatedPerformance Degradation After Replication Rolled Out
Dec 14, 2006Hi all.
Any assistance would be greatly appreciated.
We recently created transactional replication to hopefully improve performance issues we were expereincing. The replication is between 2 SQL Servers (2000), and since we have introduced the replication, the performance has degraded considerably.
I will try and explain the scenario.
We have a primary db that our internal users use and we also have the newly replicated db that our website and another application use. The users are complaining that the website and the internal application is extremely slow and I was just wondering if it is possible to do an Index Tuning on both the primary db and replicated db based on trace files so as to create new indexes or would this have an impact on the replication?
Thanks in advance.
Power Pivot :: Measure Calculated On Leaf Level And Rolled Up
Jun 19, 2015I have the following scenario. I want to apply some calculations on different levels and then aggregate them up.
First measure calculates at Productgroup,color,store,size level
ProductGroup Color Store Size Amount Quantity
Measure:Amount*Quantity (ProductGroup, Color, Store, Size)
A Blue Store A L 100 6 600
A Red Store A S 150 4 600
A Green Store A M 160 7 1120
B Blue Store A L 300 3 900
[Code] ........
The other measure ignores color
ProductGroup Store Size Amount Quantity
Measure:Amount*Quantity (ProductGroup, Store, Size)
A Store A L 100 6 600
A Store A S 150 4 600
A Store A M 160 7 1120
B Store A L 640 15 9600
[Code] ...
Ignoring that gives another figure for productgroup B. In the pivot, I should see both measures at whatever attribute, except for the measure that excludes color will be null if tried split on color
ProductGroup Amount Quantity (ProductGroup, Color, Store, Size) (ProductGroup, Store, Size)
A 410 17 2320 2320
B 640 15 2820 9600
C 170 5 430 430
How should the two measure be defined:
Measure (ProductGroup,Color,Store,Size)
Measure (ProductGroup,Store,Size)
The Log File For Database 'Home_alone' Is Full. Back Up The Transaction Log For The Database To Free Up Some Log Space.
Feb 4, 2008
Hi all
Iam working in Prodcution ENV,Please help how make space
The log file for database 'Home_alone' is full. Back up the transaction log for the database to free up some log space.
Accidentally Deleted A Table In My Local SQL Server How Can I Get Back The Table
Jan 13, 2004Hi,
I accidentally deleted a table in my local server. How can I get back the table? I did not do it as a transaction!
Thanks in Advance
Table Scans - More Rows Brought Back Than In Table
Jul 20, 2005I have a query which is quite complex. It's based on a set of data ina complex view which takes the data from several tables.In this complex query, if I allow the various parts of the query towork on the results of the view (MISView), it can take 15 minutes torun (eek !), however, if I create a temporary table with the data fromthe view and then use that for the remainder of the query, it runs inapprox 20 seconds.Now, I have examined the execution plan (my new favourite toy) andthere is a difference (as expected). However when looking at the partof the query that takes up most of the time, it shows that it bringsback 109,645,866 records from a table (Credit) that contains 13,002records. This table is one that is referenced in the view (MISView)which contains 13,653 records and does get some of it's data from thetable which is scanned (Credit).For the record, we don't have any tables with over 100,000 records in,so 109 million rows is going some for us. The part of the query thatruns slow does reference another copy of itself but this is necessaryfor the equation that is being run.Now I'm OK with why it's doing the table scan, but why does it bringback substantially more data than is in the table ? Is it somemultiple of the number of records that it's trying to work out. Iassume it tries to run a seperate plan for the view as part of it'sprocess.Ideally, I'm still going to go down the route of the temporary table,but I would like to understand more about what it does first as Idon't like leaving things unanswered.Any help would be appreciated.
View 6 Replies View Related