Question on merging partitions

SQL Server Analysis Services

Index ‹ SQL Server
- Archive
  - SharePoint
  - Windows Vista
  - Dotnet
  - Visual Studio
  - Office
  - VB
  - VFP
  - Visual C#
  - IE
  - SQL Server
  - Game
  - VC++
  - Architecture
  - Smart Device
  - Windows Live
  - Windows Forms
  - VS Express
  - VS 2008
  - ISV

- Previous
  - 1
    - Cross Tab / PIVOT problems with multiple datatypes I am building a system that accesses personal data in a view based on two tables. The view is there because the number of used/necessary fields changes with every import of data (which is, perhaps once or twice every week, approx. 50 weeks per year). The view is thus regenerated with every import. I have a Person table with fixed data such as name and address (see below), and I keep statistics and other variables for each person in a second table: PersonStat. Person table ID (PK) Name Address PhoneNo PersonStat keeps one record per person and statistics variable, and thus looked like this: ID (PK) PersonId (FK) StatName StatValue I put these two together in the view making the data easy to access as if it were one single table. Let's say PersonStat has these values for a specific record in Person: 'AcctNo', '9812xyz' 'HasChildren', 1 'BirthDay', '09/26/1946' 'Income', $25,000 This means I want the view to show this record: ID, Name, Address, PhoneNo, AcctNo, HasChildren, BirthDay, Income with data (in this case) being: 1, 'Per Bylund', '2 OneStreet', '800-555-1234', '9812xyz', 1, '09/26/1946', 25,000 The view will contain a few fields for which some persons don't have values, but I'm ok with that. The SELECT statements will usually be "IS NOT NULL" queries in order to find persons who, for instance, have children or accounts. Flexibility and easy-to-use are first priorities. I'm using the PIVOT command to create a representation of data in the view which worked fine. However, I need to change it so that I get to keep the data type of each variable (in the example above everything, i.e. StatValue, is varchar()). End users must be able to use specific functions to find certain dates, income intervals, etc. Varchar() doesn't support all those kinds of queries, so I need specific data types. To accomplish this I have changed the PersonStat table to support five common data types: ID (PK) PersonId (FK) StatName StatValue_int (int) StatValue_bool (bit) StatValue_dec (decimal) StatValue_char (varchar()) StatValue_date (smalldatetime) But now I cannot get the PIVOT function to work. My procedure requires one data type only, since it cannot implicitly convert types. How do I solve this I have tried adding CASE statements in order to get the value for each PersonStat parameter, but that didn't work. I don't know how to solve this problem: can the PIVOT command be tweaked to fit my needs or is there perhaps better ways than using the PIVOT command Please help. The procedure I'm currently using looks something like this (I've stripped it from several variables not part of the problem) and it is also the basis for the view: DECLARE @Sql varchar ( 2000 ), @Columns varchar ( 500 ) SET @Sql = 'CREATE VIEW PersonPivot AS (' --Generate a list of columns that apply to this set of data SELECT @Columns = COALESCE ( @Columns + ', ' , '' ) + '[' + RTrim ( LTrim ( tblPerson . StatName )) + ']' FROM ( SELECT DISTINCT StatName FROM PersonStat ) tblPerson --Build the SQL statement. SET @Sql = @Sql + ' SELECT T1.*, T2.' + REPLACE ( @Columns , ', ' , ', t2.' ) + ' FROM Person T1 JOIN ( SELECT * FROM ( SELECT PersonId, StatName, StatValue FROM PersonStat INNER JOIN StatDirectory ON ContactStat.StatId = StatDirectory.ID ) P PIVOT ( MAX(StatValue) FOR StatName IN (' + @columns + ') ) AS P ) T2 ON T1.ID = T2.PersonId )' --Return the data EXEC ( @Sql ) Tag: Question on merging partitions
  - 2
    - How to get relation between two feilds of same table Hi, I am new to sql and is working with sql server managment 2005 +c# 2005. My application needs to create a blockdiagram sort of thing say if in my database i got a table 'Addition' with 'a', 'b', 'c',and the primary key addition_id, and c is related to a and b as c = a+ b. there is stored procedure name usp_addition which contains this relation. Each time any insert or update is done this sp is executed and all the values are updated for accordingly. My problem starts in the front end where i need to draw the graphical representation of table addition. In this graphical representation, I need to draw the labels a, b, c and the arrows from a and b which will connect to c, showing that c has a, b as inputs. I got the label using dataset and datacolumns but hte problem is how to create the arrows the name of labels (i.e my column names from which the arrow should start and end) How does I get the information that c as two inputs a, b. I dont need the values since i just want to view the columns in table and which column is input to another column. Since I need to do this dynamically because my tablename, and the number and name of column would differ does any body knows how to do this. Priyadarshini Tag: Question on merging partitions
  - 3
    - Varbinary(max) configuration I have been instructed to setup a database that will allow the storing of files in binary format. I have looked at using the varbinary(max) data type on a column to store the information. I am not sure based on books online how much information this can store. I have files that could be as large as 5-10 GB in size. Will SQL 2005 support storing these in binary format with that data type Tag: Question on merging partitions
  - 4
    - SQL 2005 RS - Report Manager Every morning it takes about 30 seconds to load Report Manager on the server. (But of course next loading is within speed of light ). Any ideas how to decrease load time Tag: Question on merging partitions
  - 5
    - install sql 2005 on a machine that has desktop engine My coworker tried install SQL server 2005 on a server. The server already has a desktop engine running as "MSSQLServer". Because the new sql server 2005 need running as a default instance. So he still using "MSSQLServer" as the instance name. After installation, the service's running file seems still point to the old location. When view the properties on Management studio, it still report as "desk top engine" and the version is 8.xx..xxxx. My question is, 1. What has been done during the installation Is current desktop engine was upgraded 2. In current situation, what can we do to install the SQL server 2005 as a default instance. Is there way to rename the desktop engine to a named instance Thanks Tag: Question on merging partitions
  - 6
    - AS dont process cubes Hi, my english is very bad, so i try to explain.... We have a MOLAP structure and several jobs executing SSIS packages. This jobs yesterday and today don’t finished , just stay executing without error messages and the cubes not up to date. I stop and restart AS instance and the cubes is up to date. But i can not do this every time. I see in Event viewer and dont have any error messages. I see in other forun to disable proactive caching...is this correct ...wich problems i can have doing this ...and what i can do to resolve this problem .. Thanks Tag: Question on merging partitions
  - 7
    - Common Criteria certification I see that part of SP2 is the new CC certification -- can anyone give me some more details. What level is it at EAL4 I can't seem to find the certificate on the CC Web site but it could be because it's not SP2 yet. Tag: Question on merging partitions
  - 8
    - Web Site Admin Tool problem in VS2005 All, When I create a new project and try to use the WSAT security tab, I get the following : There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store. The following message may help in diagnosing the problem: Unable to connect to SQL Server database. I am using VS2005 beta2 with a local copy of SQL Server Express. I am new to all of this so a little help would be great Thx jonpfl Tag: Question on merging partitions
  - 9
    - Missing entities from sdml editing in SharePoint integration scenario I am trying to create a test Reporting Services 2005 installation on Microsoft Office Server 2007 and have downloaded the addin at http://www.microsoft.com/downloads/details.aspx FamilyID=1e53f882-0c16-4847-b331-132274ae8c84&DisplayLang=en and have configured it. However, I am running into a problem creating a report model sdml file. If I just create it from scratch all it asks me for is the rsds file, and generates it without any editing. When I click on the Edit dropdown in Sharepoint document library, it has "Load in Report Builder" which seems incorrect. Further when I do open the sdml file in Report Builder, it only provides me a subset of the tables that are available in the database. Several tables and views are missing. The experience seems almost the same as when trying to create a report itself rather than a report model. I also have my report development environment with Reporting Services and Visual Studio on the same server and do not have problems seeing the missing table and views. Thanks. Tag: Question on merging partitions
  - 10
    - KB921896 updates did not install error code 0x3b0c Hi, I tried to instal KB921896 and get: updates did not install error code 0x3b0c. In one of the summaries, I have Product Installation Status Product : Express Database Services Adv (WSUS) Product Version (Previous): 2047 Product Version (Final) : Status : Failure Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9Express_Hotfix_KB921896_SQLEXPR_ADV.EXE SQL Express Features : SQL_Data_Files,SQL_Engine,SQL_SharedTools Error Number : 15116 Error Description : Unable to install Windows Installer MSI file How do I fix this thanks Tag: Question on merging partitions
  - 11
    - Make duplicate db? I am wanting to be able to take my work home with me, problem is I can access the server from there. Is it possible to work with a local instance on my laptop, then when completed transfer it to another instance on a different pc Meaning, talbes,queries,etc. Thanks Davids Learning Tag: Question on merging partitions
  - 12
    - Error on Date Data During Insert Hi Folx, I am new to SQL Server and I am struggling with source data for a table with two db_datetime columns. The data can be inserted using native SQL, but errors when I build a Data Flow Container. Versions: Microsoft SQL Server Integration Services Designer Version 9.00.1399.00 Microsoft SQL Server Management Studio 9.00.1399.00 Error: [Flat File Source [1]] Error: The "output column "extraction_date" (24)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "extraction_date" (24)" specifies failure on error. An error occurred on the specified object of the specified component. Source Row: 101|100|2006/12/19 23:50:01.00|2006/12/19 23:50:01.00||| How I got here (in approximate order...): Using ETL I created a Container in which I created a Package Created a Flat File Connection Created a Flat File Source Edited the columns on the Flat File Connection database timestamp [DT_DBTIMESTAMP] NOTE: corresponding columns on destination table in SQL Server Management Studio are of type datetime Created an OLE DB Destination Executed the package which returned the above named error. I mucked about with data types, Derived Columns, Data Conversion, etc and Googled for solutions without success. One of the errors (which I could not duplicate...) I noticed during my "experimentation" was an out-of-memory condtion. Questions: 01. What is the proper format for my source data 02. Could this be a memory issue If so, how do I diagnose it In advance, thanks for your help. Bill ps: be kind to me...not only am i an old guy, but i'm a unix guy too... Native SQL that works: insert into "transaction" (transaction_id, org_id, extraction_date, create_date, transaction_size, profile_count, version_count) values (101,100, '2006/12/19 23:50:01', '2006/12/19 23:50:01', NULL, NULL, NULL) Tag: Question on merging partitions
  - 13
    - Data flow based on a DSV Hi, I've got this feeling that something is missing in Integration Services. You can add a DSV in any Integration Services project, but you can't use it inside a data flow. Can someone please tell me why this is Let me explain why i want this: In Dynamics AX you've can make perspectives. These perspectives are a combined set of tables. From these perspectives you can automatically deploy these as Report Models. The Reporting Services generates a DSV for you, based not only on physical tables but also on Business logic. So it generates the linking between the tables (based upon application logic), derrived enumerated tables, referred tables, etc.. So these generated DSV's are great to get a quick startup. So repeating my question. Why can't i select a DSV as source What is the function of a DSV inside Integration Services Hoping to receive an answer for this question. Kind regards, Johan Blad Tag: Question on merging partitions
  - 14
    - expression problem count disctinct Hello all, I have a problem to do a count disctinct on two different fields within reporting services using an expression. In my dataset i have a field called Description and a field called Type. I would like to do a count distinct for every Description with Type=0 and a count distinct for every Description with Type=1. Can this be done with an expression within RS (and not within my sql query from my dataset) Greetings Vinnie Tag: Question on merging partitions
  - 15
    - How to configure Smo.Scripter to get desired sprocs Hi, I've developed a sproc generator using SMO types and all works fine except when I want to generate sprocs prefaced by a drop statement. To do this I create an instance of the Scripter object and set it's options.ScriptDrops property to true ( instance.options.ScriptDrops = true). When options.ScriptDrops = true the output omits the actual stored procedure from the scriopt. When I comment out scriptDrops = true it works correctly. How do i get it working correctly WITH drop statements Example: private void CreateScripter() { if( this._SprocSscripter == null ) { this._SprocSscripter = new Scripter(); this._SprocSscripter.Server = this._tables.Parent.Parent; this._SprocSscripter.Options.IncludeHeaders = true; this._SprocSscripter.Options.IncludeIfNotExists = true; this._SprocSscripter.Options.DdlBodyOnly = false; this._SprocSscripter.Options.DdlHeaderOnly = false; this._SprocSscripter.Options.ExtendedProperties = true; this._SprocSscripter.Options.ScriptDrops = true; } } OUTPUT: /****** Object: StoredProcedure [dbo].[usp_selectCategories] Script Date: 01/18/2007 16:39:29 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_selectCategories]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_selectCategories] OUPUT when i comment out last line ( this._SprocSscripter.Options.ScriptDrops = true;) /****** Object: StoredProcedure [dbo].[usp_selectCategories] Script Date: 01/18/2007 16:37:04 ******/ IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_selectCategories]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[usp_selectCategories] @CategoryID [int] AS SELECT [CategoryID], [CategoryName], [Description], [Picture] FROM [dbo].[Categories] WHERE [CategoryID] = @CategoryID ' END If I Comment out all lines in the CreateScripter method exect the 1st, 2nd and last ( this._SprocSscripter.Options.ScriptDrops = true;) The output looks like this: DROP PROCEDURE [dbo].[usp_selectCategories] That's it-- no sproc body.. what gives Tag: Question on merging partitions
  - 16
    - Turning off validation I need to turn off validation and I've seen some threads saying this is not possible but my situation has a twist. A customer needs the package to connect to different modem dialup connections to connect to different servers (they use dialup for security reasons). We have written two VB script tasks at the beginning and end of a loop, with data flows in between. Before the loop the dialup connection info is read into a recordset along with Data Source connection information. The first script uses this information to dialup and the last script hangs up the connection. The problem is the package tries to validate the data connections and the package has not dialed up yet, so it fails. We managed to confirm it works in a test environment by putting a break in the first script, manually VPNing into the test network (to allow validation of the data flow to work), and then manually disconnecting from VPN during the break. The script dials in and pumps the data. But this won't be an option in production. So if anyone has figured out a way to turn off validation, great. Otherwise, any ideas to make this work I was thinking about setting up a dummy connection that would be connected outside the package before running just for validation (and then the script would disconnect to begin, but I would prefer to handle all of this within SSIS. Any help While I see the point of validation it's a bummer that MSFT didn't put this in the hands of the user. Thanks, Kayda Tag: Question on merging partitions
- Next
  - 1
    - SP2 Upgrade Failure - 11032 - Unable to install Windows Installer MSP file SQL Server 2005 x64 Developer Edition Log output from the SP2 installation... ------------------------------------------------------------------------------------------------ Product : Database Services (MSSQLSERVER) Product Version (Previous): 2221 Product Version (Final) : Status : Failure Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log Error Number : 11032 Error Description : Unable to install Windows Installer MSP file ------------------------------------------------------------------------------------------------ The C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log file is a 5mb file that I can't make much sense out of...here are the first many lines of the file. I'll be glad to provide more information - just let me know what you need to know... Thanks, Dave === Verbose logging started: 8/15/2007 20:32:13 Build type: SHIP UNICODE 3.01.4000.4042 Calling process: e:\47b31966cf7dcaa4e0fe0fa356ab34da\hotfix.exe === MSI (c) (D4:9C) [20:32:13:508]: Resetting cached policy values MSI (c) (D4:9C) [20:32:13:508]: Machine policy value 'Debug' is 0 MSI (c) (D4:9C) [20:32:13:508]: ******* RunEngine: ******* Product: {F14F2E25-99AF-42A9-977C-F6D0352DC59F} ******* Action: ******* CommandLine: ********** MSI (c) (D4:9C) [20:32:13:508]: Client-side and UI is none or basic: Running entire install on the server. MSI (c) (D4:9C) [20:32:13:508]: Grabbed execution mutex. MSI (c) (D4:9C) [20:32:13:508]: Cloaking enabled. MSI (c) (D4:9C) [20:32:13:508]: Attempting to enable all disabled priveleges before calling Install on Server MSI (c) (D4:9C) [20:32:13:508]: Incrementing counter to disable shutdown. Counter after increment: 0 MSI (s) (9C:BC) [20:32:13:539]: Grabbed execution mutex. MSI (s) (9C:FC) [20:32:13:539]: Resetting cached policy values MSI (s) (9C:FC) [20:32:13:539]: Machine policy value 'Debug' is 0 MSI (s) (9C:FC) [20:32:13:539]: ******* RunEngine: ******* Product: {F14F2E25-99AF-42A9-977C-F6D0352DC59F} ******* Action: ******* CommandLine: ********** MSI (s) (9C:FC) [20:32:13:539]: Machine policy value 'DisableUserInstalls' is 0 MSI (s) (9C:FC) [20:32:13:539]: Adding MSIINSTANCEGUID to command line. MSI (s) (9C:FC) [20:32:13:539]: End dialog not enabled MSI (s) (9C:FC) [20:32:13:539]: Original package ==> C:\WINDOWS\Installer\1f00a5.msi MSI (s) (9C:FC) [20:32:13:539]: Package we're running from ==> C:\WINDOWS\Installer\1f00a5.msi MSI (s) (9C:FC) [20:32:13:586]: APPCOMPAT: looking for appcompat database entry with ProductCode '{F14F2E25-99AF-42A9-977C-F6D0352DC59F}'. MSI (s) (9C:FC) [20:32:13:586]: APPCOMPAT: no matching ProductCode found in database. MSI (s) (9C:FC) [20:32:13:586]: Detected that this product uses a multiple instance transform. MSI (s) (9C:FC) [20:32:13:586]: MSCOREE not loaded loading copy from system32 MSI (s) (9C:FC) [20:32:13:586]: Applying multiple instance transform ' qlRun01.mst'... MSI (s) (9C:FC) [20:32:13:586]: Looking for storage transform: SqlRun01.mst MSI (s) (9C:FC) [20:32:13:586]: Validating transform 'SqlRun01.mst' with validation bits 0x809 MSI (s) (9C:FC) [20:32:13:586]: Transform 'SqlRun01.mst' is valid. MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2205 2: 3: Patch MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2205 2: 3: PatchPackage MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: _Tables 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: _Columns 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: Media 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: File 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: TRANSFORM: 'PatchPackage' table is missing or empty. No pre-transform fixup necessary. MSI (s) (9C:FC) [20:32:13:586]: TRANSFORM: Applying regular transform to database. MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: _Tables 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: _Columns 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: _Validation 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: File 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: MsiFileHash 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: Signature 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: RegLocator 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: AppSearch 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: CompLocator 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: DrLocator 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: Binary 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: CustomAction 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: Directory 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: Registry 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: Condition 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: CreateFolder 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: _sqlObjectSecurity 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: Icon 3: -2147287038 MSI (s) (9C:FC) [20:32:13:586]: Note: 1: 2262 2: InstallExecuteSequence 3: -2147287038 ... Tag: Question on merging partitions
  - 2
    - converting sql server 2005 data into xml format How to convert table in database, in microsoft sql server 2005 into xml format Tag: Question on merging partitions
  - 3
    - Problem with package.Execute passing variables I am having a problem with passing variables into my SSIS package from C#. The variable names match ("Variable1, ...), however they do not seem to be assigned the proper values once the package is executed. The package does run and returns a FALURE notice saying there is a problem with my expressions. When I added a new data flow, derived all the variables into columns and wrote their values to a flat file I noticed that the values still contain my default values from the SSIS package itself as though nothing was passed in from C#. I am hoping that it is a simple configuration/user error. Any ideas ---- C# ---- Reference to Microsoft.SQLServer.ManagedDTS using Microsoft.SqlServer.Dts.Runtime; Application DTSApp = new Application (); Package DTSPack = DTSApp.LoadPackage( "d:\\SSISPackages\\Package.dtsx" , null ); DTSPack.Variables.Add( "Variable1" , true , "" , var1.ToString()); DTSPack.Variables.Add( "Variable2" , true , "" , var2); DTSPack.Variables.Add( "Variable3" , true , "" , 100); DTSPack.Variables.Add( "Variable4" , true , "" , var4.ToString()); DTSExecResult pkgResult = DTSPack.Execute( null , DTSPack.Variables, null , null , null ); Tag: Question on merging partitions
  - 4
    - Conditional checks in SSIS. I have two connections in a package pointing to two different databases on the same server. I have to insert records from 'DB1' table 'Gender1' to 'DB2' table 'Gender2'. Before I do that though, I have to make sure the minimum value (of all the Gender Keys that are going to be inserted) of 'DB1' 'GenderKey' (which is an identity field) is greater than the maximum value of DB2-GenderKey (which is a primary key but not an identity field). How can I do this simple check I have to do this process for many different tables ....... Gender table is just an example. If someone can give an detailed explanation on which tasks to use and how to use them (as I am relatively new to SSIS) that'd be great. Tag: Question on merging partitions
  - 5
    - Checkboxes in Reporting Services? Hi all Is it possible to have a checkbox as a parameter.. So a user can check a box to include a column or uncheck to hide a column Thanks Neil Tag: Question on merging partitions
  - 6
    - Navigation from the header Hello I have a link on the header to jump to another report. It was working fine on test server but when deployed to production, is giving the followin error. Any suggestions Thanks Inder The path of the item "(null)" is not valid. The path must be less than 260 characters long and must start with slash. Other restrictions apply. (rsInvalidItemPath) Tag: Question on merging partitions
  - 7
    - Problems trying to insert data into the database using excel Hi all, I have encountered a problem. In the past i run a sql query to select all the data from the excel file and insert them into my SQL database. However recently i encountered an error when i run the query. Msg 7399, Level 16, State 1, Line 4 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "excel_ls" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 4 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "excel_ls". How i solve this error. Any ideas Thanks Tag: Question on merging partitions
  - 8
    - tempdb Log file - stop/start SQL Server to reset tempdb??? Hello when I start executing a transact SQL process, my tempdb file is about 5Mb and It's log file is 1Mb, when the process finishes the log file never gets cleaned again... so It uses a lot of hard disk space and I can't run the process again... Pd. the process has a "begin tran" and a "commit" at the end... Could you tell me what to do Tag: Question on merging partitions
  - 9
    - Code to flatten a CellSet into a datatable Hi, Here's some code I've written which flattens a CellSet into a datatable. I looked for a long time to find similar code but could not find any. Can I please have some thoughts on optimisation/elegance etc... I'm a bit new to .NET so any advice would be great! Thanks. Code Snippet Public Function GetFlatData() As DataTable 'If (Me.Cells.Count = 1) Then ' Return New DataTable() 'End If Dim table As DataTable = New DataTable() Dim headers As ArrayList = New ArrayList() For a As Integer = 0 To (Me.Axes.Count - 1) For i As Integer = 0 To (Me.Axes(a).Set.Hierarchies.Count - 1) Dim h As Hierarchy = Me.Axes(a).Set.Hierarchies(i) If (Not h.Name.Contains("Measure")) Then table.Columns.Add(h.Name) End If Next i Next a Dim items As ArrayList = New ArrayList() For x As Int32 = 0 To (Me.Axes(0).Set.Tuples.Count - 1) Dim tx As Tuple = Me.Axes(0).Set.Tuples(x) Dim itemBase As CellItem = New CellItem() For Each mx As Member In tx.Members If (mx.LevelName.Contains("Measure")) Then If table.Columns.IndexOf(mx.Caption) = -1 Then table.Columns.Add(New DataColumn(mx.Caption, GetType(Double))) End If itemBase.AddMeasure(mx) Else itemBase.AddAttribute(mx) End If Next If (Me.Axes.Count > 1) Then For y As Int32 = 0 To (Me.Axes(1).Set.Tuples.Count - 1) Dim item As CellItem = itemBase.Clone(True) Dim ty As Tuple = Me.Axes(1).Set.Tuples(y) Dim memy As Member For Each memy In ty.Members If (memy.LevelName.Contains("Measure")) Then item.AddMeasure(memy) Else item.AddAttribute(memy) End If Next item.ValueCell = Me.Cells.Item((y * Me.Axes(0).Set.Tuples.Count) + x) 'item.ValueCell = Me.CellGrid(x, y) items.Add(item) Next Else ' for 1 axis cellsets itemBase.ValueCell = Me.Cells.Item(x) items.Add(itemBase) End If Next '' might have no measures need to cater For i As Integer = 0 To (items.Count - 1) Dim row As DataRow = table.NewRow() Dim attributes As ArrayList = (CType(items(i), CellItem)).Attributes For j As Integer = 0 To (attributes.Count - 1) Dim member As Member = CType(attributes(j), Member) row(j) = member.Caption Next Dim measures As ArrayList = (CType(items(i), CellItem)).Measures If Not measures Is Nothing Then ' each CellItem should only have one measure For j As Integer = 0 To (measures.Count - 1) Dim member As Member = CType(measures(j), Member) Dim value As Cell = (CType(items(i), CellItem)).ValueCell If Not (String.IsNullOrEmpty(value.Value)) Then 'need to set appropriate type accroding to cell 'table.Columns[member.Caption].DataType = value.CellProperties. row(member.Caption) = value.Value Else row(member.Caption) = DBNull.Value End If Next End If table.Rows.Add(row) Next Return table End Function #End Region #Region " Private Classes " Private Class CellItem Private _measures As ArrayList Private _attributes As ArrayList Private _valueCell As Cell Public Property Attributes() As ArrayList Get Return _attributes End Get Set(ByVal value As ArrayList) Me._attributes = value End Set End Property Public Property Measures() As ArrayList Get Return _measures End Get Set(ByVal value As ArrayList) Me._measures = value End Set End Property Public Property ValueCell() As Cell Get Return _valueCell End Get Set(ByVal Value As Cell) _valueCell = Value End Set End Property Public Sub New() _attributes = New ArrayList() _measures = New ArrayList() End Sub Public Sub New(ByVal attributes As ArrayList, ByVal measures As ArrayList, ByVal valueCell As Cell) Me.Attributes = attributes Me.Measures = measures Me.ValueCell = valueCell End Sub Public Sub AddAttribute(ByVal o As Object) _attributes.Add(o) End Sub Public Sub AddMeasure(ByVal o As Object) _measures.Add(o) End Sub Public Function Clone(ByVal isDeep As Boolean) As Object If (isDeep) Then Dim item As CellItem = New CellItem(Me.Attributes.Clone(), Me.Measures.Clone(), Me.ValueCell) Return item Else Return New CellItem(Me.Attributes, Me.Measures, Me.ValueCell) End If End Function Tag: Question on merging partitions
  - 10
    - LIKE HI,I want the synthaxe of a query with "LIKE" knowing that the fields must contain strings then a parametre then strings I tested with this synthaxe but it is not true: New_VilleExtensionBase.New_name LIKE “%” + @param1 + “%” How is that done Tag: Question on merging partitions
  - 11
    - How to specify the MSSQL.1 DATA folder in SQL Express? How do you determine the data folder during the SQL2005 Express install No matter what folder I start with it gets appended with MSSQL.1 or MSSQL.2 If another vendor has installed a different instance of SQL2005 how do I know whether mine is .1 or .2 Basically I need to know the full folder in order to copy our MDF/LDF files into so we can attach them. Or is there another way Thanks. Graham Tag: Question on merging partitions
  - 12
    - Is Full-Text Index really that much overhead? I am a developer, and I have a disagreement with my DBA. He has convinced management, that SQL 2005 FullText Index is so much overhead on production, that it should NEVER be used under any circumstances. We have a Cold Fusion site, and somehow he convinced management that a bunch of Cold Fusion developers can create a more efficient full text indexing method than by using SQL 2005 Full Text Index. So now we have to come up with a method for doing this in Cold Fusion. Is there any statistical data that could possible support or refute his statements Thanks Tag: Question on merging partitions
  - 13
    - Hourly Data Hi, I am trying to get total transactions for Cashiers on an hourly basis from my SQL Database. All the data is in the DB, but I have never ran a query that Counts transaction numbers per hour. I can get the total transactions per Cashier for the day, but not per hour. Does someone have an answer for me Thanks Lawrence Tag: Question on merging partitions
  - 14
    - What happens when the 4 GB database size limit is reached? Does the user get an error message What error does an application get that tries to insert additional data via ODBC Tag: Question on merging partitions
  - 15
    - Can I debug a SQL 2005 stored procedure with the SA account I want to know if I can debug stored procedures remotely with the SA account. I can not have Active Directory in my system right now. Thanks Tag: Question on merging partitions
  - 16
    - What permission required to DROP TABLE ? Hi, I would execute the following query to allow a user to create table USE dbname GRANT CREATE TABLE TO username but it doesnt let me execute the following query GRANT DROP TABLE TO username I am wondering if there is a way to do this. I know db_ddladmin does that. But I dont want to assign the whole role or schema to do that. I want specific privilege According to SQL Server documentation, we need CONTROL permission to do this so I tried following query GRANT CONTROL TO username and GRANT CONTROL ON tablename TO username neither of them helped.. Please tell me what permission do I require to DROP a table. Thanks, Tag: Question on merging partitions

Helen999888

Hi, all experts here,

Now I am confusing about the need to merge partitions and the significant benefits of merging them.

As partitioning cube obviously helps improving cube processing time and improve query performance. But then what is the reason to merge partitions back to one single partition of a cube

I am looking forward to hearing from you for your kind advices and thanks a lot in advance.

With best regards,

Yours sincerely,

Top

Re: Question on merging partitions

Adrian Dumitrascu

(this is not the answer for the existence of the merging partitions feature, just a scenario)

There is a best practice for AS2005 to not have more than 5 small partitions in a measure group (a small partition having less than 50 MB or 2,000,000 rows). If testing on the particular configuration (depending on the hardware, the queries that needs to be optimized, ...) shows that indeed it's better to consolidate these small partitions, then merging partitions helps.

Adrian Dumitrascu

Top

Re: Question on merging partitions

Helen999888

Hi, Adrian,

Thank you for your advices.

With best regards,

Yours sincerely,

Top