Saturday, July 24, 2010

Process the Cube using Batch method

There are many ways to process an Cube.
a. Batch Processing
b. parallel processing
c. Push-mode processing
d. Lazy processing of indexes and aggregations

By Processing the cube the Analysis server reads data from the relational data source and populates the cubes, dimensions, mining models, etc. so that the cube is sync the latest data in the relational data source.

Following is the sample for processing the cube using Batch Process. Batch process is the convenient one since we can send multiple commands to the server using single xmla file.

AdomdConnection objadomdConn = new AdomdConnection();
objadomdConn = OpenAnalysisServiceConnection();
if (objadomdConn!=null )

String ExecuteProcessQuery ="

Cube DB Name Dimension Id

Cube DB Name Cube Id

AdomdCommand Execcmd;
Execcmd = objadomdConn.CreateCommand();
Execcmd.CommandType = CommandType.StoredProcedure;
Execcmd.CommandText = ExecuteProcessQuery;

How to Send mail through outlook using .NET application

Sending mail through outlook using .NET application becoming very easy now a days. It is a matter of few lines of code.
I have given you the sample code below

//Create the outlook application object
Outlook.Application outlookapp = new Outlook.Application();

//Create the Outlook Mail item object using the above outlook application object
//We can create appointment, Contact, Note, Task, Journal, Post using the aoove outllok application object
Outlook.MailItem outmail = (Outlook.MailItem)outlookapp.CreateItem(OlItemType.olMailItem);

//In the mail item add the recipients. We can add multiple recipients
outmail.Subject = "Test Mail";
outmail.Body="Test Body";

//Finally send the mail.

Refer for more details

Note : When we add the receipt, a outlook warning message saying 'A program is trying to access e-mail addressess you have stored in Outlook.Do you want to allow this?' will be asked for security purpose.

Methods to Open Outlook Express/Outlook from VB.Net Code

Methods to Open Outlook Express/Outlook from VB Code

Option :1

Dim Addr As String = ""
Dim Subject As String = "Subject"
Dim Body As String = "Body"
Dim Attach As String = "C:\sample\xyz.txt"

System.Diagnostics.Process.Start("mailto :" & Addr & "?subject=" & Subject & "&body=" & Body & "&attachment=" & Attach)
This opens the outlook or outlook express (Whatever available) with To, Subject and Body Message .But does not attaching the file

Option :2
Using process component’s start method we can able to start the Outlook Express or Outlook.
The process name for Outlook is ‘outlook’ and for Outlook Express is ‘msimn’
So it is like below
System.Diagnostics.Process.Start("outlook") - Outlook
System.Diagnostics.Process.Start("msimn") – Outlook Express

This will simply opens the application
We can also pass the parameter to the process during this startup to do our requirement
I.e the syntax for process.start method is
System.Diagnostics.Process.Start(application, argument)
So argument for Outlook to open a new message window and to attach a specified file is
“/a File name” (Note: I have listed the available command line argument for outlook)

Sample Code:

Dim mail As String = "/a C:\sample\xyz.txt"
System.Diagnostics.Process.Start("outlook ", mail)

For your Information the other list of command-line argument available for Outlook is listed below (It is available in the outlook help itself)


Creates an item with the specified file as an attachment.


"C:\Program Files\Microsoft Office\Office11\Outlook.exe" /a "C:\My Documents\labels.doc"

If no item type is specified, IPM.Note is assumed. Cannot be used with message classes that aren't based on Outlook.

/altvba otmfilename
Opens the VBA program specified in otmfilename, rather than %appdata%\Microsoft\Outlook\VbaProject.OTM.

/c messageclass
Creates a new item of the specified message class (Outlook forms or any other valid MAPI form).


/c ipm.activity creates a Journal entry

/c ipm.appointment creates an appointment

/c creates a contact

/c ipm.note creates an e-mail message

/c ipm.stickynote creates a note

/c ipm.task creates a task

Prompts for the default manager of e-mail, news, and contacts.

Starts Outlook and deletes client-based rules.

Deletes the logging records saved when a manager or a delegate declines a meeting.

Removes Search Folders from the Microsoft Exchange server store.

Clears and regenerates free/busy information. This switch can only be used when you are able to connect to your Microsoft Exchange server.

Removes invalid profile keys and recreates default registry keys where applicable.

Launches Outlook with a clean Personal Folders file (.pst) (Personal Folders file (.pst): Data file that stores your messages and other items on your computer. You can assign a .pst file to be the default delivery location for e-mail messages. You can use a .pst to organize and back up items for safekeeping.).

Clears and regenerates reminders.

Starts Outlook and deletes client- and server-based rules.

Deletes all Schedule+ data (free/busy, permissions, and .cal file) from the server and enables the free/busy information from the Outlook Calendar to be used and viewed by all Schedule+ 1.0 users.

Starts Outlook and deletes server-based rules.

Deletes duplicate reminder messages.

Deletes the subscription messages and properties for subscription features.

Restores default views. All custom views you created are lost.

Starts Outlook without figuring out if Outlook should be the default client in the first run.

Opens the specified message file (.msg) as an OLE embedding. Also used without command-line parameters for standard OLE co-create.

/f msgfilename
Opens the specified message file (.msg) or Microsoft Office saved search (.oss).

Starts Outlook as if it were run for the first time.

/hol holfilename
Opens the specified .hol file.

/ical icsfilename
Opens the specified .ics file.

/importprf prffilename
Launches Outlook and opens/imports the defined MAPI profile (*.prf). If Outlook is already open, queues the profile to be imported on the next clean launch.

/l olkfilename
Opens the specified .olk file.

/launchtraininghelp assetid
Opens a Help window with the Help topic specified in assetid.

/m emailname
Provides a way for the user to add an e-mail name to the item. Only works in conjunction with the /c command-line parameter.


Outlook.exe /c ipm.note /m emailname

Starts Outlook without loading outcmd.dat (customized toolbars) and *.fav file.

Starts Outlook with extensions turned off, but listed in the Add-In Manager.

Starts Outlook without checking mail at startup.

Starts Outlook with the Reading Pane off.

/p msgfilename
Prints the specified message (.msg). Does not work with HTML.

/profile profilename
Loads the specified profile. If your profile name contains a space, enclose the profile name in quotation marks (").

Opens the Choose Profile dialog box regardless of the Options setting on the Tools menu.

Starts Outlook using an existing Outlook window, if one exists. Used in combination with /explorer or /folder.

Resets default folder names (such as Inbox or Sent Items) to default names in the current Office user interface language.

For example, if you first connect to your mailbox Outlook using a Russian user interface, the Russian default folder names cannot be renamed. To change the default folder names to another language such as Japanese or English, you can use this switch to reset the default folder names after changing the user interface language or installing a different language version of Outlook.

Restores missing folders for the default delivery location.

Clears and regenerates the Navigation Pane for the current profile.

Opens Outlook and displays the remote procedure call (RPC) connection status dialog.

/s filename
Loads the specified shortcuts file (.fav).

Starts Outlook without extensions, Reading Pane, or toolbar customization.

Starts Outlook with the Reading Pane off.

Starts Outlook without checking mail at startup.

Starts Outlook with extensions turned off, but listed in the Add-In Manager.

Starts Outlook without loading Outcmd.dat (customized toolbars) and *.fav file.

/select foldername
Starts Outlook and opens the specified folder in a new window. For example, to open Outlook and display the default calendar use: "c:\Program Files\Microsoft Office\Office11\Outlook.exe" /select outlook:calendar

Starts Outlook and forces a detection of new meeting requests in the Inbox, and then adds them to the calendar.

/t oftfilename
Opens the specified .oft file.

/v vcffilename
Opens the specified .vcf file.

/vcal vcsfilename
Opens the specified .vcs file.

/x xnkfilename
Opens the specified .xnk file.

Saturday, July 17, 2010

How to Check Cursor Status in SQL

When we use cursor inside a try catch catch, if any exception occurs after opened the cursor, the cursor wont get closed. So when we execute the sql next time, we will get 'cursor already exist' exception.
We have to make sure that the cursor is closed and deallocated during the exception.

Following T-SQL gives the syntax.

{ 'local','cursorname' }
| { 'global','cursorname' }

Where local, global are constant and indicates the type of cursor

OPEN curTest

0,1 - Cursor opened (0 is not applicable for dynamic cursor)
-1 - Cursor deallocated

if (CURSOR_STATUS('local',curTest)<0)
CLOSE curTest

Sunday, July 11, 2010

SQL Server Unable to Access

An error has occurred while establishing an connection to the server when connecting to SQL SERVER 2005.this failure may be caused by the fact under the default settings SQL SERVER does not exists

The issues arise because of the client machine could not able access the SQL Server through default Network Protocol Port.

Set the Port to 2301. Now by setting Port No in client application's connection string will resolve the issue.

Data Type Guess While Importing Excel using ADO

When we import Excel Data using OLEDB provider, the type of each column will be determined based on the First 8 rows of the Excel. if you have numeric values mixed with text values in the same column then data of the majority type will be considered and takes Null values for the minority data type.
If the two types are equally mixed then it will consider as Numeric.

If is basically a configuration in the Registry.

Key :TypeGuessRows
By Default, its value is set as 8. This is why the OLEDB provider guess the data type based on First Eight row.
By setting the TypeGuessRows to 0, the OLEDB provider guesses the datatype for each column is based on its entire row present in the Excel.

In the same registry, even we can find few more settings
a. AppendBlankRows - It is to specify whether the blank rows should be imported
b. FirstRowHasNames - it is to specify whether the first row is a header row.
(However, due to a bug in the ODBC driver, specifying the FirstRowHasNames setting currently has no effect. In other words, the Excel ODBC driver (MDAC 2.1 and later) always treats the first row in the specified data source as field names.Ref :
c. ImportMixedTypes

Saturday, July 10, 2010

How to find Process Id of Excel Opening in ASP.NET Application

Here is the Sample C# code to kill the Excel Process Opened in an ASP.NET (using Office interop Component)

Application oXLApp;
// Here your will create your Excel Sheet and do the operation

Workbook oWB = oXLApp.Workbooks.Open("C:\TestExcel.xls",Type.Missing,Type.Missing,Type.Missing,
Sheets oSheets = oWBk.Worksheets;
Worksheet sheet = (Worksheet) oSheets.get_Item(1);

//Do all the stuff here

oWB.Close(null, null,null);
oSheets = null;
sheet = null;
oWB = null;
oXLApp = null;


After disposing the all object write the following code

//Include the System.Runtime.InteropServices namespace, since we have to use the DllImport Attributes

using System.Runtime.InteropServices;

//Now declare the two Method Findwindow anf GetWindowThreadProcessId as show below

[DllImport("user32.dll", CharSet = CharSet.Unicode)]
internal static extern IntPtr FindWindow(String className, String windowName);

internal static extern int GetWindowThreadProcessId(IntPtr wHwnd, ref int processId);


String strXLVer = oXLApp.Version;
String strXLCap = oXLApp.Caption;

IntPtr xlappHandle = IntPtr.Zero;

//Find the Version of Excel.
//If it is above 8 then we can get the Object Handle id directly
//Otherwise we have to use the FindWindow and Get the Handle Id using Catption
if (Convert.Toint(appVersion) >= 9)
xlappHandle = new IntPtr(oXLApp.Parent.Hwnd);
xlappHandle = FindWindow(null, strXLCap);

//Get the Process Id by using GetWindowThreadProcessId method and the Process Id will be the ref Parameter
int processId = 0;
GetWindowThreadProcessId(xlappHandle, ref processId);

System.Diagnostics.Process process = System.Diagnostics.Process.GetProcessById(processId);

Saturday, July 3, 2010

How to Know the SQL Transaction is Commited Or Not

To know whether the SQL Transaction is Commited or Not, Use the Following SQL Function


If the Function returns -1 then the transaction is uncommittable state
If the Fucntion returns 1 then the transaction is live and valid and it is in committable state





How to Select Random Records from a Table

Use the following T-SQL to fetch the Random Records from a Table


The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

If 'locks' value configured with any other value other than Zero you may get the above error.
To fix it, set the locks value = 0
sp_configure 'locks',0