I am attempting to load data from Active Directory users into a table which has additional data in the table.
The table structure is:
CREATE TABLE [dbo].[IMS_ADUSER-test2]
(
[IMSuserID] [bigint] NOT NULL,
[EmployID] [bigint] NOT NULL,
[DisplayName] [nvarchar](max) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[Initials] [nvarchar](10) NULL,
[LastName] [nvarchar](50) NOT NULL,
[Email] [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) NOT NULL,
[CreateDate] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](50) NOT NULL,
[UpdateDate] [datetime] NULL,
[IsDisabled] [bit] NULL,
[DisabledDate] [datetime] NULL,
[IMS_USER_ID_FK] [bigint] NULL,
[AccountClosedDate] [datetime] NULL
)
My table type is:
CREATE TYPE [dbo].[TestC_IMS_ADUSER_TMP] AS TABLE
(
[IMSuserID] [bigint] NOT NULL,
[EmployID] [bigint] NOT NULL,
[DisplayName] [nvarchar](max) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[Initials] [nvarchar](10) NULL,
[LastName] [nvarchar](50) NOT NULL,
[Email] [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,
[CreateDate] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](50) NOT NULL,
[UpdateDate] [datetime] NULL,
[IsDisabled] [bit] NULL,
[DisabledDate] [datetime] NULL,
[IMS_USER_ID_FK] [bigint] NULL,
[AccountClosedDate] [datetime] NULL
)
My stored procedure is:
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
The error that I am getting is:
Unhandled Exception: System.Data.SqlClient.SqlException: Operand type clash: datetime2 is incompatible with bit
The data for table-valued parameter "@datatable" doesn't conform to the table type of the parameter. SQL Server error is: 200, state: 7at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction)1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at LoadADuserIntoDataBase.DataAccess.DataAccess.InsertADuserTabe(DataTable dt) in c:\users\1089907450e\documents\visual studio 2015\Projects\LoadADuserIntoDataBase\LoadADuserIntoDataBase\DataAccess\DataAccess.cs:line 28 at LoadADuserIntoDataBase.Program.Main(String[] args) in c:\users\1089907450e\documents\visual studio 2015\Projects\LoadADuserIntoDataBase\LoadADuserIntoDataBase\Program.cs:line 23
As you can see that the error is stating that my table type has a datetime2 is incompatible with my bit. The SQL Server seems to be solid. I cannot see where the problem is at.
My data access programming is:
public void InsertADuserTabe(DataTable dt)
{
using(SqlConnection con = new SqlConnection(dbString()))
{
var dtAD = dt;
var cmd = new SqlCommand("TestBulkInsertion-2", con) { CommandType =
CommandType.StoredProcedure };
cmd.Parameters.Add(new SqlParameter("@datatable", dt));
con.Open();
cmd.ExecuteNonQuery();
}
}
My code is:
public static DataTable GetADusers()
{
try
{
string ou = "OU"
using (PrincipalContext ctx = new PrincipalContext(ContextType.Domain,
Environment.UserDomainName, ou))
{
UserPrincipal user = new UserPrincipal(ctx);
using (PrincipalSearcher ps = new PrincipalSearcher(user))
{
DataTable results = new DataTable();
results.Columns.Add("EmployeeID");
results.Columns.Add("DisplayName ");
results.Columns.Add("FirstName");
results.Columns.Add("Initial");
results.Columns.Add("LastName");
results.Columns.Add("mail");
results.Columns.Add("SamAccountName");
results.Columns.Add("DistinguishedName");
results.Columns.Add("ObjectGUID");
results.Columns.Add("objectSid");
results.Columns.Add("physicalDeliveryOfficeName");
int count = 0;
foreach (Principal p in ps.FindAll())
{
UserPrincipal u = p as UserPrincipal;
if (u != null)
{
DirectoryEntry entry =
(DirectoryEntry)p.GetUnderlyingObject();
DirectorySearcher search = new DirectorySearcher(entry);
//string query = "(&(objectCategory=person)
(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(&(mail=*)))";
//UAC - CAC Enabled Accounts (no disabled accounts or
password never expires) and has a Email Account
string query = "(&(objectCategory=person)(objectClass=user)
(!userAccountControl:1.2.840.113556.1.4.803:=2)
(!userAccountControl:1.2.840.113556.1.4.803:=65536)
(userAccountControl:1.2.840.113556.1.4.803:=262144)(userPrincipalName=1*@mil)(mail=*))";
search.Filter = query;
search.PropertiesToLoad.Add("EmployeeID");
search.PropertiesToLoad.Add("DisplayName");
search.PropertiesToLoad.Add("GivenName");
search.PropertiesToLoad.Add("Initials");
search.PropertiesToLoad.Add("sn");
search.PropertiesToLoad.Add("mail");
search.PropertiesToLoad.Add("SamAccountName");
search.PropertiesToLoad.Add("DistinguishedName");
search.PropertiesToLoad.Add("ObjectGUID");
search.PropertiesToLoad.Add("objectSid");
search.PropertiesToLoad.Add("physicalDeliveryOfficeName");
SearchResultCollection mySearchResultColl = search.FindAll();
foreach (SearchResult sr in mySearchResultColl)
{
DataRow dr = results.NewRow();
DirectoryEntry de = sr.GetDirectoryEntry();
dr["EmployeeID"] = de.Properties["EmployeeID"].Value;
dr["DisplayName "] = de.Properties["DisplayName"].Value;
dr["FirstName"] = de.Properties["GivenName"].Value;
dr["Initial"] = de.Properties["Initials"].Value;
dr["LastName"] = de.Properties["sn"].Value;
dr["mail"] = de.Properties["mail"].Value;
dr["SamAccountName"] =
de.Properties["SamAccountName"].Value;
dr["DistinguishedName"] =
de.Properties["DistinguishedName"].Value;
//var GUID = new
Guid((byte[])de.Properties["ObjectGUID"].Value);
dr["ObjectGUID"] = new
Guid((byte[])de.Properties["ObjectGUID"].Value);
//var sid = new
SecurityIdentifier((byte[])de.Properties["objectSid"].Value, 0);
dr["objectSid"] = new
SecurityIdentifier((byte[])de.Properties["objectSid"].Value, 0);
dr["physicalDeliveryOfficeName"] =
de.Properties["physicalDeliveryOfficeName"].Value;
//dr["guid"] = de.Properties["guid"].Value;
results.Rows.Add(dr);
count++;
}
}
}
return results;
//DataView DV = new DataView(results);
//dgvActiveDirectory.DataSource = DV;
//lblCount.Text = count.ToString();
}
}
}
catch (NullReferenceException ex)
{
Console.WriteLine("data error" + ex);
DataTable dt = new DataTable();
return dt;
}
}
I have a wrapper:
public static DataTable AddColumns(DataTable dt)
{
DataColumn PK = new DataColumn("IMSuserID", typeof(System.Int64));
DataColumn user = new DataColumn("ModifiedBy", typeof(System.String));
DataColumn CreateDate = new DataColumn("CreateDate", typeof(System.DateTime));
//Required Data
dt.Columns.Add(PK);
dt.Columns.Add(user);
dt.Columns.Add(CreateDate);
//non required data
DataColumn FK = new DataColumn("IMS_USER_ID_FK", typeof(System.Int64));
DataColumn Update = new DataColumn("UpdateDate", typeof(System.DateTime));
DataColumn IsDeleted = new DataColumn("IsDisabled", typeof(System.Boolean));
DataColumn DisabledDate = new DataColumn("DisabledDate",
typeof(System.DateTime));
DataColumn ClosedDate = new DataColumn("AccountClosedDate", typeof(System.DateTime));
dt.Columns.Add(FK);
//FK.DefaultValue = null;
dt.Columns.Add(Update);
//Update.DefaultValue = null;
dt.Columns.Add(IsDeleted);
//IsDeleted.DefaultValue = null;
dt.Columns.Add(DisabledDate);
//DisabledDate.DefaultValue = null;
dt.Columns.Add(ClosedDate);
//ClosedDate.DefaultValue = null;
return dt;
}
Implementation is:
static void Main(string[] args)
{
DataAccess.DataAccess da = new DataAccess.DataAccess();
//da.InsertADuserTabe(GetADusers());
da.InsertADuserTabe(AddColumns(GetADusers()));
Console.WriteLine("Done");
//DataAccess da = new LoadADuserIntoDataBase.DataAccess();
//InsertADuserTabe(GetADusers());
}
As you can see there does not seem any reason for for the operand type clash datetime2 is incompatible with bit. I am at a stuck point. I would appreciate any help.
I alter the table to add the sequence as a default value: Sequence as Default Value
I also changed the Stored Procedure to:
alter PROCEDURE [dbo].[TestBulkInsertion-2] @datatable TestC_IMS_ADUSER_TMP READONLY AS BEGIN
insert INTO MS_ADUSER-test4
(IMSuserID, EmployID,DisplayName,FirstName,Initials,LastName,Email,SamAccountName,DistinguishedName,ObjectGUID,ObjectSid,physicalDeliveryOfficeName,CreateDate,ModifiedBy,UpdateDate, IsDisabled, IMS_USER_ID_FK,AccountClosedDate )
SELECT IMSuserID, EmployID,DisplayName,FirstName,Initials,LastName,Email,SamAccountName,DistinguishedName,ObjectGUID,ObjectSid,physicalDeliveryOfficeName,GETDATE(),SYSTEM_USER,UpdateDate, IsDisabled, IMS_USER_ID_FK,AccountClosedDate
FROM @datatable END
As you can see, I replaced the sequence with the actual colomn name in the select query. I get the same error
