Roll backing the server
-
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 While loading, if error occurs (in xtreme cases) loading wont be perfect(ie I having four tables to be loaded. error may occur while loading the fourth table. for me its like corrupting the tables. because for tables are interconnected) So if error occurs i want to roll back other three tables to last state. The below code is i used for loading and these function is calling four times for updating four tables Can Rollback property be applied to the below code?? Please do help Or any other way to prevent the tables from getting corrupted ------------------------------------------ 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 While loading, if error occurs (in xtreme cases) loading wont be perfect(ie I having four tables to be loaded. error may occur while loading the fourth table. for me its like corrupting the tables. because for tables are interconnected) So if error occurs i want to roll back other three tables to last state. The below code is i used for loading and these function is calling four times for updating four tables Can Rollback property be applied to the below code?? Please do help Or any other way to prevent the tables from getting corrupted ------------------------------------------ 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
Check this article out ... http://msdn.microsoft.com/en-us/library/tchktcdk(VS.80).aspx[^]