shabbir hussain bohra
8/21/2004 3:52:00 PM
I am writing a distributed transaction code. My current scenario include a client database(Suppose client- having 4 main database) which can be installed anywhere which would connect to a public database placed at ISP datacentre(Suppose Server- having 2 database[host,transmissionqueue]). this server database is added to client as linked server through vb.net interface. i have a transmission routine which transmitts the data from the client to Server and update client transmit field. If i implement the distributed transaction in this scenrio is there any alternate to MTS . I m using the distributed transaction through vb.net interface using transaction object and setting 'begin distributed transaction' and 'set xact_abort on' and 'SET NOCOUNT ON' and 'SET REMOTE_PROC_TRANSACTIONS ON' options.
Sample code
''Start (Coding in VB.NET/ SQL Server 2000 backend on windows XP)
Public Function TransmitFiles(ByVal filepath As String, ByVal oDtTransmit As DataTable, ByVal iStation As Int16, ByRef TransmitErrorDetails As String) As Int32
Dim myConnection As SqlConnection = New SqlConnection("Data Source=shabbir;Database=AGS_LOGISTICS;UID=sa;pwd=SA;")
myConnection.Open()
Dim myTrans As SqlTransaction = myConnection.BeginTransaction()
Dim myCommand As SqlCommand = myConnection.CreateCommand()
myCommand.Transaction = myTrans
set_OPtions_on_database(myConnection, myTrans)
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
myCommand.ExecuteNonQuery()
myCommand.CommandText = "INSERT INTO [192.168.1.181].AGS_TRANSMISSIONQUEUE.DBO.MANIFEST SELECT * FROM MANIFEST WHERE ORIGINSTATIONID=9007 AND TRANSMITTED=1"
myCommand.ExecuteNonQuery()
myTrans.Commit()
myTrans.Dispose()
Console.WriteLine("Both records are written to database.")
Catch e As Exception
myTrans.Rollback()
end try
end sub
''End
Different problems are coming at different time
1- The problem is that transaction object is running fine but its not inserting anything in the database.
2- the executenonquery is returning -1 even where it is returning the correct record affected count without the transaction object for this command.
3-sometimes giving SQL server not found or Access Denied from the front end.
4- Some times SQL log is showing
2004-08-14 07:55:35.46 server Attempting to initialize Distributed Transaction Coordinator.
2004-08-14 07:55:35.53 server Failed to obtain TransactionDispenserInterface: Result
Code = 0x8004d01b
2004-08-14 07:55:35.54 spid3 Starting up database 'master'.
2004-08-14 07:55:36.03 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-08-14 07:55:36.03 spid5 Starting up database 'model'.
2004-08-14 07:55:36.06 server SQL server listening on 192.168.1.190: 1433.
2004-08-14 07:55:36.06 server SQL server listening on 127.0.0.1: 1433.
2004-08-14 07:55:36.06 server SuperSocket Info: Bind failed on TCP port 1433.
2004-08-14 07:55:36.09 spid3 Server name is 'SHABBIR'.
?????Can some body explain what is wrong with my coding or wht setting to made in the system?
* I m using window XP professional and .NEt studio 2003 and MSSQL 8.0
one of my testing server is also the same confing and other testing server is windows2000 server.
Important : will i have to install MTS on each client or server only if i went for Distributed transactions through DCOM and MTS?
Will i face any difficulties regarding the com registration?
Can some body provide some short sample project?
or Can some body provide some Code sample for the transaction class and how to configure in the MTS?
Can some body provide Some helpfull links to clear my concepts and helpfull in implementation and code?
All your help would be highly appreciated.
Regards,
Shabbir
___
Newsgroups brought to you courtesy of www.dotnetjohn.com