Solved

SQL stored procedure error message

Posted on 2014-03-10
4
1,336 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
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 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 250 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now