Problems with mssql IDENTITY_INSERT

[kiwih]

With the latest version of gorm, I get the following error appearing when using mssql
Cannot insert explicit value for identity column in table 'collections' when IDENTITY_INSERT is set to OFF.

Is there any chance you could install the free MsSQL from https://www.microsoft.com/en-us/server-cloud/products/sql-server-editions/sql-server-express.aspx and try it out with your test suite?

I suspect the issue is related to #647 and #652

I will try create a small test case for you that fails during use.

[kiwih]

The issue is remarked upon in https://msdn.microsoft.com/en-nz/library/ms188059.aspx
Basically, if an ID is going to be provided by GORM, the transaction must request that identity insert be set to on or else it will be rejected

[jinzhu]

Hello @kiwih

I think you could just Execute SET IDENTITY_INSERT ON after setup database, then it would be ok? If yes, I would update the mssql dialect in the v1.0 release to make it works.

(sorry, I am not on a windows, so can't test it out, it would be really helpful if you could help on that)

[kiwih]

The problem is that you need to set it per table, not database wide, and only one table can have it at a time... Really, it needs to be set per transaction, if the transaction needs it. Does gorm know whether or not it is creating something with an ID before the transaction starts?

Sent from Outlook Mobilehttps://aka.ms/blhgte

[jinzhu]

Sound like this have to be done with overwriting callback. https://github.com/jinzhu/gorm/blob/master/callback_create.go#L117

[jinzhu]

Hi @kiwih

I have pushed some fixes to the v1.0_dev branch to fix this issue, but as I don't have a installed mssql server, could you help test it out?

Let me know if still has any issues.

Thank you.

[kiwih]

I will test it ASAP. Thanks jinzhu!

[jinzhu]

Cool, thank you, just fyi, you need to import the mssql driver with:

import _ "github.com/jinzhu/gorm/dialects/mssql"

[kiwih]

Hi Jinzhu, I am now setting up test environment for gorm (I have been using older commit up to this point). I will run test suite for current version against mssql and post results

[kiwih]

Refer to #908 in future