Msg 257, Level 16, State 3, - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Msg 257, Level 16, State 3,

Author

Topic

tmackii
Starting Member

2 Posts

Posted - 2006-09-13 : 14:55:48

I updated the error message to the correct one.

I have created a trigger and cannot identify what is causing the error message below.

Msg 257, Level 16, State 3, Procedure TR_POP10100_Corp_Insert, Line 158
Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.

CREATE TRIGGER dbo.TR_POP10100_Corp_Insert ON [dbo].[POP10100]
FOR INSERT
AS
begin
/* Declare the local variables */
declare @MASTERDB char(15),
@O_TODAY char(10),
@O_DUMMYDT char(10),
@PONUMBER char(17),
@POSTATUS smallint,
@STATGRP smallint,
@POTYPE smallint,
@USER2ENT char(15),
@CONFIRM1 char(21),
@DOCDATE datetime,
@LSTEDTDT datetime,
@LSTPRTDT datetime,
@PRMDATE datetime,
@PRMSHPDTE datetime,
@REQDATE datetime,
@REQTNDT datetime,
@SHIPMTHD char(15),
@TXRGNNUM char(25),
@REMSUBTO numeric,
@SUBTOTAL numeric,
@TRDISAMT numeric,
@FRTAMNT numeric,
@MSCCHAMT numeric,
@TAXAMNT numeric,
@VENDORID char(15),
@VENDNAME char(65),
@MINORDER numeric,
@VADCDPAD char(15),
@CMPANYID smallint,
@PRBTADCD char(15),
@PRSTADCD char(15),
@CMPNYNAM char(65),
@CONTACT char(61),
@ADDRESS1 char(61),
@ADDRESS2 char(61),
@ADDRESS3 char(61),
@CITY char(35),
@STATE char(29),
@ZIPCODE char(11),
@CCode char(7),
@COUNTRY char(61),
@PHONE1 char(21),
@PHONE2 char(21),
@PHONE3 char(21),
@FAX char(21),
@PYMTRMID char(21),
@DSCDLRAM numeric,
@DSCPCTAM smallint,
@DISAMTAV numeric,
@DISCDATE datetime,
@DUEDATE datetime,
@TRDPCTPR numeric,
@CUSTNMBR char(15),
@TIMESPRT smallint,
@CREATDDT datetime,
@MODIFDT datetime,
@iStatus int,
@iGetNextNoteIdxErrState int,
@PONOTIDS_1 numeric,
@PONOTIDS_2 numeric,
@PONOTIDS_3 numeric,
@PONOTIDS_4 numeric,
@PONOTIDS_5 numeric,
@PONOTIDS_6 numeric,
@PONOTIDS_7 numeric,
@PONOTIDS_8 numeric,
@PONOTIDS_9 numeric,
@PONOTIDS_10 numeric,
@PONOTIDS_11 numeric,
@PONOTIDS_12 numeric,
@PONOTIDS_13 numeric,
@PONOTIDS_14 numeric,
@PONOTIDS_15 numeric,
@COMMNTID char(15),
@CANCSUB numeric,
@CURNCYID char(15),
@CURRNIDX smallint,
@RATETPID char(15),
@EXGTBLID char(15),
@XCHGRATE numeric,
@EXCHDATE datetime,
@TIME1 datetime,
@RATECALC smallint,
@DENXRATE numeric,
@MCTRXSTT smallint,
@OREMSUBT numeric,
@ORSUBTOT numeric,
@Originating_Canceled_Sub numeric,
@ORTDISAM numeric,
@ORFRTAMT numeric,
@OMISCAMT numeric,
@ORTAXAMT numeric,
@ORDDLRAT numeric,
@ODISAMTAV numeric,
@BUYERID char(15),
@ONORDAMT numeric,
@ORORDAMT numeric,
@HOLD tinyint,
@ONHOLDDATE datetime,
@ONHOLDBY char(15),
@HOLDREMOVEDATE datetime,
@HOLDREMOVEBY char(15),
@ALLOWSOCMTS tinyint,
@DISGRPER smallint,
@DUEGRPER smallint,
@Revision_Number smallint,
@Change_Order_Flag smallint,
@PO_Field_Changes binary(4),
@PO_Status_Orig smallint,
@TAXSCHID char(15),
@TXSCHSRC smallint,
@TXENGCLD tinyint,
@BSIVCTTL tinyint,
@Purchase_Freight_Taxable smallint,
@Purchase_Misc_Taxable smallint,
@FRTSCHID char(15),
@MSCSCHID char(15),
@FRTTXAMT numeric,
@ORFRTTAX numeric,
@MSCTXAMT numeric,
@ORMSCTAX numeric,
@BCKTXAMT numeric,
@OBTAXAMT numeric,
@BackoutFreightTaxAmt numeric,
@OrigBackoutFreightTaxAmt numeric,
@BackoutMiscTaxAmt numeric,
@OrigBackoutMiscTaxAmt numeric,
@Flags smallint,
@BackoutTradeDiscTax numeric,
@OrigBackoutTradeDiscTax numeric,
@POPCONTNUM char(21),
@CONTENDDTE datetime,
@CNTRLBLKTBY smallint,
@PURCHCMPNYNAM char(65),
@PURCHCONTACT char(61),
@PURCHADDRESS1 char(61),
@PURCHADDRESS2 char(61),
@PURCHADDRESS3 char(61),
@PURCHCITY char(35),
@PURCHSTATE char(29),
@PURCHZIPCODE char(11),
@PURCHCCode char(7),
@PURCHCOUNTRY char(61),
@PURCHPHONE1 char(21),
@PURCHPHONE2 char(21),
@PURCHPHONE3 char(21),
@PURCHFAX char(21),
@BLNKTLINEEXTQTYSUM numeric,
@CBVAT tinyint

/* Assign default values to local variables */
select @O_TODAY = convert(char(10), getdate(), 101),
@O_DUMMYDT = convert(char(10), '1900-01-01', 100),
@PONUMBER = '',
@POSTATUS = '',
@STATGRP = '',
@POTYPE = '',
@USER2ENT = '',
@CONFIRM1 = '',
@DOCDATE = '',
@LSTEDTDT = '',
@LSTPRTDT = '',
@PRMDATE = '',
@PRMSHPDTE = '',
@REQDATE = '',
@REQTNDT = '',
@SHIPMTHD = '',
@TXRGNNUM = '',
@REMSUBTO = '',
@SUBTOTAL = '',
@TRDISAMT = '',
@FRTAMNT = '',
@MSCCHAMT = '',
@TAXAMNT = '',
@VENDORID = '',
@VENDNAME = '',
@MINORDER = '',
@VADCDPAD = '',
@CMPANYID = '',
@PRBTADCD = '',
@PRSTADCD = '',
@CMPNYNAM = '',
@CONTACT = '',
@ADDRESS1 = '',
@ADDRESS2 = '',
@ADDRESS3 = '',
@CITY = '',
@STATE = '',
@ZIPCODE = '',
@CCode = '',
@COUNTRY = '',
@PHONE1 = '',
@PHONE2 = '',
@PHONE3 = '',
@FAX = '',
@PYMTRMID = '',
@DSCDLRAM = '',
@DSCPCTAM = '',
@DISAMTAV = '',
@DISCDATE = '',
@DUEDATE = '',
@TRDPCTPR = '',
@CUSTNMBR = '',
@TIMESPRT = '',
@CREATDDT = '',
@MODIFDT = '',
@iStatus = '',
@iGetNextNoteIdxErrState = 0,
@PONOTIDS_1 = '',
@PONOTIDS_2 = '',
@PONOTIDS_3 = '',
@PONOTIDS_4 = '',
@PONOTIDS_5 = '',
@PONOTIDS_6 = '',
@PONOTIDS_7 = '',
@PONOTIDS_8 = '',
@PONOTIDS_9 = '',
@PONOTIDS_10 = '',
@PONOTIDS_11 = '',
@PONOTIDS_12 = '',
@PONOTIDS_13 = '',
@PONOTIDS_14 = '',
@PONOTIDS_15 = '',
@COMMNTID = '',
@CANCSUB = '',
@CURNCYID = '',
@CURRNIDX = '',
@RATETPID = '',
@EXGTBLID = '',
@XCHGRATE = '',
@EXCHDATE = '',
@TIME1 = '',
@RATECALC = '',
@DENXRATE = '',
@MCTRXSTT = '',
@OREMSUBT = '',
@ORSUBTOT = '',
@Originating_Canceled_Sub = '',
@ORTDISAM = '',
@ORFRTAMT = '',
@OMISCAMT = '',
@ORTAXAMT = '',
@ORDDLRAT = '',
@ODISAMTAV = '',
@BUYERID = '',
@ONORDAMT = '',
@ORORDAMT = '',
@HOLD = '',
@ONHOLDDATE = '',
@ONHOLDBY = '',
@HOLDREMOVEDATE = '',
@HOLDREMOVEBY = '',
@ALLOWSOCMTS = '',
@DISGRPER = '',
@DUEGRPER = '',
@Revision_Number = '',
@Change_Order_Flag = '',
@PO_Field_Changes = '',
@PO_Status_Orig = '',
@TAXSCHID = '',
@TXSCHSRC = '',
@TXENGCLD = '',
@BSIVCTTL = '',
@Purchase_Freight_Taxable = '',
@Purchase_Misc_Taxable = '',
@FRTSCHID = '',
@MSCSCHID = '',
@FRTTXAMT = '',
@ORFRTTAX = '',
@MSCTXAMT = '',
@ORMSCTAX = '',
@BCKTXAMT = '',
@OBTAXAMT = '',
@BackoutFreightTaxAmt = '',
@OrigBackoutFreightTaxAmt = '',
@BackoutMiscTaxAmt = '',
@OrigBackoutMiscTaxAmt = '',
@Flags = '',
@BackoutTradeDiscTax = '',
@OrigBackoutTradeDiscTax = '',
@POPCONTNUM = '',
@CONTENDDTE = '',
@CNTRLBLKTBY = '',
@PURCHCMPNYNAM = '',
@PURCHCONTACT = '',
@PURCHADDRESS1 = '',
@PURCHADDRESS2 = '',
@PURCHADDRESS3 = '',
@PURCHCITY = '',
@PURCHSTATE = '',
@PURCHZIPCODE = '',
@PURCHCCode = '',
@PURCHCOUNTRY = '',
@PURCHPHONE1 = '',
@PURCHPHONE2 = '',
@PURCHPHONE3 = '',
@PURCHFAX = '',
@BLNKTLINEEXTQTYSUM = '',
@CBVAT = ''

/* Get the POP10100 table values being inserted/updated */
select @PONUMBER = isnull(PONUMBER, ''),
@USER2ENT = isnull(USER2ENT, ''),
@DOCDATE = isnull(DOCDATE,''),
@LSTEDTDT = isnull(LSTEDTDT, ''),
@LSTPRTDT = isnull(LSTPRTDT, ''),
@PRMDATE = isnull(PRMDATE, ''),
@PRMSHPDTE = isnull(PRMSHPDTE, ''),
@REQDATE = isnull(REQDATE, ''),
@REQTNDT = isnull(REQTNDT, ''),
@SHIPMTHD = isnull(SHIPMTHD, ''),
@TXRGNNUM = isnull(TXRGNNUM, ''),
@REMSUBTO = isnull(REMSUBTO, ''),
@SUBTOTAL = isnull(SUBTOTAL, ''),
@TRDISAMT = isnull(TRDISAMT, ''),
@FRTAMNT = isnull(FRTAMNT, ''),
@MSCCHAMT = isnull(MSCCHAMT, ''),
@TAXAMNT = isnull(TAXAMNT, ''),
@VENDORID = isnull(VENDORID, ''),
@VENDNAME = rtrim (isnull(VENDNAME, '')),
@MINORDER = isnull(MINORDER, ''),
@VADCDPAD = isnull(VADCDPAD, ''),
@CMPANYID = isnull(CMPANYID, ''),
@PRBTADCD = isnull(PRBTADCD, ''),
@PRSTADCD = isnull(PRSTADCD, ''),
@CMPNYNAM = rtrim(isnull(CMPNYNAM, '')),
@CONTACT = rtrim(isnull(CONTACT, '')),
@ADDRESS1 = rtrim(isnull(ADDRESS1, '')),
@ADDRESS2 = rtrim(isnull(ADDRESS2, '')),
@ADDRESS3 = rtrim(isnull(ADDRESS3, '')),
@CITY = rtrim(isnull(CITY, '')),
@STATE = rtrim(isnull(STATE, '')),
@ZIPCODE = isnull(ZIPCODE, ''),
@CCode = isnull(CCode, ''),
@COUNTRY = isnull(COUNTRY, ''),
@PHONE1 = isnull(PHONE1, ''),
@PHONE2 = isnull(PHONE2, ''),
@PHONE3 = isnull(PHONE3, ''),
@FAX = isnull(FAX, ''),
@PYMTRMID = isnull(PYMTRMID, ''),
@DSCDLRAM = isnull(DSCDLRAM, ''),
@DSCPCTAM = isnull(DSCPCTAM, ''),
@DISAMTAV = isnull(DISAMTAV, ''),
@DISCDATE = isnull(DISCDATE, ''),
@DUEDATE = isnull(DUEDATE, ''),
@CUSTNMBR = isnull(CUSTNMBR, ''),
@CREATDDT = isnull(CREATDDT, ''),
@MODIFDT = isnull(MODIFDT, ''),
@COMMNTID = isnull(COMMNTID, ''),
@OREMSUBT = isnull(OREMSUBT, ''),
@ORSUBTOT = isnull(ORSUBTOT, ''),
@Originating_Canceled_Sub = isnull(Originating_Canceled_Sub, ''),
@ORTDISAM = isnull(ORTDISAM, ''),
@ORFRTAMT = isnull(ORFRTAMT, ''),
@OMISCAMT = isnull(OMISCAMT, ''),
@ORTAXAMT = isnull(ORTAXAMT, ''),
@ORDDLRAT = isnull(ORDDLRAT, ''),
@ODISAMTAV = isnull(ODISAMTAV, ''),
@BUYERID = isnull(BUYERID, ''),
@ONORDAMT = isnull(ONORDAMT, ''),
@ORORDAMT = isnull(ORORDAMT, ''),
@TAXSCHID = isnull(TAXSCHID, ''),
@TXSCHSRC = isnull(TXSCHSRC, ''),
@PURCHCMPNYNAM = rtrim(isnull(PURCHCMPNYNAM, '')),
@PURCHCONTACT = rtrim(isnull(PURCHCONTACT, '')),
@PURCHADDRESS1 = rtrim(isnull(PURCHADDRESS1, '')),
@PURCHADDRESS2 = rtrim(isnull(PURCHADDRESS2, '')),
@PURCHADDRESS3 = rtrim(isnull(PURCHADDRESS3, '')),
@PURCHCITY = rtrim(isnull(PURCHCITY, '')),
@PURCHSTATE = isnull(PURCHSTATE, ''),
@PURCHZIPCODE = isnull(PURCHZIPCODE, ''),
@PURCHCCode = isnull(PURCHCCode, ''),
@PURCHCOUNTRY = isnull(PURCHCOUNTRY, ''),
@PURCHPHONE1 = isnull(PURCHPHONE1, ''),
@PURCHPHONE2 = isnull(PURCHPHONE2, ''),
@PURCHPHONE3 = isnull(PURCHPHONE3, ''),
@PURCHFAX = isnull(PURCHFAX, '')
from inserted

if (@PONUMBER is null) or (@PONUMBER = '')
return

/****** Begin Purchase Order Record ******/
select @PONUMBER = isnull (PONUMBER, '') from POP10100 where PONUMBER = @PONUMBER
select @PONUMBER = @PONUMBER
end
if @PONUMBER = '' or not exists(select 1 from POP10100 where PONUMBER = @PONUMBER)

/* Check for existence of purchase order record */
begin

/*** Get the PO Note Index 1 thru 11 ***/
select @CMPANYID = CMPANYID from DYNAMICS..SY01500 (nolock) where INTERID = db_name()
exec @iStatus = DYNAMICS..smGetNextNoteIndex
@I_sCompanyID = @CMPANYID,
@I_iSQLSessionID = 0,
@O_mNoteIndex = @PONOTIDS_1 output,
@O_iErrorState = @iGetNextNoteIdxErrState output

exec @iStatus = DYNAMICS..smGetNextNoteIndex
@I_sCompanyID = @CMPANYID,
@I_iSQLSessionID = 0,
@O_mNoteIndex = @PONOTIDS_2 output,
@O_iErrorState = @iGetNextNoteIdxErrState output

exec @iStatus = DYNAMICS..smGetNextNoteIndex
@I_sCompanyID = @CMPANYID,
@I_iSQLSessionID = 0,
@O_mNoteIndex = @PONOTIDS_3 output,
@O_iErrorState = @iGetNextNoteIdxErrState output

exec @iStatus = DYNAMICS..smGetNextNoteIndex
@I_sCompanyID = @CMPANYID,
@I_iSQLSessionID = 0,
@O_mNoteIndex = @PONOTIDS_4 output,
@O_iErrorState = @iGetNextNoteIdxErrState output

exec @iStatus = DYNAMICS..smGetNextNoteIndex
@I_sCompanyID = @CMPANYID,
@I_iSQLSessionID = 0,
@O_mNoteIndex = @PONOTIDS_5 output,
@O_iErrorState = @iGetNextNoteIdxErrState output

exec @iStatus = DYNAMICS..smGetNextNoteIndex
@I_sCompanyID = @CMPANYID,
@I_iSQLSessionID = 0,
@O_mNoteIndex = @PONOTIDS_6 output,
@O_iErrorState = @iGetNextNoteIdxErrState output

exec @iStatus = DYNAMICS..smGetNextNoteIndex
@I_sCompanyID = @CMPANYID,
@I_iSQLSessionID = 0,
@O_mNoteIndex = @PONOTIDS_7 output,
@O_iErrorState = @iGetNextNoteIdxErrState output

exec @iStatus = DYNAMICS..smGetNextNoteIndex
@I_sCompanyID = @CMPANYID,
@I_iSQLSessionID = 0,
@O_mNoteIndex = @PONOTIDS_8 output,
@O_iErrorState = @iGetNextNoteIdxErrState output

exec @iStatus = DYNAMICS..smGetNextNoteIndex
@I_sCompanyID = @CMPANYID,
@I_iSQLSessionID = 0,
@O_mNoteIndex = @PONOTIDS_9 output,
@O_iErrorState = @iGetNextNoteIdxErrState output

exec @iStatus = DYNAMICS..smGetNextNoteIndex
@I_sCompanyID = @CMPANYID,
@I_iSQLSessionID = 0,
@O_mNoteIndex = @PONOTIDS_10 output,
@O_iErrorState = @iGetNextNoteIdxErrState output

exec @iStatus = DYNAMICS..smGetNextNoteIndex
@I_sCompanyID = @CMPANYID,
@I_iSQLSessionID = 0,
@O_mNoteIndex = @PONOTIDS_11 output,
@O_iErrorState = @iGetNextNoteIdxErrState output

/* Check for null value being returned and set to default values */
select @PONOTIDS_1 = case
when @PONOTIDS_1 is null then 0
else @PONOTIDS_1
end,
@PONOTIDS_2 = case
when @PONOTIDS_2 is null then 0
else @PONOTIDS_2
end,
@PONOTIDS_3 = case
when @PONOTIDS_3 is null then 0
else @PONOTIDS_3
end,
@PONOTIDS_4 = case
when @PONOTIDS_4 is null then 0
else @PONOTIDS_4
end,
@PONOTIDS_5 = case
when @PONOTIDS_5 is null then 0
else @PONOTIDS_5
end,
@PONOTIDS_6 = case
when @PONOTIDS_6 is null then 0
else @PONOTIDS_6
end,
@PONOTIDS_7 = case
when @PONOTIDS_7 is null then 0
else @PONOTIDS_7
end,
@PONOTIDS_8 = case
when @PONOTIDS_8 is null then 0
else @PONOTIDS_8
end,
@PONOTIDS_9 = case
when @PONOTIDS_9 is null then 0
else @PONOTIDS_9
end,
@PONOTIDS_10 = case
when @PONOTIDS_10 is null then 0
else @PONOTIDS_10
end,
@PONOTIDS_10 = case
when @PONOTIDS_10 is null then 0
else @PONOTIDS_10
end,
@PONOTIDS_11 = case
when @PONOTIDS_11 is null then 0
else @PONOTIDS_11
end
/* Insert Purchase Order Table - POP10100 */

INSERT INTO [TWO].[dbo].[POP10100]
([PONUMBER]
,[POSTATUS]
,[STATGRP]
,[POTYPE]
,[USER2ENT]
,[CONFIRM1]
,[DOCDATE]
,[LSTEDTDT]
,[LSTPRTDT]
,[PRMDATE]
,[PRMSHPDTE]
,[REQDATE]
,[REQTNDT]
,[SHIPMTHD]
,[TXRGNNUM]
,[REMSUBTO]
,[SUBTOTAL]
,[TRDISAMT]
,[FRTAMNT]
,[MSCCHAMT]
,[TAXAMNT]
,[VENDORID]
,[VENDNAME]
,[MINORDER]
,[VADCDPAD]
,[CMPANYID]
,[PRBTADCD]
,[PRSTADCD]
,[CMPNYNAM]
,[CONTACT]
,[ADDRESS1]
,[ADDRESS2]
,[ADDRESS3]
,[CITY]
,[STATE]
,[ZIPCODE]
,[CCode]
,[COUNTRY]
,[PHONE1]
,[PHONE2]
,[PHONE3]
,[FAX]
,[PYMTRMID]
,[DSCDLRAM]
,[DSCPCTAM]
,[DISAMTAV]
,[DISCDATE]
,[DUEDATE]
,[TRDPCTPR]
,[CUSTNMBR]
,[TIMESPRT]
,[CREATDDT]
,[MODIFDT]
,[PONOTIDS_1]
,[PONOTIDS_2]
,[PONOTIDS_3]
,[PONOTIDS_4]
,[PONOTIDS_5]
,[PONOTIDS_6]
,[PONOTIDS_7]
,[PONOTIDS_8]
,[PONOTIDS_9]
,[PONOTIDS_10]
,[PONOTIDS_11]
,[PONOTIDS_12]
,[PONOTIDS_13]
,[PONOTIDS_14]
,[PONOTIDS_15]
,[COMMNTID]
,[CANCSUB]
,[CURNCYID]
,[CURRNIDX]
,[RATETPID]
,[EXGTBLID]
,[XCHGRATE]
,[EXCHDATE]
,[TIME1]
,[RATECALC]
,[DENXRATE]
,[MCTRXSTT]
,[OREMSUBT]
,[ORSUBTOT]
,[Originating_Canceled_Sub]
,[ORTDISAM]
,[ORFRTAMT]
,[OMISCAMT]
,[ORTAXAMT]
,[ORDDLRAT]
,[ODISAMTAV]
,[BUYERID]
,[ONORDAMT]
,[ORORDAMT]
,[HOLD]
,[ONHOLDDATE]
,[ONHOLDBY]
,[HOLDREMOVEDATE]
,[HOLDREMOVEBY]
,[ALLOWSOCMTS]
,[DISGRPER]
,[DUEGRPER]
,[Revision_Number]
,[Change_Order_Flag]
,[PO_Field_Changes]
,[PO_Status_Orig]
,[TAXSCHID]
,[TXSCHSRC]
,[TXENGCLD]
,[BSIVCTTL]
,[Purchase_Freight_Taxable]
,[Purchase_Misc_Taxable]
,[FRTSCHID]
,[MSCSCHID]
,[FRTTXAMT]
,[ORFRTTAX]
,[MSCTXAMT]
,[ORMSCTAX]
,[BCKTXAMT]
,[OBTAXAMT]
,[BackoutFreightTaxAmt]
,[OrigBackoutFreightTaxAmt]
,[BackoutMiscTaxAmt]
,[OrigBackoutMiscTaxAmt]
,[Flags]
,[BackoutTradeDiscTax]
,[OrigBackoutTradeDiscTax]
,[POPCONTNUM]
,[CONTENDDTE]
,[CNTRLBLKTBY]
,[PURCHCMPNYNAM]
,[PURCHCONTACT]
,[PURCHADDRESS1]
,[PURCHADDRESS2]
,[PURCHADDRESS3]
,[PURCHCITY]
,[PURCHSTATE]
,[PURCHZIPCODE]
,[PURCHCCode]
,[PURCHCOUNTRY]
,[PURCHPHONE1]
,[PURCHPHONE2]
,[PURCHPHONE3]
,[PURCHFAX]
,[BLNKTLINEEXTQTYSUM]
,[CBVAT])
select
@PONUMBER,
1,
1,
1,
@USER2ENT,
@CONFIRM1,
@DOCDATE,
@LSTEDTDT,
@LSTPRTDT,
@PRMDATE,
@PRMSHPDTE,
@REQDATE ,
@O_DUMMYDT,
@SHIPMTHD ,
@TXRGNNUM ,
@REMSUBTO ,
@SUBTOTAL ,
@TRDISAMT ,
@FRTAMNT ,
@MSCCHAMT ,
@TAXAMNT ,
@VENDORID ,
@VENDNAME ,
@MINORDER ,
@VADCDPAD ,
@CMPANYID ,
@PRBTADCD ,
@PRSTADCD ,
@CMPNYNAM ,
@CONTACT ,
@ADDRESS1 ,
@ADDRESS2 ,
@ADDRESS3 ,
@CITY ,
@STATE ,
@ZIPCODE ,
@CCode ,
@COUNTRY ,
@PHONE1 ,
@PHONE2 ,
@PHONE3 ,
@FAX ,
@PYMTRMID ,
@DSCDLRAM ,
@DSCPCTAM ,
@DISAMTAV ,
@DISCDATE ,
@DUEDATE ,
0,
@CUSTNMBR ,
1,
@CREATDDT ,
@MODIFDT ,
@PONOTIDS_1,
@PONOTIDS_2,
@PONOTIDS_3,
@PONOTIDS_4,
@PONOTIDS_5,
@PONOTIDS_6,
@PONOTIDS_7,
@PONOTIDS_8,
@PONOTIDS_9,
@PONOTIDS_10,
@PONOTIDS_11,
@PONOTIDS_12,
@PONOTIDS_13,
@PONOTIDS_14,
@PONOTIDS_15,
@COMMNTID ,
0,
@CURNCYID ,
@CURRNIDX ,
@RATETPID ,
@EXGTBLID ,
@XCHGRATE ,
@O_DUMMYDT,
@O_DUMMYDT,
0,
0,
0,
@OREMSUBT ,
@ORSUBTOT ,
@Originating_Canceled_Sub ,
@ORTDISAM ,
@ORFRTAMT ,
@OMISCAMT ,
@ORTAXAMT ,
@ORDDLRAT ,
@ODISAMTAV ,
@BUYERID ,
@ONORDAMT ,
@ORORDAMT ,
0,
@O_DUMMYDT,
@ONHOLDBY ,
@O_DUMMYDT,
@HOLDREMOVEBY ,
1,
@DISGRPER ,
@DUEGRPER ,
0,
0,
@PO_Field_Changes ,
@PO_Status_Orig ,
@TAXSCHID ,
@TXSCHSRC ,
1,
0,
2,
2,
@FRTSCHID ,
@MSCSCHID ,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
@POPCONTNUM ,
@O_DUMMYDT,
0,
@PURCHCMPNYNAM ,
@PURCHCONTACT ,
@PURCHADDRESS1 ,
@PURCHADDRESS2 ,
@PURCHADDRESS3 ,
@PURCHCITY ,
@PURCHSTATE ,
@PURCHZIPCODE ,
@PURCHCCode ,
@PURCHCOUNTRY ,
@PURCHPHONE1 ,
@PURCHPHONE2 ,
@PURCHPHONE3 ,
@PURCHFAX ,
0,
0

end
/****** End Purchase Order Record ******/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-13 : 15:37:54

All these ISNULL operations and default settings can be made directly in the INSERT statement, saving 100's of rows.

INSERT fff
select
ISNULL(@PONUMBER, ''),
1,
1,
1,
ISNULL(@USER2ENT, 0),
...

Peter Larsson
Helsingborg, Sweden

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-13 : 15:56:51

Sorry for sticking my nose in there but...some observations (if I may),

You may want to consider reexamining the table design. when you have a bunch of columns named: PONOTIDS (1 through 15) its usually an indication for a seperate one to many table.

Generally speaking, a trigger should always be designed to handle multiple row operations. Yours assumes that only one row will ever be inserted or updated at a time.
Maybe you could design it so that you are inserting directly from the inserted table.

About all those SPs you're calling from within the trigger (ie getNextIdx...). Are those getting "the next" ID for insert to your pop10100 table? If so, you're bound to have ID collisions on concurrent executions of this trigger.

EDIT:
Sorry, just noticed no one actually addressed your question:
Implicit conversion from data type varchar to binary is not allowed.

This is due to a datatype mismatch. Somewhere in those bajillion columns and variables you are attempting to assign a value to either a column or a variable or a function argument that expects a binary value but is receiving a varchar.

You should probably start by looking for that around line 158

Be One with the Optimizer
TG

tmackii
Starting Member

2 Posts

Posted - 2006-09-14 : 07:58:12

Thanks for the input. I was able to correct the error message, I needed to assign a default numeric value to each field that was a tinyint, int, numeric, etc.

I ran the trigger against my test company, now it does not populate data into the same table in the destination database. I cannot see why it will not do so. Any suggestions on best ways to troubleshoot this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-14 : 08:55:41

What happens with the trigger if you insert multiple rows?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources