Ошибка
Тип столбца «MemberHId» не поддерживается. Тип '
SqlHierarchyId
'.
На обоих серверах тип данных один и тот же HierarchyId.
Я просто использую ADO.Net.
Сборка Microsoft.SqlServer.ManagedDTS версии 13.0.0.0 Мы используем Azure Sql.
Пробовал с разными версиями сборок sql
DataTable dt = new DataTable();
dt.Columns.Add("Code", typeof(string));
dt.Columns.Add("Description", typeof(string));
dt.Columns.Add("NodeId", typeof(int));
dt.Columns.Add("MemberHId", typeof(SqlHierarchyId));
dt.Columns.Add("Level", typeof(int));
//Getting the data from serverAPI which is returning the data in the columns without null records.
dt=apicall();
SqlParameter returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
cmd.Parameters.AddWithValue("@MemberH", dt);
cmd.CommandTimeout = 0;
cmd.Connection.Open();
cmd.ExecuteNonQuery();// Its failing here
My Sp состоит из параметра табличного типа @MemberHType
, и процедура возвращает строки со следующими столбцами:
Name | Type
------------+----------
Code | varchar
Description | varchar
NodeID | smallint
MemberHId | hierarchyid
Level | smallint
Это процедура:
alter PROCEDURE [dbo].[InsertHierarchyData]
(
@MemberHType MemberH READONLY
)
AS
truncate table test
BEGIN TRY
insert into test values ('start select')
--create table test (errormessage varchar(1000))
IF EXISTS (SELECT TOP 1 1 FROM @MemberHType)
BEGIN
insert into test values ('Inside first Insert start')
INSERT INTO HierarchyStaging
(
Code
,[Description]
,NodeID
,MemberhHId
,[Level]
)
SELECT
Code
,[Description]
,NodeId
,MemberhHId
,[Level]
FROM @MemberHType
insert into test values ('Inside first Insert end')
END
END TRY
BEGIN CATCH
SELECT @comment = ERROR_MESSAGE()
,@Status = 'Error'
SET @comment = CONVERT(NVARCHAR(3000),@Comment) + ' Error Severity: ' + CAST(ERROR_SEVERITY() AS varchar(25)) + ' Error state: 30'
GOTO ErrorHandler
END CATCH
The Column in c# is SqlHierarchyId.
Трассировки стека:
at System.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
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, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()