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:
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?