Solved

MS-SQL CrossTab VIEW

Posted on 2004-08-12
14
1,532 Views
Last Modified: 2012-06-21
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:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21069502.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21068312.html
http://www.winnetmag.com/SQLServer/Article/ArticleID/15608/15608.html

I found an artice that explained how to execute a SP in a view:
SELECT     *
FROM         OPENQUERY(SEQUEL2,
                      'EXEC MyDB.dbo.sp_transform    @TableOrView_name = ''UV_TotalSpecialChargesYearSpecific'',     @Aggregate_Column = ''SumOfAmount'',     @Select_Column    = ''OVMPAY'',  @pivot_column     = ''ODHITN''')
                       Rowset_1

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;
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q279857
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20803949.html

Any ideas???
0
Comment
Question by:talal
  • 8
  • 3
  • 2
14 Comments
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11787292
What does the stored procedure do? Perhaps a user-defined function can be used instead. Can you post the code for the proc?
0
 

Author Comment

by:talal
ID: 11787348
Sure:

CREATE PROCEDURE sp_TRANSFORM  
 /*  
    Purpose:     Creates a Pivot(tm) table for the specified table,  
                 view or select statement  
   
    Input parameters:  
    @Aggregate_Function (optional)  
     the aggregate function to use for the pivot  
                 default function is SUM  
         @Aggregate_Column  
                 name of column for aggregate  
         @TableOrView_Name  
                 name of table or view to use  
                 if name contains spaces or other special  
                 characters [] should be used  
                 Can also be a valid SELECT statement  
         @Select_Column  
                 Column for first column in result table  
                 for this column row values are displayed  
         @Pivot_Column  
                 Column that is transformed into columns  
                 for this column column values are displayed  
  @DEBUG  
   Set this flag to 1 to get debug-information  
     
       Example usage:  
         Table given   aTable  
         content:      Product    Salesman    Sales  
                       P1         Sa          12  
                       P2         Sb          10  
                       P2         Sb          3  
                       P3         Sa          12  
                       P1         Sc          8  
                       P3         Sa          1  
                       P2         Sa          NULL  
  CALL  
  EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'  
 or      EXEC sp_Transform @Aggregate_Column='Sales', @TableOrViewName='aTable',  
                             @Select_Column='Product', @Pivot_Column='Salesman'  
   
 Result:  
         Product| Sa       | Sb       | Sc      | Total        
         -------+----------+----------+---------+---------  
         P1     | 12,00    |  0,00    |  8,00   |  20,00  
         P2     |  0,00    | 13,00    |  0,00   |  13,00  
         P3     | 13,00    |  0,00    |  0,00   |  13,00  
         -------+----------+----------+---------+---------  
         Total  | 25,00    | 13,00    |  8,00   |  46,00  
   
   
 */  
  @Aggregate_Function nvarchar(30) = 'SUM',  
  @Aggregate_Column   nvarchar(255),  
  @TableOrView_Name   nvarchar(255),  
  @Select_Column     nvarchar(255),  
  @Pivot_Column       nvarchar(255),  
  @DEBUG      bit = 1  
 AS  
 SET NOCOUNT ON  
 DECLARE @TransformPart   nvarchar(4000)  
 DECLARE @SQLColRetrieval nvarchar(4000)  
 DECLARE @SQLSelectIntro  nvarchar(4000)  
 DECLARE @SQLSelectFinal  nvarchar(4000)  
   
 IF @Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG', 'STDEV', 'VAR', 'VARP', 'STDEVP')    
  BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1, @Aggregate_Function) END  
 ELSE  
 BEGIN  
  SELECT @SQLSelectIntro = 'SELECT CASE WHEN (GROUPING('  +  
          QUOTENAME(@Select_Column)       +  
     ') = 1) THEN ''Total'' ELSE '   +  
     'CAST( + '                      +  
                                 QUOTENAME(@Select_Column)       +  
     ' AS NVARCHAR(255)) END As '    +  
     QUOTENAME(@Select_Column)       +  
     ', '  
  IF @DEBUG = 1 PRINT @sqlselectintro  
  SET @SQLColRetrieval =  
  N'SELECT @TransformPart = CASE WHEN @TransformPart IS NULL THEN ' +  
    N'''' + @Aggregate_Function + N'(CASE CAST(' +  
    QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +  
    N' AS VARCHAR(255)) WHEN '''''' + CAST('  +  
    QUOTENAME(@Pivot_Column) +  
    N' AS NVarchar(255)) + '''''' THEN ' + @Aggregate_Column +  
    N' ELSE 0 END) AS '' + QUOTENAME(' +  
    QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +  
    N') ELSE  @TransformPart + '', ' + @Aggregate_Function +  
    N' (CASE CAST(' + QUOTENAME(@Pivot_Column) +  
    N' AS nVARCHAR(255)) WHEN '''''' + CAST(' +  
    QUOTENAME(CAST(@Pivot_Column As VarChar(255))) +  
    N' AS nVARCHAR(255)) + '''''' THEN ' +  
    @Aggregate_Column +  
    N' ELSE 0 END) AS '' + QUOTENAME(' +  
    QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +  
    N') END FROM (SELECT DISTINCT ' +  
    QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +  
    N' FROM ' + @TableOrView_Name + ') SelInner'  
  IF @DEBUG = 1 PRINT @SQLColRetrieval  
  EXEC sp_executesql @SQLColRetrieval,  
                            N'@TransformPart nvarchar(4000) OUTPUT',  
                            @TransformPart OUTPUT  
  IF @DEBUG = 1 PRINT @TransformPart  
  SET @SQLSelectFinal =  
                            N', ' + @Aggregate_Function + N'(' +  
                            CAST(@Aggregate_Column As Varchar(255)) +  
                            N') As Total FROM ' + @TableOrView_Name + N' GROUP BY ' +  
                            @Select_Column + N' WITH CUBE'  
  IF @DEBUG = 1 PRINT @SQLSelectFinal  
  EXEC (@SQLSelectIntro + @TransformPart + @SQLSelectFinal)  
  IF @DEBUG = 1 PRINT 'FINAL ----- : ' + (@SQLSelectIntro + @TransformPart + @SQLSelectFinal)  
 END
GO
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11788788
Have you tried running the sp_Transform with @DEBUG=1 and using what is shown after FINAL: ----- to create your view?
0
 

Author Comment

by:talal
ID: 11789240
Yes i did... but it would be the same as just using the SQL statement (with CASE ELSE) which i did not want. In addition the columns are not the same each run.. FOr example more years will be next year .. etc.

I just wanted to know for once and for all if there is a way to get a CROSS TAB View .. or if i can execute a command inside the VIEW.

Thank you,
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11789278
Nope, other than being patient: you should be able to do it in SQL Server 2005, which has PIVOT and UNPIVOT operators.
0
 

Author Comment

by:talal
ID: 11789306
:( .. Someone in this big world may habe found away around this...
Could it be there no one so far really needed a CROSSTAD SQL view?

Any accountant out there?? :p

I have put a full day on this and i would like to see that end with results... I appreciate thoughts on work arounds from anyone.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11791675
I implemented your proc and a view based on that proc for one of my databases, and it works fine with no error, thus the code itself won't be the problem, i guess.

Can you tell us:
 * if SEQUEL2 is a local or remote SQL Server
 * what version you have (local and/or remote)
 * how the linked server is configured (if at all) etc

CHeers
0
 

Author Comment

by:talal
ID: 11794417
Yes, The fact that other people were able to get this to work got me here.
By the way, It works for me until you try SAVING the view. Were you able to save the view?

SEQUEL2 is a remote server (linking to the same box) Because the OPENQuery command works only with linked server i had to do this.

I am starting to think that this may be a configuration on the server itself.

I have SQL2000 (with latest SPs)
I used the configuration:
Data Access, User Remote Collation, RPC and RPC out are all set to true
Security is "Use the log in security context"

I tried changing around these settings but no luck.

Thank you,
0
 

Author Comment

by:talal
ID: 11794818
by the way;
I tried using OPENROWSET instead of OpenQuery...
Same thing.. It opens fine and runs fine when i run the view. BUT it does not save.

same error message:
MSDASQL was unable to begin a distributed transaction.
...... OLE\DB provider returned message ... Transaction context in use by another session
0
 

Author Comment

by:talal
ID: 11795094
Unbelievale!!!!
The fix was not even a thought!.

I was able to fix this problem by creating the view VIA query analyser (not the VIEW interface).

It worked!!!!!!!!!!!!!!!!

:) so THIS is how we do a CROSS TAB view!.. and how we execute a command in a VIEW
not the most efficient but hey!! does the job.
0
 

Author Comment

by:talal
ID: 11795101
Angel,, Thank you.. At least you confirmed to me that it worked... That made me put some more time on it.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 11803928
Yes, I had saved the view (using QA). I will try on monday to see if i get the same problem with the View interface (EM)...
Glad you found the fix!
CHeers
0
 

Author Comment

by:talal
ID: 12080661
:( I did not abandone the question.
I believe i solved my own problem, Thanks Angel has helped, so i will give Angel the points.

Thank you,
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now