We are using MSDTC to handle our transactions and some clients seem to be getting these errors more and more when we went to framework 2.0. Any ideas what could be causing this?
Before in framework 1.1 we would ask our clients to re-register their computer into the domain and/or rename their computer and this worked but it seems like in framework 2.0 it doesn't do the trick. Also, the wrong dns setup causes this too but after trying to fix both things it still doesn't work. I believe this error means either the server has finished the transaction and can't find the client to return the result or the active directory account/computer account is corrupted. Any ideas?
The transaction has already been implicitly or explicitly committed or aborted.
Type: TransactionException
StackTrace:
Server stack trace:
at System.Transactions.Oletx.OletxTransactionManager.ProxyException(COMException comException)
at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
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
hey. I have a formview - inertitemtemplate. In here I have a button with CommandName="Insert". In code behind under FormView1.ItemInserting I want to be able to abort the inserting in some cases. Is this possible?
On several occasions I have experienced crashes while running an SSIS package in debug mode. It happens when I start process on Control Flow table and click on Data Flow tab to monitor progress. I have forwarded the error reports and have searched the boards for similar references. Is this problem going to be fixed?
SSIS Data Sources
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) ----------------------
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Visiual Studio 2005 Professional SP1 SP.050727-7600 .NET Framework 2.0.50727
This morning I needed to update some bad address's in our production DB. I ran this query in ISQLW:
UPDATE mytable SET city = 'blah' WHERE city <> 'blah' GO
it should have been: UPDATE mytable SET city = 'blah' WHERE city <> 'blah' AND name = 'blah' GO
So I basically started updating my entire DB rather than just a handful of records. The query seemed to be running too long (30seconds) then I noticed and clicked the Cancel button. Does this transaction get rolled back when you click cancel? Im not sure where to look to see if the rollback occurred.?
I do have a way to re-update all the address's and a backup but Ive never restored and Im not sure I even need to. I could also run a query to group all the address's but it will be very difficult to tell what got updated. Where talking about 500k records.
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.
Context: I have a web page that uplaods a file that is then imported to the database via an SSIS package.
Problem: If and when the package encounters an error, I want to surface that error to the user via the web page.
Approach: I derived my own class from the DefaultEvents class and then overrode the OnError event handler to catch any errors raised during package execution. I then pass my class to the package Execute method.
Results: The OnError is triggered, but I don't know how to abort package execution nor how to pass the error context to the user. I tried raising a custom exception in the OnError handler, but it failed to propogate to the calling code (ie the code that executes the package).
Another approach would be to write to a database table and then do a query from my web page after the package is done executing (alternatively, I could generate a log file). Seems like a lot of work though. All I want is to pass the error context back to my calling code so I can tell the user what happened.
I am using the sample SSIS Event Log reports provided by Microsoft: http://www.microsoft.com/downloads/details.aspx?familyid=526e1fce-7ad5-4a54-b62c-13ffcd114a73&displaylang=en
The Event Log Summary report is showing a negative value for #Abort. Why is the aborted count negative?
Hi Guys, I have a SQL block which I want to run based on some values in the DB ----------------- I am implementing the check as follows.. --------------------- PRINT 'Checking the DATABASE name and Schema Version '
SET NOCOUNT ON CREATE TABLE chkversion (table_name VARCHAR(30), id INT, reason varchar(200)) GO
DECLARE @MSG CHAR(1) print @MSG GO
/* Make sure user is running the right script and right db user */
declare @ver varchar(100), @username varchar(100), @cnt int begin select @ver = value from propertytable where id = 33086 select @cnt = count(*) from propertytable where parentid=5001 and propertyid = 726; select @username = db_name(dbid)from master.dbo.sysprocesses where status ='runnable' insert into chkversion (table_name, id, reason) values ('', -1, 'You are logging on Database - ' + @username) insert into error_report (table_name, id, reason) values ('', -1, 'Your agile database version is ' + @ver) if (@ver != '9.0.144'or @cnt !=0) begin insert into chkversion (table_name, id, reason) values ('', -1, 'STOP: Your Schema Version and/or database version are not at original 90SP2. Please abort!') end else insert into chkversion (table_name, id, reason) values ('', -1, 'PROCEED: You can run this script to upgrade your db schema from 9.0.144 to 9.0.178') end GO
DECLARE @MSG CHAR(200) SELECT @MSG = convert(varchar(255), reason) from chkversion where id = -1 print @MSG GO
DECLARE @MSG CHAR(1) print @MSG GO SET NOCOUNT OFF ---------------------------------------------------------- After this, I want user to press 1 to continue or CTRL +C to abort based on the output message from above. How Do I implement this feature? In Oracle, I use... Accept agproceed number prompt 'Enter 1 to continue or CTRL C to abort.';
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.
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.
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.
Hello everyone and thanks for your help in advance. I am encountering an error with a SQL Server 2000 database. When trying to write through a web application, I receive the error Transaction Log is full. In looking at the database,the transaction log (LDF) file is nearly 4 times that of the MDF file. Because of this, I am running out of disk space. I have read some articles regarding this topic, but can't seem to find one definitive source as to how to rectifify this problem (for example, do I need to TRUNCATE the log or does backing up the databse fix this issue?). I currently have the database allowing unrestricted growth to the transaction log, but don't know the ramifications of selecting a smaller size. Any help on this topic would be greatly appreciated. Thanks.
I unable to resolve the following error when I backup the transaction log.
BACKUP failed to complete the command BACKUP LOG [DatabaseName] TO DISK = 'D:MSSQLBackUpLogFilesDatabaseNameDatabaseName 20040810.TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
The error occured for the first time after sucessful completion of a full back up. The day after the full back up job completed, the transaction log back up job ran and it failed giving the error above.
Also the tran log is 17 GB. Does this error have to do with insufficient space on the tran log back up folder?
The transaction log for database 'DBName' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databaseswww.website.net/contact_us.aspx
Declare @ID int BEGIN TRY BEGIN TRANSACTION -- Start the transaction
Insert into t1([name]) values('Sample') SELECT @ID = @@identity
// IT FAILS HERE BECAUSE DOES NOT FIND INSERTED RECORD Insert into t1Details([idt1],[number]) values (@ID,20)
COMMIT END TRY BEGIN CATCH IF @@TRANCOUNT > 0 BEGIN ROLLBACK
END -- Raise an error with the details of the exception DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1) END CATCH
But it fails inserting the details because does not find the record inserted before. IS there any way to correct this? Thanks in advance
Hi all,I am getting below error messages mentioned between dotted lines.---------------------------------fcb::ZeroFile(): GetOverLappedResult() failed with error 2.The log file for database 'UAT' is full. Back up the transaction log for thedatabase to free up some log space..Could not write a CHECKPOINT record in database ID 7 because the log is outof space.Automatic checkpointing is disabled in database 'UAT' because the log is outof space. It will continue when the database owner successfully checkpointsthe database. Free up some space or extend the database and then run theCHECKPOINT statement.-----------------------------------------------------------------------------I have more then enough space and the log files is set to autogrow withunlimited space.I googled and found few people have similar issue but the resolution is notfound, yet.Can someone please suggest something to solve this issue.Thanks,
I dont want to insert nulls in my PK column but i thought the ID would generate automatically by using the @@Idenity field...can someone help me out on where I am going wrong?
{"Cannot insert the value NULL into column 'transactionId', table 'LibraryManager.dbo.BookOrder'; column does not allow nulls. INSERT fails. The statement has been terminated."} [System.Data.SqlClient.SqlException]: {"Cannot insert the value NULL into column 'transactionId', table 'LibraryManager.dbo.BookOrder'; column does not allow nulls. INSERT fails. The statement has been terminated."}
I'm completly confused. I'm trying to execute the following stored procedure:
BEGIN TRY BEGIN TRANSACTION
DELETE FROM Account_TB (i've purposely mistyped the table name to throw an error) WHERE PK_AccountNumber_STR_ID = @userID;
COMMIT END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK
-- Raise an error with the details of the exception DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1) END CATCH
When i execute the stored procedure, i get the following error:
Msg 208, Level 16, State 1, Procedure up_DeleteAccountInfo_Web, Line 17Invalid object name 'Account_TB'.Msg 266, Level 16, State 2, Procedure up_DeleteAccountInfo_Web, Line 17Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 3.
The activity monitor shows that the transaction is still running even after the stored procedure finished executing. Any ideas? Please help me with your advice.
Within the DB Maintenace Plan1, the Transaction Log is being backup. But the Transaction Log backup fails with the following message in Event Veiwer...
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'' (0xC83E14E2E989D147985A1AF15BD81A84) - Status: Failed - Invoked on: 2003-12-29 23:00:00 - Message: The job failed. The Job was invoked by Schedule 6 (Schedule 1). The last step to run was step 1 (Step 1).
The DataBase backup finishes. The Transaction does not. Can't find anything wrong. Help! Thanks...dmc
Hi, I have configured a linked server, and i have a procedure which makes an UPDATE in a local table using the data in the linked server.
Specifically, I have a function which checks if a given code exists in a linked server's table. The UPDATE changes the value of a column in a local table, if the function returns 1.
I've run the procedure and it gave an error after a few hours cause a simple conversion error inside the function. I solved the error. After this, the procedure did not work more. It gives me the following message:
Server: Msg 7391, Level 16, State 1, Procedure EXISTEONC, Line 16 The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
(EXISTEONC is the function, and in the line 16 there is an OPENQUERY) Im sure MSDTC is working... i'm lost because i dont know why it worked the first time and not now. Ive also wrote the function again as it was before, but it still doesent works.
I have a process that failed with the following error message. The SQL server error messages also said the Transaction log was full and there was not enough disc space.
Strangely, when I checked all the drives on the server, there was plenty of free space, the smallest amount free on one drive, where SQL server is located was 20GB. I am confused.
I backed up the DB to another server and deleted a lot of the transaction logs and now the drive has 30GB and is okay.
Does anyone know how the transaction log fills up? Can I change a setting somewhere to increase the maximum size of the transaction log? Or maybe change the location where it is saved to another drive? Or have it automatically cleared out every once in a while?
Any of your input is greatly appreciated.
Thanks.
Rodney
-------------------------------------------------------------------------- DS-DBMS-E400: UDA driver reported the following on connection 'Data Target (ODBC)': DMS-E-DBPARSER, The underlying database detected an error during processing of the SQL request. [Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database 'ancosalesdm' is full. Back up the transaction log for the database to free up some log space. (for details, see Build_SAL_FA_ShipSKU_0584.log) [PROGRESS - 00:32:42] Build Node 68 'SAL_FA_ShipSKU'; failed DS-DBMS-E400: UDA driver reported the following on connection 'ALIAS_00DF1E74': DMS-E-GENERAL, A general exception has occurred during operation 'execute immediate'. The log file for database 'ancosalesdm' is full. Back up the transaction log for the database to free up some log space. DMS-E-GENERAL, A general exception has occurred during operation 'execute immediate'. General SQL Server error: Check messages from the SQL Server. DS-DBMS-E400: UDA driver reported the following on connection 'ALIAS_00DF1E74': DMS-E-GENERAL, A general exception has occurred during operation 'rollback transaction'. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. DMS-E-GENERAL, A general exception has occurred during operation 'rollback transaction'. General SQL Server error: Check messages from the SQL Server. --------------------------------------------------------------------------
I wonder if anyone can help me with the following error message.We use Sql 2000.I wanted to backup the transaction log of a database with this command:BACKUP LOG [testdb] TO [TestdbBCK] WITH NOINIT , NOUNLOAD ,NAME = N'TestDB backup', NOSKIP , STATS = 10, NOFORMATand I got the following error message:Server: Msg 3132, Level 16, State 1, Line 1The media set for database 'testdb' has 2 family members but only 1 areprovided. All members must be provided.I know who MY family members are but I never knew that my databases havefamily members !!!Can anyone help me with this error ?Thanks !!David Greenberg
Hi all, I'm having an issue with transactions in SSIS.
I have a Sequence Container, that contains 5 Tasks.
1) Data Flow Task using an OLEDB Connection to access an Oracle RDB database via a linked server. then insert into SQL Server 2005.
2) Execute Sql Task on Sql Server 2005 (UPDATE Statement)
3) Execute Sql Task on Sql Server 2005 (DELETE Statement)
4) Data Flow Task using an OLEDB Connection to insert data into a SQL Server 2005 DB
5) Execute Sql Task performing an UPDATE statement on an Oracle RDB database using an OLEDB Connection to to access the database via a linked server
If the Sequence container and all contained tasks are set to Transaction: Supported (No transaction will be created I believe), then the package runs successfully.
If I change the Sequence container to Transaction: Required, and leave all contained tasks as Transaction: Supported, then Task 1 fails with the following error:
[SELECT From RDB Change Table [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SSISPOC" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Any Ideas would be much appreciated, as I'm stumped!
I've created 2 new jobs in Microsoft SQL Management Studio - one for full backup of database, and another - for backup of tran logs. The full backup is scheduled to run once every morning, and it runs just fine. However, tran log backup, which is scheduled to run every hour, has some problems. Sometimes it runs successfully 3-4 times and failing after that with this error: Executing the query "BACKUP LOG [survey_p0037832] TO DISK = N'G:\database backups\logs\survey_p0037832_backup_200708021000.trn' WITH NOFORMAT, NOINIT, NAME = N'survey_p0037832_backup_20070802100002', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
After I execute the full database backup on demand, then tran logs backup is runnung fine again for next 3-4 times... We do not have any conflict in time, I avoided that scenario... Do you know how can I fix it? Thank you
Hi,My code worked fine before i placed "SqlTransaction" command in my code. Now it is showing "Unexpected existing transaction." Please tell me where I am going wrong. My Code:
protected void Page_Load(object sender, EventArgs e) { using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Mfund_String"].ConnectionString)) { con.Open(); using (SqlConnection destinationConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Mfund_String"].ConnectionString)) { destinationConnection.Open(); using (SqlTransaction transaction = destinationConnection.BeginTransaction()) { SqlCommand DelCmd = new SqlCommand("delete from mfund_data", con); DelCmd.ExecuteNonQuery(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection)) { bulkCopy.DestinationTableName = "mfund_data"; try { bulkCopy.WriteToServer(CreateDataTableFromFile()); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); Response.Write(ex.Message); } } } destinationConnection.Close(); } con.Close(); } }Regards,Jagadeesh
Hi, i get this error while i manually execute dts. But when i execute dts on my .aspx page, i can't handle this error on "... catch(Exception ex) {... }" part. catch (Exception ex) { return ex.Message ; //DtsPackage.OnError += new PackageEvents_OnErrorEventHandler(DtsPackage_OnError); } Here is dts message in a text file. Step 'DTSStep_DTSDataPumpTask_3' failedStep Error Source: Microsoft OLE DB Provider for SQL ServerStep Error Description:Transaction (Process ID 124) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.Step Error code: 80004005Step Error Help File:Step Error Help Context ID:0 Any idea?
We developed an application in the ASP.Net with C#. We are going to do a task in the transaction. It is working perfectly. But if two users comes simultaneously, then It gives an exception that Transaction is already commited.
First transaction Commited Successfully. When Second transaction goes to Commit() Then it generate this error. We are unable to find this problem Whether this is problom from SQL Server or DOTNET.
