1

I'm working with SQL Server 2012 and SSRS. Using this very useful answer, I have a parameterized query working that pulls data from a DB2 system through openquery. I'd like to make this into an SSRS report. I don't see how to do it with a query, so I built a little stored procedure to wrap the query in, like so:

ALTER PROCEDURE dbo.QueryInfoByID
-- Add the parameters for the stored procedure here
@inputid varchar(10),
@outputfield varchar(50) output
AS
BEGIN
    DECLARE @TSQL varchar(8000);
    SELECT  @TSQL = 'SELECT * FROM OPENQUERY(IMG2677,''SELECT outputfield FROM mytable WHERE outputfield = ''''' + @inputid + ''''''')'
    EXEC (@TSQL)
END

How do I connect up the outputfield embedded in the @TSQL with the @outputfield in the output parameter list of the SP? I've tried some googling and can't find anything that addresses this. Apologies if this is a newbie question; I haven't done much with output parameters in stored procedures.

Community
  • 1
  • 1
Mike Christie
  • 381
  • 1
  • 10

2 Answers2

1

I believe you just need to alter your dynamic query to account for the parameter.

  ALTER PROCEDURE dbo.QueryInfoByID
-- Add the parameters for the stored procedure here
@inputid varchar(10),
@outputfield varchar(50) output
AS
BEGIN
    DECLARE @TSQL varchar(8000);
    SELECT  @TSQL = 'SELECT * FROM OPENQUERY(IMG2677,''SELECT '''' + @outputfield + '''' FROM mytable WHERE '''' + @outputfield + ''''= ''''' + @inputid + ''''''')'
    EXEC (@TSQL)
END
C Bell
  • 347
  • 1
  • 8
  • 1
    Thanks, but I think something more is needed. After some more reading I realized I don't want output parameters; I want a result set from the SP. However, the above doesn't seem to actually produce a resultset -- when I run it interactively it works, but SSRS doesn't see the resultset so I can't assign the fields. I tried declaring a table @t and changing the TSQL to "insert into @t select * from...' followed by a "select * from @t" to generate the resultset but it fails at SSRS execution time with "Must declare the table variable @t". – Mike Christie Jul 12 '17 at 19:44
1

There should be no reason why you can't just execute the same query you use when you run it interactivity. You should not even need a stored proc to do this. However, the SSRS account may not have access rights to execute the command.

I have done this in the past by creating a new SQL Server login, granting the correct permissions and executing the SQL statement with that account (I actuallt started off giving it full access to prove it worked then removed uneccessary access).

For example I created a login called AceDriverLogin that I use when accessing Excel files directly on the server.

Then build your SQL statement (in your case you have done this in @TSQL). Then execute the statement using the following syntax.

EXEC (@TSQL) AS LOGIN= 'AceDriverLogin'
Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • 1
    It does execute successfully. The problem appears to be that SSRS can't detect the resultset that is formed. If I put the EXEC (@TSQL) line of code into another stored procedure, and run that interactively in SSMS, I get a resultset output in the lower half of the screen as you would expect. When I tell SSRS to use that stored procedure to generate a report, it can't see a resultset, as it would if there were a "select * from..." statement in the SP. Instead it says "no fields" when I try to choose a data field to display. – Mike Christie Jul 13 '17 at 12:53
  • 1
    I have a workaround now, but it's ugly. If I do this: "SELECT @TSQL = 'truncate table ; Insert into SELECT * FROM OPENQUERY(..." then I I can do "select * from " and I get a resultset that SSRS can see. Obviously this wouldn't work for a report that has any chance of being used by two users, but in this case there's no more than one likely user (in IT) so it should be fine. Not something I would want to put in production, though. If anyone knows how to get around this I would still like to hear a solution. – Mike Christie Jul 13 '17 at 13:15