Output Parameter Stored Procedure problem

[kxramse]

(Background info: Running XP locally, MS 2003 server for the DB operating system, XI Crystal developer edition with MS SQL 2005 database using Microsoft SQL Server ODBC Driver Version 03.85.1117 – Driver.)

I have a stored procedure that looks like this:

CREATE PROCEDURE dbo.spSumRates
	-- Add the parameters for the stored procedure here
	@GroupNumber varchar(255),
	@ScaleName varchar(255),
	@SiteId int,
	@SitesName nvarchar(255),
	@ReportMonth int,
	@ReportYear int,
	@Rate decimal (10,2) Output,
	@AdjRate decimal (10,2) Output,
	@Received decimal (10,2) Output

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
SELECT  /*CompanyName.Name, GroupInfo.[GroupNumber], CommissionScales.[Name], Sites.Name, */
	@Rate= SUM(CAST(ReportAdjBreaks.Rate AS decimal(10, 2))),
	@AdjRate = 	SUM(
					(case WHEN ReportAdjustments.IsCredit = 1 THEN -1 else 1 END) 
					   * ReportAdjMonths.Months 
					   * CAST(ReportAdjBreaks.Rate AS decimal(10, 2)
					)
				   ),
	@Received = SUM(
					CAST (Reports.AmountReceived AS decimal(10, 2))
				   )
FROM  [wolftracs].[dbo].[Reports] 
	-- All rows from  Reports even those which do not have records in Sites.
	LEFT OUTER JOIN  [wolftracs].[dbo].[Sites] ON Reports.[SiteID] = Sites.[ID]

	-- All rows from Sites, even those which do not have records in SiteGroups.
	LEFT OUTER JOIN [wolftracs].[dbo].[SiteGroups] ON Sites.[SiteGroupID] = SiteGroups.[ID]

	-- All rows from SiteGroups, even those which do not have records in Companies.
	LEFT OUTER JOIN [wolftracs].[dbo].[Companies] ON SiteGroups.[CompanyID] = Companies.[ID]

	-- All rows from Companies, even those which do not have records in GroupInfo.
	LEFT OUTER JOIN [wolftracs].[dbo].[GroupInfo] ON Companies.[ID] = GroupInfo.[TargetID]

	-- Only rows that are in both tables
		INNER JOIN [wolftracs].[dbo].[ReportAdjustments] ON Reports.[ID] = ReportAdjustments.[ReportID]
		INNER JOIN[wolftracs].[dbo].[ReportAdjMonths] ON ReportAdjustments.[ID] = ReportAdjMonths.[rAdjustmentID]
		INNER JOIN [wolftracs].[dbo].[ReportAdjBreaks] ON ReportAdjMonths.[ID] = ReportAdjBreaks.[rAdjMonthID]

	-- All rows from ReportAdjustments, even those which do not have records in ReportPlans.
	LEFT OUTER JOIN [wolftracs].[dbo].[ReportPlans] ON ReportAdjustments.[rPlanID] = ReportPlans.[ID]

	-- All rows from ReportPlans, even those which do not have records in PlansAvailable.	
	LEFT OUTER JOIN [wolftracs].[dbo].[PlansAvailable] ON ReportPlans.[AvailID] = PlansAvailable.[ID]

	-- All rows from PlansAvailable, even those which do not have records in CommissionScales.
	LEFT OUTER JOIN [wolftracs].[dbo].[CommissionScales] ON PlansAvailable.[CommissionScaleID] = CommissionScales.[ID]

WHERE	GroupInfo.GroupNumber = @GroupNumber and
	CommissionScales.[Name] = @ScaleName and
	substring(Sites.[Name], 6, len(Sites.[Name]) - 5) = @SitesName and
	Reports.ReportMonth = @ReportMonth and
	Reports.ReportYear = @ReportYear and
	Reports.SiteID = @SiteId
GROUP BY Companies.[Name], 
	GroupInfo.GroupNumber, 
	CommissionScales.[Name], 
	substring(Sites.[Name], 6, len(Sites.[Name]) - 5),
	Reports.ReportMonth,
	Reports.ReportYear

I created a command in Crystal XI that states the following:

EXEC spSumRates @GroupNumber={?GroupNumber},@ScaleName={?ScaleName}, @SiteID={?SiteID}, @SitesName={?SitesName}, @ReportMonth={?ReportMonth}, @ReportYear={?ReportYear}, @Rate={?Rate}, @AdjRate={?AdjRate}, @Received={?Received}

My problem is that while Crystal can send the input variables its freaking out about the output variables.

Currently my error message is:
Database Connector Error: 'HY010:[Microsoft][ODBC Driver Manager] Function sequence Error'


I've tried different combinations of using my output variables like removing them, or having only the Crystal Parameter there, or only the SQL Server Paramater name, but those combinations don't work either. Examples are below:

EXEC spSumRates @GroupNumber={?GroupNumber},@ScaleName={?ScaleName}, @SiteID={?SiteID}, @SitesName={?SitesName}, @ReportMonth={?ReportMonth}, @ReportYear,@Rate,@AdjRate,@Received

Nor did this work:

EXEC spSumRates @GroupNumber={?GroupNumber},@ScaleName={?ScaleName}, @SiteID={?SiteID}, @SitesName={?SitesName}, @ReportMonth={?ReportMonth}, {ReportYear},{Rate},{AdjRate},{Received}

Any idea how I get past this?

Thanks,
Keith

 

[synapsevampire]

Why are you using a Command Object?

I'd get rid of the OUTPUT on the variables, and just declare them as variables, they shouldn't be parameters anyway.

Once you've gotten rid of the OUTPUT parameters, just point to the SP and Crystal will create the parameters for you and prompt accordingly.

-k

 

[kxramse]

I'm getting a datbase Connection Error: Can Not Obtain Error Message from Server when I connect to the SP both as a command and as an object from the database exlorer inside Crystal XI.

I removed the output keyword on the parameters, and I checked and double checked the security but that isn't working. I know it can at lease see the parameters because when I remove one, it gives me a different error message.

Now I'm confused.

Why did you say they shouldn't be parameters anyway? What am I missing?

 

[synapsevampire]

You need to move those variables so that they are NOT parameters.

Note that I statd to move them so they are not parameters.

Please try doing what I suggest, and then if you need further help or clarification I'll help or explain, but if you aren't going to fix the problem first I can't help you.

-k

 

[kxramse]

I'm confused. I'm trying to follow your advice.

I can't remove the parameters from the SP because it wouldn't know what data to calculate on.

I can't remove parameters from Crystal because without the parameters, you get two objects that have no linkage between them.

When I change the SQL statement in the SP to have no parameters I get an error message.
SELECT CompanyName.Name, GroupInfo.[GroupNumber], CommissionScales.[Name], Sites.Name,
SUM(CAST(ReportAdjBreaks.Rate AS decimal(10, 2))) as Rate,
SUM(
(case WHEN ReportAdjustments.IsCredit = 1 THEN -1 else 1 END)
* ReportAdjMonths.Months
* CAST(ReportAdjBreaks.Rate AS decimal(10, 2)
)
) as AdjRate,
SUM(
CAST (Reports.AmountReceived AS decimal(10, 2))
) As Received

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "CompanyName.Name" could not be bound.

A little more clarification would be really appreciated, I'm lost.

 

[synapsevampire]

I didn['t say to not use parameters, I said to not use the output parameters.

Move these below the AS statement:

@Rate decimal (10,2) Output,
@AdjRate decimal (10,2) Output,
@Received decimal (10,2) Output

Can't recall the SQL Server syntax off the top of my head.

The point is to LOSE the output parms.

-k

 

[kxramse]

Ok. I moved those below the AS statement. My problem now is that the input variables aren't map-able in the links explorer so I can't tell the SP what the values of those parameters are and they can't link to each other.

I'm thouroughly confused.

I have this table with five fields that will help me calculate three fields of data when they are passed into a stored proceedure. What am I missing?

PROCEDURE [dbo].[spSumRates]
	-- Add the parameters for the stored procedure here
	@GroupNumber varchar(255),
	@ScaleName varchar(255),
	@SiteId int,
	@SitesName nvarchar(255),
	@ReportMonth int,
	@ReportYear int
AS
	Declare
	@Rate decimal (10,2),
	@AdjRate Decimal (10,2),
	@Received decimal (10,2)

 

[synapsevampire]

Perhaps I;m missing something here.

So does this accomplish what you want?

PROCEDURE [dbo].[spSumRates]
-- Add the parameters for the stored procedure here
@GroupNumber varchar(255),
@ScaleName varchar(255),
@SiteId int,
@SitesName nvarchar(255),
@ReportMonth int,
@ReportYear int
AS
Declare
@Rate decimal (10,2),
@AdjRate Decimal (10,2),
@Received decimal (10,2)
SELECT /*CompanyName.Name, GroupInfo.[GroupNumber], CommissionScales.[Name], Sites.Name, */
SUM(CAST(ReportAdjBreaks.Rate AS decimal(10, 2)))Rate,
SUM(
(case WHEN ReportAdjustments.IsCredit = 1 THEN -1 else 1 END)
* ReportAdjMonths.Months
* CAST(ReportAdjBreaks.Rate AS decimal(10, 2)
)
) Adjrate,
SUM(
CAST (Reports.AmountReceived AS decimal(10, 2))
) Received
FROM [wolftracs].[dbo].[Reports]
-- All rows from Reports even those which do not have records in Sites.
LEFT OUTER JOIN [wolftracs].[dbo].[Sites] ON Reports.[SiteID] = Sites.[ID]
-- All rows from Sites, even those which do not have records in SiteGroups.
LEFT OUTER JOIN [wolftracs].[dbo].[SiteGroups] ON Sites.[SiteGroupID] = SiteGroups.[ID]
-- All rows from SiteGroups, even those which do not have records in Companies.
LEFT OUTER JOIN [wolftracs].[dbo].[Companies] ON SiteGroups.[CompanyID] = Companies.[ID]

-- All rows from Companies, even those which do not have records in GroupInfo.
LEFT OUTER JOIN [wolftracs].[dbo].[GroupInfo] ON Companies.[ID] = GroupInfo.[TargetID]

-- Only rows that are in both tables
INNER JOIN [wolftracs].[dbo].[ReportAdjustments] ON Reports.[ID] = ReportAdjustments.[ReportID]
INNER JOIN[wolftracs].[dbo].[ReportAdjMonths] ON ReportAdjustments.[ID] = ReportAdjMonths.[rAdjustmentID]
INNER JOIN [wolftracs].[dbo].[ReportAdjBreaks] ON ReportAdjMonths.[ID] = ReportAdjBreaks.[rAdjMonthID]

-- All rows from ReportAdjustments, even those which do not have records in ReportPlans.
LEFT OUTER JOIN [wolftracs].[dbo].[ReportPlans] ON ReportAdjustments.[rPlanID] = ReportPlans.[ID]

-- All rows from ReportPlans, even those which do not have records in PlansAvailable.
LEFT OUTER JOIN [wolftracs].[dbo].[PlansAvailable] ON ReportPlans.[AvailID] = PlansAvailable.[ID]

-- All rows from PlansAvailable, even those which do not have records in CommissionScales.
LEFT OUTER JOIN [wolftracs].[dbo].[CommissionScales] ON PlansAvailable.[CommissionScaleID] = CommissionScales.[ID]

WHERE GroupInfo.GroupNumber = @GroupNumber and
CommissionScales.[Name] = @ScaleName and
substring(Sites.[Name], 6, len(Sites.[Name]) - 5) = @SitesName and
Reports.ReportMonth = @ReportMonth and
Reports.ReportYear = @ReportYear and
Reports.SiteID = @SiteId
GROUP BY Companies.[Name],
GroupInfo.GroupNumber,
CommissionScales.[Name],
substring(Sites.[Name], 6, len(Sites.[Name]) - 5),
Reports.ReportMonth,
Reports.ReportYear

-k