I want to insert some big values in an excel sheet, but it is giving errors.
Error
Text values in formulas are limited to 255 characters. To create text values longer in a formula, use the CONCATENATE function or the concatenation operator (&).
Value
=CONCATENATE("BEGIN TRY
BEGIN TRANSACTION
BEGIN --User defined values
DECLARE @FieldName NVARCHAR(100) = '",F2,"';
DECLARE @Currencycode VARCHAR(3) = '",A2,"';
DECLARE @Countrycode VARCHAR(2) = '",B2,"';
DECLARE @RuleType NVARCHAR(100) = 'CannotContainSpecialCharOtherThan';
DECLARE @RuleValue INT = 0;
DECLARE @InsFieldTypeDesc NVARCHAR(50) = 'AnyType';
DECLARE @RuleValueAlpha NVARCHAR(4000) = '/: (),.''-?+';
DECLARE @ErrMsg NVARCHAR(500) = '",N2,"';
DECLARE @ErrCode NVARCHAR(10) = '",M2,"';
DECLARE @ErrPrior TINYINT = '",L2,"';
DECLARE @IsLength INT = 0; ---if length is there then set this 1 or 0
DECLARE @DependantOn NVARCHAR(100) = NULL
END
BEGIN --Consts
DECLARE @UTCTime DATETIME = Getutcdate();
DECLARE @CTTime DATETIME = Dateadd(hour, -5, Getutcdate());
END
DECLARE @FieldRuleConfigPk INT;
DECLARE @ErrorMsgpk INT;
DECLARE @countryfk INT;
DECLARE @Fieldnamefk INT;
DECLARE @InsFieldTypeFk INT;
DECLARE @RuleValue1 INT = 0;
DECLARE @RuleValue2 INT = 19;
DECLARE @RuleValue3 INT = 19;
DECLARE @RuleTypeFk INT;
DECLARE @ErrorFk INT;
DECLARE @IsOk INT = 0
PRINT( 'Starts script' )
BEGIN --INIT
SET @countryfk = (SELECT countrypk
FROM mas_country
WHERE countrycode = @Countrycode);
SET @Fieldnamefk = (SELECT fieldnamepk
FROM mas_fieldname
WHERE fieldname = @FieldName);
SET @InsFieldTypeFk = (SELECT instructionfieldtypepk
FROM mas_instructionfieldtype
WHERE fieldtypedesc = @InsFieldTypeDesc);
END
IF NOT EXISTS (SELECT 1
FROM mas_fieldruleconfig
WHERE currencycode = @Currencycode
AND countryfk = @countryfk
AND fieldnamefk = @Fieldnamefk)
BEGIN
INSERT INTO mas_fieldruleconfig
(currencycode,
countryfk,
fieldnamefk,
instructionfieldtypefk,
createddateutc,
createddatect)
VALUES ( @Currencycode,
@countryfk,
@Fieldnamefk,
@InsFieldTypeFk,
@UTCTime,
@CTTime )
SELECT @FieldRuleConfigPk = Scope_identity();
SET @IsOk = 1
PRINT( 'mas_fieldruleconfig insert success' )
END
ELSE
BEGIN
SELECT @FieldRuleConfigPk = fieldruleconfigpk
FROM mas_fieldruleconfig
WHERE currencycode = @Currencycode
AND countryfk = @countryfk
AND fieldnamefk = @Fieldnamefk
PRINT( 'mas_fieldruleconfig setting already exists' )
END
IF NOT EXISTS (SELECT 1
FROM mas_ruletype
WHERE ruletype = @RuleType
AND fieldruleconfigfk = @FieldRuleConfigPk
--Need to add this check, otherwise it will fail
AND rulevalue = @RuleValue --Not needed
AND rulevaluealphanumeric = @RuleValueAlpha)
--Not needed
BEGIN
INSERT INTO mas_ruletype
(ruletype,
fieldruleconfigfk,
rulevalue,
rulevaluealphanumeric,
createddateutc,
createddatect,
dependanton)
VALUES ( @RuleType,
@FieldRuleConfigPk,
@RuleValue,
@RuleValueAlpha,
@UTCTime,
@CTTime,
@DependantOn)
SELECT @RuleTypeFk = Scope_identity();
SET @IsOk = 1
PRINT( 'mas_ruletype insert success' )
END
ELSE
BEGIN
SELECT @RuleTypeFk = (SELECT ruletypepk
FROM mas_ruletype
WHERE ruletype = @RuleType
AND fieldruleconfigfk =
@FieldRuleConfigPk
--Need to add this check, otherwise it will fail
AND rulevalue = @RuleValue
--Not needed
AND rulevaluealphanumeric =
@RuleValueAlpha)
PRINT( 'mas_ruletype settings already exists' )
END
IF NOT EXISTS (SELECT 1
FROM mas_errormessage
WHERE errormessage = @ErrMsg)
BEGIN
INSERT INTO mas_errormessage
(errormessage,
createddateutc,
createddatect)
VALUES ( @ErrMsg,
@UTCTime,
@CTTime )
SELECT @ErrorMsgpk = Scope_identity()
SET @IsOk = 1
PRINT( 'mas_errmsg insert success' )
END
ELSE
BEGIN
SELECT @ErrorMsgpk = errormessagepk
FROM mas_errormessage
WHERE errormessage = @ErrMsg
PRINT( 'mas_errormsg settings already exists' )
END
IF NOT EXISTS (SELECT 1
FROM mas_error
WHERE fieldnamefk = @Fieldnamefk
AND errorcode = @ErrCode)
BEGIN
INSERT INTO mas_error
(fieldnamefk,
errorcode,
errorpriority,
errormessagefk,
createddateutc,
createddatect)
VALUES ( @Fieldnamefk,
@ErrCode,
@ErrPrior,
@ErrorMsgpk,
@UTCTime,
@CTTime )
SELECT @ErrorFk = Scope_identity();
SET @IsOk = 1
PRINT( 'mas_error insert success' )
END
ELSE
BEGIN
SELECT @ErrorFk = (SELECT errorpk
FROM mas_error
WHERE fieldnamefk = @Fieldnamefk
AND errorcode = @ErrCode);
PRINT( 'Mas_Error settings already exists' )
END
IF NOT EXISTS (SELECT 1
FROM [lnk_fieldruleerror]
WHERE [fieldruleconfigfk] = @FieldRuleConfigPk
AND [fieldnamefk] = @Fieldnamefk
AND [ruletypefk] = @RuleTypeFk
AND @ErrorFk = @ErrorFk)
BEGIN
INSERT INTO [dbo].[lnk_fieldruleerror]
([fieldruleconfigfk],
[fieldnamefk],
[ruletypefk],
[errorfk])
VALUES (@FieldRuleConfigPk,
@Fieldnamefk,
@RuleTypeFk,
@ErrorFk)
END
IF @IsOk = 1
BEGIN
COMMIT TRANSACTION
PRINT( 'commit' )
END
END TRY
BEGIN CATCH
PRINT 'Error'
PRINT ( 'Rollback' )
ROLLBACK TRANSACTION;
END CATCH")
I have followed this link also but could not find a solution.
