Stored procedure does not prompt for parameters and hence does not refresh fields
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.
August 20th, 2012 11:00am

Can you please post your result when you run stored procedure... and also the stored procedure details. - Prabhas
Free Windows Admin Tool Kit Click here and download it now
August 20th, 2012 11:06am

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
August 20th, 2012 11:17am

Remove duplicate ord_no column from the query and try -Prabhas
Free Windows Admin Tool Kit Click here and download it now
August 20th, 2012 11:27am

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.
August 20th, 2012 11:31am

I manually added columns. That is a temp solution out of sheer desperation. L
Free Windows Admin Tool Kit Click here and download it now
August 20th, 2012 4:09pm

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
August 20th, 2012 4:35pm

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
Free Windows Admin Tool Kit Click here and download it now
August 20th, 2012 4:37pm

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 YinMike Yin TechNet Community Support
August 26th, 2012 6:31am

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!
Free Windows Admin Tool Kit Click here and download it now
August 28th, 2012 10:08am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics