title: SSRS using a SQL stored procedure
body: <p>I have a stored procedure that retrieves data from a linked server.  I can run the stored procedure by setting the single variable.  The results end up in a table in my SQL 2008 R2 database.  The dataset in SSRS calls this stored procedure using the EXEC dbo.ImagePlaceholder @Name command.  Here is the stored procedure:</p>
  
  <pre><code>     USE [LagoReporting]
  
  
  GO
   /****** Object:  StoredProcedure [dbo].[ImagePlaceholder]    Script Date: 03/27/2013 14:22:21    ******/
      SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  -- =============================================
  -- Author:Tim Vavra
  -- Create date: 3/27/13
  -- Description: Stored procedure to create the table necessary to produce a report that shows all images that are listed
  -- as placeholders in Lago.  Included in table will be any job which has a street date greater than today - 30.
  -- =============================================
  
  
  ALTER PROCEDURE [dbo].[ImagePlaceholder]
  (
  @Name varchar(80)
  )
  AS
  BEGIN
  
      SET NOCOUNT ON;
      SET FMTONLY OFF; 
  
  
  declare @SQL nvarchar(4000)
  declare @KATBEZC varchar(80)
  
  drop table dbo.ImagePH
  
  set @KATBEZC = @Name    
  
  Create Table #ActiveTemp(
      media nvarchar(max),
      div nvarchar(max),
      folio nvarchar(max),
      pgblock nvarchar(max),
      item nvarchar(max),
      itemname nvarchar(max),
      photoinstruction nvarchar(max),
      photodirection nvarchar(max),
      PH nvarchar(100),
      imagename nvarchar(max))
  
  Create Table [dbo].[ImagePH](
      media nvarchar(max),
      div nvarchar(max),
      folio nvarchar(max),
      pgblock nvarchar(max),
      item nvarchar(max),
      itemname nvarchar(max),
      photoinstruction nvarchar(max),
      photodirection nvarchar(max),
      PH nvarchar(100),
      imagename nvarchar(max))
  
        set @SQL=
        N'insert into #ActiveTemp 
        select media, div,folio,pgblock,item, itemname, photoinstruction, photodirection,PH,imagename
        from  OPENQUERY(LAGO,
        ''SELECT DISTINCT KATBEZC media,
  art.artvf1c div,
  CONCAT(pagprec,pagpags) folio,
  pro.probezc pgblock,
  art.artvf2c item,
  art.artbezc itemname,
  art.artvf7c photoinstruction,
  art.artvf6c photodirection,
  bld.bldtmps PH,
  bld.bldfnmc ImageName
  FROM dok,
  pro,
  art,
  aez,
  kat,
  PAG,
  kav,
  kom,
  bld,
  boz
  WHERE kavkatkeyi = katkeyi
  AND artkavkeyi = kavkeyi
  AND aezartkeyi = artkeyi
  AND aezkavkeyi = artkavkeyi
  AND prokeyi = aezprokeyi
  AND prokavkeyi = aezkavkeyi
  AND komprokeyi = prokeyi
  AND (komkavkeyi = 0 OR komkavkeyi = prokavkeyi)
  AND dokkeyi = komdokkeyi
  AND (prosnei = doksnli OR prosnei = doksnei)
  AND dokkeyi = pagdokkeyi
  AND PAGPAVKEYI = KATPAVKEYI
  and bldtmps = 1
  and bozbldkeyi = bldkeyi
  and bozobjkeyi = artkeyi
  and katbezc = ''''' + @Name + '''''
  and boztyps = 2
  Group by kat.katbezc,
  CONCAT(pagprec,pagpags),
  art.artvf1c,
  pro.probezc,
  art.artvf2c,
  art.artbezc,
  kom.kombezc,
  art.artvf7c,
  art.artvf6c,
  bld.bldtmps,
  bld.bldfnmc
  '')'
  
      exec (@SQL)
  
      insert into dbo.ImagePH 
      select * from #ActiveTemp
  
  END
  </code></pre>
  
  <p>I am not getting the data back to the report although when I query the database dbo.ImagePH has all of the correct information.</p>
  
  <p>Thanks in advance for any help.  Sorry about the code format.</p>