x

SSRS brings no fields in from Stored Procedure dataset

I am trying to use a stored procedure as the dataset for an SSRS report. SSRS sees the stored procedure and brings in the parameters, but none of the fields. here is the stored procedure:

 USE [vmms]
 GO
 /****** Object:  StoredProcedure [dbo].[pr_property_exception_listing]    Script Date: 1/15/2015 10:35:00 AM ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 
 
 ALTER PROCEDURE [dbo].[pr_property_exception_listing] 
     @p_division_id int, 
     @p_location_code varchar(30),
     @p_listing_date date,
     @p_include_types nvarchar(1),
     @p_include_missing_flag bit
 AS
 --variable declaration --
 
 declare @asset_code varchar(30);
 declare @equipment_code varchar(30);
 declare @model_code varchar(30);
 declare @a_division_id int;
 declare @a_equipment_code varchar(30);
 declare @gl_no varchar(30);
 declare @e_equipment_code varchar(30);
 declare @e_model_code varchar(30);
 declare @e_active_flag bit;
 declare @status varchar(10);
 declare @customer_code_2 varchar(30) = Null;
 declare @equipment_code_2 varchar(30) = Null;
 declare @v_model_code varchar(30);
 declare @v_model_message varchar(50);
 declare    @v_asset_flag bit;
 declare    @skip_flag bit;
 declare @book money;
 declare @mia int;
 declare @last_seen_date date;
 declare @last_seen_message nvarchar(50);
 declare @last_seen_date_t date;
 declare @last_seen_message_t nvarchar(50);
 declare @location_desc nvarchar(50);
 declare @on_file_as nvarchar(30);
 
 -- cursor decaration --
 
 declare @history cursor
 set @history = cursor fast_forward
 for
 select    asset_code, 
         equipment_code, 
         model_code, 
         customer_code_2, 
         status_code, 
         equipment_code_2,
         gl_no
 from    EQUIPMENT_PHYSICAL_HISTORY
 where    division_id = @p_division_id
 and        location_code = @p_location_code
 and        history_date = @p_listing_date
 and        status_code is not null
 and        ((@p_include_missing_flag = 0
 and        status_code not in ('m', 'cwip'))
 or        @p_include_missing_flag = 1)
 order by asset_code;
 
 
 BEGIN
     SET NOCOUNT ON;
     set fmtonly off;

 -- get location description --
 select    @location_desc = description
 from    locations
 where    division_id = @p_division_id
 and        location_code = @p_location_code;

 -- Loop to go through history records --
 open @history;
 fetch    next 
         from    @history 
         into    @asset_code, 
                 @equipment_code, 
                 @model_code, 
                 @customer_code_2, 
                 @status, 
                 @equipment_code_2,
                 @gl_no;

 while @@FETCH_STATUS = 0
 begin
     -- skip record if status = m or dwip and last transfer is c, o, w, or i
     SET @skip_flag = 0;
     if @status = 'm' or @status = 'cwip'
     begin
         if    (select  max(tran_his_date) from equipment_transfer_history 
             where    division_id = @p_division_id
             and        asset_code = @asset_code
             and        customer_code = @p_location_code
             and        dbo.is_current_period(@p_division_id, tran_his_date) = 1
             and        type in ('c', 'o', 'w', 'i')) <>
             (select  max(tran_his_date) from equipment_transfer_history 
             where    division_id = @p_division_id
             and        asset_code = @asset_code
             and        customer_code = @p_location_code
             and        dbo.is_current_period(@p_division_id, tran_his_date) = 1)
             set @skip_flag = 1;
     end
     if @skip_flag = 0
     begin
         set @book = 0;
         -- get assets table values --
         select    @a_division_id = division_id,
                 @a_equipment_code = equipment_code,
                 @book = acquisition_cost + total_adj_amt - accum_depr
         from assets
         where    asset_code = @asset_code;
         if @a_equipment_code is null
             select    @a_division_id = division_id,
                     @a_equipment_code = equipment_code,
                     @book = acquisition_cost + total_adj_amt - accum_depr
             from assets
             where    division_id = @p_division_id
             and        equipment_code = @equipment_code;


         -- if asset is found, get the equipment record --
         if @a_equipment_code is not null
             select    @e_equipment_code = equipment_code, 
                     @e_model_code = model_code,
                     @e_active_flag = active_flag
             from    equipment where division_id = @a_division_id 
             and        equipment_code = @a_equipment_code
             
         -- set model fields --
         if @model_code is not null
         begin
             set @v_model_code = @model_code
             set @v_model_message = ltrim(@e_model_code) + ' (Model_on file)'
         end
         else
             set @v_model_code = @e_model_code

         -- If doing assets or non-assets, but not both, get asset_flag from model table --
         set @skip_flag = 0
         if @p_include_types = 'A' or @p_include_types = 'N'
         begin
             select @v_asset_flag = asset_flag from MODELS
             where    model_code = @v_model_code
             if (@p_include_types = 'A' and @v_asset_flag = 0) or
                 (@p_include_types = 'N' and @v_asset_flag = 1)
                 set @skip_flag = 1
         end

         if @skip_flag = 0
         begin
             set @mia = dbo.periods_on_pel(@p_division_id, @p_location_code, @asset_code, @p_listing_date);
             -- determine last activity --
             if @status = 'cs'
             begin
                 set @last_seen_message    = 'Customer # ' + @customer_code_2
             end
             if @status = 'wl'
             begin
                 set @last_seen_message = @p_location_code + ' - ' + @location_desc
             end
             if @status = 'm' or @status = 'cwip'
             begin
                 set @last_seen_date = '01-01-1950';
                 if @e_equipment_code is not null
                 begin
                     select top 1 
                             @last_seen_message = 'Cust # ' + customer_code + ' - WO # ' + str(work_order_id),
                             @last_seen_date = completion_date
                     from    WORK_ORDER_HEADERS
                     where    division_id = @p_division_id
                     and        equipment_code = @e_equipment_code
                     order by  completion_date desc
                     select top 1 
                             @last_seen_message_t = 'Cust # ' + customer_code + ' - EMO # ' + str(emo_id) + ' (Dlv)',
                             @last_seen_date_t = finish_date
                     from    EMO_HISTORY
                     where    division_id = @p_division_id
                     and        equipment_code = @e_equipment_code
                     and        complete_flag = 1
                     and        finish_date > @last_seen_date
                     order by  finish_date desc
                     if @last_seen_date_t > @last_seen_date
                         begin
                             set @last_seen_date = @last_seen_date_t
                             set @last_seen_message = @last_seen_message_t
                         end
                     select top 1 
                             @last_seen_message_t = 'Cust # ' + customer_code + ' - EMO # ' + str(emo_id) + ' (Pkup)',
                             @last_seen_date_t = finish_date
                     from    EMO_HISTORY
                     where    division_id = @p_division_id
                     and        equipment_code_2 = @e_equipment_code
                     and        complete_flag = 1
                     and        finish_date > @last_seen_date
                     order by  finish_date desc
                     if @last_seen_date_t > @last_seen_date
                         begin
                             set @last_seen_date = @last_seen_date_t
                             set @last_seen_message = @last_seen_message_t
                         end
                     select top 1 
                             @last_seen_message_t = 
                                 case
                                     when customer_code_2 is not null
                                         then  'Rec. from Location'+ customer_code_2
                                     else
                                         'Rec. from ' + outside_entity
                                     end,
                             @last_seen_date_t = tran_his_date
                     from    EQUIPMENT_TRANSFER_HISTORY
                     where    division_id = @p_division_id
                     and        equipment_code = @e_equipment_code
                     and        type like 'r%'
                     and        tran_his_date > @last_seen_date
                     order by  tran_his_date desc
                     if @last_seen_date_t > @last_seen_date
                         begin
                             set @last_seen_date = @last_seen_date_t
                             set @last_seen_message = @last_seen_message_t
                         end
                     if @e_active_flag = 0
                     begin
                         select top 1
                             @last_seen_message_t = 
                             case 
                                 when type = 'c'
                                     then 'Customer Sold on ' + convert(nvarchar(10), tran_his_date, 101)
                                 when type = '0'
                                     then 'Outside Sold on ' + convert(nvarchar(10), tran_his_date, 101)
                                 when type = 'w'
                                     then 'Written Off on ' + convert(nvarchar(10), tran_his_date, 101)
                                 end,
                             @last_seen_date_t = tran_his_date
                         from EQUIPMENT_TRANSFER_HISTORY
                         where    division_id = @p_division_id
                         and        equipment_code = @e_equipment_code
                         and        type in ('c', 'o', 'w')
                         and        tran_his_date > @last_seen_date
                     if @last_seen_date_t > @last_seen_date
                         begin
                             set @last_seen_date = @last_seen_date_t
                             set @last_seen_message = @last_seen_message_t
                         end
                     end
                 end        -- equipment available --
             end        -- @status = 'm' or 'cwip' --
             else
                 if @status = 'nof'
                 begin
                     select top 1
                         @last_seen_message = 
                         case 
                             when type = 'c'
                                 then 'Customer Sold on ' + convert(nvarchar(10), tran_his_date, 101)
                             when type = '0'
                                 then 'Outside Sold on ' + convert(nvarchar(10), tran_his_date, 101)
                             when type = 'w'
                                 then 'Written Off on ' + convert(nvarchar(10), tran_his_date, 101)
                             end,
                         @last_seen_date = tran_his_date
                     from EQUIPMENT_TRANSFER_HISTORY
                     where    asset_code = @asset_code
                     and        type in ('c', 'o', 'w')
                 end
                 set @on_file_as = null;
                 if @status = 'wa'
                     set @on_file_as = @asset_code
                 if @status = 'ws'
                     set @on_file_as = @equipment_code_2
                 if @last_seen_date = '01-01-1950'
                     set @last_seen_date = Null

                 
         end
     end
 fetch    next 
         from    @history 
         into    @asset_code, 
                 @equipment_code, 
                 @model_code, 
                 @customer_code_2, 
                 @status, 
                 @equipment_code_2,
                 @gl_no;
 end
 close @history;
 deallocate @history;

 END

Not sure what I am doing wrong. any help would be appreciated.

more ▼

asked Jan 15, 2015 at 06:17 PM in Default

avatar image

jacqui
1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Hi when you use this as your dataset prefix it with

SET FMTONLY OFF;

EXEC [pr_property_exception_listing] ect...;

There is a good SO that explains more here.

more ▼

answered Jan 16, 2015 at 08:00 AM

avatar image

@SQLShark
3.9k 6 11 14

Thanks, but as shown in the code, I am already using the set fmtonly off, trick. It didn't work. Also, apparently this is being depracated:(

Jan 16, 2015 at 12:44 PM jacqui
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x690
x7

asked: Jan 15, 2015 at 06:17 PM

Seen: 444 times

Last Updated: Jan 16, 2015 at 12:44 PM

Copyright 2017 Redgate Software. Privacy Policy