Stored procedure does not prompt for parameters and hence does not refresh fields

Question

I stumbled on a rather VERY VERY annoying problem here. 

I am using SQL server 2008 SSRS. 

I have a stored procedure that is not returning anything for my fields. The refresh fields button does not ask for parameters and hence the data set does not have any fields associated with it.  This is not the first time that this has happened and it is super annoying. If I click on query designer and run the code the stored procedure parameters come up and the correct fields are displayed but then again no fields are updated in the Fields section of the data set. 

Pleas help me figure this out. In the meantime i will restart my computer perhaps that will help. 

Answer 1

Can you please post your result when you run stored procedure... and also the stored procedure details.

- Prabhas

Answer 2

Hi,

Here is the result set example:
ord_type ord_no oe_po_no ord_no item_no qty_ordered qty_to_ship unit_price discount_pct qty_bkord cus_no search_name promise_date
O 1134451 CS11127968                1134451 1422AI-ES       1 0 1.27 0 1 203100 customer name                              *PLANNED 11/01/2012

Here is the SP code:

ALTER PROCEDURE [dbo].[usp_ssrs_rcus_Customer_Backorder_report] @UserID nvarchar(300), @cmp_code char(20)
as 
/*
Programmer: Goran Borojevic 
Date: 8.17.2012 
Purpose: This stored procedure will be used in SSRS report called Customer Backorder report. The report will return all open orders for a specified customer. 
This stored procedure will be used in the Salesrep/Customer reports as well as employees. Needless to say security is essential. 
NOTE: 
For all cusotmers pass % for @cmp_code
*/

BEGIN
	if @UserID is null or @UserID = '' 
	begin 
		RAISERROR('User ID Cannot be blank',15,1)
	end
	
	DECLARE @EmpType char(1) 
	DECLARE @AccountNumber varchar(15) 	
	DECLARE @SQL NVARCHAR(MAX) 
	DECLARE @Cus_no char(12)
	DECLARE @cus_alt_adr_cd nvarchar(15)
	--GB. SQL injection prevention. we should not even get to here but lets follow procedure. 

	SET @UserID = REPLACE(@UserID,'''','') 
	SET @cmp_code = REPLACE(@cmp_code, '''','')
	SET @Cus_no = null 
	SET @cus_alt_adr_cd = NULL 

	if (patindex('%\%', @UserID)) <> 0 
	BEGIN
		set @UserID = right(ltrim(rtrim(@UserID)), len(ltrim(rtrim(@UserID))) - patindex('%\%',ltrim(rtrim(@UserID))))
	END
	
	SET @EmpType = (SELECT ltrim(rtrim(emp_type)) from ESYNERGY.dbo.vw_HLIUser WHERE USR_ID = @UserID)
	SET @AccountNumber = (select cmp_code from ESYNERGY.dbo.cicmpy c (nolock) inner join ESYNERGY.dbo.vw_HLIUser usr (nolock) on c.cmp_wwn = usr.cmp_wwn where usr.usr_id = @UserID)

	
	--security check here. Lets just grab set the account numbers in here. 
	IF (@EmpType = 'K')
	BEGIN
		select @Cus_no = MacolaCustNo, @cus_alt_adr_cd = AlternateAddress from vw_HLI_Customer where CusNo = convert(char(20),@AccountNumber)
	END
	--GB. 8/20/2012 FOR SECURITY REASONS lets cover the case if it is an employee or a rep
	ELSE IF (@EmpType in ('R','E') )
	BEGIN
		if(@cmp_code <> '%')
		BEGIN 
			select @Cus_no = MacolaCustNo, @cus_alt_adr_cd = AlternateAddress from vw_HLI_Customer where CusNo = convert(char(20),@cmp_code)
		END
	END
	
	

	SET @SQL =' 
	SELECT
		OEORDHDR_SQL.ord_type, OEORDHDR_SQL.ord_no, OEORDHDR_SQL.oe_po_no,
		OEORDLIN_SQL.ord_no, OEORDLIN_SQL.item_no, OEORDLIN_SQL.qty_ordered, OEORDLIN_SQL.qty_to_ship, OEORDLIN_SQL.unit_price, OEORDLIN_SQL.discount_pct, OEORDLIN_SQL.qty_bkord, OEORDLIN_SQL.cus_no,
		ARCUSFIL_SQL.search_name,
		HLIORDPRDTS_SQL.promise_date		
	FROM
		DATA.dbo.OEORDHDR_SQL OEORDHDR_SQL INNER JOIN DATA.dbo.ARCUSFIL_SQL ARCUSFIL_SQL ON
			OEORDHDR_SQL.cus_no = ARCUSFIL_SQL.cus_no
		 INNER JOIN DATA.dbo.OEORDLIN_SQL OEORDLIN_SQL ON
			OEORDHDR_SQL.ord_no = OEORDLIN_SQL.ord_no
		 LEFT OUTER JOIN DATA.dbo.HLIORDPRDTS_SQL HLIORDPRDTS_SQL ON
			OEORDLIN_SQL.ord_no = HLIORDPRDTS_SQL.ord_no AND
		OEORDLIN_SQL.item_no = HLIORDPRDTS_SQL.item_no
	WHERE
		OEORDHDR_SQL.ord_type in (''O'', ''I'')'
	IF @EmpType = 'R' 
	BEGIN
		SET @SQL = @SQL + ' AND OEORDHDR_SQL.slspsn_no = ''' + ltrim(rtrim(@AccountNumber)) + ''''
		if (@Cus_no is not null)
		begin 
			SET @SQL = @SQL + ' AND OEORDHDR_SQL.cus_no = ''' + @Cus_no + ''''
			IF (@cus_alt_adr_cd IS NOT NULL)
			BEGIN
				SET @SQL = @SQL + ' AND convert(NVARCHAR(15), dbo.MacolaRemoveZeroes(OEORDHDR_SQL.cus_alt_adr_cd)) = ''' + @cus_alt_adr_cd + ''''
			END
		end
	END
	ELSE IF @EmpType = 'K' 
	BEGIN 
		SET @SQL = @SQL + ' AND OEORDHDR_SQL.cus_no = ''' + @Cus_no + ''''
		IF (@cus_alt_adr_cd IS NOT NULL)
		BEGIN
			SET @SQL = @SQL + ' AND convert(NVARCHAR(15), dbo.MacolaRemoveZeroes(OEORDHDR_SQL.cus_alt_adr_cd)) = ''' + @cus_alt_adr_cd + ''''
		END
	END		
	ELSE IF (@EmpType = 'E')
	BEGIN
		if (@Cus_no is not null)
		begin 
			SET @SQL = @SQL + ' AND OEORDHDR_SQL.cus_no = ''' + @Cus_no + ''''
			IF (@cus_alt_adr_cd IS NOT NULL)
			BEGIN
				SET @SQL = @SQL + ' AND convert(NVARCHAR(15), dbo.MacolaRemoveZeroes(OEORDHDR_SQL.cus_alt_adr_cd)) = ''' + @cus_alt_adr_cd + ''''
			END
		end
	END
	ELSE
	BEGIN
		--final security check if I am not a rep, customer or employee I get nothing.
		set SET @SQL = @SQL + ' AND convert(NVARCHAR(15), dbo.MacolaRemoveZeroes(OEORDHDR_SQL.cus_alt_adr_cd)) = ''Invalid USER'''
	END

	--select @SQL
	execute sp_executesql @SQL
	
END

Answer 3

Remove duplicate ord_no column from the query and try

-Prabhas

Answer 4

Thanks but that is not it.

That would have errored the execution with that already exists kind of error.

I tried it and again it does not prompt for parameters. I also deleted the data set and re created it. I also closed down VS and re opened  it.

Answer 5

I manually added columns. That is a temp solution out of sheer desperation. L

Answer 6

Hi Be !

Lets try to do one thing;

1)

Go to your dataset Query -> Select Query Type as Text -> Press Expression 'Fx' icon and put below SQL in it;

SELECT	1 AS ID

Now press Refresh Fields button.

Now move to Parameter Tab of your dataset -> Delete all the parameter defined here. Let's see what it bring. It should surely be ID column only.

2)

Now go back to your dataset Query -? Select Query Type as Stored Procedure -> Press Expression 'Fx' icon and your Stored Procedure Name in it.

Now go to Parameter Tab of your dataset -> Add parameter '@Param1' in Parameter Name and '[@Param1]' in Parameter Value. Remove the single quote i put here. Please make sure that input parameter sequence should match the parameter sequence you have defined here.

Now lets press the Refresh Fields button once again and you will be able to see New Columns list that will be returned by your 'sp', to add one more point if you have any Integer as Input Parameter then try to put some Integer Value when it pop up's for Value when you Press Refresh Fields button.

Please let me know if this helps. Hopefully i have answered you correctly.

Thanks, Hasham Niaz

Answer 7

Hi Be05x5,

Based on your scenario, you can also try the "SET FMTONLY" statement which returns only metadata to the client. For the detailed information, please see the following article and a thread with a similar topic:

• SET FMTONLY
• Reporting Services 2008 - Store Procedure data set now showing fields?

Hope this helps.

Regards,
Mike Yin

---

Mike Yin

TechNet Community Support

Answer 8

Hi Be05x5,

The below solution works for me:

1). Use "Text" instead of "Stored Procedure" to create your dataset.

2). In the Query box, type "exec up_ReportStyle @ReportStyleId"

3). Click on Refresh Fields, type 1 in the Parameter Value, and click OK

4). Click OK again to finish.

Voila, the fields are now there! :)

Good luck!

Answer 9

I was having this exact same problem.  I hit refresh on my dataset and it would return a few columns but most were missing.  It did not let me select parameters.

Turns out the person who wrote the sp had included some debugging code near the top of the stored procedure.

IF @Debug = 1 
BEGIN
SELECT StudentID = @StudentID, StartTerm = @StartTerm, AcadLevel = @ProgAcadLevel,
					BeginGradDate = @BeginGradDate, EndGradDate = @EndGradDate
END

For some reason SSRS was picking this up as the result set even though I specified Debug = 0.  I commented this chunk out and it works correctly (even though I have similar debugging statements throughout).  Not sure why, but there you have it.

Answer 10

I found out what the problem is and unfortunately there is no good solution other then enter the field names manually.

The problem is with stored procedures that use execute sp_executesql command. Apparently SSRS cannot retreive data in this case.

I appologize if I am wrong.