From: tshad on

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9CE262BF3DD49Yazorman(a)127.0.0.1...
> tshad (toms(a)pdsa.com) writes:
>> The problem is that this is being executed by a reporting engine (of
>> which I have no control) and the it expects a View. It works fine as a
>> stored procedure but it needs to be a view and also as mentioned before
>> needs to run on SQL Server 2000.
>
> It's an impossible requirement. A view, just like a table, has a fixed
> number of columns with static names and data types.
>

I agree.

I have not been able to solve the issue nor has anyone else I have talked to
and have discussed it with the client to have him add the option of handling
a SP, which would make this a snap.


> You will have to talk with your management/client that what they are
> asking
> for cannot be done with the current platform.
>
> The best you can do from an SQL Server perspective is to write a multi-
> statement function that returns a fixed number of columns with fixed
> names,
> and the report tool gets what it gets.
>
Since we have no way of knowing this now or in the future, this wouldn't
work.

> Or they need to change/tweak the report tool so it can call a stored
> procedure.

That was what was decided.

Thanks,

Tom
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


From: Erland Sommarskog on
tshad (tfs(a)dslextreme.com) writes:
>> The best you can do from an SQL Server perspective is to write a multi-
>> statement function that returns a fixed number of columns with fixed
>> names, and the report tool gets what it gets.
>>
> Since we have no way of knowing this now or in the future, this wouldn't
> work.

At least not well.

>> Or they need to change/tweak the report tool so it can call a stored
>> procedure.
>
> That was what was decided.

That seems like the best decision.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx