Bulk insert fails when computed field in table

[markweissman]

I'm trying to bulk insert into a table that had a computed column, particularly:

create TABLE [dbo].[tstBCPComputedColumn] (
Id int not null identity(1,1) UNIQUE,
Code varchar(4096) not null,
Format varchar(10) not null default 'xml',
Hash
AS CAST(hashbytes('SHA1',[Code]) AS VARBINARY(20))
PERSISTED NOT NULL,
DateInserted DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CreatedBy varchar(30) NOT NULL DEFAULT CURRENT_USER,
CONSTRAINT pk_enumTestBCPComputedColumn PRIMARY KEY(Id))

I'm using

conn.bulk_insert(table, batch, batch_size=batch_size)

from ctds and providing all columns. If I remove the Hash column from the table, it works well.
With that column,

_tds.ProgrammingError: ('unrecognized msgno', 'query', 'BCP [BOS-DBRESEARCH01].mweissman.dbo.tstBCPComputedColumn', 'params', 'None', 'connection', 'DRIVER={FreeTDS};TDS_Version=8.0;ClientCharSet=ISO8859-1;SERVER=BOS-DBRESEARCH01;PORT=1433;VAULT_NAME=db-research01;DATABASE=master', 'type', 'ProgrammingError')

I've tried passing in:

• 0
• b''
• None
• leaving out the Hash column from batch
• passing dictionaries without Hash

but none of those work. Is this supported? Can you provide some advice on how to insert into this table?

This works with pymssql btw but I need kerberos support which only ctds has.

Thanks, Mark Weissman

[HuangRicky]

Hi Mark, I think you should ask the question on original ctds repo. I only compiled it under windows platform and post the binary. Second, how did you construct the dict passing to bcp function? usually you need the function ctds.Varchar, etc. I haven't used hash yet but you can scan through their source code to see how to handle. Thanks R

…

On Wed, Sep 14, 2022, 7:54 AM markweissman ***@***.***> wrote: I'm trying to bulk insert into a table that had a computed column, particularly: create TABLE [dbo].[tstBCPComputedColumn] ( Id int not null identity(1,1) UNIQUE, Code varchar(4096) not null, Format varchar(10) not null default 'xml', Hash AS CAST(hashbytes('SHA1',[Code]) AS VARBINARY(20)) PERSISTED NOT NULL, DateInserted DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CreatedBy varchar(30) NOT NULL DEFAULT CURRENT_USER, CONSTRAINT pk_enumTestBCPComputedColumn PRIMARY KEY(Id)) I'm using conn.bulk_insert(table, batch, batch_size=batch_size) from ctds and providing all columns. If I remove the Hash column from the table, it works well. With that column, _tds.ProgrammingError: ('unrecognized msgno', 'query', 'BCP [BOS-DBRESEARCH01].mweissman.dbo.tstBCPComputedColumn', 'params', 'None', 'connection', 'DRIVER={FreeTDS};TDS_Version=8.0;ClientCharSet=ISO8859-1;SERVER=BOS-DBRESEARCH01;PORT=1433;VAULT_NAME=db-research01;DATABASE=master', 'type', 'ProgrammingError') I've tried passing in: - 0 - b'' - None - leaving out the Hash column from batch - passing dictionaries without Hash but none of those work. Is this supported? Can you provide some advice on how to insert into this table? This works with pymssql btw but I need kerberos support which only ctds has. Thanks, Mark Weissman — Reply to this email directly, view it on GitHub <#2>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AEWEAWM4I2NVJ4M26LXSW2TV6G4HNANCNFSM6AAAAAAQMK2TGE> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>