PM48715: STORED PROCEDURE DATA SOURCE NOT WORKING IN DATA MANAGER

APAR status

• Closed as program error.

Error description

• Client is connecting to a SQL Server 2005 database, they are
  trying to run a stored procedure as a data-source for a Data
  Manager build to get source data from a system.  The stored
  procedure uses some temp tables on the source side to perform
  some calculations.?Through SQL Server Management Studio, they
  have no issues running and receiving a result set from the SP.
  However, when they connect from DM, they are unable to reviece a
  result set.?Their syntax in the Data Source is "exec
  usp_ModelDetailsforDW"??I have tried using the SQL 2000 Oledb
  connection type and I get this error:?1. ERROR?DM-DBM-0400 UDA
  driver reported the following on connection
  rCasinos:?UDA-SQL-0564 [Microsoft OLE DB Provider for SQL
  Server]Invalid object?name '#SingleThemeModels'.
  (SQLSTATE=42S02, SQLERRORCODE=208)?If I switch it to 2005 Native
  I get:?1. ERROR?DM-DBM-0380 The SQL statement for data selection
  is not valid.?If I switch it to 2008 Native (which should be
  backwards compatible?with the 2005 source DB), I get:?1.
  ERROR?DM-DBM-0400 UDA driver reported the following on
  connection rCasinos:?UDA-SQL-0564 [Microsoft SQL Server Native
  Client 10.0]Invalid object?name '#SingleThemeModels'.
  (SQLSTATE=42S02, SQLERRORCODE=208)?FYI:   #SingleThemeModels is
  one of the temporary tables created within?the SP.?Is it
  possible to have Temp table in procedure for Data manager??I
  attaching their and my sample which gives us the same error and
  both of them execute fine in SQL Server 2005.??The have a
  workaround working which is to load it to a table first, then
  grab from the table.?
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See error description.                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to IBM Cognos Business Intelligence 10.2             *
  ****************************************************************
  

Problem conclusion

• Code Fix
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  COG8 BI DATA MG

• Fixed component ID

  5724W12DM

Applicable component levels