Saturday, November 21, 2015

SSAS Parallel MaxParallel Issues


You may have got very excited when you find out that you can control the number of partitions you can process in your processing process, aren't you? I was at least.

We have lots of cubes and they are very resource intensive so we couldn't afford to do process all of the partitions in every run. We have designed a custom solution to process X number of partitions in the day job and Y number of partitions in the minute/hour job, ofcourse X is bigger than Y which mean we process more partitions in the day job than minute /hour job.

Our SSAS servers are less busy when compared to the SQL server which is the source for our cubes, so when we let the SSAS decide the number of partitions to process in parallel, it is processing way too many partitions in parallel and SQL Server was not able to handle that much load and is failing by the deadlocks of the processing job.

That's when we decided to play with the Parallel MaxParallel setting in the XLMA of the Analysis Server.

  oServer.CaptureXml() = True

If varPartitionsToProcessInParallel <> -1 Then
    oServer.CaptureLog.Add("<Parallel MaxParallel=""" & varPartitionsToProcessInParallel.ToString() & """>")
End If

'Figure out the paritions to process

If varPartitionsToProcessInParallel <> -1 Then
    oServer.CaptureLog.Add("</Parallel>")
End If

oServer.CaptureXml() = False

oServer.ExecuteCaptureLog(True, True)

Executing the above code throws the following exception.

 An Error has occured while processing The Parallel element at line 9, column 46 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) cannot appear under Envelope/Body/Execute/Command/Batch/Parallel..

By looking at the XLMA generated I have found that two parallel elements are added to the generated script and the reason is I am adding the Parallel element manually and ExecuteCaptureLog is adding the other as we have set the "Parallel" argument to True.

<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine' Transaction='true'>
<Parallel>
<Parallel MaxParallel="4">
<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>ProcessData</Type>
  <Object>
    <DatabaseID>XXXXX</DatabaseID>
    <CubeID>VwXXXXX</CubeID>
    <MeasureGroupID>XXXXX</MeasureGroupID>
    <PartitionID>XXXXX</PartitionID>
  </Object>
</Process>
</Parallel>
</Parallel>
</Batch>

Changing the Parallel argument to False gave the right XLMA and all is cool after.

oServer.ExecuteCaptureLog(True, False)

<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine' Transaction='true'>
<Parallel MaxParallel="4">
<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>ProcessData</Type>
  <Object>
    <DatabaseID>XXXXX</DatabaseID>
    <CubeID>VwXXXXX</CubeID>
    <MeasureGroupID>XXXXX</MeasureGroupID>
    <PartitionID>XXXXX</PartitionID>
  </Object>
</Process>
</Parallel>
</Batch>

Here is the simplc code to find out what is being sent to the server to execute.

Dim FILE_NAME As String = "C:\temp\processingmanagerparallel.txt"

Dim objWriter As New System.IO.StreamWriter(FILE_NAME)

objWriter.Write(oServer.ConcatenateCaptureLog(True, False))
objWriter.Close()

Saturday, February 1, 2014

Excel Add in Compile Errors and Upgrade Problems/Issues

Six years ago an Excel add in was developed using VSTO 3.0 and excel 2007, thank you Microsoft for giving out such a stable platform for developing solutions for Microsoft Office applications.

Add in worked well with Excel 2007 and 2010, Excel 2013 started giving problems as Microsoft decided not to support MDI in 2013, so we had to compile and make few changes to make it work well with the new SDI of Excel 2013. But the code in version control system is from 6 years and wont compile, so we had to create a new project in Visual Studio 2012 and VSTO 4.0 and add all the files to that. New project is compile and was able to get a working add in.

Old add in was published to a shared location(in production) and the update interval is set for a week. We wanted to push the new add in and overwrite the existing one but as the old one has a published location that is in production we could take the risk of publishing the new one to the old published location and test it out there to see if it is properly overwriting the old one.

So we went into the registry and changed the url path of the old one to a new path that has the new add in. When we clicked on the vsto from the new location we were able to overwrite the add in that was installed, but there were multiple entries in the Add/Remove Programs/ Programs and Features for our add ins.

To fake the new add in as old one we gave it the same name(product name) and same guid in the assembly information of the project.

We have decided to try out the new add in, so we published it to the old prod location and the was prompted for something about updating the key.

When clicked on the new vsto old one was overwritten properly and there were no multiple entries in Add/Remove Programs/ Programs and Features for our addin.



vsto,4.0,3.0,excel visual studio,2013,2012,2010,2008,2005,office,add remove programs,programs,addin,add-in,add in,upgrade,sdi,mdi,microsoft,compile,issues

Sunday, December 15, 2013

Excel 2013 Addin problems after upgrade

Did you just upgrade to excel 2013 and realized that your excel addin can be used only in the first instance of the excel. This may be because Microsoft has doesn't support MDI any more in Excel 2013.

You can fix the code and redeploy the add in that will take care of the problems, you can find what is changed in the article below.
http://msdn.microsoft.com/en-us/library/office/dn251093.aspx

If you are busy and don't have time you can try the following work around.
Try running all the instances of excel using "excel /X" which will open all the instances of excel in its own process.

Tuesday, September 24, 2013

SSRS Compile Errors


Error 3 [rsCompilerErrorInExpression] The Value expression for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ contains an error: [BC30277] Type character '&' does not match declared data type 'Object'.

Error 3 [rsCompilerErrorInExpression] The Value expression for the textrun ‘.Paragraphs[0].TextRuns[0]’ contains an error: [BC30277] Type character '&' does not match declared data type 'Object'.

You can get the above exception if you have a expression of
="Parameter A's : " & Parameters!ParameterA.Label& " value."

or

="Parameter A's : " & ParameterA.Label
& " value."

In the first expression & is next to parameter label and in the second one & is on the new line and in either case there is no space between the parameter label and &.

You can fix the issue by changing the expression to

="Parameter A's : " & Parameters!ParameterA.Label & " value."

or

="Parameter A's : " ParameterA.Label
 & " value." (Notice the SPACE before &)

Saturday, August 31, 2013

Free SQL Server Management Studio Addin/Plugin/External Tool to get object information quickly

A small utility written in c# to help get object information in SQL Server Management Studio quickly and easily. It can be used on any version of SQL Server and on any SQL Server databases. You might have been a big fan of Alt-F1 (sp_help) but it has it own restrictions, object has to be in the same database to use Alt-F1 key and get the object information.This utility can help you overcome that limitation along with other features. Please read the entire documentation to configure and use the utility.

Download VAKATI SSMS Helper
Google Drive by default opens in preview mode and shows you all the files, go to File Menu and click download menu item to download as a zip file.
 
Features
Can run outside of current database context.
Can get information on all the valid objects in the selection at once.


Displays output in a different window so your last execution results stays in the results grid.
























Can be used using user defined hotkeys(SSMS 2012).

Cons:
Output in text, so not selectable using column headers.
Supported Arguments at this time:
-Columns
-Indexes
-Constraints

(Get the objects similar to selection)
-EndsWith
-StartsWith
-Like

(Get the object info of similar objects in selection)
-EndsWithInfo
-StartsWithInfo
-LikeInfo
 


















Configuring the tool:
Tool can be found at here

Extract the zip file from above to a folder where you have access level to execute or better yet to a network folder where your entire team has execute access so that updates can be put in one place and the entire team can benefit from the updates.

This tool is entierly dependent on the external tools support of Sql Server Management Studio, to start configuring open external tools dialog under Tools menu item.


Give a meaningful name in the title for the external tool definition based on the tool and parameters.




Select “CurrentText” argument using the arrow key.

You can specify more than one parameter that is supported( indexes,-columns,-constraints, -startswith,-endswith,-like,-likeinfo,startswithinfo,endswithinfo) separating them with a space

Check the “Use Output Window”

You can define multipe external tool definitions using the Add button and repeating the steps described above.

Click OK to close the external tools dialog and start enjoying the tool, highlight the text you want to get info on and run the utility from the Tools menu item or you can use the hot keys defined to run the utility on the selected text.

Configuring Hot Keys(Only SQL Server Management Studio 2012):

From Tools Options Select “Keyboard”


Search in “Show commands containing” for Tools.ex and select “Tools.External Command1” and assign a hotkey by having focus in “Press shortcut keys” textbox.


Sample outputs:





Outputs can be switched using the drop down “Show output from”.

This is still under development, so expect blowups!.

Next Steps
1.Drive the application using xml file so users can run their own commands(stored procedures) based on arguments.
2.Display output in grid like below.

3.Support for DBA commands.
4.Add more intelligence to get information from invalid sql stetements.

I have used TSqlParser library for parsing
http://sqlmetadata.codeplex.com/
Please read their license before using the tool

Microsoft Public License (Ms-PL)

Microsoft Public License (Ms-PL)

This license governs use of the accompanying software. If you use the software, you accept this license. If you do not accept the license, do not use the software.

1. Definitions

The terms "reproduce," "reproduction," "derivative works," and "distribution" have the same meaning here as under U.S. copyright law.

A "contribution" is the original software, or any additions or changes to the software.

A "contributor" is any person that distributes its contribution under this license.

"Licensed patents" are a contributor's patent claims that read directly on its contribution.

2. Grant of Rights

(A) Copyright Grant- Subject to the terms of this license, including the license conditions and limitations in section 3, each contributor grants you a non-exclusive, worldwide, royalty-free copyright license to reproduce its contribution, prepare derivative works of its contribution, and distribute its contribution or any derivative works that you create.

(B) Patent Grant- Subject to the terms of this license, including the license conditions and limitations in section 3, each contributor grants you a non-exclusive, worldwide, royalty-free license under its licensed patents to make, have made, use, sell, offer for sale, import, and/or otherwise dispose of its contribution in the software or derivative works of the contribution in the software.

3. Conditions and Limitations

(A) No Trademark License- This license does not grant you rights to use any contributors' name, logo, or trademarks.

(B) If you bring a patent claim against any contributor over patents that you claim are infringed by the software, your patent license from such contributor to the software ends automatically.

(C) If you distribute any portion of the software, you must retain all copyright, patent, trademark, and attribution notices that are present in the software.

(D) If you distribute any portion of the software in source code form, you may do so only under this license by including a complete copy of this license with your distribution. If you distribute any portion of the software in compiled or object code form, you may only do so under a license that complies with this license.

(E) The software is licensed "as-is." You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. You may have additional consumer rights under your local laws which this license cannot change. To the extent permitted under your local laws, the contributors exclude the implied warranties of merchantability, fitness for a particular purpose and non-infringement.

sp_help from msdn website:

sp_help

SQL Server 2000
12 out of 21 rated this helpful - Rate this topic
Reports information about a database object (any object listed in the sysobjects table), a user-defined data type, or a data type supplied by Microsoft® SQL Server™.
Syntax
sp_help [ [ @objname = ] name ]
Arguments
[@objname =] name
Is the name of any object, in sysobjects or any user-defined data type in the systypes table. name is nvarchar(776), with a default of NULL. Database names are not acceptable.
Return Code Values
0 (success) or 1 (failure)
Result Sets
The result sets returned depend on whether name is specified, and when specified, what database object it is.
  1. If sp_help is executed with no arguments, summary information of objects of all types that exist in the current database is returned.
    Column name Data type Description
    Name nvarchar(128) Object name
    Owner nvarchar(128) Object owner
    Object_type nvarchar(31) Object type

  2. If name is a SQL Server data type or user-defined data type, sp_help returns this result set.
    Column name Data type Description
    Type_name nvarchar(128) Data type name.
    Storage_type nvarchar(128) SQL Server type name.
    Length smallint Physical length of the data type (in bytes).
    Prec int Precision (total number of digits).
    Scale int Number of digits to the right of the decimal.
    Nullable varchar(35) Indicates whether NULL values are allowed: Yes or No.
    Default_name nvarchar(128) Name of a default bound to this type. NULL, if no default is bound.
    Rule_name nvarchar(128) Name of a rule bound to this type. NULL, if no default is bound.
    Collation sysname Collation of the data type. NULL for non-character data types.

  3. If name is any database object (other than a data type), sp_help returns this result set, as well as additional result sets based on the type of object specified.
    Column name Data type Description
    Name nvarchar(128) Table name
    Owner nvarchar(128) Table owner
    Type nvarchar(31) Table type
    Created_datetime datetime Date table created

    Depending on the database object specified, sp_help returns additional result sets.
    If name is a system table, user table, or view, sp_help returns these result sets (except the result set describing where the data file is located on a file group is not returned for a view).
    • Additional result set returned on column objects:
      Column name Data type Description
      Column_name nvarchar(128) Column name.
      Type nvarchar(128) Column data type.
      Computed varchar(35) Indicates whether the values in the column are computed: (Yes or No).
      Length int Column length in bytes.
      Prec char(5) Column precision.
      Scale char(5) Column scale.
      Nullable varchar(35) Indicates whether NULL values are allowed in the column: Yes or No.
      TrimTrailingBlanks varchar(35) Trim the trailing blanks (yes or no).
      FixedLenNullInSource varchar(35) For backward compatibility only.
      Collation sysname Collation of the column. NULL for non-character data types.

    • Additional result set returned on identity columns:
      Column name Data type Description
      Identity nvarchar(128) Column name whose data type is declared as identity.
      Seed numeric Starting value for the identity column.
      Increment numeric Increment to use for values in this column.
      Not For Replication int IDENTITY property is not enforced when a replication login, such as sqlrepl, inserts data into the table:
      1 = True
      0 = False

    • Additional result set returned on columns:
      Column name Data type Description
      RowGuidCol sysname Name of the global unique identifier column.

    • Additional result set returned on filegroups:
      Column name Data type Description
      Data_located_on_filegroup nvarchar(128) The filegroup in which the data is located (Primary, Secondary, or Transaction Log).

    • Additional result set returned on index:
      Column name Data type Description
      index_name sysname Index name.
      Index_description varchar(210) Description of the index.
      index_keys nvarchar(2078) Column name(s) on which the index is built.

    • Additional result set returned on constraints:
      Column name Data type Description
      constraint_type nvarchar(146) Type of constraint.
      constraint_name nvarchar(128) Name of the constraint.
      delete_action nvarchar(9) Indicates whether the DELETE action is: No Action, CASCADE, or N/A.(Only applicable to FOREIGN KEY constraints.)
      update_action nvarchar(9) Indicates whether the UPDATE action is: No Action, Cascade, or N/A.(Only applicable to FOREIGN KEY constraints.)
      status_enabled varchar(8) Indicates whether the constraint is enabled: Enabled, Disabled or N/A. (Only applicable to CHECK and FOREIGN KEY constraints.
      status_for_replication varchar(19) Indicates whether the constraint is for replication. (Only applicable to CHECK and FOREIGN KEY constraints.)
      constraint_keys nvarchar(2078) Names of the columns that make up the constraint or, in the case for defaults and rules, the text that defines the default or rule.

    • Additional result set returned on referencing objects:
      Column name Data type Description
      Table is referenced by nvarchar(516) Identifies other database objects that reference the table.

  4. If name is a system stored procedure or an extended stored procedure, sp_help returns this result set.
    Column name Data type Description
    Parameter_name nvarchar(128) Stored procedure parameter name.
    Type nvarchar(128) Data type of the stored procedure parameter.
    Length smallint Maximum physical storage length (in bytes).
    Prec int Precision (total number of digits).
    Scale int Number of digits to the right of the decimal point.
    Param_order smallint Order of the parameter.

Remarks
The sp_help procedure looks for an object in the current database only.
When name is not specified, sp_help lists object names, owners, and object types for all objects in the current database. sp_helptrigger provides information about triggers.
Permissions
Execute permissions default to the public role.
Examples
A. Return information about all objects
This example lists information about each object in sysobjects.
USE master
EXEC sp_help
B. Return information about a single object
This example displays information about the publishers table.
USE pubs
EXEC sp_help publishers

 http://technet.microsoft.com/en-us/library/aa933429(v=sql.80).aspx

Tags:
 sql server 2000 2005 2008 r2 2012 tsql database engine free freeware helper utility sp_help alt-f1 alt f1 addin add-in plugin plug-in porr man software visual studio redgate plugins quest toad model modeler ide The Project Lucy intellisense intelli-sense ssmsboost ssms tools ssmstools ssms boost sql sentry sqllite lite nosql hadoop hive SSMS Tools Pack: The best SQL Server Management Studio Add-In SQL Server Management Studio 2005 and 2008 find interesting and useful Addins Productivity SSMS add-in full featured, free edition It's 100% free, no registration required SSMS Addons  SQL Treeo free add-in ApexSQL Search free Red Gate's SQL Search Add-In

Friday, August 30, 2013

Kingston KTD-PE316K4/32G and ASUS M5A97 R2.0 Socket AM3+ ATX AMD Motherboard

The following two products don't go well together, so get only the ram mentioned in the qualified vendor list from asus.

I couldn't understand why it doesn't work even though the motherboard spec clearly says that it can support DDR3 ECC, Non ECC 1600mhz ram modules.


http://www.microcenter.com/product/410801/32GB_DDR3-1600_(PC3-12800)_Desktop_Memory_Kit_(Four_8GB_Memory_Modules)_KTD-PE316K4-32G

Kingston 32GB DDR3-1600 (PC3-12800) Desktop Memory Kit (Four 8GB Memory Modules) KTD-PE316K4/32G

Specifications
Features
FeaturesFor Dell PowerEdge R620, T620
Specification
Memory TypeDDR3
Number of Modules4
Memory Capacity32GB (4 x 8GB)
Memory SpeedDDR3-1600 (PC3-12800)
Error Correction CodeECC
Memory Slot240-pin DIMM
What's in the Box
What's in the Box4 x 8GB DDR3 Memory Modules
Manufacturer Warranty
Parts5 Years
Labor5 Years

http://www.microcenter.com/product/398235/M5A97_R20_Socket_AM3_ATX_AMD_Motherboard

ASUS M5A97 R2.0 Socket AM3+ ATX AMD Motherboard

Specifications

General
FeaturesDual Intelligent Processors - TPU + EPU; Remote GO! - One-stop PC Remote Control and Home Entertainment; USB 3.0 Boost - Faster USB 3.0 Transmission with UASP; Network iControl - Real-time Network Bandwidth Control; DirectKey - A Dedicated Button to Access the BIOS Directly; USB BIOS Flashback - Easy, Worry-free USB BIOS Flashback with Hardware-based Design; UEFI BIOS - Flexible & Easy BIOS Interface; Windows 8 Ready Assured Compatibility
Microsoft CertificationWindows 8 Ready
Overclocking FeaturesASUS C.P.R.(CPU Parameter Recall)
Chipset
North BridgeAMD 970
BIOS64 Mb Flash ROM, UEFI BIOS, PnP, DMI2.0, WfM2.0, SM BIOS 2.7, ACPI 2.0a, Multi-language BIOS, ASUS EZ Flash 2, F12 PrintScreen, F3 Shortcut Function and ASUS DRAM SPD (Serial Presence Detect) memory information
CPU Supported
Socket TypeAM3+
AMD CPU SupportPhenom II
Athlon II
Sempron 100 series
Maximum Processor Wattage Supported140W
Memory
Maximum Memory Supported Per Slot32GB
Memory TypeDDR3
Memory Speeds SupportedDDR3-2100 (O.C.), DDR3-1866, DDR3-1800, DDR3-1600, DDR3-1333, DDR3-1066
Memory Slots4 x 240pin DIMM
Max Memory Supported32GB
Memory Channel SupportDual Channel
Supported Memory TechnologiesECC and non ECC Memory Supported
Storage Controllers
SATA 6Gb/s6
SATA RAID0/1/5/10
Video
Onboard Graphic ChipsetNone
Multi-GPU SupportAMD Quad-GPU CrossFireX
Audio
Audio SystemALC887
Audio Channels8-Channel
Networking
LAN ChipsetRealtek 8111F
LAN Speed10/100/1000Mbps
Expansion Slots
PCI Express x12
PCI2
Back Panel Ports
PS/22
USB 2.06
USB 3.02
LAN RJ-451
Audio Ports6
Digital Audio1 x Optical
Internal I/O
USB3 x USB 2.0 Headers (supports 6 additional USB ports)
1 x USB 3.0 Header (supports 2 additional USB ports)
Other I/OS/PDIF-Out Header
Clearing CMOS Jumper
TPM Module Connector
MemOK! Button
System Panel (Q-Connector)
COM
CPU Fan Connector
3 x Chassis Fan Connector
EATX Power Connector
ATX 12V Power Connector
Front Panel Audio Connector
USB BIOS Flashback Button
DirectKey Button
DRCT Header
Power
ATX Power Connector24-pin
ATX12V / EPS 12V Connector8-pin
Regulatory ApprovalsFCC
CE
ROHS CompliantYes
Physical Specifications
Form FactorATX
Dimensions (WxD)12" x 9" (305mm x 229mm)
What's in the Box
What's in the BoxMotherboard, User's Manual, I/O Shield, 2 x SATA 6Gb/s Cables, Support Discs
Manufacturer Warranty
Parts3 Years
Labor3 Years

Creative Sound Blaster Audigy2 ZS SB0350 7.1 Channels 24-bit 192KHz PCI Interface Sound Card Windows 7 64 bit NO SOUND

I like my Creative Sound Blaster Audigy2 ZS SB0350 so I took it from my old pc and installed it on a newly built windows 7 64 bit edition pc but there was no sound. I installed the driver properly no exclamations in device manager but no sound.

I uninstalled the device  from the device manager, restarted the pc and got my sound going.

Creative Sound Blaster Audigy2 ZS SB0350 7.1 Channels 24-bit 192KHz PCI Interface Sound Card

Specs from creative/newegg
http://www.newegg.com/Product/Product.aspx?Item=N82E16829102178

Model

Brand
Creative
Series
Sound Blaster Audigy2 ZS
Model
SB0350

Audio core

Audio Chipset
Audigy2
Channels
7.1
Sample Rate
192KHz
Digital Audio
24-bit
Hardware Decode
Dolby Digital EX 6.1
Polyphony
64 Voices (Hardware)

Ports

Line In
Yes
Line Out
Yes
SPDIF Out
Yes
MIC In
Yes
MIDI/Joystick
Yes
CD/Aux In
Yes
FireWire (IEEE 1394)
1

Spec

Interface
PCI
Operating Systems Supported
Windows 98SE, ME, 2000 SP2, or XP
System Requirements
Processor: Genuine Intel Pentium II, Celeron 350MHZ,AMD-K6 450MHz or faster
Motherboard: Intel, AMD or 100% compatible
System RAM: 64MB for Windows 98SE and ME, 128MB for Windows 2000 SP2 and XP
Hard Disk: 600MB free space
Operating System: Windows 98SE, ME, 2000 SP2, or XP
Available PCI 2.1 compliant slot
Available adjacent slot for Joystick/MIDI bracket (optional)
Headphones or amplified speakers (available separately)
CD-ROM or DVD-ROM drive required for software installation

Packaging

Package Contents
Sound Blaster Audigy2 ZS Sound Card
Driver Disk
Audigy 2 ZS Internal I/O Drive

Manufacturer Warranty

Parts
1 year limited