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