PLEASE HELP FOR MICROSOFT DISTRIBUTED TRANSACTION NOT WORKING IN VB.NET
From: DotNetJunkies User (User_at_-NOSPAM-DotNetJunkies.com)
Date: 08/18/04
- Next message: DotNetJunkies User: "PLEASE HELP FOR MICROSOFT DISTRIBUTED TRANSACTION NOT WORKING IN VB.NET"
- Previous message: matthew c. harad: "design/architecture questions"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 18 Aug 2004 08:02:54 -0700
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. I dont wnt to use MTS as i fear doing the settings at each client site which could be hundreds. I m using the distributed transaction through vb.net interface.
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.
'' Database Settings
Private Sub set_xact_abort_on_database(ByRef ocon As System.Data.SqlClient.SqlConnection, ByRef trans As System.data.SqlClient.SqlTransaction)
oCom = New SqlCommand("set xact_abort on", ocon)
oCom.CommandTimeout = 600
oCom.Transaction = trans
oCom.ExecuteNonQuery()
oCom = New SqlCommand("Begin Distributed Transaction", ocon)
oCom.CommandTimeout = 600
oCom.Transaction = trans
oCom.ExecuteNonQuery()
oCom = New SqlCommand("SET NOCOUNT ON", ocon)
oCom.CommandTimeout = 600
oCom.Transaction = trans
oCom.ExecuteNonQuery()
oCom.Dispose()
End Sub
''
'' This is the Ultimate code and class
Imports System
Imports System.Data.SqlClient
Imports System.Configuration
Public Class clsTransmitData
Private oCon As SqlConnection
Private oCom As SqlCommand
Private sTransmitIP, UID, PWD As String
Private sSqlQry As String
Private cls As New ClassLibrary.DBClass
Dim clsIntegrateData As New clsIntegrateData ' added by shabbir 040804
'Added by shabbir 120804
Private Sub set_xact_abort_on_database(ByRef ocon As System.Data.SqlClient.SqlConnection, ByRef trans As System.data.SqlClient.SqlTransaction)
oCom = New SqlCommand("set xact_abort on", ocon)
oCom.CommandTimeout = 600
oCom.Transaction = trans
oCom.ExecuteNonQuery()
oCom = New SqlCommand("Begin Distributed Transaction", ocon)
oCom.CommandTimeout = 600
oCom.Transaction = trans
oCom.ExecuteNonQuery()
oCom = New SqlCommand("SET NOCOUNT ON", ocon)
oCom.CommandTimeout = 600
oCom.Transaction = trans
oCom.ExecuteNonQuery()
oCom.Dispose()
End Sub
'End-Added by shabbir 120804
#Region "Transmitting Files"
Public Function TransmitFiles(ByVal filepath As String, ByVal oDtTransmit As DataTable, ByVal iStation As Int16, ByRef TransmitErrorDetails As String) As Int32 'modified by shabbir 110804
Dim sw As System.IO.StreamWriter
Dim logfile As System.IO.File
Dim reccount As Int32 = 0
Dim i, TotalRec As Int32
Dim bRecInsert As Boolean = False
Dim arrText As New ArrayList
Dim tablearray As New ArrayList
Dim iCounter As Int32 = 0
Dim TableName As String
Dim loopCounter As Boolean = False
TotalRec = 0
If logfile.Exists(filepath) = True Then
logfile.Delete(filepath)
End If
Try
sw = logfile.CreateText(filepath)
oCon = New SqlConnection
oCon = cls.OpenConnection()
Dim trans As System.Data.SqlClient.SqlTransaction
'Added by shabbir 120804
trans = oCon.BeginTransaction()
'oCon.EnlistDistributedTransaction(trans)
set_xact_abort_on_database(oCon, trans)
'End-Added by shabbir 120804
sTransmitIP = "[" & sTransmitIP & "]"
sw.WriteLine("Transmission start to host at : " & Now())
While loopCounter = False
Try
If i < oDtTransmit.Rows.Count Then
For i = iCounter To oDtTransmit.Rows.Count - 1
If Convert.ToInt32(oDtTransmit.Rows(i)(i)) > 0 Then
TableName = oDtTransmit.Columns(i).ColumnName
GetQuery(TableName, iStation)
oCom = New SqlCommand(sSqlQry, oCon)
oCom.CommandTimeout = 600
oCom.Transaction = trans
reccount = oCom.ExecuteNonQuery()
oCom.Dispose()
TotalRec = TotalRec + reccount
If reccount > 0 Then
tablearray.Add(TableName) 'added by shabbir 150804
'UpdateData(TableName, trans, TransmitErrorDetails) 'commented by shab 150804
'sw.WriteLine("Transmitted " & Convert.ToString(reccount) & " " & oDtTransmit.Columns(i).ColumnName & " to host")
arrText.Add("Transmitted " & Convert.ToString(reccount) & " " & TableName & " to host")
reccount = 0
End If
bRecInsert = True
End If
Next
End If
'Added by shabbir 150804
For i = 0 To tablearray.Count - 1
UpdateData(tablearray(i), trans, TransmitErrorDetails)
Next
'End- Added by shabbir 150804
'trans.Commit() 'Added by shabbir 150804
'cls.CloseConnection(oCon)
'oCon = cls.OpenConnection()
'trans = oCon.BeginTransaction(IsolationLevel.ReadCommitted) 'Added by shabbir 150804
If bRecInsert = True Then
bRecInsert = False
sSqlQry = sTransmitIP & ".AGS_HOST.DBO.AGS_TransmitToHost"
oCom = New SqlCommand(sSqlQry, oCon)
oCom.CommandType = CommandType.StoredProcedure
oCom.CommandTimeout = 600
oCom.Transaction = trans 'Added by shabbir 110804
oCom.Parameters.Add("@OriginStid", SqlDbType.SmallInt).Value = iStation
oCom.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
Dim j As Int32 = Convert.ToInt32(oCom.Parameters("@Return").Value)
j = oCom.ExecuteNonQuery()
End If
trans.Commit() 'Added by shabbir 110804
trans.Dispose()
cls.CloseConnection(oCon)
cls = Nothing
If arrText.Count > 0 Then
For i = 0 To arrText.Count - 1
sw.WriteLine(Convert.ToString(arrText(i)))
Next
End If
sw.WriteLine("Transmission end to host at : " & Now())
sw.Close()
sw = Nothing
loopCounter = True
TransmitFiles = TotalRec
'clsIntegrateData.IntegrateStationData(oDtTransmit, TransmitErrorDetails) 'Added by shabbir 110804
End Try
End While
Catch ex As Exception
'Added by shabbir 110804
TransmitErrorDetails = TransmitErrorDetails & Chr(13) & "CLS-Transmit-Data Transmission error Source: " & ex.Source & Chr(13) & "CLS-Transmit-Data Transmission error Message: " & ex.Message 'Added by shabbir 110804
sw.WriteLine("Transmission end to host at : " & Now())
sw.Close()
sw = Nothing
'End-Added by shabbir 110804
End Try
End Function
#End Region
#Region "GetQuery"
Private Sub GetQuery(ByVal ColumnName As String, ByVal iOriginStationID As Int16)
Select Case ColumnName
Case "ADC"
sSqlQry = "INSERT INTO " & sTransmitIP & ".AGS_TRANSMISSIONQUEUE.DBO.ADC SELECT * FROM ADC WHERE MANIFESTSTATIONID=" & iOriginStationID & " AND TRANSMITTED=1"
Case "ADCSummary"
sSqlQry = "INSERT INTO " & sTransmitIP & ".AGS_TRANSMISSIONQUEUE.DBO.ADCSUMMARY SELECT * FROM ADCSUMMARY WHERE MASTERMANIFESTSTATIONID=" & iOriginStationID & " AND TRANSMITTED=1"
Case "CheckIn"
sSqlQry = "INSERT INTO " & sTransmitIP & ".AGS_TRANSMISSIONQUEUE.DBO.CHECKIN SELECT * FROM CHECKIN WHERE ORIGINSTATIONID=" & iOriginStationID & " AND TRANSMITTED=1"
End Sub
#End Region
#Region "AttachToHost"
#Region "Getting HostInfo"
#Region "Retransmit Data"
--- Posted using Wimdows.net NntpNews Component - Post Made from http://www.DotNetJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
- Next message: DotNetJunkies User: "PLEASE HELP FOR MICROSOFT DISTRIBUTED TRANSACTION NOT WORKING IN VB.NET"
- Previous message: matthew c. harad: "design/architecture questions"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
