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
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
2 Answers
Reset to default 2You 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.
Not the solution I was looking for, iterating through thousands of rows, but it looks like bulk update doesn't work where computed values are involved. This checks to see if there are any computed ones and then inserts them one at time. If anyone has a better solution I would love to hear.
Basically, dynamically pulling each Table schema from SQL Anywhere changing it to SQL Server, adding the table to SQL Server then inserting the data
Private Async Sub DataTransfer_Run(sender As Object, e As RoutedEventArgs)
Try
Dim DTTB As TBx = DataTransfer_Grid.FindName("DTTB")
Dim vTable As String = DTTB.Text
vImage = New LoadingImage
LoadingStarted("Creating tables... Please wait...", vImage)
Dim SB As New System.Text.StringBuilder
SB.Append("IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" & vTable & "') ")
SB.AppendLine()
SB.Append("CREATE TABLE " & vTable & "(")
SB.AppendLine()
Dim vRowsTotal As Integer = MSSQLDT.Rows.Count
Dim vRow As Integer = 0
Await Task.Run(Sub()
For Each Row As DataRow In MSSQLDT.Rows
Dim vName As String = Row("Name")
Dim vType As String = Row("Type")
Dim vNulls As String = Row("Nulls")
Dim vDefault As String = Row("Default")
Dim vComputed As String = Row("Computed")
Dim vPrimary As String = Row("Primary")
If vComputed = "N" Then
SB.Append(vName & " " & vType)
If vPrimary = "Y" Then
SB.Append(" NOT NULL IDENTITY(1,1) PRIMARY KEY")
ElseIf vComputed = "Y" Then
SB.Append(vName & " AS " & vDefault)
ElseIf vNulls = "N" Then
SB.Append(" NOT NULL")
End If
Select Case vDefault
Case "autoincrement"
'Do nothing
Case ""
'Do nothing
Case Else
SB.Append(" DEFAULT " & vDefault)
End Select
Else
SB.Append(vName & " AS " & vDefault)
End If
vRow += 1
If Not vRow = vRowsTotal Then
SB.Append(", ")
SB.AppendLine()
End If
Next
End Sub)
SB.Append(");")
DataTransfer_StatusRight.Content = "Creating tables for " & vTable
Dim IsError As Boolean = False
Await Task.Run(Sub()
strSQL = SB.ToString
If InsertData_DataMS(strSQL, DBID, 432, False) = False Then
IsError = True
End If
End Sub)
If IsError = True Then
LoadingCompleted("Error", "There was an error saving the table", vImage)
Else
'Add the data
DataTransfer_StatusRight.Content = "Table was saved... Switching on IDENTITY_INSERT..."
Dim vTotalRows As Integer = 0
Await Task.Run(Sub()
strSQL = "SET IDENTITY_INSERT " & vTable & " ON"
If InsertData_DataMS(strSQL, DBID, 450, False) = False Then
IsError = True
End If
If IsError = False Then
strSQL = "SELECT COUNT(*) FROM " & vTable
vTotalRows = ReturnScalarInteger_Data(strSQL, DBID, False)
End If
End Sub)
If IsError = False Then
DataTransfer_StatusRight.Content = "IDENTITY_INSERT set to ON...Updating data of " & vTotalRows & " rows"
Await Task.Run(Sub()
'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
Dim DT As DataTable = Nothing
If vCurrentRow > 0 Then
'Update a row at a time
Dim vInsert As String = "SET IDENTITY_INSERT " & vTable & " ON INSERT INTO " & vTable & "("
Using DS As DataSet = ReturnDataSet_Data(strSQL, DBID, False)
DT = DS.Tables(0).Copy
End Using
Dim vColumns As Integer = DT.Columns.Count
Dim vCurrentCol As Integer = 0
For Each Col As DataColumn In DT.Columns
Dim vColName As String = Col.ColumnName
vInsert += vColName
vCurrentCol += 1
If vColumns = vCurrentCol Then
vInsert += ") VALUES ('"
Else
vInsert += ", "
End If
Next
Dim CurrentSubRow As Integer = 0
Dim vTotalSubRows As Integer = DT.Rows.Count
Dim vInsertValue As String = ""
Dim vValue As String = ""
For Each Row As DataRow In DT.Rows
Dim vIndex = Row.Table.Rows.IndexOf(Row)
For i As Integer = 0 To vColumns - 1
vInsertValue += Row(i).ToString
If i = vColumns - 1 Then
vInsertValue += "') "
Else
vInsertValue += "', '"
End If
Next
If InsertData_DataMS(vInsert & vInsertValue, DBID, 518, False) = False Then
IsError = True
Exit Sub
End If
vInsertValue = ""
Next
Else
'Bulk update
strSQL = "SELECT * FROM " & vTable
Using DS As DataSet = ReturnDataSet_Data(strSQL, DBID, False)
DT = DS.Tables(0).Copy
End Using
If BulkUpdate_DataMS(DT, DBID, False, vTable) = False Then
IsError = True
End If
End If
End Sub)
Await Task.Run(Sub()
strSQL = "SET IDENTITY_INSERT " & vTable & " OFF"
If InsertData_DataMS(strSQL, DBID, 495, False) = False Then
IsError = True
End If
End Sub)
End If
If IsError = True Then
LoadingCompleted("Error", "There was an error uploading the data", vImage)
AppBoxError("There was an error uploading the data")
Else
LoadingCompleted("Ready...", vTable & " was successfully saved", vImage)
AppBoxSuccess(vTable & " was successfully saved")
End If
End If
Catch ex As Exception
EmailError(ex, 60, PageName)
LoadingCompleted("Error", "There was an internal error", vImage)
End Try
End Sub
SELECTwould not generate that error. That error comes from trying toINSERTorUPDATEinto a table and providing a value for a computed column: db<>fiddle Edit Your question to demonstrate theINSERT/UPDATEyou are using. – Thom A Commented Feb 7 at 15:38SELECTstatement that's the problem it's theINSERTstatement. You aren't populating theColumnMappingson the bulk copy so it's going to be doing ordinal column inserts - and will fail unless all of your computed columns are last in the destination table's column definitions. It's the difference betweenINSERT Table VALUES (1, 3), (10, 30);(ordinal) andINSERT Table (Col1, Col3) VALUES (1, 3), (10, 30);(named). – AlwaysLearning Commented Feb 8 at 0:01