{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/10185","repository_url":"https://api.github.com/repos/sequelize/sequelize","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/10185/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/10185/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/10185/events","html_url":"https://github.com/sequelize/sequelize/issues/10185","id":383023281,"node_id":"MDU6SXNzdWUzODMwMjMyODE=","number":10185,"title":"Seeding values to identity column on MSSQL","user":{"login":"mubaidr","id":2222702,"node_id":"MDQ6VXNlcjIyMjI3MDI=","avatar_url":"https://avatars2.githubusercontent.com/u/2222702?v=4","gravatar_id":"","url":"https://api.github.com/users/mubaidr","html_url":"https://github.com/mubaidr","followers_url":"https://api.github.com/users/mubaidr/followers","following_url":"https://api.github.com/users/mubaidr/following{/other_user}","gists_url":"https://api.github.com/users/mubaidr/gists{/gist_id}","starred_url":"https://api.github.com/users/mubaidr/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mubaidr/subscriptions","organizations_url":"https://api.github.com/users/mubaidr/orgs","repos_url":"https://api.github.com/users/mubaidr/repos","events_url":"https://api.github.com/users/mubaidr/events{/privacy}","received_events_url":"https://api.github.com/users/mubaidr/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":9,"created_at":"2018-11-21T09:18:12Z","updated_at":"2018-11-21T09:57:21Z","closed_at":null,"author_association":"NONE","body":"## What are you doing?\r\nTrying to seed data using seeder (including identity values)\r\n\r\n```js\r\nconst data = [\r\n  {\r\n    ID: 1,\r\n    DESCRIPTION: 'Male',\r\n  },\r\n  {\r\n    ID: 2,\r\n    DESCRIPTION: 'Female',\r\n  },\r\n  {\r\n    ID: 3,\r\n    DESCRIPTION: 'Other',\r\n  },\r\n].map(d => {\r\n  d.CREATED_AT = new Date()\r\n  d.UPDATED_AT = new Date()\r\n  return d\r\n})\r\n\r\nconst TBL_NAME = 'CAT_GENDER'\r\n// TODO: fix forighn key check and identity insert\r\nmodule.exports = {\r\n  up: async queryInterface => {\r\n    await queryInterface.sequelize.query(`SET IDENTITY_INSERT ${TBL_NAME } ON`)\r\n    await queryInterface.bulkInsert(TBL_NAME, data, {})\r\n    await queryInterface.sequelize.query(`SET IDENTITY_INSERT ${TBL_NAME } OFF`)\r\n  },\r\n  down: queryInterface => queryInterface.bulkDelete(TBL_NAME, null, {}),\r\n}\r\n```\r\n\r\n## What do you expect to happen?\r\nData should be inserted successfully. \r\n\r\n## What is actually happening?\r\nMSSQL DB error\r\n\r\n_Output, either JSON or SQL_\r\n```bash\r\nRequestError: Cannot insert explicit value for identity column in table 'CAT_GENDER' when IDENTITY_INSERT is set to OFF.\r\n```\r\n\r\n\r\n__Dialect:__ mssql\r\n__Dialect version:__ \"tedious\": \"^3.0.1\"\r\n__Database version:__ MSSQL 2012\r\n__Sequelize version:__ 4.41.2\r\n__Tested with latest release:__ yes, 4.41.2\r\n\r\n\r\n**Note :** _Your issue may be ignored OR closed by maintainers if it's not tested against latest version OR does not follow issue template._\r\n"},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/440604216","html_url":"https://github.com/sequelize/sequelize/issues/10185#issuecomment-440604216","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/10185","id":440604216,"node_id":"MDEyOklzc3VlQ29tbWVudDQ0MDYwNDIxNg==","user":{"login":"mubaidr","id":2222702,"node_id":"MDQ6VXNlcjIyMjI3MDI=","avatar_url":"https://avatars2.githubusercontent.com/u/2222702?v=4","gravatar_id":"","url":"https://api.github.com/users/mubaidr","html_url":"https://github.com/mubaidr","followers_url":"https://api.github.com/users/mubaidr/followers","following_url":"https://api.github.com/users/mubaidr/following{/other_user}","gists_url":"https://api.github.com/users/mubaidr/gists{/gist_id}","starred_url":"https://api.github.com/users/mubaidr/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mubaidr/subscriptions","organizations_url":"https://api.github.com/users/mubaidr/orgs","repos_url":"https://api.github.com/users/mubaidr/repos","events_url":"https://api.github.com/users/mubaidr/events{/privacy}","received_events_url":"https://api.github.com/users/mubaidr/received_events","type":"User","site_admin":false},"created_at":"2018-11-21T09:57:21Z","updated_at":"2018-11-21T09:57:21Z","author_association":"NONE","body":"Logging only: \r\n```bash\r\n== CAT_GENDER: migrating =======\r\nExecuting (b69ec226aaaa16de7407): SET IDENTITY_INSERT CAT_GENDER ON\r\nExecuting (b69ec226aaaa16de7407): INSERT INTO [CAT_GENDER] ([ID],[DESCRIPTION],[CREATED_AT],[UPDATED_AT]) VALUES (1,N'Male','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00'),(2,N'Female','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00'),(3,N'Other','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00');\r\n```\r\nComplete with error: \r\n```bash\r\nLoaded configuration file \"config\\config.js\".\r\nUsing environment \"development\".\r\nsequelize deprecated String based operators are now deprecated. Please use Symbol based operators for better security, read more at http://docs.sequelizejs.com/manual/tutorial/querying.html#operators node_modules\\sequelize\\lib\\sequelize.js:242:13\r\ntedious deprecated The default value for `options.encrypt` will change from `false` to `true`. Please pass `false` explicitly if you want to retain current behaviour. node_modules\\sequelize\\lib\\dialects\\mssql\\connection-manager.js:70:26\r\n== CAT_GENDER: migrating =======\r\nExecuting (b69ec226aaaa16de7407): SET IDENTITY_INSERT CAT_GENDER ON\r\nExecuting (b69ec226aaaa16de7407): INSERT INTO [CAT_GENDER] ([ID],[DESCRIPTION],[CREATED_AT],[UPDATED_AT]) VALUES (1,N'Male','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00'),(2,N'Female','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00'),(3,N'Other','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00');\r\n{ SequelizeDatabaseError: Cannot insert explicit value for identity column in table 'CAT_GENDER' when IDENTITY_INSERT is set to OFF.\r\n    at Query.formatError (D:\\Current\\ors-node\\api\\node_modules\\sequelize\\lib\\dialects\\mssql\\query.js:315:12)\r\n    at Request.connection.lib.Request [as userCallback] (D:\\Current\\ors-node\\api\\node_modules\\sequelize\\lib\\dialects\\mssql\\query.js:107:25)\r\n    at Request.callback (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\request.js:37:27)\r\n    at Connection.endOfMessageMarkerReceived (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\connection.js:1880:20)\r\n    at Connection.dispatchEvent (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\connection.js:997:36)\r\n    at Parser.tokenStreamParser.on (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\connection.js:827:14)\r\n    at Parser.emit (events.js:182:13)\r\n    at Parser.parser.on.token (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\token-stream-parser.js:27:14)\r\n    at Parser.emit (events.js:182:13)\r\n    at addChunk (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_readable.js:301:12)\r\n    at readableAddChunk (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_readable.js:283:11)\r\n    at Parser.Readable.push (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_readable.js:247:10)\r\n    at Parser.Transform.push (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_transform.js:149:32)\r\n    at Parser.afterTransform (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_transform.js:92:10)\r\n    at Parser._transform (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:40:7)\r\n    at Parser.Transform._read (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_transform.js:185:10)\r\n    at Parser.Transform._write (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_transform.js:173:83)\r\n    at doWrite (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_writable.js:429:139)\r\n    at writeOrBuffer (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_writable.js:418:5)\r\n    at Parser.Writable.write (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_writable.js:327:11)\r\n    at Parser.addEndOfMessageMarker (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\token-stream-parser.js:45:24)\r\n    at Connection.message (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\connection.js:1869:32)\r\n    at Connection.dispatchEvent (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\connection.js:997:36)\r\n    at MessageIO.messageIo.on (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\connection.js:897:14)\r\n    at MessageIO.emit (events.js:182:13)\r\n    at Message.message.on (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\message-io.js:34:14)\r\n    at Message.emit (events.js:187:15)\r\n    at endReadableNT (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_readable.js:1038:12)\r\n  name: 'SequelizeDatabaseError',\r\n  parent:\r\n   { RequestError: Cannot insert explicit value for identity column in table 'CAT_GENDER' when IDENTITY_INSERT is set to OFF.\r\n       at RequestError (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\errors.js:32:12)\r\n       at Parser.tokenStreamParser.on.token (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\connection.js:639:32)\r\n       at Parser.emit (events.js:182:13)\r\n       at Parser.parser.on.token (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\token-stream-parser.js:27:14)\r\n       at Parser.emit (events.js:182:13)\r\n       at addChunk (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_readable.js:301:12)\r\n       at readableAddChunk (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_readable.js:283:11)\r\n       at Parser.Readable.push (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_readable.js:247:10)\r\n       at Parser.Transform.push (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_transform.js:149:32)\r\n       at doneParsing (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:80:14)\r\n       at token (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\infoerror-token-parser.js:48:5)\r\n       at call.lineNumber (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\infoerror-token-parser.js:13:19)\r\n       at awaitData (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:185:7)\r\n       at Parser.awaitData (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:109:7)\r\n       at Parser.readUInt32LE (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:182:10)\r\n       at parser.readBVarChar.procName (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\infoerror-token-parser.js:12:90)\r\n       at readBuffer.data (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:322:9)\r\n       at awaitData (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:314:7)\r\n       at Parser.awaitData (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:109:7)\r\n       at Parser.readBuffer (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:311:10)\r\n       at readUInt8.length (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:321:12)\r\n       at awaitData (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:129:7)\r\n       at Parser.awaitData (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:109:7)\r\n       at Parser.readUInt8 (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:126:10)\r\n       at Parser.readBVarChar (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:320:10)\r\n       at parser.readBVarChar.serverName (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\infoerror-token-parser.js:11:22)\r\n       at readBuffer.data (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:322:9)\r\n       at awaitData (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:314:7)\r\n     message:\r\n      \"Cannot insert explicit value for identity column in table 'CAT_GENDER' when IDENTITY_INSERT is set to OFF.\",\r\n     code: 'EREQUEST',\r\n     number: 544,\r\n     state: 1,\r\n     class: 16,\r\n     serverName: 'MUBAIDR-DESKTOP\\\\SQLEXPRESS',\r\n     procName: '',\r\n     lineNumber: 1,\r\n     sql:\r\n      \"INSERT INTO [CAT_GENDER] ([ID],[DESCRIPTION],[CREATED_AT],[UPDATED_AT]) VALUES (1,N'Male','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00'),(2,N'Female','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00'),(3,N'Other','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00');\" },\r\n  original:\r\n   { RequestError: Cannot insert explicit value for identity column in table 'CAT_GENDER' when IDENTITY_INSERT is set to OFF.\r\n       at RequestError (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\errors.js:32:12)\r\n       at Parser.tokenStreamParser.on.token (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\connection.js:639:32)\r\n       at Parser.emit (events.js:182:13)\r\n       at Parser.parser.on.token (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\token-stream-parser.js:27:14)\r\n       at Parser.emit (events.js:182:13)\r\n       at addChunk (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_readable.js:301:12)\r\n       at readableAddChunk (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_readable.js:283:11)\r\n       at Parser.Readable.push (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_readable.js:247:10)\r\n       at Parser.Transform.push (D:\\Current\\ors-node\\api\\node_modules\\tedious\\node_modules\\readable-stream\\lib\\_stream_transform.js:149:32)\r\n       at doneParsing (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:80:14)\r\n       at token (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\infoerror-token-parser.js:48:5)\r\n       at call.lineNumber (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\infoerror-token-parser.js:13:19)\r\n       at awaitData (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:185:7)\r\n       at Parser.awaitData (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:109:7)\r\n       at Parser.readUInt32LE (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:182:10)\r\n       at parser.readBVarChar.procName (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\infoerror-token-parser.js:12:90)\r\n       at readBuffer.data (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:322:9)\r\n       at awaitData (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:314:7)\r\n       at Parser.awaitData (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:109:7)\r\n       at Parser.readBuffer (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:311:10)\r\n       at readUInt8.length (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:321:12)\r\n       at awaitData (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:129:7)\r\n       at Parser.awaitData (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:109:7)\r\n       at Parser.readUInt8 (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:126:10)\r\n       at Parser.readBVarChar (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:320:10)\r\n       at parser.readBVarChar.serverName (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\infoerror-token-parser.js:11:22)\r\n       at readBuffer.data (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:322:9)\r\n       at awaitData (D:\\Current\\ors-node\\api\\node_modules\\tedious\\lib\\token\\stream-parser.js:314:7)\r\n     message:\r\n      \"Cannot insert explicit value for identity column in table 'CAT_GENDER' when IDENTITY_INSERT is set to OFF.\",\r\n     code: 'EREQUEST',\r\n     number: 544,\r\n     state: 1,\r\n     class: 16,\r\n     serverName: 'MUBAIDR-DESKTOP\\\\SQLEXPRESS',\r\n     procName: '',\r\n     lineNumber: 1,\r\n     sql:\r\n      \"INSERT INTO [CAT_GENDER] ([ID],[DESCRIPTION],[CREATED_AT],[UPDATED_AT]) VALUES (1,N'Male','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00'),(2,N'Female','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00'),(3,N'Other','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00');\" },\r\n  sql:\r\n   \"INSERT INTO [CAT_GENDER] ([ID],[DESCRIPTION],[CREATED_AT],[UPDATED_AT]) VALUES (1,N'Male','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00'),(2,N'Female','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00'),(3,N'Other','2018-11-21 09:55:28.350 +00:00','2018-11-21 09:55:28.350 +00:00');\" }\r\n== CAT_GENDER: migrated (0.046s)\r\n```"}}
{"actor":{"display_login":"mubaidr"}}