JR35551: ON WINDOWS , MS SQL SERVER FEDERATED PROCEDURE CONTAINING QUERY STATEMENT AND ANY OTHER STATEMENTS CAN NOT RETURN RESULT SET

DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• On Windows platforms, when you create a federated procedure for
  a remote Microsoft SQL Server stored procedure, if there is any
  other statement defined before or after query statement in that
  stored procedure, after calling this federated procedure you
  can't get any records or description of result set.
  
   For example:
  
  1. Create a remote procedure like follow on SQL Server
  
   CREATE PROCEDURE SQLProcedure @V1 INTEGER AS
   BEGIN
   IF ( @V1 = 0 )
   SELECT * FROM SQLTable
   END
  
  2. Create an federated procedure like follow on Federation
  Server
  
  CREATE PROCEDURE FedProcedure SOURCE SQLSchema.SQLProcedure  FOR
  SERVER "SQLSERVER" WITH RETURN TO CLIENT ALL READS SQL DATA
  
  3. Call the federated procedure on Windows platform
  
  call SQLProcedure(0)
  
  You will get message "Return Status = 0" with no result set
  returned. This problem is because of that ODBC driver on Windows
  platform (SQL Server Native Client), as opposed to those on
  other platforms, returns an extra informational result set when
  stored procedure includes any other statement besides query
  statement, and Federation takes this informational result set as
  the first real result set.
  

Local fix

Problem summary

• Users affected:
     Users of the MS SQL Server wrapper in InfoSphere Federation
  Server
  Problem description and summary:
     See error description.
  

Problem conclusion

• Problem was first fixed in Version 9.1 FixPak 9 (s100326 ). This
  fix should be applied on the federation server.
  

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

  FEDERATION SERV

• Fixed component ID

  5724N9700

Applicable component levels

• R910 PSN

     UP

• R911 PSN

     UP