Moving data from SQL Anywhere to SQL Server and after a few hiccups it was going well until I got the error
The column "A_Future" cannot be modified because it is either a computed column or is the result of a UNION'
Fair enough I was inserting data from SELECT * -- so used this to return only columns that were not calculated.
'Remove computed columns from the SELECT query
Dim DR() As DataRow = MSSQLDT.Select("Computed = 'N'")
Dim vSelectedRows As Integer = DR.Count
Dim vCurrentRow As Integer = 0
strSQL = "SELECT "
For Each Row In DR
strSQL += Row("Name")
vCurrentRow += 1
If vCurrentRow = vSelectedRows Then
strSQL += " "
Else
strSQL += ", "
End If
Next
strSQL += "FROM " & vTable
This returns the following query string...
SELECT Transaction_ID, Debit, Credit, Paid, P_Description, Document_Date, Supplier_ID, Nominal_Transaction, HOA_Code, Document_Saved, Document_ID, Document_No, Supplier_Inv_No, Type, Paid_Date, Part_Paid, Open_Editing, Editing_Name, Updated_Name, Updated, Reserve_Item, Hold, eCheck_Pending, eSig_Required, eSigOne_ID, eSigTwo_ID FROM A_Purchase_Ledger
.. and it doesn't include any calculated columns. Ran it again, but it still threw the same error (for all calculated columns)
Microsoft.Data.SqlClient.SqlException (0x80131904): The column "A_Future" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_Current" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_30" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_60" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_90" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_Older" cannot be modified because it is either a computed column or is the result of a UNION operator.
This now makes no sense since I am not trying to insert data into them, unless there is a quirk with SqlBulkCopy I am not aware of?
The SELECT Data is added to a DataTable then it goes to this function
Public Function BulkUpdate_DataMS(DT As DataTable, HOAID As Integer, IsHASoftware As Boolean, TableName As String) As Boolean
Try
Using Conn As New Microsoft.Data.SqlClient.SqlConnection
If IsHASoftware = True Then
Conn.ConnectionString = HASConString
Else
Conn.ConnectionString = ReturnConnStringMS(HOAID)
End If
Conn.Open()
Using vTrans = Conn.BeginTransaction
Using vBulk As New Microsoft.Data.SqlClient.SqlBulkCopy(Conn, Microsoft.Data.SqlClient.SqlBulkCopyOptions.Default, vTrans)
vBulk.DestinationTableName = TableName
vBulk.WriteToServer(DT)
End Using
vTrans.Commit()
End Using
Conn.Close()
End Using
Return True
Catch ex As Exception
EmailError(ex, 222, PageName)
Return False
End Try
End Function
You need to set the ColumnMappings otherwise it's just going to do an ordinal match.
Also there is no need for a transaction, as the bulk insert will use its own transaction. And if you have a Using you don't need to close the connection manually.
Public Function BulkUpdate_DataMS(DT As DataTable, HOAID As Integer, IsHASoftware As Boolean, TableName As String) As Boolean
Try
Using Conn As New SqlConnection(If(IsHASoftware, HASConString, ReturnConnStringMS(HOAID)))
Using vBulk As New SqlBulkCopy(Conn, SqlBulkCopyOptions.Default)
vBulk.DestinationTableName = TableName
For Each col As DataColumn in DT.Columns
vBulk.ColumnMappings.Add(col.Name, col.Name)
Next
Conn.Open()
vBulk.WriteToServer(DT)
End Using
End Using
Return True
Catch ex As Exception
EmailError(ex, 222, PageName)
Return False
End Try
End Function
You should probably also consider using Async and Await for all this.
The correct way to do async wouldn't be to use Task.Run, instead use Await directly on the bulk insert within a function marked as Async
Public Async Function BulkUpdate_DataMS(DT As DataTable, HOAID As Integer, IsHASoftware As Boolean, TableName As String) As Task(Of Boolean)
Try
Using Conn As New SqlConnection(If(IsHASoftware, HASConString, ReturnConnStringMS(HOAID)))
Using vBulk As New SqlBulkCopy(Conn, SqlBulkCopyOptions.Default)
vBulk.DestinationTableName = TableName
For Each col As DataColumn in DT.Columns
vBulk.ColumnMappings.Add(col.Name, col.Name)
Next
Await Conn.OpenAsync() ' NOTE the async
Await vBulk.WriteToServerAsync(DT)
End Using
End Using
Return True
Catch ex As Exception
EmailError(ex, 222, PageName)
Return False
End Try
End Function
Also if you are reading the data from another server, you don't need to save it in a DataTable at all. You can pass its DbDataReader that you get from ExecuteReader/ExecuteReaderAsync directly to WriteToServer/WriteToServerAsync.