I wrong delete all contents of a table.
I have only have the actual Transaction Log.
With Log Navigator i found the lines that i wish/need to roll back.
Can you please give me any ideas of how to recover/uncommitted/rollback data from the transaction log.
Thanks,
Paulo
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Hi,
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Thanks in advance.
How to purge data in transaction table or we can delete some data and store in separate table in data warehouse?
View 7 Replies View RelatedI wrong delete all contents of a table.
I was going to restore the full backup database but the file is corrupt.
I have:
The last 5 days backups of Transaction Log;
The actual Transaction Log;
Can you please give me any ideas of how to restore de transaction logs backups and/or uncommitted de deletion of all rows of the table.
Thanks,
Paulo
Hi
DepartmentMaster table has been deleted
how can I retrive the data from transaction log?
thanks
Good Afternoon,
I have a package with reads data from a text file and persist that data in a table "X" on sql server. I have one dataflow wich does this operation. In the sequence, i have this dataflow connected to the next dataflow, wich read the data from the table "X" and do some joins with another tables and persist that new data on a table "Y". The first problem, the table "Y" have a foreign key to table "X" and i have to do this operation with transaction, the package give problem, it's something like "Cannot enlist this connection on distributed transaction...", but i have many packages, wich I exec with transaction and runs sucessfully.
If i turn off the transaction, runs perfectly, when i put the transaction,it fails.
Sorry my english...
Thanks
Hi,
I've 6 data flow tasks in my package. I need to put all of these dataflows into a transaction and rollback if any one of the task fails. I dont want to use MSDTC.
Can anyone help?
Thanks and Regards,
Subha
I am in the process of choosing between either SQL Workgroup or Standard Edition. I see the differences in features on the comparison table, but do not see any references to the differing capabilities in handling transactions.
Is there any differences between Workgroup and Standard in terms of handling transaction/data capabilities? i.e. Does Standard have the superior capability in handling X times more TPMs than Workgroup?
If not, am I correct to assume that this is totally determined by hardware configuration (# of CPUs, processor speed, HD speed, RAM) ?
If the data volume / transactions handling is solely determined by hardware configuration, and I know the # of transactions and amount of R/W per second, .......where would be a good reference to know what kind of hardware configuration I need (ideally, once I know the hardware configuration, I guess I would be able to determine I need Workgroup or Standard)
Thanks in advance,
benbry
Hello,
I have a database that I am setting up in SQL Server 2005. Initially, I am doing very large imports of data. Every time I run an import, I am having the increase the size of my transaction logs, and now they are approaching 2 GB. Should these be purging themselves? I have to keep increasing the max size of the log so that I can get my data in. While this will work for now, it is not a long term solution, because I can see the log size growing quite large and the amount of space on the server obviously isn't infinite. Is there a setting that I can change so they will automatically purge? If not, how do I purge this information myself?
Thanks so much!
Christine
Hello everyone, My web application uses SQL Server database and I am connecting via standard SqlConnection object and running stored procedures using SqlCommand object. In one of my page, I have data coming from 2 different tables. Now , data from 1 table comes as only single record. But from other table it comes as multiple records. Meaning, data that I read as 1 record, goes to different textbox and dropdown controls on page. Data that comes in multiple rows, I am binding that data with DataGrid. Now, in aspx page data from both table can be updated and on aspx page I only need to provide a single save button. Now, I am not sure how to save/insert/update a single row in 1 table and multiple rows in another table in 1 transaction. I thought of stored procedure. But I don't think its straightforward with stored procedures since table with multiple records, I am not sure how to pass all the records in stored procedure's arguments.Is there any way that I can control whole transaction in ASP .NET? Thanks,Ujjaval
View 5 Replies View RelatedHi !
I want to insert master/detail data using transaction if while insert if error it will Rollback. Help me! Thanks
Here is my dilema. I have a 120 GB database that I need to mask customercredit card numbers in. The field is a varchar (16). I need to updatethe field so that we only store the first 4 numbers and the last 4numbers of the credit card and insert * to fill in the rest of thecredit card number.I was going to do this as a loop using the following code:While Exists (Select Top 10 * From Header Where IsNumeric(CCNbr) = 1)BeginBegin Transaction T1UpdateHeaderSetHeader.CCNbr = Left (D1.CCNbr, 4) + '********' + Right (D1.CCNbr, 4)From(Select Top 10 * From Header Where IsNumeric(CCNbr) = 1) as D1Commit Transaction T1If Not Exists(Select Top 10 * From Header Where IsNumeric(CCNbr) = 1)BreakElseContinueEndIn theory this only selects the top 10 rows, updates them, dumps the logand moves on to the next 10 until all the rows are updated.I tried running this on my test database and it fills up the transactionlog.Can anyone tell me the best way to go about doing what I need?Thanks*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedWe have two db's. One live and one test.When I right click on the live one in SQL Enterprise Manager andselect properties -> Data Files ->File Name is LIVE.MDFLocation is F:DataLIVE.MDFWhen I right click on the test one in SQL Enterprise Manager andselect properties -> Data Files ->File Name is LIVE.MDFLocation is F:DataTEST.MDFSame thing applies to Transaction log files too.My concern is File Name is same in both the above cases even thoughthe location is different. What are the consequences of this.Thanks for your helpGVV
View 1 Replies View Related
I am working on reformating, cleaning, adding derived elements, etc... on data with no data validity checks on the front-end, source data. Its an incremental process where each month I add new data to the prior months' history. Sometimes the new monthly source data "surprises" me -- different values, different data definitons, etc. than expected.
I deal with these surprises by accumulating the fields/values that are unexpected after converting them to varchar with some explanatory language into a table [program_newdataprobs]
If there are any records in [program_newdataproblem] I rollback the transactions so the prior months' history remains unaltered. The problem is I then "lose" the contents of [program_newdataproblem] which I would like to hand to the source data people to troubleshoot.
I have tried the following:
Begin Transaction tran1
.... code that reformats data here
if (select count(*) from program_newdataproblem)>0
BEGIN
Begin Transaction tran2
select * into #t1 from program_newdataproblem
Commit Transaction tran2
Rollback Transaction tran1
insert into program_newdataproblem
Select * from #t1
END
This bombs because because #t1 no longer exits
Any way I can "keep" the data from program_newdataproblem when I Rollback the other transactions? (without having to store the history in separate tables that I can then access if new data errors occur)
Thanks,
We are designing an ETL solution for a BI project using SSIS.
We need to load a dimension table from a source DB into the DW; inside the DW there are two tables for each source dimension table: a current dimension table and a storical dimension table.
The source table includes technical columns that indicate if each record was processed correctly by ETL procedures.
Each row in the source table can be a new record, or an exisisting one. If it's a new record, a corresponding new record should be inserted into the current dimension table of the DW; if it's an exisisting one, a new record should be inserted in the storical dimension table and the existing record in the current dimension table should be updated.
Furthermore for each record we need to update the source table with an error code. If the record was processed with succes the code is zero, otherwise it contains an error code.
I try to use a single data flow task, using the 'ole db command trasformation' task for managing update and 'ole db destination' task for managing insert.
The problem is that some insert and update should be executed inside a single transaction, for each record; for example if the source contains a new record I should insert the record in the current dimension table and update the source record with error code zero if every think goes fine.
There is some way to group task in the data flow pane in a single transaction ?
There is a better way to do that ?
Cosimo
I have one SP in which I am opening transaction and on fail I am rolling back transaction.
Is there any way I can preserve data in temp table before failing record.
I means to say At record no 21 get failed I need to see 20 records in any temp tables.
Hi,
I am having a application in which from the front end i am saving details of three different things
i.Enquiry Details
ii.Parts Details
iii.Machine details
i am saving the Enquiry detail in a data table,Parts Details in a data table and machine detail in a data table and finally i am adding the three data tables into a single data set and passing the data set to data access layer there i have three insert command one for each data table in my case the enquiry data table will be saved first and then the next two details will be saved and i am saving the details in three different tables in the database, my problem is some times the enquiry details will save to the database and while saving the Parts details there may be some exception and i will throw an exception in that case the enquiry details will be saved and the remaining two details are not saved(Which are also part of the same Transaction).I wanted to know about how to call the transaction function in case of Data Access Layer.
Hello, everyone:
I have a local transaction,
BEGIN TRAN
INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE
COMMIT
View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query:
Server: Msg 8501, Level 16, State 1, Line 12
MSDTC on server 'PROD' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].
It looks like remote server is not available inside the local transaction. How to handle that?
Thanks
ZYT
Hi techiesI have set up a Transaction replication from My Primary Server toSecondary Server on Orders table.Thousand of records gets inserted on Orders every hour which getreplicated on the secondary server. it works finereporting apps uses Secondory server's Orders table data for generatingreports .The Problem :Let say if i want to Remove older records from Orders table in theprimary serverwith out reflecting this change on the secondary server.is there a way to PREVENT this operation /transaction to be propogatedto the secondary server.Note : i am moving the records to another table (orders_Archive ) anddeleteing the rows from orders table . Also I need all the rows to bepresent on the secondary server table.Please advice ASAPRegards,Raj
View 4 Replies View RelatedHi Guys,
I have a package that inserts data from a sql server table to an oracle table. I use a dataflow task to do the loading.
The weird thing is, if the package fails during execution, the transaction does not perform a rollback and the inserted data is committed.
The dataflow component has its TransactionOption set to Supported (this has always been the case for our sql to sql packages). However, now that I have a sql to oracle package, it won't work anymore.. also, setting the TransactionOption of the dataflow component to Required causes the whole package to fail.
Anyone have any thoughts on this?
Thanks,
Kervy
Out of using stored procedure, reports and all this staff, I want to know the possible way to make sure that the data inside my Secondary Server Read only database are same as data in my primary server database.
So what is the simple way to do this check?
First of all i do not know whether this is the right form to ask the question Let me describe the scenario iam using Iam generating xml files at a particular place and sending them to a server xml1|--------------------->dataset1------------------------------>adapter1.update(dataset1)xml2|----------------------->dataset2----------------------------->adapter2.update(dataset2)xml3|----------------------->dataset3------------------------------>adapter3.update(dataset3) all the three updates should happen in only one transaction if any one of the update fails then the transaction should rollbackcan anyone tell me a way to do iti am desperately in search of any ways to do it can anybody help please
View 2 Replies View RelatedI have two tables one list changes of hospital ward and one lists changes of consultant doctor. These can change independently ie a ward change can occur without a consultant change and vice versa. I want to summarise these changes to give the status at each date_serial value.
create table #temp_ward_stay(date_serial bigint,ward_id varchar(10))
;
insert into #temp_ward_stay
values
(201501021200,'W1'),(201501031201,'W2'),(201501091200,'W3'),(201501161200,'W4'),(201501161201,'W5')(201501271200,'W3'),(201502101200,'W5')
;
create table #temp_consultant_episode(
date_serial bigint,
consultant_id varchar(10))
;
insert into #temp_consultant_episode values
(201501021200,'C1'),(201501031200,'C2'),(201501031201,'C3'),(201501091201,'C4'),
(201501121200,'C3'),(201501301200,'C6'),(201502111200,'C6'),(201502111201,'C7')
;
If I use:
select date_serial,consultant_id,null as ward_id from #temp_consultant_episode
union
select date_serial,null as consultant_id,ward_id from #temp_ward_stay
I get:
date_serial ward_id consultant_id
-------------------- ---------- -------------
201501021200 NULL C1
201501021200 W1 NULL
201501031200 NULL C2
201501031201 NULL C3
201501031201 W2 NULL
201501091200 W3 NULL
201501091201 NULL C4
201501121200 NULL C3
201501161200 W4 NULL
[code].....
whereas what I actually want is:
date_serialward_idconsultant_id
201501021200W1C1
201501031200W1C2
201501031201W2C3
201501091200W3C3
201501091201W3C4
201501121200W3C3
201501161200W4C3
201501161201W5C3
[code].....
I can see how I could remove the nulls where the date_serial values are the same:
select ce.date_serial,ward_id,consultant_id from #temp_consultant_episode ce
join #temp_ward_stay ws
on ce.date_serial=ws.date_serial
but I can't see how to move forward from here.
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.
View 8 Replies View Related
I want to list out the pending transaction for transaction replication by publication.
Help needed.
I'm receiving the below error when trying to implement Execute SQL Task.
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'
I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.
Anyone know of the reason?
I created a Calculated measure in cube something like this : ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent].&[SPEND],[Measures].[Transaction Amount]). To get only spend transactions. Now, I want to slice this measure with same hierarchy to find the amount distribution across different transaction types under spend transaction. But this query behaving like the measure doesn't have relation with measure.
you can think this as below query:
WITH
MEMBER SPEND AS ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent].&[SPEND],[Measures].[Transaction Amount])
SELECT NON EMPTY {SPEND} ON 0
,NON EMPTY ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent]) ON 1
FROM [CUBE]
Hi
How do I make use of begin transaction and commit transaction in SSIS.
As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:
Hresult:0x80004005
descriptionyntax error or access violation.
its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.
Thanks,
Prashant
Error returned when trying to commit the transaction to a database that is a replication distributor. (sql2005 ctp16)
View 10 Replies View Related