Roll back to the previous state
-
Hello Friends, I am in trouble while loading data to SQL Server. i am using bulkcopy code to load datas. I dont have much knowledge with SQL If error occurs, the loading may not complete(ie i having four tables to be loaded. error may occur while loading the fourth table. for me its like corrupting the table So if error occurs i want to roll back to last state. The code i used is With these code can i use rollback property ?? Please do help Public Function rb_BCP(ByVal Server As String, ByVal Database As String, _ ByVal Table As String, ByVal FileName As String) Dim objServer As SQLDMO.SQLServer Dim objBCP As SQLDMO.BulkCopy Dim objDB As SQLDMO.Database On Error GoTo ErrorHandler Set objServer = New SQLDMO.SQLServer Set objBCP = New SQLDMO.BulkCopy objServer.LoginSecure = True objServer.EnableBcp = True objServer.QuotedIdentifier = True objServer.Connect (Server) Set objDB = objServer.Databases(Database) With objBCP .UseExistingConnection = True .DataFilePath = FileName .RowDelimiter = vbCrLf .IncludeIdentityValues = True .ServerBCPKeepNulls = True .DataFileType = SQLDMODataFile_SpecialDelimitedChar .ColumnDelimiter = "|" .IncludeIdentityValues = True .ImportRowsPerBatch = 1000000 'Below speeds things up 'but does not log the bulk copy operation 'comment out if this is not what you .UseBulkCopyOption = True End With objDB.Tables(Table).ImportData objBCP Set objBCP = Nothing objServer.Disconnect Set objServer = Nothing Exit Function ErrorHandler: MsgBox "Error " & Err.Number & vbCrLf & Err.Description End Function jishith
-
Hello Friends, I am in trouble while loading data to SQL Server. i am using bulkcopy code to load datas. I dont have much knowledge with SQL If error occurs, the loading may not complete(ie i having four tables to be loaded. error may occur while loading the fourth table. for me its like corrupting the table So if error occurs i want to roll back to last state. The code i used is With these code can i use rollback property ?? Please do help Public Function rb_BCP(ByVal Server As String, ByVal Database As String, _ ByVal Table As String, ByVal FileName As String) Dim objServer As SQLDMO.SQLServer Dim objBCP As SQLDMO.BulkCopy Dim objDB As SQLDMO.Database On Error GoTo ErrorHandler Set objServer = New SQLDMO.SQLServer Set objBCP = New SQLDMO.BulkCopy objServer.LoginSecure = True objServer.EnableBcp = True objServer.QuotedIdentifier = True objServer.Connect (Server) Set objDB = objServer.Databases(Database) With objBCP .UseExistingConnection = True .DataFilePath = FileName .RowDelimiter = vbCrLf .IncludeIdentityValues = True .ServerBCPKeepNulls = True .DataFileType = SQLDMODataFile_SpecialDelimitedChar .ColumnDelimiter = "|" .IncludeIdentityValues = True .ImportRowsPerBatch = 1000000 'Below speeds things up 'but does not log the bulk copy operation 'comment out if this is not what you .UseBulkCopyOption = True End With objDB.Tables(Table).ImportData objBCP Set objBCP = Nothing objServer.Disconnect Set objServer = Nothing Exit Function ErrorHandler: MsgBox "Error " & Err.Number & vbCrLf & Err.Description End Function jishith
This forum holds all about MySQL which is different from SQL Server, Access and many others handled in the Database forum. :)
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google - the quality and detail of your question reflects on the effectiveness of the help you are likely to get - use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets