Solved

SQL stored procedure error message

Posted on 2014-03-10
4
1,284 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
Comment Utility
>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
Comment Utility
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
Comment Utility
Thanks these suggestions were veru helpful.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In this article I will describe the Backup & Restore 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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

14 Experts available now in Live!

Get 1:1 Help Now