SQL Server не может вставить оператор вставки внутри курсора

Question

Оператор вставки завершается неудачно внутри курсора, когда я пытаюсь вставить значения в связанный сервер SQL.

Если я запускаю ту же инструкцию вставки вне курсора, то она работает нормально. Есть ли какие-либо настройки, которые необходимо выполнить при создании связанного сервера?

Сообщение об ошибке:

Поставщик OLE DB "SQL...." для связанного сервера "" вернул сообщение "Параметр неверен.".

Это мой кодовый сервер в курсоре

SET NOCOUNT ON;

DECLARE @SUBCATEGORY_NAME AS VARCHAR(100), @CategoryStatus AS BIT, @BRAND_NAME AS VARCHAR(100), @BrandMasterStatus AS BIT, @BrandManfacturerName AS VARCHAR(MAX), @PRODUCT_NAME VARCHAR(100), @ProductStatus AS BIT,
 @ProductIsReturnable AS BIT, @PRINT_ON_RECEIPT AS VARCHAR(40), @TenantId INT, @CreationTime DATETIME2, @ParentId INT, @BAR_CODE_NO AS VARCHAR(40), 
 @TAX_CODE AS VARCHAR(MAX), @TaxName AS VARCHAR(MAX), @TaxInclusive AS BIT, @TAX_PERCENTAGE AS FLOAT, @TaxStartDateTime DATETIME2, @BUSINESS_TYPE VARCHAR(20);

--PRINT '-------- Product table migration --------';

DECLARE Product_Cursor CURSOR FOR
SELECT
SC.SUBCATEGORY_NAME,CASE WHEN SC.DEFUNCT_IND = 'N' THEN 1 ELSE 0 END AS CategoryStatus,
BM.BRAND_NAME,CASE WHEN BM.DEFUNCT_IND = 'N' THEN 1 ELSE 0 END AS BrandMasterStatus,BM.MANUFACTURER AS BrandManfacturerName,
P.PRODUCT_NAME,CASE WHEN P.DEFUNCT_IND = 'N' THEN 1 ELSE 0 END AS ProductStatus,
CASE WHEN P.RETURNABLE = 'Y' THEN 1 ELSE 0 END AS ProductIsReturnable,
P.PRINT_ON_RECEIPT,
--SubCategoryId,
--BrandId,
--1 AS TaxId,
1 AS TenantId,
GETDATE() AS CreationTime,
0 AS ParentId,
P.BAR_CODE_NO,
TS.TAX_CODE,
TS.TAX_DESC AS TaxName,
CASE WHEN TS.TAX_INCLUDED = '1' THEN 1 ELSE 0 END AS TaxInclusive,
TS.TAX_PERCENTAGE,
TS.EFFECTIVE_DATE AS TaxStartDateTime,
P.BUSINESS_TYPE
FROM CISPROD.dbo.PRODUCT AS P
RIGHT OUTER JOIN CISPROD.dbo.SUBCATEGORY AS SC ON SC.SUBCATEGORY_ID = P.SUBCATEGORY_ID
FULL OUTER JOIN CISPROD.dbo.BRANDMSTR AS BM ON BM.BRANDMSTR_ID = P.BRANDMSTR_ID
FULL OUTER JOIN CISPROD.dbo.TAX_SETUP AS TS ON TS.TAX_CODE = P.TAX_GROUP

OPEN Product_Cursor

FETCH NEXT FROM Product_Cursor   
INTO @SUBCATEGORY_NAME,@CategoryStatus,@BRAND_NAME, @BrandMasterStatus, @BrandManfacturerName, @PRODUCT_NAME, @ProductStatus, @ProductIsReturnable, 
@PRINT_ON_RECEIPT, @TenantId, @CreationTime,@ParentId,@BAR_CODE_NO,@TAX_CODE, @TaxName, @TaxInclusive, @TAX_PERCENTAGE, @TaxStartDateTime, @BUSINESS_TYPE;

BEGIN TRANSACTION
BEGIN TRY

WHILE @@FETCH_STATUS = 0
BEGIN
    --PRINT ' '
    --DECLARE @message VARCHAR(MAX)
    --SELECT @message = '----- Products CISPROD: ' + @PRODUCT_NAME
    --PRINT @message

    --Insert Product Categories Table
    DECLARE @CategoryId INT

    IF ISNULL(@SUBCATEGORY_NAME,'') <>''
    BEGIN
    ---IF NOT EXISTS(SELECT [Name] FROM ProductCategories WHERE ISNULL([Name], '') = ISNULL(@SUBCATEGORY_NAME,''))
    IF NOT EXISTS(SELECT [Name] FROM [AZUREDATABASE].[sds-pos-storiveo-db].dbo.ProductCategories WHERE [Name] = @SUBCATEGORY_NAME)
        BEGIN
            INSERT INTO [AZUREDATABASE].[sds-pos-storiveo-db].dbo.ProductCategories([Name],CreationTime,Inactive,ParentId,TenantId) VALUES(@SUBCATEGORY_NAME,@CreationTime,@CategoryStatus,@ParentId,@TenantId)
            SELECT @CategoryId = @@IDENTITY
        END
        ELSE
        BEGIN
            SELECT @CategoryId = Id FROM [AZUREDATABASE].[sds-pos-storiveo-db].dbo.ProductCategories WHERE [Name] = @SUBCATEGORY_NAME
        END
    END

    --Insert Product Brand Table
    DECLARE @BrandId INT
    --DECLARE @DefaultBrandId INT
    --SET @DefaultBrandId = 1

    IF ISNULL(@BRAND_NAME,'') <>''
    BEGIN
        --IF NOT EXISTS(SELECT BrandName FROM ProductBrands WHERE ISNULL(BrandName,'') = ISNULL(@BRAND_NAME,''))
        IF NOT EXISTS(SELECT BrandName FROM [AZUREDATABASE].[sds-pos-storiveo-db].dbo.ProductBrands WHERE BrandName = @BRAND_NAME)
        BEGIN
            INSERT INTO [AZUREDATABASE].[sds-pos-storiveo-db].dbo.ProductBrands(BrandName,ManufacturerName,CreationTime,Inactive) VALUES(@BRAND_NAME,@BrandManfacturerName,@CreationTime,@BrandMasterStatus)
            SELECT @BrandId = @@IDENTITY
        END
        ELSE
        BEGIN
            SELECT @BrandId = Id FROM [AZUREDATABASE].[sds-pos-storiveo-db].dbo.ProductBrands WHERE BrandName = @BRAND_NAME
        END
    END
    --ELSE
    --BEGIN
    --  SET @BrandId = @DefaultBrandId
    --END

    --Insert Tax Table Records
    DECLARE @TaxId INT

    IF ISNULL(@TAX_CODE,'') <>''
    BEGIN
    IF NOT EXISTS(SELECT Code FROM [AZUREDATABASE].[sds-pos-storiveo-db].dbo.Taxes WHERE Code = @TAX_CODE)
        BEGIN
            INSERT INTO [AZUREDATABASE].[sds-pos-storiveo-db].dbo.Taxes(Code,Inactive,IsInclusive,[Name],TaxTypeId,TenantId) VALUES(@TAX_CODE,1,@TaxInclusive,@TaxName,1,@TenantId)
            SELECT @TaxId = @@IDENTITY

            INSERT INTO [AZUREDATABASE].[sds-pos-storiveo-db].dbo.TaxSchedules([Percentage],StartDateTime,TaxId) VALUES(@TAX_PERCENTAGE,@TaxStartDateTime,@TaxId)
        END
        ELSE
        BEGIN
            SELECT @TaxId = Id FROM [AZUREDATABASE].[sds-pos-storiveo-db].dbo.Taxes WHERE Code = @TAX_CODE
        END
    END

    --Insert Product Table Records
    DECLARE @ProductId INT

    IF ISNULL(@PRODUCT_NAME,'') <>''
    BEGIN
        IF (@BUSINESS_TYPE = 'NF')
        BEGIN
            INSERT INTO [AZUREDATABASE].[sds-pos-storiveo-db].dbo.Products([Name],Inactive,IsReturnable,PrintName,
            ProductBrandId,ProductCategoryId,TaxId,TenantId,CreationTime)
            VALUES(@PRODUCT_NAME,@ProductStatus,@ProductIsReturnable,@PRINT_ON_RECEIPT,@BrandId,@CategoryId,@TaxId,@TenantId,@CreationTime)
        END
        ELSE
        BEGIN
            INSERT INTO [AZUREDATABASE].[sds-pos-storiveo-db].dbo.FuelProducts([Name],Inactive,PrintName,
            ProductCategoryId,TaxId,TenantId,CreationTime)
            VALUES(@PRODUCT_NAME,@ProductStatus,@PRINT_ON_RECEIPT,@CategoryId,@TaxId,@TenantId,@CreationTime)
        END
        SELECT @ProductId = @@IDENTITY
    END

    --Insert Product Barcode Records
    IF ISNULL(@BAR_CODE_NO,'') <>''
    BEGIN
        IF (@BUSINESS_TYPE = 'NF')
        BEGIN
            INSERT INTO [AZUREDATABASE].[sds-pos-storiveo-db].dbo.ProductBarcodes(Barcode,Inactive,ProductId)VALUES(@BAR_CODE_NO,@ProductStatus,@ProductId)
        END
    END

    FETCH NEXT FROM Product_Cursor
INTO @SUBCATEGORY_NAME,@CategoryStatus,@BRAND_NAME, @BrandMasterStatus, @BrandManfacturerName, @PRODUCT_NAME, @ProductStatus, @ProductIsReturnable, 
@PRINT_ON_RECEIPT, @TenantId, @CreationTime, @ParentId, @BAR_CODE_NO, @TAX_CODE, @TaxName, @TaxInclusive, @TAX_PERCENTAGE, @TaxStartDateTime,@BUSINESS_TYPE

END

END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
 -- any other logiing or cleanup
END CATCH

IF @@TranCount>0 -- Transaction still open, so must have succeeded. If rolled back, trancount would be 0
 COMMIT TRANSACTION

CLOSE Product_Cursor;
DEALLOCATE Product_Cursor;