shabbir
8/21/2004 9:55:00 AM
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"
******'' TESTING Main transmit proc for testing. this testing proc is also not working for distributed transaction giving SQL server not found or Access Denied. though it worked fine with single SQL Server.
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
'' testing 170804
Dim myConnection As SqlConnection = New SqlConnection("Data Source=ag-vts;Database=AGS_LOGISTICS;UID=sa;pwd=SA;")
myConnection.Open()
' Start a local transaction.
Dim myTrans As SqlTransaction = myConnection.BeginTransaction()
' Enlist the command in the current transaction.
Dim myCommand As SqlCommand = myConnection.CreateCommand()
myCommand.Transaction = myTrans
set_xact_abort_on_database(myConnection, myTrans)
Try
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
myCommand.ExecuteNonQuery()
'myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"
myCommand.CommandText = "INSERT INTO aamir.AGS_TRANSMISSIONQUEUE.DBO.MANIFEST SELECT * FROM MANIFEST WHERE ORIGINSTATIONID=9007 AND TRANSMITTED=1"
myCommand.ExecuteNonQuery()
myTrans.Commit()
Console.WriteLine("Both records are written to database.")
Catch e As Exception
Try
myTrans.Rollback()
Catch ex As SqlException
If Not myTrans.Connection Is Nothing Then
Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
" was encountered while attempting to roll back the transaction.")
End If
End Try
End Sub
___
Newsgroups brought to you courtesy of www.dotnetjohn.com