SQL Server messaggio 245 - Conversione non riuscita durante la conversione del valore varchar 'indirect' nel tipo di dati INT durante l'esecuzione di Close Project Year at Actual (AL). CPA.00)

Questo articolo fornisce una risoluzione per risolvere l'errore SQL Server Message 245 - Conversion failed che si verifica in Microsoft Dynamics SL.

Si applica a: Microsoft Dynamics SL
Numero KB originale: 2953415

Sintomi

Quando si esegue Project Allocator (PA. PRO.00) in modalità finale o quando si esegue l'anno di chiusura del progetto in fase effettiva (AL. CPA.00), è possibile che venga visualizzato l'errore seguente:

SQL Server Messaggio 245: [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversione non riuscita durante la conversione del valore varchar 'ACCOUNT CATAGORY' nel tipo di dati int.value 'ACCOUNT CATAGORY' in tipo di dati int.

Risoluzione

Eseguire lo script SQL seguente in ogni database dell'applicazione.

-- FIX for bug 25152 for 2011 FP1
--
-- Run against SL Application databases

/****** Object: View [dbo].[PJvAIC_source] Script Date: 12/12/2012 13:30:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

alter VIEW [dbo].[PJvAIC_source]
AS
-- This is part of a group of SQL tables, views, and functions used by the PJAIC SQL stored procedure to
-- generate actual indirect cost amounts.

select pt.alloc_calc_type,
 pt.alloc_method_cd,
 pt.alloc_method1_cd,
 pt.alloc_rate,
 amount_01 = isnull(s.amount_01, 0), amount_02 = isnull(s.amount_02, 0), amount_03 = isnull(s.amount_03, 0),
 amount_04 = isnull(s.amount_04, 0), amount_05 = isnull(s.amount_05, 0), amount_06 = isnull(s.amount_06, 0),
 amount_07 = isnull(s.amount_07, 0), amount_08 = isnull(s.amount_08, 0), amount_09 = isnull(s.amount_09, 0),
 amount_10 = isnull(s.amount_10, 0), amount_11 = isnull(s.amount_11, 0), amount_12 = isnull(s.amount_12, 0),
 amount_13 = isnull(s.amount_13, 0), amount_14 = isnull(s.amount_14, 0), amount_15 = isnull(s.amount_15, 0),
 pt.begin_step,
 pt.end_step,
 pt.emp_CpnyId,
 pt.emp_gl_subacct,
 pt.fsyear_num,
 pt.from_method,
 pt.ptd_indirectgrp,
 pt.ytd_indirectgrp,
 pt.pjt_entity,
 post_acct_ptd_indirectgrp = g.ptd_indirectgrp,
 post_acct_ytd_indirectgrp = g.ytd_indirectgrp,
 pt.project,
 project_cpnyid = pt.CpnyId,
 pt.src_acct,
 pt.src_CpnyId,
 pt.src_gl_subacct,
 pt.step_number
from ( -- Need to union the project allocation method and the second project allocation method
 select distinct p.project, t.pjt_entity, p.alloc_method_cd, alloc_method1_cd = p.alloc_method_cd, a.alloc_calc_type,
 a.alloc_rate, a.begin_step, a.end_step, from_method = a.al_id10, a.ptd_indirectgrp, a.ytd_indirectgrp,
 a.post_acct, a.step_number, p.CpnyId, s.fsyear_num, s.src_acct, s.src_CpnyId, s.src_gl_subacct, s.emp_CpnyId, s.emp_gl_subacct
 from PJPROJ p with (nolock)
 inner join PJPENT t with (nolock)
 on t.project = p.project
 inner join PJALLOC a with (nolock)
 on a.alloc_method_cd = p.alloc_method_cd
 -- Link to PJINDSRC without using step_number to get all the fiscal year numbers and source account cateogies possible for each step
 inner join PJINDSRC s with (nolock)
 on s.project = p.project
 and s.pjt_entity = t.pjt_entity
 and s.alloc_method_cd = a.alloc_method_cd
 where len(rtrim(p.alloc_method_cd)) <> 0
 and a.alloc_basis = 'A' -- Only interested in steps based on amounts
 union
 select distinct p.project, t.pjt_entity, p.alloc_method2_cd, p.alloc_method_cd, a.alloc_calc_type,
 a.alloc_rate, a.begin_step, a.end_step, from_method = a.al_id10, a.ptd_indirectgrp, a.ytd_indirectgrp,
 a.post_acct, a.step_number, p.CpnyId, s.fsyear_num, s.src_acct, s.src_CpnyId, s.src_gl_subacct, s.emp_CpnyId, s.emp_gl_subacct
 from PJPROJ p with (nolock)
 inner join PJPENT t with (nolock)
 on t.project = p.project 
 inner join PJALLOC a with (nolock)
 on a.alloc_method_cd = p.alloc_method2_cd
 inner join PJINDSRC s with (nolock)
 on s.project = p.project
 and s.pjt_entity = t.pjt_entity
 and s.alloc_method_cd = a.alloc_method_cd
 where len(rtrim(p.alloc_method2_cd)) <> 0
 and a.alloc_basis = 'A'
 ) pt
-- Link to PJINDSRC once again, this time just to get amounts where they exists
left join PJINDSRC s with (nolock)
 on s.fsyear_num = pt.fsyear_num
 and s.project = pt.project
 and s.pjt_entity = pt.pjt_entity
 and s.src_CpnyId = pt.src_CpnyId
 and s.alloc_method_cd = pt.alloc_method_cd
 and s.step_number = pt.step_number
 and s.src_acct = pt.src_acct
 and s.src_gl_subacct = pt.src_gl_subacct
 and s.emp_CpnyId = pt.emp_CpnyId
 and s.emp_gl_subacct = pt.emp_gl_subacct
-- Link to get the post account category indirect groups
left join PJvPJACCT_IndirectGrps g
 on g.acct = dbo.PJfMask_acct(pt.post_acct, s.src_acct)

GO

go
ALTER VIEW [dbo].[PJvAIC_source_summary] 
AS 
-- This is part of a group of SQL tables, views, and functions used by the PJAIC_summary SQL stored procedure to 
-- generate actual indirect cost amounts. 

-- Dependent on: PJvPJINDSRC_summary, PJvPJACCT_IndirectGrps 

select pt.alloc_calc_type, 
 pt.alloc_method_cd, 
 pt.alloc_method1_cd, 
 pt.alloc_rate, 
 amount_01 = isnull(s.amount_01, 0), amount_02 = isnull(s.amount_02, 0), amount_03 = isnull(s.amount_03, 0), 
 amount_04 = isnull(s.amount_04, 0), amount_05 = isnull(s.amount_05, 0), amount_06 = isnull(s.amount_06, 0), 
 amount_07 = isnull(s.amount_07, 0), amount_08 = isnull(s.amount_08, 0), amount_09 = isnull(s.amount_09, 0), 
 amount_10 = isnull(s.amount_10, 0), amount_11 = isnull(s.amount_11, 0), amount_12 = isnull(s.amount_12, 0), 
 amount_13 = isnull(s.amount_13, 0), amount_14 = isnull(s.amount_14, 0), amount_15 = isnull(s.amount_15, 0), 
 pt.begin_step, 
 pt.end_step, 
 pt.fsyear_num, 
 pt.from_method, 
 pt.ptd_indirectgrp, 
 pt.ytd_indirectgrp, 
 pt.pjt_entity, 
 post_acct_ptd_indirectgrp = g.ptd_indirectgrp, 
 post_acct_ytd_indirectgrp = g.ytd_indirectgrp, 
 pt.project, 
 project_cpnyid = pt.CpnyId, 
 pt.src_acct, 
 pt.step_number 
from ( -- Need to union the project allocation method and the second project allocation method 
 select distinct p.project, t.pjt_entity, p.alloc_method_cd, alloc_method1_cd = p.alloc_method_cd, a.alloc_calc_type, 
 a.alloc_rate, a.begin_step, a.end_step, from_method = a.al_id10, -- HERE
 a.ptd_indirectgrp, a.ytd_indirectgrp, 
 a.post_acct, a.step_number, p.CpnyId, s.fsyear_num, s.src_acct 
 from PJPROJ p with (nolock) 
 inner join PJPENT t with (nolock) 
 on t.project = p.project 
 inner join PJALLOC a with (nolock) 
 on a.alloc_method_cd = p.alloc_method_cd 
 -- Link to PJvINDSRC_summary without using step_number to get all the fiscal year numbers and source account cateogies possible for each step 
 inner join PJvPJINDSRC_summary s with (nolock) 
 on s.project = p.project 
 and s.pjt_entity = t.pjt_entity 
 and s.alloc_method_cd = a.alloc_method_cd 
 where len(rtrim(p.alloc_method_cd)) <> 0 
 and a.alloc_basis = 'A' -- Only interested in steps based on amounts 
 union 
 select distinct p.project, t.pjt_entity, p.alloc_method2_cd, p.alloc_method_cd, a.alloc_calc_type, 
 a.alloc_rate, a.begin_step, a.end_step, from_method = a.al_id10, -- HERE
 a.ptd_indirectgrp, a.ytd_indirectgrp, 
 a.post_acct, a.step_number, p.CpnyId, s.fsyear_num, s.src_acct 
 from PJPROJ p with (nolock) 
 inner join PJPENT t with (nolock) 
 on t.project = p.project 
 inner join PJALLOC a with (nolock) 
 on a.alloc_method_cd = p.alloc_method2_cd 
 inner join PJvPJINDSRC_summary s with (nolock) 
 on s.project = p.project 
 and s.pjt_entity = t.pjt_entity 
 and s.alloc_method_cd = a.alloc_method_cd 
 where len(rtrim(p.alloc_method2_cd)) <> 0 
 and a.alloc_basis = 'A' 
 ) pt 
-- Link to PJINDSRC once again, this time just to get amounts where they exists 
left join PJvPJINDSRC_summary s with (nolock) 
 on s.fsyear_num = pt.fsyear_num 
 and s.project = pt.project 
 and s.pjt_entity = pt.pjt_entity 
 and s.src_acct = pt.src_acct 
 and s.alloc_method_cd = pt.alloc_method_cd 
 and s.step_number = pt.step_number 
-- Link to get the post account category indirect groups 
left join PJvPJACCT_IndirectGrps g 
 on g.acct = dbo.PJfMask_acct(pt.post_acct, s.src_acct) 

GO