Solved

SQL stored procedure error message

Posted on 2014-03-10
4
1,356 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 143

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

808 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