This question has been asked alot.
Howvere there is no clear answers on how to do this.
I read alot of articles about this.
all of wich use Stored Procedure and seem to work fine.
However since i can not execute a stored procedure in a view i can not use the results as a VIEW and link it to another table, etc.
There was one article on the web that came pretty close in allowing me to execute a Stored Procedure in a view using OPENQUERY command and linked server. But it did not work for me.
Here are the articles that i tried:
On Stored Procedures that generate CrossTabs:
I found an artice that explained how to execute a SP in a view:
'EXEC MyDB.dbo.sp_transform @TableOrView_name = ''UV_TotalSpecialChargesYe
'', @Aggregate_Column = ''SumOfAmount'', @Select_Column = ''OVMPAY'', @pivot_column = ''ODHITN''')
It game me a long error about "Transaction context in use by another session"
and finally after getting the error message:
Transaction context in use by another session
I looked at this article and did not help;