?
Solved

SQL stored procedure error message

Posted on 2014-03-10
4
Medium Priority
?
1,496 Views
Last Modified: 2014-03-10
I am running a system that calls SQL from vb6.

I get following error message...
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '#'.

The code is below. Could someone possibly give me an idea as to why this error is happening?
USE [rpr_DEV]
GO
/****** Object:  StoredProcedure [dbo].[spITS_ShippingReportsShippingServiceDetail]    Script Date: 03/10/2014 11:19:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[spITS_ShippingReportsShippingServiceDetail]  
-- =============================================
-- parameters
-- =============================================
	@l_comp_code char(5),
	@l_StartDate char(8),
    @l_EndDate char(8)
AS
-- =============================================
-- documentation
-- =============================================
/*
	Author:			William Levis
	Date Created:	04/04/2012
	Purpose:	Shipping Summary in a variety of groupings
			UPS Feedback data incorporated
	Where used:	ITS
	Notes: Reworked for SSRS
	Revision History:
*/

--Create temp table to store result set from remote stored procedure
CREATE TABLE #l_tblShipments
(
	DataSource varchar(10),
	carrier_name varchar(30), 
	service_name varchar(30),
	order_numb varchar(10),
	ChargeActual float,
	ChargeDiscounted float, 
	WeightBillable float, 
	PackageCount int,
	ImportOrderNumb varchar(10),
	ImportCharge1 float,
	ImportWeight float,
	ImportPackageCount int
)

-- Execute remote stored procedure and store results into temp table 
SET NOCOUNT ON
INSERT INTO #l_tblShipments
EXEC [MACShip9].[macship].[dbo].MacShipToRpr @l_comp_code, @l_StartDate, @l_EndDate
Print 'Insert from MAC systems complete ' + Convert(varchar(100),GetDate(),108)
--select count(*) from #l_tblshipments
UPDATE #l_tblShipments 
SET ImportOrderNumb = si.order_numb,
    ImportPackageCount = si.PackageCount,
    ImportWeight = si.SumOfWeight,
    ImportCharge1 = si.SumOfCharge1
FROM #l_tblShipments s
	LEFT JOIN (SELECT order_numb,
			service_name,
			'United Parcel Service' As CarrierName,
			Sum(Case WHEN charge1 Is NULL Then 0 ELSE charge1 END) as SumOfCharge1,
			Sum(Case When tracking_number is NULL 
					or tracking_number = '' 
					or weight Is NULL
					or type = 'SCC'		--Shipping Srv. Correction
					or type = 'GSR'		--Guaranteed Srv. Refund
			         Then 0
				 ELSE weight
			    END) As SumOfWeight,
			CASE WHEN s.service_name = 'Other'
		 THEN SUM(numberofpackages)
	     ELSE COUNT(*)
		 END As PackageCount
		FROM MACShip9.macship.dbo.MACShip_Shipment_Import
		WHERE order_numb <> ''
		GROUP BY service_name, order_numb
      		) si on si.CarrierName = s.carrier_name
			and si.service_name = s.service_name
			and si.order_numb = s.order_numb
WHERE s.order_numb Is Not Null
	and s.carrier_name = si.CarrierName 
	and s.service_name = si.service_name 
	and s.order_numb = si.order_numb

PRINT 'Update of Import data complete ' + Convert(varchar(100),GetDate(),108)

-- Insert ORDERS matching #l_tblShipments into #tblOrders
SELECT	RTrim(c.comp_name) As comp_name,
	o.order_numb,
	o.sales_forc,
	o.ship_type,
	o.order_type,
	ot.type_desc As order_type_desc
INTO #tblOrders
FROM (SELECT order_numb FROM #l_tblShipments GROUP BY order_numb) s
	LEFT JOIN orders o on s.order_numb = o.order_numb
	LEFT JOIN company c on o.comp_code = c.comp_code
	LEFT jOIN order_type ot on o.order_type = ot.type

Print 'Insert from Orders complete ' + Convert(varchar(100),GetDate(),108)

-- Insert ORDER_DETAIL matching #tblOrders (no grouping needed) into #tblOrderDetail
SELECT 	od.order_numb,
	od.div_code,
	Max(d.desc_) As DivisionDesc,
	Count(*) As LineCount
INTO #tblOrderDetail
FROM #tblOrders o
	LEFT JOIN order_detail od on o.order_numb = od.order_numb AND od.div_code NOT IN('SLC','SLD','SLK')
	LEFT JOIN division d on od.div_code = d.div_code
		AND d.comp_code = @l_comp_code
GROUP BY od.order_numb,
	 od.div_code 
Print 'Insert from Order Detail complete ' + Convert(varchar(100),GetDate(),108)

-- Select data for "Shipping Service Detail"
SELECT  Case when carrier_name is null then 'Unknown'
	     when carrier_name = ' ' then 'Unknown'
	     else carrier_name END As CarrierName,
	Case when service_name is null then 'Unknown'
	     when service_name = ' ' then 'Unknown'
	     else service_name end As ServiceName,
	Count(distinct o.order_numb) As ServiceOrderCount,
	Sum(Case When o.OrderTotalWeight > 0 Then
		o.SumOfLineCount * (Convert(float,s.WeightBillable) / o.OrderTotalWeight)
		Else 0 End) As ServiceLineItemCount,
	Sum(ChargeActual) As SumOfChargeActual,
	Sum(s.ChargeDiscounted) As SumOfChargeDiscounted,
	Sum(s.WeightBillable) As SumOfWeightBillable,
	Sum(s.PackageCount) As SumOfPackageCount,
	Count(distinct s.ImportOrderNumb) As CountOfImportOrderNumb,
	Sum(s.ImportCharge1) As SumOfImportCharge1,
	Sum(s.ImportWeight) As SumOfImportWeight,
	Sum(s.ImportPackageCount) As SumOfImportPackageCount
FROM #l_tblShipments s
	LEFT JOIN 
		(SELECT A.order_numb,
			A.SumOfLineCount,
			B.OrderTotalWeight,
			B.ImportTotalWeight
 	 	FROM
	 		-- Get lic (line item count) for each order
			(SELECT order_numb,
			  	Sum(LineCount) As SumOfLineCount 
		   	FROM #tblOrderDetail
		   	GROUP BY order_numb) A
			LEFT JOIN
				-- Get total weight for each order for use in calculating what % of
				--    the line items should be attributed to each service
				(SELECT order_numb,
					Sum(WeightBillable) As OrderTotalWeight,
					Sum(ImportWeight) As ImportTotalWeight
 		 		FROM #l_tblShipments
 		 		GROUP BY order_numb) B on A.order_numb = B.order_numb
		) o on s.order_numb = o.order_numb
GROUP BY carrier_name, service_name

Print 'Shipping Service Detail select complete ' + Convert(varchar(100),GetDate(),108)

Open in new window

0
Comment
Question by:tesla764
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 750 total points
ID: 39917938
>Could not find stored procedure '#'.
this message is something where I only know of the scenario with dynamic sql (EXEC) which is missing the brackets when calling it.
looking at your code, the only place where this could happen is inside this one:
EXEC [MACShip9].[macship].[dbo].MacShipToRpr

can you check that stored proc, and also show how you actually call the stored procedure?
0
 
LVL 5

Assisted Solution

by:advfinance
advfinance earned 750 total points
ID: 39917974
My colleague tells me he has sometimes run into problems when object names contain an underscore ("_") and suggests that you surround your object names with square brackets ("[" and "]").

E.g.

CREATE TABLE #l_tblShipments

Open in new window


becomes

CREATE TABLE [#l_tblShipments]

Open in new window


Does that help?

--
Chris
0
 

Author Comment

by:tesla764
ID: 39917999
Thanks these suggestions were veru helpful.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question