Link to home
Start Free TrialLog in
Avatar of jdr0606
jdr0606Flag for United States of America

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_PacklistDetailintl_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.uvwPacklistDetailMU 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.detail_seq_nbr
      left join Comp.dbo.sop10202 sop202 on sop202.sopnumbe=mu_arc.order_id
            and sop202.lnitmseq=mu_arc.detail_seq_nbr
      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
ASKER CERTIFIED SOLUTION
Avatar of COANetwork
COANetwork

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jdr0606

ASKER

Thanks

I should have known that myself!