I am using an ASP page to read records from a text file and make multiple entries into a SQL database. I wish this to be an all or nothing entry, if one fails then all the entries will rollback. This seems to work with under 10 records, but fails after that point with the following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Not sure why I am getting the error message when rolling back more than 10 records? It works fine for small files.
Thanks,
Greg
[Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Code:
<%@ TRANSACTION = Required %>
<%'server.ScriptTimeout = 900
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html;">
<META content="MSHTML 6.00.2800.1126" name=GENERATOR>
</HEAD>
<BODY>
<%On error resume next
Dim objFSO,f,strFileName,strTextFile,strRecord,strSQL,rsconn
strFileName = trim(Request.Form("txtFile"))
strTextFile = "d:\web\applications\FixedAssets\pcw\text\" & strFileName
strNewLoc = "d:\web\applications\FixedAssets\pcw\text\processed\"
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
Set f = objFSO.OpenTextFile(strTextFile, 1)
if err = 0 then
Response.Write ("<font color=navy>Writing File to Database...</font><BR>")
if not f.AtEndOfStream then
'loop through each line of the text file and write it to the db
intCount = 0
do while not f.AtEndOfStream
strRecord = f.ReadLine
strRecord = mid(strRecord,8)
strTransfer_From = left(strRecord,instr(1,strRecord,"|")-1)
strRecord = mid(strRecord,instr(1,strRecord,"|")+1)
strTransfer_To = left(strRecord,instr(1,strRecord,"|")-1)
strRecord = mid(strRecord,instr(1,strRecord,"|")+1)
strTransfer_Date = strRecord 'left(strRecord,instr(1,strRecord,"|")-1)
strSQL = "execute VCSFA.dbo.transfer_assets_add '" & strAstId & "'"
conn.execute(strSQL)
'******************** check for errors to rollback transactions
If err <> 0 Then
strError = "Error Saving Line Detail. " & err.description
ObjectContext.SetAbort
conn.close
set conn=nothing
f.Close
Set f=Nothing
Set objFSO=Nothing
response.write ("<font color=maroon>There was an error writing to the Database.</font> <br>Records attempted: " & intCount -1 & "<br> Error Message: " & err.Description & "<br> Failed on: " & strRecord)
Response.Flush
response.End
End if
'**************************************************
end if
loop
end if
Response.flush
'set rsconn=nothing
else
f.Close
Set f=Nothing
Set objFSO=Nothing
response.Write ("There was an error reading from text file.<BR>")
response.Write ("Please make sure that the text file '" & strFileName & "' exists and try again.")
Response.Flush
response.end
end if
f.Close
Set f=Nothing
Response.Write "File written to database successfully. <BR>"
Response.Flush
'now move the text file to the processed directory
Set f=objFSO.GetFile(strTextFile)
f.Move(strNewLoc)
if err = 0 then
Response.Write "Moved '" & strFileName & "' to Processed Directory. <BR>"
Response.Flush
else
Response.Write ("There was an error moving file.<BR>")
Response.write err.Description
Response.Flush
Response.end
end if
set f=nothing
Set objFSO=Nothing
'**********************************************************
'commit and rollback code
sub OnTransactionCommit()
response.write ("Records Committed")
End Sub
sub OnTransactionAbort()
response.write ("Rollback Records")
End Sub
'**********************************************************Thanks,
Greg
![[banghead]](/data/assets/smilies/banghead.gif "[banghead] [banghead]")