Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

No parameters and arguments were supplied. Can anyone see my mistake here.

Posted on 2013-10-29
7
Medium Priority
?
1,405 Views
Last Modified: 2013-10-29
I get the following error: The parameter is @DB_Include and when I run it I do put the value in the query a 0,1,2 but nothing works. I do not see where the argument is supplied. Can someone please clarify. Any way to identify an argument. Please advise. Here is the error.

Msg 8146, Level 16, State 1, Procedure Rpt_SnyderAccount_PL_Summary_RB, Line 0
Procedure Rpt_SnyderAccount_PL_Summary_RB has no parameters and arguments were supplied.

USE [WebSnakDS_SNYB_Test]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_SnyderAccountP&LSummary_RB]    Script Date: 10/22/2013 11:47:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Declare 
	ALTER PROCEDURE [dbo].[Rpt_SnyderAccount_PL_Summary_RB] 
	(
	@DB_Include			int -- 0 = WebSNAK Only, 1 = RAMS Only, 2 = WebSNAK and RAMS 

	) AS
-----------------------------------------------------------------------------------
-- Creating TEMP Table Bringing Macola RAMS and WebSNAK together
-----------------------------------------------------------------------------------

CREATE TABLE #TEMPTABLE
		
(
	[Data_Source] VARCHAR(12),--  what database is the data coming from  macola or rams?
	[InvoiceDate] DATETIME,
	[SNAK_SaleTypesID] CHAR (1),
	[CustPromoAmt] MONEY,
	[MFGCostAmt] MONEY, 
	[GrossAmt] MONEY,
	[CustName] VARCHAR(30),
	[SNAK_CustomerID] VARCHAR(8),
	[ChainName] VARCHAR(30),
	[SNAK_ChainID] VARCHAR(10)
	
	
)

 
-----------------------------------------------------------------------------------
-- WEBSNAK DATA
-----------------------------------------------------------------------------------

if (@DB_Include = 0 or @DB_Include = 2) -- 0 = WebSNAK Data only; 2 = Both WebSNAK and RAMS data
	BEGIN
	
INSERT  INTO #TEMPTABLE

SELECT
	'WEBSNAK',
	Invoices.InvoiceDate AS InvoiceDate,
	InvoiceDetails.SNAK_SaleTypesID AS SNAK_SaleTypesID, 
	InvoiceDetails.CustPromoAmt AS CustPromoAmt,
	InvoiceDetails.MFGCostAmt AS MFGCostAmt,
	InvoiceDetails.GrossAmt AS GrossAmt,
	Customers.CustName AS CustName, 
	Customers.SNAK_CustomerID AS SNAK_CustomerID,
	Chains.ChainName AS ChainName, 
	Chains.SNAK_ChainID AS SNAK_ChainID
FROM
    { oj ((dbo.Invoices Invoices 
     INNER JOIN dbo.Customers Customers 
			ON	Invoices.Customers_Syskey = Customers.Customers_Syskey)
     INNER JOIN dbo.InvoiceDetails InvoiceDetails 
			ON	Invoices.Invoices_Syskey = InvoiceDetails.Invoices_Syskey)
     INNER JOIN dbo.Chains Chains 
			ON	Customers.Chains_Syskey = Chains.Chains_Syskey}
--WHERE
	--((Invoices.InvoiceDate >= {ts '2012-01-10 00:00:00.00'} AND
    --Invoices.InvoiceDate < {ts '2012-09-11 00:00:00.00'}) OR
    --(Invoices.InvoiceDate >= {ts '2013-01-10 00:00:00.00'} AND
    --Invoices.InvoiceDate < {ts '2013-09-11 00:00:00.00'}))

END
-----------------------------------------------------------------------------------
--RAMS DATA--
-----------------------------------------------------------------------------------

if (@DB_Include = 1 or @DB_Include = 2) -- 1 = RAMS Data only; 2 = Both WebSNAK and RAMS data
	BEGIN
	
INSERT  INTO #TEMPTABLE

SELECT
    	'RAMS',
    	ROSS_SALESHDR.OH_DELIVERY_DATE AS InvoiceDate,
    	ROSS_SALESDTL.OD_RECORD_TYPE AS SNAK_SalesTypesID,
 		CustPromoAmt = SUM(ROSS_SALESDTL.OD_TOTAL_DELIV_QUANTITY * ROSS_SALESDTL.OD_NET_PROMO_PRICE),
 		MFGCostAmt = SUM(ROSS_SALESDTL.OD_TOTAL_DELIV_QUANTITY * ROSS_SALESDTL.OD_PRODUCT_COST_PRICE),
 		GrossAmt = SUM(ROSS_SALESDTL.OD_TOTAL_DELIV_QUANTITY * OD_BASE_PRICE),
 		ROSS_CUSTOMER.CM_FULLNAME AS CustName,
    	RAMS_CrossRef.RAMS_CHAIN_NO AS SNAK_CustomerID,
    	RAMS_CrossRef.SNAK_CHAIN_NO AS ChainName,
    	RAMS_CrossRef.CHAIN_IDENT AS SNAK_ChainID
    	
    	
FROM
     CHLROS05.RAMSDB.dbo.ROSS_SALESHDR ROSS_SALESHDR 
	INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER ROSS_CUSTOMER 
		ON ROSS_SALESHDR.OH_CUSTOMER_NUMBER = ROSS_CUSTOMER.CM_CUSTKEY
    INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_SALESDTL ROSS_SALESDTL 
		ON ROSS_SALESHDR.OH_TICKET_NUMBER = ROSS_SALESDTL.OD_TICKET_NUMBER
		AND ROSS_SALESDTL.OD_RECORD_TYPE IN ('S','R','M')
    INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS ROSS_PRODUCTS 
		ON ROSS_SALESDTL.OD_PRODUCT = ROSS_PRODUCTS.PRD_PRODUCT
	LEFT OUTER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE1
        ON ROSS_CUSTOMER.CM_SELLZONE = ROSS_IDCODE1.ID_FIELD
    LEFT OUTER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE2
        ON ROSS_PRODUCTS.PRD_A_GLTYPE = ROSS_IDCODE2.ID_FIELD 
	INNER JOIN WebSnakDS_SNYB_test.dbo.RAMS_CrossRef RAMS_CrossRef
		ON ROSS_CUSTOMER.CM_CHAINID = RAMS_CrossRef.RAMS_CHAIN_NO
		
--WHERE
    --ROSS_SALESHDR.OH_DELIVERY_DATE >= {ts '2013-01-01 00:00:00.00'} AND
    --ROSS_SALESHDR.OH_DELIVERY_DATE < {ts '2013-04-30 00:00:00.00'} --AND
    --ROSS_SALESDTL.OD_PRODUCT >= '0*' AND
    --ROSS_SALESDTL.OD_PRODUCT <= '9999*' AND
    --ROSS_SALESDTL.OD_RECORD_TYPE = 'O' --AND
    --(ROSS_CUSTOMER.CM_DELIVZONE = '400002' OR
    --ROSS_CUSTOMER.CM_DELIVZONE = '400001')
    
GROUP BY
        ROSS_SALESHDR.OH_DELIVERY_DATE,
        ROSS_SALESDTL.OD_RECORD_TYPE,
        ROSS_CUSTOMER.CM_FULLNAME,
        RAMS_CrossRef.RAMS_CHAIN_NO,
        RAMS_CrossRef.SNAK_CHAIN_NO,
        RAMS_CrossRef.CHAIN_IDENT

    END
    
    SELECT * FROM #TEMPTABLE

Open in new window

0
Comment
Question by:ruavol2
[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
7 Comments
 
LVL 8

Accepted Solution

by:
5teveo earned 1000 total points
ID: 39609734
Set a default value for paramter as follows then use print command debug as needed... verify you are sending paramenter if needed

i added '= 0' after 'int'


ALTER PROCEDURE [dbo].[Rpt_SnyderAccount_PL_Summary_RB]
      (
      @DB_Include int = 0 -- 0 = WebSNAK Only, 1 = RAMS Only, 2 = WebSNAK and RAMS
      ) AS
.
.
.
.
rest of stored procedure
.
.
.
0
 
LVL 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 500 total points
ID: 39609738
You have to add a parameter to crystal . . . and specify it there.

Ensure you have the parameter named correctly with the right data type.



Confirm you get data if you run this from SSMS:
exec Rpt_SnyderAccount_PL_Summary_RB  2
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 500 total points
ID: 39609831
Remove the  (  ) in lines 10 and 13

      ALTER PROCEDURE [dbo].[Rpt_SnyderAccount_PL_Summary_RB]
      (
      @DB_Include                  int -- 0 = WebSNAK Only, 1 = RAMS Only, 2 = WebSNAK and RAMS

      ) AS

mlcc
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:ruavol2
ID: 39609837
This is what I get when I put your code in Steveo

Msg 208, Level 16, State 1, Procedure Rpt_SnyderAccount_PL_Summary_RB, Line 10
Invalid object name 'dbo.Invoices'.
Msg 208, Level 16, State 1, Procedure Rpt_SnyderAccount_PL_Summary_RB, Line 10
Invalid object name 'dbo.Customers'.
Msg 208, Level 16, State 1, Procedure Rpt_SnyderAccount_PL_Summary_RB, Line 10
Invalid object name 'dbo.InvoiceDetails'.
Msg 208, Level 16, State 1, Procedure Rpt_SnyderAccount_PL_Summary_RB, Line 10
Invalid object name 'dbo.Chains'.

Open in new window


I am only running it for Crystal Reports after it is ready. There is no report assigned to it.
0
 

Author Comment

by:ruavol2
ID: 39610095
I tried all those options and none I am afraid worked. I get the SP has no parameters and arguments were supplied.
0
 
LVL 8

Assisted Solution

by:5teveo
5teveo earned 1000 total points
ID: 39610120
Please verify your StoredProcedure runs error-free in SQL management environment

are you getting same errors outside Crystal call?
0
 

Author Closing Comment

by:ruavol2
ID: 39610147
SSMS had switched in window to a different server. Don't know if I did it or another IT staffer but when I looked at the window it showed.

Rpt_SnyderAccount_PL_Summary_RB  2 it switches to a different server. So sorry about that.
Maybe there is some value there......Check the darn Server Window RUAVOL2......!
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

636 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