Monday, August 20, 2012 2:58 PM
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.
Monday, August 20, 2012 3:04 PM
Can you please post your result when you run stored procedure... and also the stored procedure details.
Monday, August 20, 2012 3:16 PM
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
Monday, August 20, 2012 3:25 PM
Remove duplicate ord_no column from the query and try
Monday, August 20, 2012 3:29 PM
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.
Monday, August 20, 2012 8:08 PM
I manually added columns. That is a temp solution out of sheer desperation. L
Monday, August 20, 2012 8:35 PMAnswerer
Hi Be !
Lets try to do one thing;
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.
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
Sunday, August 26, 2012 10:30 AMModerator
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:
Hope this helps.
TechNet Community Support
Tuesday, August 28, 2012 2:06 PM
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! :)
Wednesday, October 03, 2012 5:53 PM
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.
Wednesday, October 03, 2012 7:22 PM
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.
- Marked As Answer by Be05x5 Wednesday, October 03, 2012 7:23 PM