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 ="


ProcessFull



ProcessFull

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

http://msdn.microsoft.com/en-us/library/ms345142(SQL.90).aspx

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.Recipients.Add("name@domain.com");
outmail.Subject = "Test Mail";
outmail.Body="Test Body";

//Finally send the mail.
outmail.Send();

Refer for more details
http://msdn.microsoft.com/en-us/library/aa289167(VS.71).aspx

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 = "user@address.com"
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)

Switch
Description

/a
Creates an item with the specified file as an attachment.

Example:

"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).

Examples:

/c ipm.activity creates a Journal entry

/c ipm.appointment creates an appointment

/c ipm.contact creates a contact

/c ipm.note creates an e-mail message

/c ipm.stickynote creates a note

/c ipm.task creates a task

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

/cleanclientrules
Starts Outlook and deletes client-based rules.

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

/cleanfinders
Removes Search Folders from the Microsoft Exchange server store.

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

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

/cleanpst
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.).

/cleanreminders
Clears and regenerates reminders.

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

/cleanschedplus
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.

/cleanserverrules
Starts Outlook and deletes server-based rules.

/cleansniff
Deletes duplicate reminder messages.

/cleansubscriptions
Deletes the subscription messages and properties for subscription features.

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

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

/embedding
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).

/firstrun
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.

Example:

Outlook.exe /c ipm.note /m emailname

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

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

/nopollmail
Starts Outlook without checking mail at startup.

/nopreview
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 (").

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

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

/resetfoldernames
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.

/resetfolders
Restores missing folders for the default delivery location.

/resetnavpane
Clears and regenerates the Navigation Pane for the current profile.

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

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

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

/safe:1
Starts Outlook with the Reading Pane off.

/safe:2
Starts Outlook without checking mail at startup.

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

/safe:4
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

/sniff
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.

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.

CURSOR_STATUS
(
{ 'local','cursorname' }
| { 'global','cursorname' }
)

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


DECLARE curTest CURSOR FOR SELECT * FROM table
OPEN curTest

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

if (CURSOR_STATUS('local',curTest)<0)
BEGIN
CLOSE curTest
DEALLOCATE curTest
END

SQL Server Unable to Access

Issue:
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

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

Resolutions:
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.

RegEdit-->HKEY_LOCAL_MACHINE-->SOFTWARE-->MICROSOFT-->JET-->4.0-->ENGINES-->EXCEL
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 : http://support.microsoft.com/kb/257819)
c. ImportMixedTypes

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,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,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);
oXLApp.Workbooks.Close();
oXLApp.Quit();
oSheets = null;
sheet = null;
oWB = null;
oXLApp = null;

Marshal.ReleaseComObject(oXLApp);
Marshal.ReleaseComObject(oSheets);
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(oWB);
GC.Collect();

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);

[DllImport("user32.dll")]
internal static extern int GetWindowThreadProcessId(IntPtr wHwnd, ref int processId);

oXLApp.Caption="TestExcel";

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);
}
else
{
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);
process.Kill();

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

XACT_STATE()

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

TRY CATCH in SQL Server

BEGIN TRY

DECLARE @DIVIDEBYZERO INT
SET @DIVIDEBYZERO = 100
SET @DIVIDEBYZERO=@DIVIDEBYZERO/0

END TRY
BEGIN CATCH
PRINT ERROR_NUMBER() -- RETURNS THE ERROR NUMBER
PRINT ERROR_MESSAGE() -- RETURNS THE ACTUAL ERROR MESSAGE
PRINT ERROR_LINE() -- RETURNS THE LINE NUMBER AT WHICH ERROR OCCURS
PRINT ERROR_PROCEDURE()-- RETURNS THE PROCEDURE IN WHICH THE ERROR OCCURS
END CATCH

How to Select Random Records from a Table

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

SELECT TOP 1 * FROM TABLE ORDER BY NEWID()

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
reconfigure