My data-value parameter does not conform to the parameter SQL Server error is: 200 state 7

• bdkdavid

  Ten Centuries

  Points: 1220

  More actions

  October 10, 2018 at 9:33 am

  #416038

  I am attempting to get data from active directory Users and place them into a datatable in SQL Server 2014 Standard. I first placed this question on stack overflow. The link is here
  Stack OverFlow
  The error seems to SQL. I am not sure there are many components. All the details are on the stack overflow site.
  The specific error
  The Table
  The Table Type
  Stored Procedure
  As well as the C# code
  This seems it should work

  I would appreciate any help
  Thank You,
  David

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 10, 2018 at 11:46 am

  #2008912

  This is the only way I could get the the CREATE PROCEDURE to parse:
  
CREATE PROCEDURE [TestBulkInsertion-2]
  @datatable TestC_IMS_ADUSER_TMP READONLY
AS
BEGIN
  INSERT INTO [IMS_ADUSER-test2] (
  IMSuserID
  , EmployID
  , DisplayName
  , FirstName
  , Initials
  , LastName
  , Email
  , SamAccountName
  , DistinguishedName
  , ObjectGUID
  , ObjectSid
  , physicalDeliveryOfficeName
  , CreateDate
  , ModifiedBy
  , UpdateDate
  , IsDisabled
  , IMS_USER_ID_FK
  , AccountClosedDate
  )
  SELECT
  NEXT VALUE FOR sequence_test
  , EmployID
  , DisplayName
  , FirstName
  , Initials
  , LastName
  , Email
  , SamAccountName
  , DistinguishedName
  , ObjectGUID
  , ObjectSid
  , physicalDeliveryOfficeName
  , GETDATE()
  , system_user
  , UpdateDate
  , IsDisabled
  , IMS_USER_ID_FK
  , AccountClosedDate
  FROM
    @datatable
END

• bdkdavid

  Ten Centuries

  Points: 1220

  More actions

  October 10, 2018 at 5:18 pm

  #2008973

  Hi Lynn,
  Thanks for the response! I ended up changing the Table Type and stored procedure
  Here is the table type:
  CREATE TYPE [dbo].[Test_IMS_ADUSER_TMP] AS TABLE(
    [EmployID] [bigint] NOT NULL,
    [DisplayName] [nvarchar](max) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [Initials] [nvarchar](10) NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [mail] [nvarchar](100) NOT NULL,
    [SamAccountName] [nvarchar](50) NOT NULL,
    [DistinguishedName] [nvarchar](max) NOT NULL,
    [ObjectGUID] [nvarchar](100) NOT NULL,
    [ObjectSid] [nvarchar](100) NOT NULL,
    [physicalDeliveryOfficeName] [nvarchar](50) NULL
)
GO
  Here is the stored procedure:
  CREATE proc [dbo].[TestBulkInsertion-1]
@datatable Test_IMS_ADUSER_TMP READONLY
as
begin
insert into IMS_ADUSER
(
[EmployID],
[DisplayName],
[FirstName],
[Initials],
[LastName],
[Email],
[SamAccountName],
[DistinguishedName],
[ObjectGUID],
[ObjectSid],
[PhysicalDeliveryOfficeName]
)
select
EmployID,
[DisplayName],
[FirstName],
[Initials],
[LastName],
[mail],
[SamAccountName],
[DistinguishedName],
[ObjectGUID],
[ObjectSid],
[PhysicalDeliveryOfficeName]
from @datatable
end
  It worked after I made these changes. I am not sure why this is so, there are more columns should be able to entire more data