none
SQL adapter : trying to insert a record to products table in Northwind Database RRS feed

  • Question

  • I am getting following error in events, when I tried to insert a sample record to Northwind database in SQL Server 2008 using SQL adapter in Biztalk Server 2009. Can anybody suggest work around, I did run Query SET IDENTITY_INSERT dbo.Products ON independently from sql server management studio, but error still exist

    The adapter failed to transmit message going to send port "SendNewProductTODB" with URL "SQL://localhost/Northwind/". It will be retransmitted after the retry interval specified for this Send Port. Details:"HRESULT="0x80040e14" Description="Cannot insert explicit value for identity column in table 'Products' when IDENTITY_INSERT is set to OFF."
     <Root xmlns:ns00="urn:schemas-microsoft-com:xml-updategram"><?MSSQLError HResult="0x80040e14" Source="Microsoft OLE DB Provider for SQL Server" Description="Cannot insert explicit value for identity column in table 'Products' when IDENTITY_INSERT is set to OFF."?></Root>".

    For more information, see Help and Support Center at 
    Friday, November 26, 2010 1:31 AM

Answers

  • Hi,

    You can't insert a value into an IDENTITY type field without first setting IDENTITY_INSERT to ON.

    Also look at this hotfix(though it is for SQL Server 2000)

    http://support.microsoft.com/kb/878501

    Regards,

    Abhijit


    Abhijit Mahato - MCTS BizTalk Server blog: http://abhijitmahato.wordpress.com/ Please "Mark as Answer" if Post has Answered the Question
    Friday, November 26, 2010 6:15 AM