jdr0606
asked on
SQL 2005 SPROC over Linked server
I'm trying to create a SPROC that includes a view over a linked server and am receiving an error.
Msg 2715, Level 16, State 3, Procedure usr_PacklistDetailintl_MU, Line 2
Column, parameter, or variable #1: Cannot find data type Order_ID.
Parameter or variable '@Order_ID' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure usr_PacklistDetailintl_MU, Line 2
Column, parameter, or variable #2: Cannot find data type MU_ID.
Parameter or variable '@MU_ID' has an invalid data type.
The SPROC is below
create PROCEDURE [dbo].[usr_PacklistDetaili ntl_MU] @Order_ID Order_ID
, @MU_ID MU_ID AS
set nocount on
select mu_arc.mu_id as mu_id
,mu_arc.order_id as Sopnumbe
,mu_arc.sku as itemnmbr
,mu_arc.original_qty as original_qty
,mu_arc.ship_qty as actual_qty
,mu_arc.picked_qty as picked_qty
,mu_arc.print_line_only as masterSKU
,(case when mu_arc.ASSORTMENT_NBR<>'' then 'Y' else 'N' end) as componentSKU
,sop1.custnmbr as custnmbr
,rm1.custname as custname
,hm5.hm_string_5 as hm_string_5
,hm5.hm_string_6 as hm_string_6
,sop1.DOCDATE as docdate
,SOP1.ACTLSHIP as ACTLSHIP
,SOP1.DISCDATE as DISCDATE
,SOP1.DSCDLRAM as DSCDLRAM
,SOP1.PYMTRMID as PYMTRMID
,SOP1.CSTPONBR as CSTPONBR
,SOP1.ShipToName as ShipToName
,sop1.ADDRESS1 as ADDRESS1
,SOP1.ADDRESS2 as ADDRESS2
,SOP1.ADDRESS3 as ADDRESS3
,SOP1.CITY as CITY
,SOP1.STATE as STATE
,SOP1.ZIPCODE as ZIPCODE
,SOP1.SHIPMTHD as SHIPMTHD
,SOP1.TRDISAMT as TRDISAMT
,SOP1.SUBTOTAL as SUBTOTAL
,SOP1.FRTAMNT as FRTAMNT
,SOP1.MISCAMNT as MISCAMNT
,SOP1.TXBTXAMT as TXBTXAMT
,SOP1.TAXAMNT as TAXAMNT
,SOP1.CNTCPRSN as CNTCPRSN
,SOP1.COUNTRY as COUNTRY
,SOP2.CMPNTSEQ as CMPNTSEQ
,SOP2.ITEMNMBR as ITEMNMBR
,SOP2.ITEMDESC as ITEMDESC
,SOP2.UNITPRCE as UNITPRCE
,SOP2.ATYALLOC as ATYALLOC
,SOP2.QUANTITY as QUANTITY
,SOP2.QTYTBAOR as QTYTBAOR
,SOP2.QTYPRINV as QTYPRINV
,SOP2.CMPNTSEQ as CMPNTSEQ
,RM1.ADDRESS1 as ADDRESS1
,RM1.ADDRESS2 as ADDRESS2
,RM1.ADDRESS3 as ADDRESS3
,RM1.CITY as CITY
,RM1.STATE as STATE
,RM1.ZIP as ZIP
,SOP6.CMMTTEXT as OrderCMMTTEXT
,SOP202.CMMTTEXT as LineCMMTTEXT
, 0 as Freightcharges
from prd.dbo.dc21gpd.uvwPacklis tDetailMU mu_arc
join Comp.dbo.sop10100_sop30200 _union sop1 on sop1.sopnumbe=mu_arc.order _id
left join Comp.dbo.sop10200_sop30300 _union sop2 on sop2.sopnumbe=mu_arc.order _id
and sop2.itemnmbr=mu_arc.sku
and sop2.lnitmseq=mu_arc.detai l_seq_nbr
left join Comp.dbo.sop10202 sop202 on sop202.sopnumbe=mu_arc.ord er_id
and sop202.lnitmseq=mu_arc.det ail_seq_nb r
left join Comp.dbo.sop10106 sop6 on sop6.sopnumbe=mu_arc.order _id
left join Comp.dbo.rm00101 rm1 on rm1.custnmbr=sop1.custnmbr
left join Comp.dbo.hm00500 hm5 on hm5.itemnmbr=mu_arc.sku
where mu_arc.mu_id=@MU_ID
and order_id=@Order_ID
order by muarc.masterSKU desc
,(case when mu_arc.ASSORTMENT_NBR<>'' then 'Y' else 'N' end) desc
, mu_arc.sku
Msg 2715, Level 16, State 3, Procedure usr_PacklistDetailintl_MU,
Column, parameter, or variable #1: Cannot find data type Order_ID.
Parameter or variable '@Order_ID' has an invalid data type.
Msg 2715, Level 16, State 3, Procedure usr_PacklistDetailintl_MU,
Column, parameter, or variable #2: Cannot find data type MU_ID.
Parameter or variable '@MU_ID' has an invalid data type.
The SPROC is below
create PROCEDURE [dbo].[usr_PacklistDetaili
, @MU_ID MU_ID AS
set nocount on
select mu_arc.mu_id as mu_id
,mu_arc.order_id as Sopnumbe
,mu_arc.sku as itemnmbr
,mu_arc.original_qty as original_qty
,mu_arc.ship_qty as actual_qty
,mu_arc.picked_qty as picked_qty
,mu_arc.print_line_only as masterSKU
,(case when mu_arc.ASSORTMENT_NBR<>'' then 'Y' else 'N' end) as componentSKU
,sop1.custnmbr as custnmbr
,rm1.custname as custname
,hm5.hm_string_5 as hm_string_5
,hm5.hm_string_6 as hm_string_6
,sop1.DOCDATE as docdate
,SOP1.ACTLSHIP as ACTLSHIP
,SOP1.DISCDATE as DISCDATE
,SOP1.DSCDLRAM as DSCDLRAM
,SOP1.PYMTRMID as PYMTRMID
,SOP1.CSTPONBR as CSTPONBR
,SOP1.ShipToName as ShipToName
,sop1.ADDRESS1 as ADDRESS1
,SOP1.ADDRESS2 as ADDRESS2
,SOP1.ADDRESS3 as ADDRESS3
,SOP1.CITY as CITY
,SOP1.STATE as STATE
,SOP1.ZIPCODE as ZIPCODE
,SOP1.SHIPMTHD as SHIPMTHD
,SOP1.TRDISAMT as TRDISAMT
,SOP1.SUBTOTAL as SUBTOTAL
,SOP1.FRTAMNT as FRTAMNT
,SOP1.MISCAMNT as MISCAMNT
,SOP1.TXBTXAMT as TXBTXAMT
,SOP1.TAXAMNT as TAXAMNT
,SOP1.CNTCPRSN as CNTCPRSN
,SOP1.COUNTRY as COUNTRY
,SOP2.CMPNTSEQ as CMPNTSEQ
,SOP2.ITEMNMBR as ITEMNMBR
,SOP2.ITEMDESC as ITEMDESC
,SOP2.UNITPRCE as UNITPRCE
,SOP2.ATYALLOC as ATYALLOC
,SOP2.QUANTITY as QUANTITY
,SOP2.QTYTBAOR as QTYTBAOR
,SOP2.QTYPRINV as QTYPRINV
,SOP2.CMPNTSEQ as CMPNTSEQ
,RM1.ADDRESS1 as ADDRESS1
,RM1.ADDRESS2 as ADDRESS2
,RM1.ADDRESS3 as ADDRESS3
,RM1.CITY as CITY
,RM1.STATE as STATE
,RM1.ZIP as ZIP
,SOP6.CMMTTEXT as OrderCMMTTEXT
,SOP202.CMMTTEXT as LineCMMTTEXT
, 0 as Freightcharges
from prd.dbo.dc21gpd.uvwPacklis
join Comp.dbo.sop10100_sop30200
left join Comp.dbo.sop10200_sop30300
and sop2.itemnmbr=mu_arc.sku
and sop2.lnitmseq=mu_arc.detai
left join Comp.dbo.sop10202 sop202 on sop202.sopnumbe=mu_arc.ord
and sop202.lnitmseq=mu_arc.det
left join Comp.dbo.sop10106 sop6 on sop6.sopnumbe=mu_arc.order
left join Comp.dbo.rm00101 rm1 on rm1.custnmbr=sop1.custnmbr
left join Comp.dbo.hm00500 hm5 on hm5.itemnmbr=mu_arc.sku
where mu_arc.mu_id=@MU_ID
and order_id=@Order_ID
order by muarc.masterSKU desc
,(case when mu_arc.ASSORTMENT_NBR<>'' then 'Y' else 'N' end) desc
, mu_arc.sku
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I should have known that myself!