Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Transact-SQL (2008)  Call Dynamics GP procedures from SQL

Author  Topic  JR1204 Starting Member 7 Posts Posted - 2014-03-27 : 09:14:18 I'm trying to enhance a stored procedure that takes accounts payable data from one database and inserts into Dynamics GP company database. The first half was working (The execute statement for taPMTransactionInsert), and that creates the record in the 'main' transaction screen in GP. However, I want to use the second half of the procedure (taPMDistributionInsert) to create the distribution lines in the detail screen in GP. Please help!! All the variables appear to be accounted for, but i am receiving this error: Error converting data type varchar to numeric. 'BOAUS', 'AP032714.31726' This is the CODE: EXECUTE @RCOUT = BOAUS.[dbo].[taPMDistributionInsert] ' 1', ' ', ' ', ' 0', '0.00000', '3200.00000', ' 6', ' 0', ' 0', ' 0', ' 0', ' ', ' ', '0.0000000', '01/01/1900', '01/01/1900', ' 0', ' ', '0.00000', '0.00000', ' 0' , @O_iErrorStateOUT OUTPUT , @oErrStringOUT OUTPUT...This is the Parm: @RCOUT int output, @O_iErrorStateOUT int output, @oErrStringOUT nvarchar(255) output HERE IS THE CODE USE [Operations6] GO /****** Object: StoredProcedure [dbo].[webAPExpensClaimUploadHDR] Script Date: 3/27/2014 7:54:24 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[webAPExpensClaimUploadHDR] @DBSRC nvarchar(15), @BATCHNO nvarchar(50), @RC int output as declare @SQL nvarchar(max) declare @ParmDef nvarchar(max) DECLARE @I_vBACHNUMB char(15)=@BatchNO DECLARE @I_vVCHNUMWK char(17)='' DECLARE @I_vVENDORID char(15)='' DECLARE @I_vDOCNUMBR char(20)='' DECLARE @I_vDOCTYPE smallint=0 DECLARE @I_vDOCAMNT numeric(19,5)=0 DECLARE @I_vDOCDATE datetime=convert(nvarchar(10), getdate(), 101) DECLARE @I_vPSTGDATE datetime=convert(nvarchar(10), getdate(), 101) DECLARE @I_vVADCDTRO char(15)='' DECLARE @I_vVADDCDPR char(15)='' DECLARE @I_vPYMTRMID char(20)='' DECLARE @I_vTAXSCHID char(15)='' DECLARE @I_vDUEDATE datetime='1/1/1900' DECLARE @I_vDSCDLRAM numeric(19,5)=0 DECLARE @I_vDISCDATE datetime='1/1/1900' DECLARE @I_vPRCHAMNT numeric(19,5)=0 DECLARE @I_vCHRGAMNT numeric(19,5)=0 DECLARE @I_vCASHAMNT numeric(19,5)=0 DECLARE @I_vCAMCBKID char(15)='' DECLARE @I_vCDOCNMBR char(20)='' DECLARE @I_vCAMTDATE datetime='1/1/1900' DECLARE @I_vCAMPMTNM char(20)='' DECLARE @I_vCHEKAMNT numeric(19,5)=0 DECLARE @I_vCHAMCBID char(15)='' DECLARE @I_vCHEKDATE datetime='1/1/1900' DECLARE @I_vCAMPYNBR char(20)='' DECLARE @I_vCRCRDAMT numeric(19,5)=0 DECLARE @I_vCCAMPYNM char(20)='' DECLARE @I_vCHEKNMBR char(20)='' DECLARE @I_vCARDNAME char(15)='' DECLARE @I_vCCRCTNUM char(20)='' DECLARE @I_vCRCARDDT datetime='1/1/1900' DECLARE @I_vCHEKBKID char(15)='' DECLARE @I_vTRXDSCRN char(30)='' DECLARE @I_vTRDISAMT numeric(19,5)=0 DECLARE @I_vTAXAMNT numeric(19,5)=0 DECLARE @I_vFRTAMNT numeric(19,5)=0 DECLARE @I_vTEN99AMNT numeric(19,5)=0 DECLARE @I_vMSCCHAMT numeric(19,5)=0 DECLARE @I_vPORDNMBR char(20)='' DECLARE @I_vSHIPMTHD char(15)='' DECLARE @I_vDISAMTAV numeric(19,5)=0 DECLARE @I_vDISTKNAM numeric(19,5)=0 DECLARE @I_vAPDSTKAM numeric(19,5)=0 DECLARE @I_vMDFUSRID char(15)='' DECLARE @I_vPOSTEDDT datetime='1/1/1900' DECLARE @I_vPTDUSRID char(15)='' DECLARE @I_vPCHSCHID char(15)='' DECLARE @I_vFRTSCHID char(15)='' DECLARE @I_vMSCSCHID char(15)='' DECLARE @I_vPRCTDISC numeric(19,2)=0 DECLARE @I_vTax_Date datetime='1/1/1900' DECLARE @I_vCURNCYID char(15)='' DECLARE @I_vXCHGRATE numeric(19,7)=0 DECLARE @I_vRATETPID char(15)='' DECLARE @I_vEXPNDATE datetime='1/1/1900' DECLARE @I_vEXCHDATE datetime='1/1/1900' DECLARE @I_vEXGTBDSC char(30)='' DECLARE @I_vEXTBLSRC char(50)='' DECLARE @I_vRATEEXPR smallint=0 DECLARE @I_vDYSTINCR smallint=0 DECLARE @I_vRATEVARC numeric(19,7)=0 DECLARE @I_vTRXDTDEF smallint=0 DECLARE @I_vRTCLCMTD smallint=0 DECLARE @I_vPRVDSLMT smallint=0 DECLARE @I_vDATELMTS smallint=0 DECLARE @I_vTIME1 datetime='1/1/1900' DECLARE @I_vBatchCHEKBKID char(15)='' DECLARE @I_vCREATEDIST smallint=0 DECLARE @I_vRequesterTrx smallint=0 DECLARE @I_vUSRDEFND1 char(50)='' DECLARE @I_vUSRDEFND2 char(50)='' DECLARE @I_vUSRDEFND3 char(50)='' DECLARE @I_vUSRDEFND4 varchar(8000)='' DECLARE @I_vUSRDEFND5 varchar(8000)='' DECLARE @O_iErrorState int=0 DECLARE @oErrString varchar(255)='' set @RC=0 set @I_vDOCTYPE=1 declare Batchcur cursor for select employid, Amount, ClaimName from webparamExpenseClaimhor where apbatchno = @batchno and DBSRC = @DBSRC and donotpostap = 0 open batchcur fetch batchcur into @I_vVendorID, @I_vDOCAMNT, @I_vTRXDSCRN while @@fetch_status = 0 begin set @I_vChrgamnt = @I_vDOCAMNT set @I_vPRCHAMNT = @I_vDOCAMNT -- DECLARE @I_vInc_Dec tinyint DECLARE @O_iPMNPYNBR varchar(21) ---- TODO: Remove hardcoded BOAUS EXECUTE @RC = boaus.[dbo].[taGetPMNextPaymentNumber] 1 ,@O_iPMNPYNBR OUTPUT ,@O_iErrorState OUTPUT set @I_vdocnumbr = @O_iPMNPYNBR set @I_vVCHNUMWK=@O_iPMNPYNBR set @SQL='EXECUTE @RCOUT = ' + rtrim(@dbsrc) + '.[dbo].[taPMTransactionInsert] ' + '''' +@I_vBACHNUMB+''', '+ '''' +@I_vVCHNUMWK+''', '+ '''' +@I_vVENDORID+''', '+ '''' +@I_vDOCNUMBR+''', '+ '''' +str(@I_vDOCTYPE)+''', '+ '''' + convert(varchar(50),@I_vDOCAMNT)+''', '+ '''' +convert(nvarchar(10), @I_vDOCDATE, 101) +''', '+ '''' +convert(nvarchar(10), @I_vPSTGDATE, 101) +''', '+ '''' +@I_vVADCDTRO+''', '+ '''' +@I_vVADDCDPR+''', '+ '''' +@I_vPYMTRMID+''', '+ '''' +@I_vTAXSCHID+''', '+ '''' +convert(nvarchar(10), @I_vDUEDATE, 101) +''', '+ '''' +str(@I_vDSCDLRAM)+''', '+ '''' +convert(nvarchar(10), @I_vDISCDATE, 101) +''', '+ '''' +convert(varchar(50),@I_vPRCHAMNT)+''', '+ '''' +convert(varchar(50),@I_vCHRGAMNT)+''', '+ '''' +convert(varchar(50),@I_vCASHAMNT)+''', '+ '''' +@I_vCAMCBKID+''', '+ '''' +@I_vCDOCNMBR+''', '+ '''' +convert(nvarchar(10), @I_vCAMTDATE, 101) +''', '+ '''' +@I_vCAMPMTNM+''', '+ '''' +convert(varchar(50),@I_vCHEKAMNT)+''', '+ '''' +@I_vCHAMCBID+''', '+ '''' +convert(nvarchar(10), @I_vCHEKDATE, 101) +''', '+ '''' +@I_vCAMPYNBR+''', '+ '''' +convert(varchar(50),@I_vCRCRDAMT)+''', ' + '''' +@I_vCCAMPYNM+''', '+ '''' +@I_vCHEKNMBR+''', '+ '''' +@I_vCARDNAME+''', '+ '''' +@I_vCCRCTNUM+''', '+ '''' +convert(nvarchar(10), @I_vCRCARDDT, 101) +''', '+ '''' +@I_vCHEKBKID+''', '+ '''' +@I_vTRXDSCRN+''', ' + '''' +convert(varchar(50),@I_vTRDISAMT)+''', '+ '''' +convert(varchar(50),@I_vTAXAMNT)+''', '+ '''' +convert(varchar(50),@I_vFRTAMNT)+''', '+ '''' +convert(varchar(50),@I_vTEN99AMNT)+''', '+ '''' +convert(varchar(50),@I_vMSCCHAMT)+''', '+ '''' +@I_vPORDNMBR+''', '+ '''' +@I_vSHIPMTHD+''', '+ '''' +str(@I_vDISAMTAV)+''', '+ '''' +str(@I_vDISTKNAM)+''', '+ '''' +str(@I_vAPDSTKAM)+''', '+ '''' +@I_vMDFUSRID+''', '+ '''' +convert(nvarchar(10), @I_vPOSTEDDT, 101) +''', '+ '''' +@I_vPTDUSRID+''', '+ '''' +@I_vPCHSCHID+''', '+ '''' +@I_vFRTSCHID+''', '+ '''' +@I_vMSCSCHID+''', '+ '''' +str(@I_vPRCTDISC)+''', '+ '''' +convert(nvarchar(10), @I_vTax_Date, 101) +''', '+ '''' +@I_vCURNCYID+''', '+ '''' +convert(varchar(50), @I_vXCHGRATE)+''', '+ '''' +@I_vRATETPID+''', '+ '''' +convert(nvarchar(10), @I_vEXPNDATE, 101) +''', '+ '''' +convert(nvarchar(10), @I_vEXCHDATE, 101) +''', '+ '''' +@I_vEXGTBDSC+''', '+ '''' +@I_vEXTBLSRC+''', '+ '''' +str(@I_vRATEEXPR)+''', '+ '''' +str(@I_vDYSTINCR)+''', '+ '''' +str(@I_vRATEVARC)+''', '+ '''' +str(@I_vTRXDTDEF)+''', '+ '''' +str(@I_vRTCLCMTD)+''', '+ '''' +str(@I_vPRVDSLMT)+''', '+ '''' +str(@I_vDATELMTS)+''', '+ '''' +convert(nvarchar(10), @I_vTIME1, 101) +''', ' + '''' +@I_vBatchCHEKBKID+''', ' + '''' +str(@I_vCREATEDIST)+''', '+ '''' +str(@I_vRequesterTrx)+''', '+ '''' +@I_vUSRDEFND1+''', '+ '''' +@I_vUSRDEFND2+''', '+ '''' +@I_vUSRDEFND3+''', '+ '''' +@I_vUSRDEFND4+''', '+ '''' +@I_vUSRDEFND5+''' , @O_iErrorStateOUT OUTPUT , @oErrStringOUT OUTPUT' set @ParmDef=N'@RCOUT int OUTPUT, @O_iErrorStateOUT int OUTPUT, @oErrStringOUT NVarchar(255) OUTPUT' EXECUTE sp_executesql @SQL, @ParmDef, @RCOUT=@RC OUTPUT, @O_iErrorStateOUT =@O_iErrorState, @oErrStringOUT =@oErrString if @RC<> 0 break --Need to call this procedure twice: once for debit, once for credit. DECLARE @I_vACTINDX int=0 -- This will come from the posting config param DECLARE @I_vDEBITAMT numeric(19,5)=0 DECLARE @I_vCRDTAMNT numeric(19,5)=0 DECLARE @I_vDISTTYPE smallint=0 DECLARE @I_vCURRNIDX smallint=0 DECLARE @I_vDECPLCUR int=0 DECLARE @I_vTRXSORCE char(13)='' DECLARE @I_vDistRef char(30)='' DECLARE @I_vEXGTBLID char(15)='' DECLARE @I_vORDBTAMT numeric(9,5)=0 DECLARE @I_vORCRDAMT numeric(9,5)=0 set @I_vDOCTYPE=1 set @I_vVENDORID='' set @I_vVCHNUMWK='' set @I_vCURNCYID='' set @I_vPTDUSRID='' set @I_vRATETPID='' set @I_vXCHGRATE=0 set @I_vEXCHDATE='1/1/1900' set @I_vEXPNDATE='1/1/1900' set @I_vTIME1='1/1/1900' set @I_vRTCLCMTD=0 set @I_vDEBITAMT=0 set @O_iErrorState=0 set @oErrString='' set @RC = 0 set @O_iErrorState=0 set @oErrString='' Set @I_vDISTTYPE=6 set @I_vCRDTAMNT=@I_vDOCAMNT set @SQL= 'EXECUTE @RCOUT = ' +rtrim(@dbsrc) + '.[dbo].[taPMDistributionInsert] ' + '''' +str(@I_vDOCTYPE)+''', '+ '''' +@I_vVCHNUMWK+''', '+ '''' +@I_vVENDORID+''', '+ '''' +str(@I_vACTINDX)+''', '+ '''' +convert(varchar(50),@I_vDEBITAMT)+''', '+ '''' +convert(varchar(50),@I_vCRDTAMNT)+''', '+ '''' +str(@I_vDISTTYPE)+''', '+ '''' +str(@I_vCURRNIDX)+''', '+ '''' +str(@I_vDECPLCUR)+''', '+ '''' +str(@I_vTRXSORCE)+''', '+ '''' +str(@I_vDistRef)+''', '+ '''' +@I_vPTDUSRID+''', '+ '''' +@I_vRATETPID+''', '+ '''' +convert(nvarchar(50), @I_vXCHGRATE)+''', '+ '''' +convert(nvarchar(10), @I_vEXCHDATE, 101) +''', '+ '''' +convert(nvarchar(10), @I_vEXPNDATE, 101) +''', '+ '''' +str(@I_vEXGTBLID)+''', '+ '''' +@I_vRATETPID+''', '+ '''' +convert(varchar(50),@I_vORDBTAMT)+''', '+ '''' +convert(varchar(50),@I_vORCRDAMT)+''', '+ '''' +str(@I_vRTCLCMTD)+''' , @O_iErrorStateOUT OUTPUT , @oErrStringOUT OUTPUT' set @ParmDef=N'@RCOUT int output, @O_iErrorStateOUT int output, @oErrStringOUT nvarchar(255) output' print 'This is the CODE: ' + @SQL + '...This is the Parm: ' + @ParmDef EXECUTE sp_executesql @SQL, @ParmDef, @RCOUT=@RC OUTPUT, @O_iErrorStateOUT=@O_iErrorState, @oErrStringOUT= @oErrString if @RC<> 0 break fetch batchcur into @I_vVendorID, @I_vDocamnt, @I_vTRXDSCRN end close batchcur deallocate batchcur