0
votes

I'm in a situation where we're converting reports from Crystal Report to SSRS (Sql Server Reporting Services).

The original report was provided results from a stored procedure which returned columns with the same exact name.

How this is possible? I explain you:

The previous programmer did some horrible hack. It put a select result into a #TempTable.

Let's assume that the select to stuff #TempTable does

SELECT a,b,c INTO #TempTable FROM mytable 

Then at the end of the stored procedure this is what is returned:

SELECT aliasTemp.*, a, b, c from #TempTable aliasTemp

The result returned is

a,b,c,a,b,c

This is HORRIBLE in terms of SQL but I can't change database as my customer doesn't want any change to be made to the production environment.

When I try to add this stored procedure as a datasource to the report I get this:

Error

Which is a self explanatory error which says that while reporting services tries to retrieve the fields that the stored procedure returns, if finds duplicate named columns so it won't work.

Since Reporting services allows you to use both sql queries or stored procedures, what I tought could be a solution would be doing this query directly into the report as a SQL SELECT

CREATE TABLE #tmpBus
(
   a INT,
   b INT,
   c INT
)

INSERT INTO #tempTable
Exec StupidStoredProcedure'Params'

Avoiding the extra columns retrieved which have the same name and extracting only the first 3.

But it says the extracted columns doesn't match the temp table.

How I can achieve this?

2
If you cannot change the existing stored procedure, create a new one (a copy) that works like it should. Get rid of the old one when the conversion is complete. Am I missing something? No need for this to be difficult, or add yet another hack where one already exists. Fix it! - R. Richards
I can't do any changes as customer refuses to modify database/serverside code, otherwise I would have fixed the stored procedure. - Liquid Core

2 Answers

0
votes

Your temp table needs 6 columns. When using exec with insert the table def must match: number of columns, data type and I believe nullability.

0
votes

I've found the solution myself, I've just renamed the fields differently in the temp table.

Here's the code, assuming that StupidStoredProcedure extracts these columns with identical names (SIGH :S)

a,b,c,a,b,c

IF OBJECT_ID('tempdb.dbo.#ARandomTempTable', 'U') IS NOT NULL
    DROP TABLE #ARandomTempTable

CREATE TABLE #ARandomTempTable (
  a NVARCHAR(255),
  b NVARCHAR(255),
  c NVARCHAR(255),
  a2 NVARCHAR(255),
  b2 NVARCHAR(255),
  c2 NVARCHAR(255)
)

INSERT INTO #ARandomTempTable
EXEC StupidStoredProcedure @StupidParam = "Hello"

SELECT
  a,
  b,
  c,
  a2,
  b2,
  c2
FROM #ARandomTempTable

This way I'm completely indipendent about the stored procedure output and I can work the resulting parameters with my naming at my will.

Thanks to who tried to help