Variables in stored procedure not allowing refresh of Fields

• Jamie McCleary

  Grasshopper

  Points: 16

  More actions

  December 20, 2007 at 12:19 pm

  #181452

  I am having a problem in Reporting Serivces accessing an ORACLE stored procedure which has a variable populating one of the "fields" or columns.

  Background

  1. stored procedure works fine in all instances without the variable

  2. when variable placed into "insert" statement and then used as "column", PL/SQL runs the query with correct results, Reporting Services runs query with correct results, "refreshing" the list in Reporting Services causes error ORA-06550 with "check query syntax or click refresh fields on the query toolbar"

  I have tried the following with no solution as of yet..

  1. Using a separate stored procedure; result = cannot use the result in the same tables as is needed

  2. wrapping it in nvl(column,0) - tried this since I saw that the dataset was trying to pass in a null value only on the refresh of columns

  3. taking the "insert into variable [name] and creating an inline select within the stored procedure; result = works and runs great in ORACLE; takes too long to render in Reporting Services

  4. "buried" the variable inside an inline select query and then pulled it out with an "outer select"; result = runs but ignores the entire list of columns that were contained in the inline select

  If anyone has found a work-around for using a varibale to populate a "field" in Reporting Services, your help would be appreciated.

  Thanks!

  Jem

• Jeff Moden

  SSC Guru

  Points: 1004593

  More actions

  December 20, 2007 at 1:05 pm

  #761895

  Heh... I hate Oracle...

  I create a linked server to the Oracle DB, grab the data I need and store it in permanent "working" tables in SQL Server, and then all problems of this nature simply melt away. Might mean that you have to translate the Oracle proc into T-SQL, but well worth it in my humble opinion.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jamie McCleary

  Grasshopper

  Points: 16

  More actions

  December 20, 2007 at 1:13 pm

  #761898

  I appreciate the input but unfortunately I need this variable to change with each "user" who signs in.