.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL Server 8 pivot without the word pivot

Posted By:      Posted Date: October 05, 2010    Points: 0   Category :Sql Server

Using SQL Server 8.00.2039 to create a pivot-like table of one field from each of 159 tables of 850k records each where all tables even the final "Answer" table has a primary key - without going over 15 gigabytes and overloading the temporary file is my goal.

Because the Transform-Pivot command is non existent in this version, a way to place one column each from 159 tables, that have ~850k records each, into a column in the "Answer" table where all tables concern have a Primary key.  The "Answer" table should have 850k records with 159 additional columns other than the key and the two columns that concatenated to become the key. 

Since I'd like to have the "Answer" table with 53 of the columns having the data type decimal(9,2) and 106 of the columns having the data type decimal(7,0), I created it first with the Primary key, and the 2 fields that concatenate to become the Primary key and placed all zeroes in the 159 columns.  That took like 45 seconds.  But insert into won't acknowledge the key and tries to create an additional 850k rows.  Select into will create the table from scratch giving all of the columns (38,0) in the data type.  I would go with that, but it gets to over 20 gigs while its running, and overloads the temp database and so

View Complete Post

More Related Resource Links

SQL Server 2005 Analysis Services - Performance of Excel Pivot Tables

Hi, I've several cubes available for several users within my organization. I've the SQL Server 2005 and the Analysis Services running on the same machine but it has 30Gb of RAM, 8 processors and a quick enouth disk. The problems is that i've some automatic processes that "build" some excel reports and the users use pivot tables with olap connections to the cubes regularly. I've noticed that the excel "locks" when the user is drilling down the data and the server has only one processor (there are 8...) at 100%... Is there a way to make the analysis services use the maximum processing capacity that is available? The users are complaining about the slowness of the cubes... Thanks in advance Hugo

Word Automation Issue in Windows Server 2008 Hosting



The problem I am posting here is that I was facing nearly 2 weeks around. Any body comes with this stuff please help.

Word Automation in sample ASP.NET(C#) application.

I am using Microsoft.Office.Inetrop.Word Assembly for automation. Here I am reading a XXX.dot template file and fill the contents with dynamic data.

When i am executing my code in localhost:someportnumber the automation is working fine and I could get expected result and when I am hosting in my inetmgr(Windows XP is my OS) it is also working fine.

But the problem is that when I am hosting in my production server(Windows Server 2008 Standard Edition) I am not able to perform automation and results in the following error.

Data: System.Collections.ListDictionaryInternal
Message: Word has encountered a problem.
Source: Microsoft Word

The code gets failed in the following line:

ApplicationClass wordApp = new Microsoft.Office.Interop.Word.ApplicationClass();

Document wordDoc = wordApp.Documents.Add(ref oTemplate, ref oFalse, ref oMissing, ref oMissing); // Error in this line

I cannot able to proceed further. Can anybody please help me in solving this issue?

Thank you.

With Regards,


Office Services: Merging Word Documents on the Server Side with SharePoint 2010


Office 2010 and SharePoint 2010 include a new set of components called Application Services. These put a rich set of tools in a developer's bag for Office automation. We'll show you how to use Office OpenXML, Word Automation Services and SharePoint to build a simple application that merges separate status reports into a single document.

Manvir Singh, Ankush Bhatia

MSDN Magazine July 2010

Basic Instincts: Server-Side Generation of Word 2007 Docs


This month, Office Open XML, which allows ASP.NET and SharePoint developers to read, write, and generate Word, Excel, and PowerPoint documents on the server without running an Office desktop application there.

Ted Pattison

MSDN Magazine November 2006

Converting Word Documents to PDF using SharePoint Server 2010 and Word Services (Visual How To)

Watch the video and explore code as you learn how to programmatically convert Word documents to PDF format on the server by using Word Automation Services with SharePoint Server 2010.

Sample: Converting Word Documents to PDF using SharePoint Server 2010 and Word Automation Services

Explore the code in this visual how-to article as you learn how to programmatically convert Word documents to PDF format on the server by using Word Automation Services with SharePoint Server 2010.

Video: Converting Word Documents to PDF Format Using SharePoint Server 2010 and Word Services

Watch this visual how-to video as you learn how to programmatically convert Word documents to PDF format on the server by using Word Automation Services with SharePoint Server 2010. (Length: 6:15)

MS Word Component Not Getting Created on Windows 2008 64 bit Web Server Edition When I Try Using ASP

Hi All, This might look like a similar question to a lot of you considering there are a lot of web activity happening in terms of blogs/forums/suggestions that I have seen with reference to changes that one needs to do in COM+ Services to enable their ASP.NET Web Application to be able to create Word Objects & I have actually used them personally & they worked for me on Windows 2003 32 bit/64 bit servers. The issue started some 1 month back when I was told that we are upgrading our server to Windows 2008 Web Server edition 64 bit ‘cause of some real benefits it offers if used only as a web server. This was just great as we tested a lot of features /functionality that we were using on our existing server & everything seems to be working just fine on this product by Microsoft. However we also have one web module which is responsible for opening up a MS Word 2007 template file at runtime and fill it with data & is then converted into a PDF document. A lot of ideas on Web suggested that we should move onto Open XML standards to generate documents at runtime, which I have been able to do with no issues. But one activity which still requires me to create a Word document is when I have to convert/SaveAs the Word (docx) document into PDF. I tried all the COM+ & registry related changes that I did on my Windows 2003 64 bit server, but nothing seems

Pivot w/o aggregation...query not working

Hi all--Given this query, I need to make this query into a pivot table: select Personnel_id, First Name, Last Name, E-mail, Division, Region, Country, Manager, Question, Answer, Date Completed from #pivot where Question Answer 01. Received File No 02. Read File Yes 03. Signed File Yes ... Needs to look like this: Personnel_id First Name ... 01. Received File 02. Read File 03. Signed File ... Test01 Test No Yes Yes I have the following pivot query: SELECT [Personnel_id] ,[First Name] ,[Last Name] ,[Email] ,[Division] ,[Region] ,[Country] ,[Manager] ,[Question] ,[Answer] ,[Date Completed] FROM (SELECT [Personnel_id] ,[First Name] ,[Last Name] ,[Email] ,[Division] ,[Region] ,[Country] ,[Manager] ,[Question] ,[Answer] ,[Date Completed] FROM #pivot) topvt PIVOT (MAX([Personnel_id]) FOR Answer in ([1],[2], [3], [4], [5], [6], [7], [8] , [9], [10])) as pvt ORDER BY pvt.[Personnel_id] I get this error: Msg 207, Level 16, State 1, Line 83 Invalid column name 'Personnel_id'. Any ideas? Thanks!

Using Pivot to arranging data

Hello, I am attempting to optimize a selection query and I thought I would try a PIVOT. My question is simply is this the best way to do it? If not, why and what may be better? Please note the Tmstmp column is not necessarily spaced evenly like this example. Sample table:   Declare @temp table (Tmstmp datetime, Value integer ) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/01/10',1) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/02/10',2) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/03/10',11) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/04/10',1) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/05/10',3) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/06/10',5) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/07/10',11) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/08/10',1) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/09/10',2) ;With CTE as ( SELECT t1.Tmstmp as StartDate, t2.Tmstmp as EndDate, t1.Value as Start_Val, t2.Value as End_Val, Row_Number() Over(Partition By t1.TmStmp ORDER BY DateDiff(day,t1.TmStmp,t2.TmStmp)) as Seq FROM @temp t1 INNER JOIN @temp t2 ON t2.Tmstmp > t1.Tmstmp WHERE t1.Value = 1 AND t2.Value = 11 ) SELECT StartDate, EndDate, [2] as TmStmp2, [3] as TmStmp3, [4] as TmStmp4, [5] as TmStmp5 FROM ( SELECT a.StartDate, a.EndDat

PIvot Table in SSRS 2008

Hi All, Is there any data Region (Pivot) in SSRS 2008 or higher version? Our requirement is to show the report like excel pivot gererated from a OLAP Cube where we can select or unselect any dimesion attribute or measures. Thanks, IndiaSQl  

SSAS Cube & Pivot table

Hello, I am connecting to my SSAS cube through excel Pivot table. I have the following questions: 1) I have a filter for Company. However, instead of selecting multiple companies from a drop down list, I would like to have a range e.g. Company between 100 and 200. We have this "Filter expression and operator" option in SSAS but I dont see in excel. 2) I have 90 users who will be using my cube via pivot tables. How do I automatically have the data in excel refresh automatically such that when the user comes in, in the morning and open the pivot table, they have the refreshed data. Instead of them manually refresh it as it runs the query for a long time. Thanks in advance for all your help and suggestions! KJ

Excel 2007 Pivot table OLAP- SSAS 2008 Error

Hi, I do have a strange issue with Excel 2007 pivot table report; the data source is SSAS 2008 cube. When refresh the excel 2007 report data using the 'Refresh All' or 'Refresh' button available under the Data menu, the below error is thrown. "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." After some analysis, the report works well on the below workaround, 1. Remove all the calculated measures from report (3 measures in this scenario) 2. Remove one row label (dimension attribute) from report But the measures, attributes are valid one from cube and it is required fields in the report. So, understand that removing few items is not at all a solution. The below workaround also works. 1. Check the 'Defer Layout Update' option from the Pivot Table Field List window. 2. Click on the Update button After that Unchecked the 'Defer Layout Update' option and apply the filter, etc in the report. The report works very well. But every time, we need to use the above work around after open the excel report. I have NO clue on this issue. Please help to resolve this issue. Thanks, Jey


Hello EveryOne,               I am making a monthly attendance details of all employees who is working in my company. I am using sql server management studio 2005.       My table is in this format. staffcode   name                                   date            Intime   Outtime Duration                         1               Amit                                  01-08-2010    00:00    00:00    00:00 1              Amit                                   02-08-2010    09:52    20:01    10:09 1&n

using Live Labs Pivot in SharePoint 2010

Hi, Any pointers to get started with PIVOT in SharePoint applications. Want to create application similar to http://www.wssdemo.com/SitePages/LiveLabsPivot.aspx Any pointers will be very appreciated. Thanks!

How to use SharePoint Server 2007 SP2 to compare documents without opening one by one in Word ? (ver

Hi All, How to use / access the versiondiff.apsx feature ? I'm using SharePoint 2007 SP2 Enterprise but somehow i cannot use that features like in this website said: http://www.ssw.com.au/ssw/Standards/BetterSoftwareSuggestions/SharePointTeamServices.aspx for example: here's my document library - http://mossdev01/Correspondence/Forms/AllItems.aspx Diff - http://mossdev01/Correspondence/versiondiff.aspx --> FAILED 404 ? Any kind of help and suggestion would be greatly appreciated. Thanks, Albert/* Infrastructure Support Engineer */

Excel 2003 pivot table - parent showing grand total than the sum of its children

All, In Excel 2003, I have a product hierarchy, for simplistic sake lets say it goes A > B > C > model nbr.  I have filtered on model nbr 1.  I have the product hierarchy in my pivot table and unit cnt as the measure. Starting at level A in the hierarchy I am showing a total unit of 12000, if I drill down to B it still shows 12000, if I drill down to C it shows 100 and if I drill down to model nbr it shows me model nbr 1 with a unit cnt of 100. So my question is why are my parents not filtering the model nbr, it is taking the total for ALL records that have level A as their parent regardless of the model nbr? This does NOT happen in Excel 2007 or when I browse my cube in SSAS.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend