The formatmessage function requires 1 to 21 arguments.

• ringovski

  Hall of Fame

  Points: 3521

  More actions

  December 20, 2016 at 11:05 pm

  #333079

  I have trying to use some cursor to read through a header and some rows, but the format message keeps saying;

  Msg 189, Level 15, State 1, Line 56

  The formatmessage function requires 1 to 21 arguments.

  Msg 189, Level 15, State 1, Line 105

  The formatmessage function requires 1 to 21 arguments.

  But the number of columns is the same.

  DECLARE @custList TABLE (CustNo NVARCHAR(MAX))

  INSERT INTO @custList

  SELECT DISTINCT

  [VCRCustNo] FROM [dbo].[VehicleAnalysisStaging]

  DECLARE @CustNo NVARCHAR(MAX)

  DECLARE db_cursor CURSOR FOR

  SELECT * FROM @custList

  OPEN db_cursor

  DECLARE @results TABLE (ID INT IDENTITY (1, 1), Line NVARCHAR(MAX))

  FETCH NEXT FROM db_cursor INTO @CustNo

  WHILE @@FETCH_STATUS = 0

  BEGIN

  /*---VCR Data Set---*/

  INSERT INTO @results (Line)

  SELECT DISTINCT FORMATMESSAGE('%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s'

  ,CAST([VCRRecType] as VARCHAR(MAX))

  ,CAST([VCRCustNo] as VARCHAR(MAX))

  ,CAST([VCRCollationFlag] as VARCHAR(MAX))

  ,CAST([VCRPrintFlag] as VARCHAR(MAX))

  ,CAST([VCRNotUsed]as VARCHAR(MAX))

  ,CAST([VCRImageFlag] as VARCHAR(MAX))

  ,CAST([VCRTradingName] as VARCHAR(MAX))

  ,CAST([VCRContactName]as VARCHAR(MAX))

  ,CAST([VCRAddress1]as VARCHAR(MAX))

  ,CAST([VCRAddress2] as VARCHAR(MAX))

  ,CAST([VCRAddress3] as VARCHAR(MAX))

  ,CAST([VCRAddress4] as VARCHAR(MAX))

  ,CAST([VCROfficeFlag] as VARCHAR(MAX))

  ,CAST([VCRGrandTotalPeriod] as VARCHAR(MAX))

  ,CAST([VCRGrandTotalCosttoDate] as VARCHAR(MAX))

  ,CAST([VCRBillFromDate] as VARCHAR(MAX))

  ,CAST([VCRBillToDate] as VARCHAR(MAX))

  ,CAST([VCRReprintFlag] as VARCHAR(MAX))

  ,CAST([VCRemailAddress] as VARCHAR(MAX))

  )

  FROM [dbo].[VehicleAnalysisStaging] WHERE [VCRCustNo] = @CustNo

  DECLARE @costCentre NVARCHAR(MAX)

  DECLARE cc_cursor CURSOR FOR

  SELECT DISTINCT CCRCostCentre FROM [dbo].[VehicleAnalysisStaging] WHERE VCRCustNo = @CustNo

  OPEN cc_cursor

  FETCH NEXT FROM cc_cursor INTO @costCentre

  WHILE @@FETCH_STATUS = 0

  BEGIN

  /*---CCR Data Set---*/

  INSERT INTO @results (Line)

  SELECT DISTINCT FORMATMESSAGE('%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s'

  ,CAST([CCRRecType]as VARCHAR(MAX))

  ,CAST(CCRCostCentre as VARCHAR(MAX))

  ,CAST(CCRBPSubtotalFuel as VARCHAR(MAX))

  ,CAST(CCRSBPubTotalRepairs as VARCHAR(MAX))

  ,CAST(CCRBPSubTotalLease as VARCHAR(MAX))

  ,CAST(CCRBPSubtotaltoll as VARCHAR(MAX))

  ,CAST(CCRBPSubTotalRUCRego as VARCHAR(MAX))

  ,CAST(CCRBPSubTotalOtherExp as VARCHAR(MAX))

  ,CAST(CCRBPSubtotalAccident as VARCHAR(MAX))

  ,CAST(CCRBPSubToalMgmt as VARCHAR(MAX))

  ,CAST(CCRBPSubTotal as VARCHAR(MAX))

  ,CAST(CCRCTDSubtotalFuelOil as VARCHAR(MAX))

  ,CAST(CCRCTDSubtotalRepairsandMaint as VARCHAR(MAX))

  ,CAST(CCRCTDSubtotalLeaseCharges as VARCHAR(MAX))

  ,CAST(CCRCTDSubtotalTollRoadCharges as VARCHAR(MAX))

  ,CAST(CCRCTDSubtotalRUCandRego as VARCHAR(MAX))

  ,CAST(CCRCTDSubtotalOtherExpenses as VARCHAR(MAX))

  ,CAST(CCRCTDSubtotalAccidentInsurance as VARCHAR(MAX))

  ,CAST(CCRCTDSubtotalMgmtCharges as VARCHAR(MAX))

  ,CAST(CCRCTDSubtotalTotal as VARCHAR(MAX))

  ,CAST(CCRTransactionFees as VARCHAR(MAX))

  )

  FROM [dbo].[VehicleAnalysisStaging] WHERE [VCRCustNo] = @CustNo AND CCRCostCentre = @costCentre

  /*---BTP Data Set---*/

  INSERT INTO @results (Line)

  SELECT FORMATMESSAGE('%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s'

  ,CAST([BTPRecType]as VARCHAR(MAX))

  ,CAST(BPtRegnno as VARCHAR(MAX))

  ,CAST(BPTModel as VARCHAR(MAX))

  ,CAST(BPTVehdesc as VARCHAR(MAX))

  ,CAST(BPTDriverName as VARCHAR(MAX))

  ,CAST(BPTOdometer as VARCHAR(MAX))

  ,CAST(BTPSpan as VARCHAR(MAX))

  ,CAST(BPTFueloil as VARCHAR(MAX))

  ,CAST(BPTRepairs as VARCHAR(MAX))

  ,CAST(BPTLease as VARCHAR(MAX))

  ,CAST(BPTToll as VARCHAR(MAX))

  ,CAST(BPTRUCEGO as VARCHAR(MAX))

  ,CAST(BPTOtherExp as VARCHAR(MAX))

  ,CAST(BPTAccident as VARCHAR(MAX))

  ,CAST(BPTMngmt as VARCHAR(MAX))

  ,CAST(BPTTotal as VARCHAR(MAX))

  )

  FROM [dbo].[VehicleAnalysisStaging] WHERE [VCRCustNo] = @CustNo AND CCRCostCentre = @costCentre

  /*---CTD Data Set---*/

  INSERT INTO @results (Line)

  SELECT FORMATMESSAGE('%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s'

  ,CAST([CTDRecType]as VARCHAR(MAX))

  ,CAST(regnno as VARCHAR(MAX))

  ,CAST(modelyear as VARCHAR(MAX))

  ,CAST(vehicledesc as VARCHAR(MAX))

  ,CAST(drivername as VARCHAR(MAX))

  ,CAST(odometer as VARCHAR(MAX))

  ,CAST(span as VARCHAR(MAX))

  ,CAST([Fuel/Oil] as VARCHAR(MAX))

  ,CAST(Repairs as VARCHAR(MAX))

  ,CAST(LeaseCharges as VARCHAR(MAX))

  ,CAST(TollCharges as VARCHAR(MAX))

  ,CAST([RUC/Rego] as VARCHAR(MAX))

  ,CAST(OtherExpenses as VARCHAR(MAX))

  ,CAST(Accident as VARCHAR(MAX))

  ,CAST(Managment as VARCHAR(MAX))

  ,CAST(TotalexclGST as VARCHAR(MAX))

  ,CAST(TotalCents as VARCHAR(MAX))

  ,CAST([Litres/100Km] as VARCHAR(MAX))

  ,CAST([Litres/National] as VARCHAR(MAX))

  ,CAST(C02 as VARCHAR(MAX))

  ,CAST(TotalKms as VARCHAR(MAX))

  )

  FROM [dbo].[VehicleAnalysisStaging] WHERE [VCRCustNo] = @CustNo AND CCRCostCentre = @costCentre

  FETCH NEXT FROM cc_cursor INTO @costCentre

  END

  CLOSE cc_cursor

  DEALLOCATE cc_cursor

  FETCH NEXT FROM db_cursor INTO @CustNo

  END

  CLOSE db_cursor

  DEALLOCATE db_cursor

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 21, 2016 at 2:53 am

  #1919102

  You're passing too many parameters to the FORMATMESSAGE function.

  For /*---CCR Data Set---*/, you're passing in msg_string as the first argument, followed by 21 param_values. The maximum number of param_values is 20.

  See https://msdn.microsoft.com/en-us/library/ms186788.aspx

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 21, 2016 at 3:31 am

  #1919108

  The nested-cursor approach in your code is likely to underperform, in addition to increasing the complexity of a very simple operation. You can almost certainly simplify your code to something like this:

  DECLARE @results TABLE (ID INT IDENTITY (1, 1), Line NVARCHAR(MAX))

  ;WITH VCR AS (

  SELECT DISTINCT

  [VCRRecType],

  [VCRCustNo],

  [VCRCollationFlag],

  [VCRPrintFlag],

  [VCRNotUsed],

  [VCRImageFlag],

  [VCRTradingName],

  [VCRContactName],

  [VCRAddress1],

  [VCRAddress2],

  [VCRAddress3],

  [VCRAddress4],

  [VCROfficeFlag],

  [VCRGrandTotalPeriod],

  [VCRGrandTotalCosttoDate],

  [VCRBillFromDate],

  [VCRBillToDate],

  [VCRReprintFlag],

  [VCRemailAddress]

  FROM [dbo].[VehicleAnalysisStaging]

  )

  INSERT INTO @results (Line)

  SELECT

  CAST([VCRRecType] as VARCHAR(MAX)) + '|' +

  CAST([VCRCustNo] as VARCHAR(MAX)) + '|' +

  CAST([VCRCollationFlag] as VARCHAR(MAX)) + '|' +

  CAST([VCRPrintFlag] as VARCHAR(MAX)) + '|' +

  CAST([VCRNotUsed]as VARCHAR(MAX)) + '|' +

  CAST([VCRImageFlag] as VARCHAR(MAX)) + '|' +

  CAST([VCRTradingName] as VARCHAR(MAX)) + '|' +

  CAST([VCRContactName]as VARCHAR(MAX)) + '|' +

  CAST([VCRAddress1]as VARCHAR(MAX)) + '|' +

  CAST([VCRAddress2] as VARCHAR(MAX)) + '|' +

  CAST([VCRAddress3] as VARCHAR(MAX)) + '|' +

  CAST([VCRAddress4] as VARCHAR(MAX)) + '|' +

  CAST([VCROfficeFlag] as VARCHAR(MAX)) + '|' +

  CAST([VCRGrandTotalPeriod] as VARCHAR(MAX)) + '|' +

  CAST([VCRGrandTotalCosttoDate] as VARCHAR(MAX)) + '|' +

  CAST([VCRBillFromDate] as VARCHAR(MAX)) + '|' +

  CAST([VCRBillToDate] as VARCHAR(MAX)) + '|' +

  CAST([VCRReprintFlag] as VARCHAR(MAX)) + '|' +

  CAST([VCRemailAddress] as VARCHAR(MAX))

  FROM VCR

  ;WITH CCR AS (

  SELECT DISTINCT

  CCRRecType,

  CCRCostCentre,

  CCRBPSubtotalFuel,

  CCRSBPubTotalRepairs,

  CCRBPSubTotalLease,

  CCRBPSubtotaltoll,

  CCRBPSubTotalRUCRego,

  CCRBPSubTotalOtherExp,

  CCRBPSubtotalAccident,

  CCRBPSubToalMgmt,

  CCRBPSubTotal,

  CCRCTDSubtotalFuelOil,

  CCRCTDSubtotalRepairsandMaint,

  CCRCTDSubtotalLeaseCharges,

  CCRCTDSubtotalTollRoadCharges,

  CCRCTDSubtotalRUCandRego,

  CCRCTDSubtotalOtherExpenses,

  CCRCTDSubtotalAccidentInsurance,

  CCRCTDSubtotalMgmtCharges,

  CCRCTDSubtotalTotal,

  CCRTransactionFees

  FROM [dbo].[VehicleAnalysisStaging]

  )

  INSERT INTO @results (Line)

  SELECT

  CAST(CCRRecType as VARCHAR(MAX)) + '|' +

  CAST(CCRCostCentre as VARCHAR(MAX)) + '|' +

  CAST(CCRBPSubtotalFuel as VARCHAR(MAX)) + '|' +

  CAST(CCRSBPubTotalRepairs as VARCHAR(MAX)) + '|' +

  CAST(CCRBPSubTotalLease as VARCHAR(MAX)) + '|' +

  CAST(CCRBPSubtotaltoll as VARCHAR(MAX)) + '|' +

  CAST(CCRBPSubTotalRUCRego as VARCHAR(MAX)) + '|' +

  CAST(CCRBPSubTotalOtherExp as VARCHAR(MAX)) + '|' +

  CAST(CCRBPSubtotalAccident as VARCHAR(MAX)) + '|' +

  CAST(CCRBPSubToalMgmt as VARCHAR(MAX)) + '|' +

  CAST(CCRBPSubTotal as VARCHAR(MAX)) + '|' +

  CAST(CCRCTDSubtotalFuelOil as VARCHAR(MAX)) + '|' +

  CAST(CCRCTDSubtotalRepairsandMaint as VARCHAR(MAX)) + '|' +

  CAST(CCRCTDSubtotalLeaseCharges as VARCHAR(MAX)) + '|' +

  CAST(CCRCTDSubtotalTollRoadCharges as VARCHAR(MAX)) + '|' +

  CAST(CCRCTDSubtotalRUCandRego as VARCHAR(MAX)) + '|' +

  CAST(CCRCTDSubtotalOtherExpenses as VARCHAR(MAX)) + '|' +

  CAST(CCRCTDSubtotalAccidentInsurance as VARCHAR(MAX)) + '|' +

  CAST(CCRCTDSubtotalMgmtCharges as VARCHAR(MAX)) + '|' +

  CAST(CCRCTDSubtotalTotal as VARCHAR(MAX)) + '|' +

  CAST(CCRTransactionFees as VARCHAR(MAX))

  FROM CCR

  ;WITH CTD AS (

  SELECT -- no DISTINCT

  [CTDRecType],

  regnno,

  modelyear,

  vehicledesc,

  drivername,

  odometer,

  span,

  [Fuel/Oil],

  Repairs,

  LeaseCharges,

  TollCharges,

  [RUC/Rego],

  OtherExpenses,

  Accident,

  Managment,

  TotalexclGST,

  TotalCents,

  [Litres/100Km],

  [Litres/National],

  C02,

  TotalKms

  FROM [dbo].[VehicleAnalysisStaging]

  )

  INSERT INTO @results (Line)

  SELECT

  CAST([CTDRecType]as VARCHAR(MAX)) + '|' +

  CAST(regnno as VARCHAR(MAX)) + '|' +

  CAST(modelyear as VARCHAR(MAX)) + '|' +

  CAST(vehicledesc as VARCHAR(MAX)) + '|' +

  CAST(drivername as VARCHAR(MAX)) + '|' +

  CAST(odometer as VARCHAR(MAX)) + '|' +

  CAST(span as VARCHAR(MAX)) + '|' +

  CAST([Fuel/Oil] as VARCHAR(MAX)) + '|' +

  CAST(Repairs as VARCHAR(MAX)) + '|' +

  CAST(LeaseCharges as VARCHAR(MAX)) + '|' +

  CAST(TollCharges as VARCHAR(MAX)) + '|' +

  CAST([RUC/Rego] as VARCHAR(MAX)) + '|' +

  CAST(OtherExpenses as VARCHAR(MAX)) + '|' +

  CAST(Accident as VARCHAR(MAX)) + '|' +

  CAST(Managment as VARCHAR(MAX)) + '|' +

  CAST(TotalexclGST as VARCHAR(MAX)) + '|' +

  CAST(TotalCents as VARCHAR(MAX)) + '|' +

  CAST([Litres/100Km] as VARCHAR(MAX)) + '|' +

  CAST([Litres/National] as VARCHAR(MAX)) + '|' +

  CAST(C02 as VARCHAR(MAX)) + '|' +

  CAST(TotalKms as VARCHAR(MAX))

  FROM CTD

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden