The Partner Transaction Manager Has Disabled Its Support For Remote/network Transactions.
Aug 23, 2006
I'm trying to run an SSIS package. The package runs on an SQL 2005 server on Win2k3 server. The package tries to connect to another win2k3 server with sql 2000 to retrieve some data. However, I recieve the errormessage shown in the topic.
I found info about modifying the MSDTC security settings under "component services" and did so. I made sure everything was allowed. However, the result was the same. Does anyone have any other idéa about what could cause this problem?
PS. The package works fine if I set up both databases on the same physical machine...
Hi All, I am using the CSLA framework for the data access with SQL Server 2005 and we are not using distributed transaction. When we are using this architecture for our local machine it is working fine and our DB is in local machine itself. But I am getting the following error in our development server and our DB is in the Network. " DataPortal.Fetch failed (DataPortal.Fetch failed (DataPortal.Fetch failed (Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.))) ---> DataPortal_Fetch method call failed ---> DataPortal.Fetch failed (DataPortal.Fetch failed (Network access for Distributed Transaction Manager (MSDT... Procedure Script call failed." Please help me out to get rid of it. Your help would be appreciated. RegardsGomaz
I have a quick question which I am hoping someone can answer for me.
In many of the guidelines / checklists to secuing MS SQL Server 2000 (particularly SANS and CIS) , it says that all unused network libaries should be disabled.
Can anyone please tell me what the security risk is if Named Pipes is enabled and the default path (is that what it is called?) of \.pipesqlquery is set, as I believe is the default setting upon installation.
All I can seem to find is guidance on what to do (disable unused network libraries) and not why you would want to do it.
I've tried to enclose a few database operations in a TransactionScope block but it looks like SQL Server CE RM does ignores ambiental transaction.
Here is the code:
static void TestTxn() { // Command to insert an integer in a table with a single integer column string cmdPassText = "INSERT TESTTABLE (INTFIELD) VALUES(1)"; // Command to force field type mismatch exception string cmdFailText = "INSERT TESTTABLE (INTFIELD) VALUES('Foo')";
using (TransactionScope scope = new TransactionScope()) { using (SqlCeConnection conn = new SqlCeConnection("DataSource = 'Test.sdf'")) { try { conn.Open(); SqlCeCommand cmdPass = new SqlCeCommand(cmdPassText, conn); returnValue = cmdPass.ExecuteNonQuery(); SqlCeCommand cmdFail = new SqlCeCommand(cmdFailText, conn); returnValue = cmdFail.ExecuteNonQuery(); } catch (Exception ex){ Console.WriteLine("Command failed"); Console.WriteLine("Exception Message: {0}", ex.Message); } } scope.Complete(); } }
After first command suceeds and seccond command failes table still has one affected row after transaction.
Am I doing something wrong or System.Transactions.Transaction is not supported with SQL Server CE RM?
I want to use transactions to protect my tasks. I have ten tasks that need to be completed in a single package. If any of the tasks fail - I need the entire process to rollback.
I have 5 execute sql tasks to truncate groups of tables in my destination database.
After each of the single truncate tasks - I use data flow tasks to copy the data from the source to the destination db.
Both dbs are on the same server.
I am basically copying the entire db - with the exception of a few tables.
package.transactionOption = required , and all tasks transactionOptions are set to supported.
Several questions. It seems to hang on the first data flow task - the output window stops, etc.
If I set package.transactionOption = supported - The package will execute. If I create an error on the thrid truncate task - all previous tasks will complete - the package will fail on step 3 and the truncation for this step is not commited.
I am partially there. Can anyone help point me in the right direction? I need the entire process to commit if all tasks are successful or rollback if any fail.
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.
This is my code in vb.net with Sql transactionI am using insertcommand and update command for executing the sqlqueryin consecutive transactions as follows.How can I achive parallel transactions in sql------------------start of code---------------------trybID = Convert.ToInt32(Session("batchID")) strSQL = "" strSQL = "Insert into sessiondelayed (batchid,ActualEndDate) values (" & bID & ",'" & Format(d1, "MM/dd/yyyy") & "')" sqlCon = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("conString")) Dim s1 As String = sqlCon.ConnectionString.ToString sqlDaEndDate = New System.Data.SqlClient.SqlDataAdapter("Select * from sessiondelayed", sqlCon) dsEndDate = New DataSet sqlDaEndDate.Fill(dsEndDate) dbcommandBuilder = New SqlClient.SqlCommandBuilder(sqlDaEndDate) 'sqlCon.BeginTransaction() 'sqlDaEndDate.InsertCommand.Transaction = tr If sqlCon.State = ConnectionState.Closed Then sqlCon.Open() End If sqlDaEndDate.InsertCommand = sqlCon.CreateCommand() tr = sqlCon.BeginTransaction(IsolationLevel.ReadCommitted) sqlDaEndDate.InsertCommand.Connection = sqlCon sqlDaEndDate.InsertCommand.Transaction = tr sqlDaEndDate.InsertCommand.CommandText = strSQL sqlDaEndDate.InsertCommand.CommandType = CommandType.Text sqlDaEndDate.InsertCommand.ExecuteNonQuery() tr.Commit() sqlDaEndDate.Update(dsEndDate) sqlCon.Close() End If Catch es As Exception Dim s2 As String = es.Message If sqlCon.State = ConnectionState.Closed Then sqlCon.Open() End If strSQL = " update SessionDelayed set ActualEndDate= '" & Format(d1, "MM/dd/yyyy") & "' where batchid=" & bID & "" sqlDaEndDate.UpdateCommand = sqlCon.CreateCommand() tr1 = sqlCon.BeginTransaction(IsolationLevel.ReadCommitted) sqlDaEndDate.UpdateCommand.Connection = sqlCon sqlDaEndDate.UpdateCommand.Transaction = tr1 sqlDaEndDate.UpdateCommand.CommandText = strSQL sqlDaEndDate.UpdateCommand.CommandType = CommandType.Text sqlDaEndDate.UpdateCommand.ExecuteNonQuery() tr1.Commit() sqlDaEndDate.Update(dsEndDate) sqlCon.Close()
I have several SQL Server 6.5 machines. I would like to see what network support each server uses and change it if possible through the Enterprise Manager.
When trying to Add a Network support from within SQL6.5 Setup I can only see Named Pipes and Multi-prototcol as my only options. I would like to have TCP-IP. How can I do that. TCP-IP is setup on the NT server. (Most likely after the SQL installation). How can I add the TCP-IP protocol support in SQL without re-installing SQL.
We are trying to determine what SQL Server 2000 Edition is required for our environment. We really don't need any of the features that Enterprise Edition offers over Standard Edition, except that our disk storage is on a System Area Network (SAN). BOL says that SAN is not supported under Standard Edition, but I am under the impression, that if we are not using distributed queries or replication, then Enterprise Edition is not neccessary.
Hi, I need to get help for a SQL issue from Microsoft. On the website there seem to be two options--one is email support for $99 and another is "online" support for $259. What's the difference? Does the $259 option mean that Microsoft will remote log into my SQL Server and fix the issue while I watch? This is what I need done. Does this $259 pay for the whole issue, until it is resolved?
I am a Microsoft Certified Professional (MCSA)--do I get any discounts or is there any other avenues I should take to get remote support for SQL Server 2005? I wasn't sure where to ask this question, if it is more appropriate elsewhere then let me know.
A colleague wants to insert many millions of records where thevalues are computed in a C++ program. He connects to thedatabase with ODBC, and does an INSERT for each row.This is slow, apparently because each INSERT is a separatetransaction. Is there a way to delay committing the datauntil several thousand records have been written? InsideSQL Server this is simple, but I don't see an equivalentwhen using ODBC. Or is there something better than ODBC?Or might it be faster to write values to a file and thenuse bulk insert? I would appreciate any thoughts on thisgeneral problem!Thanks,Jim
My client recently (well, the middle of January, anyway) attempted to restore their year-end backup to do some reporting, only to find that the backup file was corrupted and unrestorable. They do have earlier monthly backups, but they do not keep transaction log backups past those monthly backups (i.e. transaction log backups for November are discarded once the monthly full backup has been completed).
My question is this: is there any way to restore the month-end backup from January, then read the transaction log backups for the month of January to undo January's transactions?
This is a highly business-critical issue; they are going to be in a lot of hot water with the SEC if they can't produce the financials stored in the DB.
My client recently (well, the middle of January, anyway) attempted to restore their year-end backup to do some reporting, only to find that the backup file was corrupted and unrestorable. They do have earlier monthly backups, but they do not keep transaction log backups past those monthly backups (i.e. transaction log backups for November are discarded once the monthly full backup has been completed).
My question is this: is there any way to restore the month-end backup from January, then read the transaction log backups for the month of January to undo January's transactions?
This is a highly business-critical issue; they are going to be in a lot of hot water with the SEC if they can't produce the financials stored in the DB.
I need to connect to a sql ce data file via Intranet . Does sql ce support that ? When I attempt to connect that remote file, it show me the error message "There is a file sharing violation .A different process migth be using this file".
Taht is my code. I have a defined common transaction. In my call path, I use: using (IDbConnection connection = GetConnection ()) { retval = q.Find (connection, out DataParticleList); connection.Close (); } DetermineDataParticles ((DataParticle[]) DataParticleList.ToArray (typeof(DataParticle))); And then, in Determine DataParticles, I do: using (TransactionScope scope = new TransactionScope (_Transaction)) { using (IDbConnection connection = GetConnection ()){ again. As both are using a TransactionScope that - uses the same transaction, and as the first connection has been closed - should the whole thing not reuse the same connection? GetConnection () has the same connection string. I get a promotion to DTC in the second GetConnection (). That indicates that a new connection is being opened (and attached), and not the first one being used. Any hint on why that is? What can I change for this? I would really like this not to be promoted upward to DTC. It makes no sense to me.
I have a requirement from the client that specifies to rollback every insert/update that happenned in the package if any task (control or data flow) fails.
I'm certain the SSIS package-level transactions take care of this, however, in this package, there is an OLE DB Transformation that executes a stored procedure which has a transaction in itself.
so to draw a quick picture... Package {
Transaction1 {
Data Flow {
OLE DB Transformation {
Stored Procedure {
Transaction2 { } } } } } }
Here's my question: What would happen if an error occured in the stored procedure (Transaction2)?
Does it behave like SQL Server 2005 where, given a scenario of nested transactions, the innermost transaction is comitted and the outermost transaction is rolled back?
I'm hoping that if the stored procedure decides to rollback Transaction 2 via error handling or if an SQL error occurs that I can rollback Transaction 2 and log an entry in the audit log.
Hi,I have 2 stored procedure 1st insert the data in parent tables and return the Id. and second insert child table data using that parent table id as paramenter. I have foreign key relationship between these two tables also.my data layer methods somewhat looks likepublic void Save(order value){using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required)) { int orderId = SaveOrderMaster(value); value.OrderId = orderid; int childId = SaveOrderDetails(value); //complete the transaction transactionScope.Complete(); }}here 1. SaveOrderMaster() calls an stored procedure InserOrderData which insert a new record in order table and return the orderId which is identity column in Order table.2. SaveOrderDetails() call another sotored procedure which insert order details in to table "orderdetail" using the foreign key "orderid".My Problem:Some time the above method works correctly but when i call it repeatledly (in a loop) with data, some time it gives me foreign key error which state that orderid is not existsin table Order. This will happen only randomly. I am not able to figureout the reason. does some one face the same problem. if yes, what could be the reason and/or solution.
We have connected a Access to a MS SQL Server 2005 as a Linked Server with the followoing settings:
1) Provider: Microsoft Jet 4.0 OLE DB Provider
2) Product Name: Access
3) Data source: X:XXXXX.mdb
4) Provider string: ;pwd=YYYYYY;
5) Collation Compatible: False
6) Data Access: True
7) Rpc: False
8) Rpc Out: False
9) Use Remote Collation: True
10) Collation Name:
11) Connection Timeout: 0
12) Query Timeout: 0
We found that when cannot have any insert/update/delete statement for this linked server if transaction is began. Otherwise, we will have the following exception.
============
Msg 7390, Level 16, State 2, Line 1 The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ZZZZZZ" does not support the required transaction interface.===========
We wonder whether the problem is because Access does not support distributed transaction. If so, do you know any workaround will work for us?
I am trying to determine if it is possible to add transaction support for a SSIS package. Here are the basic tasks:
Read New/Updated records from SQL Server Update changed records in Access Insert new records into Access Update new records in SQL Server with Access key assigned on insert in previous step Reset dirty_flag
I would like to wrap a transaction around this. If the package fails in the task that updates sql server with the Access key, the next time it runs, it duplicates the new records in Access because it did not finish the process. Is it possible to have a transaction wrap this entire process so I can undo the inserts to Access if I fail any subsequent tasks?
I have a update trigger. In this trigger I need to insert few records in 3 tables. If error comes in any of these inserts then previous inserts to get committed. This trigger was written in Sybase and it was possible to create transaction and commit the transactions.
We are having a really big problem with a zombie process/transactionthat is blocking other processes. When looking at Lock/ProcessIDunder Current Activity I see a bunch of processes that are blocked byprocess 94 and process 94 is blocked by process -2. I assume -2 is azombie that has an open transaction. I cannot find this process tokill and it seems that this transaction is surviving databaserestarts. I know which table is locked up and when I run a select *from this table it never returns. Does anyone have any ideas as tohow to kill is transaction.Any help is appreciated.A. Tillman
I'm trying to create a transaction replication from SQL Server 2000 to 2005. Basic replication between the servers works just fine. However, what I want to accomplish is to be able to skip some of the transactions. Example - from time to time we want to purge some of the historical data from the main database (the publisher). We don't want the same purging to occur on the destination database, which will be used for reporting purposes and needs to include all the historical information. I wanted to simply stop the replication log reader, purge the records, backup the transaction log with truncation and then restart the reader. The only problem - the truncation on the replicated database keeps the transactions of the purging until they are replicated, so the transaction log backup doesn't help. Any ideas would be greatly appreciated!
I have an SSIS package which accesses a map UNC path drive. It works fine when I run SSIS from Visual Studio. However, when I run this from a SQL Server job, it gives an error.
I am not allowed to use xp_cmdshell due to security reasons. If someone can point me to right direction, that would be greatly appricated.
I have scheduled a backup on a remote machine. Everyday I FTP these backups from the remote machine to the local drive.
A local drive is maped to the remote machine in Windows NT Explorer but SQLSERVER7 Enterprise Manager can't see this drive so that I could do the backup to it.
I need to backup a remote Database to a local device. Does anyone know how I do this . How do I define a remote Network device/ what priviliges are needed How I do set up the Backup job....
I am converting over from MSDE to SQL Express. My front end program is unable to connect to the database. To troubleshoot, from the remote computer I tried connecting with the management program and I get the Error 26 unable to connect.
Here is what I have done: I made sure that the SQL Server configuration has been changed to allow network connections (both TCP and named pipes). I have disable windows firewall on both machines. I confirmed that I can connect from the remote machined to the server by network browsing. Both machines are running XP Proff. There is no other security software running on either machine.
More Info. when attempting to log on I am using the server authntication, I use "sa" as the user id plus my password.
Goal. The front end of database program connects to the server remotely using a program called Hamachi over the internet. This works without a problem on MSDE. But for now I am simply try to connect using the managment program which lets you browse for other serves. When I do this it shows the remote computer, but not the name of the computer but not the SQL server which I type in like the following: "INTELsql1"
What I have not Done: I have not changed any permissions or added any users to the server, since this does not seem necessary. As stated the user of remote machine can connect to the server and change files.
hi i added 2 databases to my SQL server on a machine next door using Microsoft SQL server management studio express... I made sure that the server allows remote connection
then from this computer i pass a connection string to my SQLCLient in VB2005 that looks a bit like this
for which it gives me this error when i open the connection SQLConnecton.Open()
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.Att emptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.Log inNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.Ope nLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Flying_School_Scheduler.DBEngine.StartConnection() in
whats the big idea? i'm in a bit of a tight schedule here... can anyone help me here? ??
I have SQL Server and OLAP Server running on an NT Server box, and a pc running NT Workstation which has OLAP Manager installed on it. I can register the OLAP Server just fine in OLAP Manager, but it takes about 30sec to a minute for the pc to actually connect to the server.
We've also installed SQL Server and OLAP Server on a Win 2000 machine, and the OLAP connection is faster.
Could this be related to a network routing issue, OLAP or SQL server install issue?
I'm investigating a poorly performing procedure that I have never seen before. The procedure sets the transaction isolation level, and I suspect it might be doing so incorrectly, but I can't be sure. I'm pasting a bastardized version of the proc below, with all the names changed and the SQL mucked up enough to get through the corporate web filters.
The transaction isolation level is set, but there is no explicit transaction. Am I right that there are two implicit transactions in this procedure and each of them uses snapshot isolation?
SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL SNAPSHOT; DECLARE @l_some_type varchar(20), @some_type_code varchar(3), @error int, @error_msg varchar(50);
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!
