Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SqlProgrammability provider fails to update/bulk copy into tables with computed persistent columns #272

Open
ntr opened this issue Jun 22, 2017 · 2 comments
Labels

Comments

@ntr
Copy link
Contributor

ntr commented Jun 22, 2017

Description

SqlProgrammability provider fails to update/bulk copy into tables with computed persistent columns - you get exception when you call update method:
System.Data.NoNullAllowedException: Column '{persistentColumn}' does not allow nulls.

Repro steps

Please provide the steps required to reproduce the problem

  1. Create table:
CREATE TABLE [dbo].[T1](
	[A] [int] NOT NULL,
	B  AS ((0)) PERSISTED NOT NULL
)
  1. Step B

type Database = SqlProgrammabilityProvider<"name=someConnectionString">
let t = new Database.dbo.Tables.T1()
t.AddRow(3)
t.Update() 

Expected behavior

New row is inserted into the table.

Actual behavior

System.Data.NoNullAllowedException: Column 'B' does not allow nulls.

Known workarounds

Following code can be used - works for update only:

    let t = new Database.dbo.Tables.T1()
    let r = t.NewRow(3)
    r.["B"] <- 0
    t.Rows.Add(r)
    t.Update()

Related information

.Net Standard 4.6.2
SqlServer 2016
SqlClient 1.8.1

@ntr ntr added the bug label Jun 22, 2017
@smoothdeveloper
Copy link
Collaborator

smoothdeveloper commented Dec 27, 2017

I've added a test case for this issue in a branch:

https://github.com/smoothdeveloper/FSharp.Data.SqlClient/tree/issue_bulkcopy_computed_columns

FSharp.Data.DataTablesTests.can bulk copy with computed column [FAIL]                                                                                                                                                                                                                   
   System.Data.SqlClient.SqlException : The column "year" cannot be modified because it is either a computed column or is the result of a UNION operator.                                                                                                                               
   Stack Trace:                                                                                                                                                                                                                                                                         
      at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)                                                                                                                                                       
      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)                                                                                                                                   
      at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)                                                                   
      at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)                                                                                          
      at System.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)                                                                                                                                                                                           
      at System.Data.SqlClient.SqlBulkCopy.SubmitUpdateBulkCommand(String TDSCommand)                                                                                                                                                                                                   
      at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)                                                                                                
      at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)                                                                                                       
      at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)                                                                                                                                                         
      at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)                                                                                                                                                                                         
      at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)                                                                                                                                                                     
      at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)                                                                                                                                                                                        
      C:\dev\projects\github.com\fsprojects\FSharp.Data.SqlClient\src\SqlClient\DataTable.fs(104,0): at FSharp.Data.DataTable`1.BulkCopy(FSharpOption`1 connection, FSharpOption`1 copyOptions, FSharpOption`1 transaction, FSharpOption`1 batchSize, FSharpOption`1 timeout)           

@samhanes
Copy link
Contributor

By my reading of dotnet/SqlClient#275, it looks like this is an issue for System.Data.SqlClient on netcore, but it has been fixed in Microsoft.Data.SqlClient. (I have not tested this.)

This issue might be resolved by migrating to Microsoft.Data.SqlClient as suggested in #374

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

3 participants