locked
reportviewer dynamic stored procedure RRS feed

  • Question

  • User152990470 posted

    Good day.  I have the following problem. In my asp.net 4.0 web system there is a stored procedure that puts together a sql string based on a users privilages (stored in a session variable session("priv") ) and then executes this string to return results.

     

    set @iwherestring = (SELECT dbo.filterstring('o.depot', 'Depot' ,@priv))
    SET @SQLString =  N'select o.orderno,i.itemNo,o.clientCode, o.orderdate, o.depot , i.product, i.amount from orders o join order_items i on o.orderno = i.orderno and o.clientCode = i.clientCode ' + @iwherestring;
    
    EXECUTE sp_executesql @SQLString
    


    The procedure works perfectly when called from withing SSMS but when i try to attach the stored procedure to the datasource of a reportviewer control it does not work. Is this becaue i am putting the sql together dynamically?

    Any help would be greatly appreciated!

    Wednesday, May 1, 2013 5:49 AM

All replies

  • User-67082742 posted

    If you are taking query type as Stored Proc and associating with stored proc, it might be a problem.

    Try using it in query statement itself  with the execute statement. Or is this what you are doing already?

    Wednesday, May 1, 2013 8:27 AM
  • User152990470 posted
    Hi thanks for the reply, the query works when i call the stored procedure from within the ssms query window. When i associate the report viewer contol with the stored procedure that fails...
    Wednesday, May 1, 2013 1:08 PM
  • User-67082742 posted

    yep... So please try doing the sam ein SSRS dataset query window. I mean add the stored proc with exec statement there and then it should work like it works in SSIS query window for you.

    Umm.. I think i am gettng it wrong. Do you mean you are associating stored proc from .NetCode to reportViewer control?

     

    Thursday, May 2, 2013 12:31 AM