MSMQ Vulnerabilities (Cont.) - Outgoing Messages blocked with 'Unacknowledged' state

This is another annoying MSMQ issue that consumed most of my time. One of my previous articles, I explained about a MSMQ message rejection issue due to a bug on Microsoft hot-fix and service packs. This time it was more complicated to find out the root cause since there were no errors logged at both client and server sides.


Symptoms:


If you have a WCF with MSMQ binding in your application, in certain instances, Messages get stuck on Outgoing Queues with ‘Unacknowledged’ state due to no reason. The application logs say, it has successfully delivered to the MSMQ. However, those messages were not consumed and also this issue occurred in a random manner.



Analysis of the problem:


Mainly, if the MSMQ Messages block under the Outgoing Queues with ‘Unacknowledged’ state, which means:

  1. The application successfully delivered messages to the Outgoing Queues.

  2. The Outgoing queue delivered those messages to the destination queue.

  3. However, since you use transactional MSMQs, the destination MSMQ Server didn’t send Acknowledgement back to the Sender MSMQ Server.

  4. Therefore, those messages remain under the Outgoing Queue’s with Unacknowledged State at the Senders side.


If you consider the possibilities for the above issue, mainly could be happened due to:

  1. Once the destination MSMQ Server received messages, it sends the Acknowledgement back to the Sender MSMQ Server, however, due to network issues; the Acknowledgement didn’t reach the Sender.

  2. Once the destination MSMQ Server received messages, it sends the Acknowledgement to a wrong IP address.

  3. Once the destination MSMQ Server received messages, it couldn’t send the Acknowledgement due to MSMQ component issues.


In this article, I am considering the above possibility #2, that sending Acknowledgement to a wrong IP address. Mainly, this can be occurred, if you have more than one cloned machines that work as client (MSMQ Sender) and they are belongs to the same master image.



Root Cause:


If you look at the following registry path of the Cloned machines (belongs to the same image), the QMid registry value is same on each those machines.

HKLM\Software\Microsoft\MSMQ\Parameters\Machine Cache


The root cause is, when Client communicates the Server, the Server cache the Sender’s QMid value (which come along with incoming messages) with the Clients IP address. This cache table gets cleared in a regular basis. So the Server sends the Acknowledgement back to the Client by looking at its cache after the 1st time communication.


Therefore, when more than one cloned Client machines belongs the same image send messages to the Server, the Server will send ‘Acknowledgement’ to the same IP address by looking at its cache.


So, one Client machine receives many additional Acknowledgements and it discards them, nevertheless, the other original senders never receive Acknowledgements for its outgoing messages. Hence the messages remain as ‘Unacknowledged’ in Outgoing Queues.


Therefore, it is clear that in certain instances you can see the Outgoing messages successfully transferred (during 1st time communication or when cache got cleared.) however, the outgoing messages get blocked in a regular basis for sure.



Resolution:


  1. Locate “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSMQ\Parameters\Workgroup” registry key and make sure the value is “1” on each Cloned machine

  2. Stop the MSMQ Service

  3. Clear the QMId value completely

  4. Add a SysPrep DWORD (Under HKLM\Software\Microsoft\MSMQ\Parameters) and set it to 1

  5. Start the MSMQ Service


Next Steps:


  • Make sure to exclude the MSMQ component when you are cloning a machine.



Reference:


http://blogs.msdn.com/b/johnbreakwell/archive/2007/02/06/msmq-prefers-to-be-unique.aspx
3 comments

BizTalk Server Optimization & Maintenance

Download this post as Word | PDF

I have engaged with BizTalk Server 2006 R2 application development, maintenance & performance tuning processes during my past projects. I have learnt that when it comes to the production server maintenance, it is very important to proactively monitor and optimize the BizTalk & SQL Server in a timely manner.
There are many numbers of articles available under the BizTalk Server optimization. Following are the summarizations of some key steps that you need to be followed.
Mainly, I am categorizing it as:
  • Infrastructure Support
  • BizTalk Application Server Maintenance
  • BizTalk SQL Server Maintenance

Infrastructure Support

Having a good Infrastructure is very important due to its dependencies that are directly affecting your BizTalk Server performance.
  • Have servers with very good performance by considering CPU, DISKS (SANs), Memory (> 8 GB), Sufficient Network bandwidth, Etc.
  • Use 64-Bit Operating System and also BizTalk Server, SQL Server 64-Bit Enterprise Editions.
    • There are some significant performance differences between 32 Bit & 64 Bit.
    • For example the 64-bit virtual address space is exponentially larger than the 32-bit virtual address space. Moving to 64-bit will effectively resolve most of the BizTalk Memory Leaks issues.
  • Your BizTalk Server should be implemented in a Multi Hosted environment at least with 2/3 Servers by dividing Send, Receive, Process Host Instances respectively.
  • You should need to think about the I/O Contingency for heavy disk read/write processes and allocate separate disks such as SANs to isolate heavy I/O processes.
  • Maintain separate Backup Servers.
  • Customize your Anti Virus, File System Scan processes efficiently by excluding your BizTalk, SQL heavy read/write folders.
  • Keep your O/S & SWs up-to-date.
  • Implement monitors such as SCOM in order to proactively monitor the health of BizTalk Server and SQL Server.

BizTalk Application Server Maintenance
  • Create separate host instances & handlers for Send, Receive & Process. (If is it in the multi-hosted environment, create them respectively in separate servers). Also create a dedicated host for tracking purposes.
  • Disable tracking for orchestrations, send ports, receive ports and pipelines as it not required.

Note: - Tracking may cause the potential performance overhead for BizTalk Server.


BizTalk SQL Server Maintenance

Consider the following performance guidelines I gathered from Microsoft articles and I have added some additional details regarding configuring Microsoft SQL Server™ with BizTalk Server 2006:
  • Whenever possible, use a fast disk subsystem with SQL Server. Use a redundant array of independent disks type 10 (RAID10/0+1) or a storage area network (SAN) with backup power supply.
  • Isolate each MessageBox database on a separate server from the BizTalk Tracking database (BizTalkDTADb). For smaller deployments if CPU resources are available, it might be sufficient to isolate the MessageBox database on a separate physical disk from the BizTalk Tracking database.
  • The primary MessageBox database could be the bottleneck due to CPU processor saturation or latency from disk operations (average disk queue length). If CPU processing is the bottleneck, add CPU processors to the primary MessageBbox. If not, try to disable publishing on the master MessageBox database. This way the master MessageBox database can more efficiently handle routing of messages to the other MessageBox databases
  • If disk operations are the bottleneck, move the BizTalk Tracking database to a dedicated SQL Server computer and/or dedicated disk. If CPU processing and disk operations on the primary MessageBox database are not the bottleneck, you can create new MessageBox databases on the same SQL Server computer to leverage your existing hardware.
  • Follow SQL Server best practices to isolate the transaction and data log files for the MessageBox and BizTalk Tracking databases onto separate physical disks.
  • Allocate sufficient storage space for the data and log files. Otherwise SQL Server will automatically consume all of the available space on the disks where the log files are kept. The initial size of the log files depends on the specific requirements in your scenario. Estimate the average file size in your deployment based on testing results, and expand the storage space before implementing your solution.
  • Allocate sufficient storage space for high-disk-use databases, such as the MessageBox, Health and Activity Tracking (HAT), and Business Activity Monitoring (BAM). If your solution uses the BizTalk Framework messaging protocol, allocate sufficient storage space for the BizTalk Configuration database (BizTalkMgmtDb).
  • Configure BizTalk SQL Jobs properly. There are almost 12 SQL Jobs available for BizTalk. If they were not configured them properly or not running, there can be some severe BizTalk performance issues. Ex:- BizTalk Backup Jobs, DTA Purge and Archive Jobs
  • Depending on business needs, such as data retention periods, and the volume of data processed in your scenario, configure the Archive/Purge jobs on the HAT-Tracking database such that the BizTalk Tracking database does not grow too large. The growth of this database can degrade performance because reaching the full capacity of the database imposes a limit on the rate of data insertion. This is especially true when especially when one BizTalk Tracking database supports multiple MessageBox databases.

Use following extensive values for the “DTA Purge and Archive (BizTalkDTADb)” job for better performance

declare @dtLastBackup datetime

set @dtLastBackup = GetUTCDate()

exec dtasp_PurgeTrackingDatabase 1, 0, 1, @dtLastBackup

  • Scale up the servers hosting the MessageBox and BizTalk Tracking databases if they are the bottleneck. You can scale up the hardware by adding CPUs, adding memory, upgrading to faster CPUs, and using high-speed dedicated disks.
  • Splitting the TempDB files across multiple files may resolve performance issues related to I/O operations. As a general guideline, create one file data file per processor and use the same size for all files created.
  • Change the database auto-grow settings to a fixed value such as 100-150MB. By default the database growth is configured to 10%, which can lead to delays when growing larger databases.
  • SQL Server memory should be set to a fixed value by setting both Min Server Memory and Max Server Memory to the same value. In general, allocate 75% of physical memory to SQL Server and leave 25% for the rest of the operating system and any applications. If this is a dedicated SQL Server, you can decrease the amount reserved for the operating system to a minimum of 1GB.
  • Make sure the BizTalk Database backup jobs are configured properly.

0 comments

Ride Hard or Stay Home!!!

Sometimes our lives are really suck and very hard to survive due to our job, responsibilities or due to our own madness or stupidity. We may need strong guts to face the reality and overcome adversity.

No matter what we should never give up and I called it as “Ride Hard or Stay Home!!!”


Cheers..

0 comments

Forward Outlook Emails to your Pager or to your Public Email Account

Last few weeks I was searching the ways of forwarding Outlook incoming emails to my mobile or to a public email account. After writing few code blocks and testing various SMTP related methodologies the best option I found so far is the Outlook Macro option.

Using Outlook Macro option you can write a custom VB code as the way you want and trigger it by using Outlook events such as when a new email received, send, etc. You can easily forward your Outlook emails to any number of public email addresses, your mobile Pager address in a totally customized way. You will receive the forwarded email to your public mail account or to your mobile as same way you send through the Outlook.

Here is the code I have written and try it with your own risk.

  1. MS Outlook 2007 > Tools > Macro > Visual Basic Editor
  2. Copy & paste below code
  3. Edit and provide your public email address within the code, Save and Run it.
  4. This will trigger when you receive a new email address and forward the content to given mail account

'This event trigger each time when a new email received to your Outlook

Private Sub Application_NewMail()

Dim sSubject As String

Dim sBody As String

Dim flag As String

' Open the default Inbox Folder.

Set objItemInbox = Application.GetNamespace("MAPI"). _

GetDefaultFolder(olFolderInbox).Items.GetLast

' Open the custom mail box folder that you have created.

Set objItemLA = Session.Application.GetNamespace("MAPI"). _

Folders("Personal Folders").Folders("LA Support").Items.GetLast

flag = 1

' If unread mail exist in your inbox, retrieve the 1st unread mail and assign details to the variables.

If objItemInbox.UnRead Then

sSubject = objItemInbox.Subject & " From: " & _

objItemInbox.SenderName

sBody = objItemInbox.Body

' If unread mail exist in your custom mail box, retrieve the 1st unread mail and assign details to the variables.

ElseIf objItemLA.UnRead Then

sSubject = objItemLA.Subject & " From: " & _

objItemLA.SenderName

sBody = objItemLA.Body

Else

flag = 0

End If

If flag = 1 Then

' Create a new outlook email Object using received email contents

Set objMailItem = Application.CreateItem(olMailItem)

With objMailItem

.Subject = sSubject

.Body = sBody

'Add your preferred public email account

.Recipients.Add "YourPublicEmail@gmail.com"

.Send

End With

Set objMailItem = Nothing

End If

End Sub

0 comments

Some days are better than other days..

It is true that some days are better than other days.

Its like:-
When I turn ON the radio, most rocking songs are playing & there are sensational..
When I am driving, I could see lots of stunning girls everywhere and that's phenomenal..
When I start working, I feel no dull and receive appreciation for a spectacular performance..
Also I am definitely enjoying with my fabulous breakfast for sure..

I am just wondering how we could make everyday like that awesome. At least most of the days.

0 comments

Patching MSMQ Vulnerabilities

I was working with WCF Technologies in one of my past projets and some of the Microsoft bugs really freak me out. Mainly, we have a WCF channel with NetMsmqBinding and MSMQ channel is authenticated through Certificates in a bi-directional way. In certain instances, we have identified that received MSMQ messages become poisoned due to no reason.

This is an identified Microsoft bug in MSMQ 3.0 version in following OS, Service Packs and patches; however it is hard to find a link explaining all affected OS and Service Packs.

Symptom:-

When we have a WCF Channel with MSMQ Binding, and we receive some messages from another source using an external certificate, the MSMQ 3.0 is rejecting those received messages by considering it as a poisoned message.

Root Cause:-

This is a known issue in the MSMQ 3.0 version in following OS+ Service Packs+ Patches.

Microsoft Windows Server 2003 + Service Pack 2 + KB 971032 (KB971032 cause this issue)

Microsoft Windows XP + Service Pack 2 + KB 971032 (KB971032 cause this issue)

Microsoft Windows XP + Service Pack 3 (Service Pack 3 cause this issue)

Resolution:-

Affected SW

Resolution

Microsoft Windows Server 2003 + Service Pack 2 + KB 971032

Install following hot-fix to resolve this issue

http://support.microsoft.com/kb/2028919

Microsoft Windows XP + Service Pack 2 + KB 971032

Upgrade to Service Pack 3 and install following hot-fix to resolve this issue

http://support.microsoft.com/kb/959682

Microsoft Windows XP + Service Pack 3

Install following hot-fix to resolve this issue

http://support.microsoft.com/kb/959682
0 comments

State Management in ASP.NET Basic concepts

(I). Client Side State Management

This stores information on the client's computer by embedding the information into a Web page, a uniform resource locator(url), or a cookie. The techniques available to store the state information at the client end are listed down below:

1). View State – Asp.Net uses View State to track the values in the Controls. You can add custom values to the view state. It is used by the Asp.net page framework to automatically save the values of the page and of each control just prior to rendering to the page. When the page is posted, one of the first tasks performed by page processing is to restore view state.

2). Control State – If you create a custom control that requires view state to work properly, you should use control state to ensure other developers don’t break your control by disabling view state.

3). Hidden fields – Like view state, hidden fields store data in an HTML form without displaying it in the user's browser. The data is available only when the form is processed.

4). Cookies – Cookies store a value in the user's browser that the browser sends with every page request to the same server. Cookies are the best way to store state data that must be available for multiple Web pages on a web site.

5). Query Strings - Query strings store values in the URL that are visible to the user. Use query strings when you want a user to be able to e-mail or instant message state data with a URL.

Advantages:-
a. Better Scalability:
b. Supports multiple Web servers:


(II). Server Side State Management

1). Application State - Application State allows to store application specific information is available to all pages, regardless of which user requests a page.

Three Events you can use to initialize Application variables

a. Application_Start: Raised when the application starts. This is the perfect place to initialize Application variables.

b. Application_End: Raised when an application shuts down. Use this to free application resources and perform logging.

c. Application_Error: Raised when an unhandled error occurs. Use this to perform error logging.


2). Session State – Session State information is available to all pages opened by a user during a single visit. You can use session state to store user-specific information.

Advantages:-
a. Better security
b. Reduced bandwidth

More Info:-
http://msdn.microsoft.com/en-us/library/75x4ha6s.aspx
http://msdn.microsoft.com/en-us/library/z1hkazw7.aspx
http://www.dotnetfunda.com/articles/article61.aspx
0 comments

LIST Search using Anonymous Methods

An Anonymous method is a new feature introduced in C# 2.0 to have methods without name. which can help increase the readability and maintainability of your applications by keeping the caller of the method and the method itself as close to one another as possible. Prior to C# 2.0, a developer would have to define a separate method when needing to execute a callback using a delegate. But in C# 2.0 Anonymous method uses the keyword, delegate, instead of a method name and contain the body of the method.

Following sample code contains a C# List Searching and filtering technique by using Anonymous method.

public class Employee
{
private string name;
private double salary;

public string Name
{
get { return name; }
set { name = value; }
}

public double Salary
{
get { return salary; }
set { salary = value; }
}
}

Employee emp1 = new Employee();
emp1.Name = "Jeff";
emp1.Salary = 200000.00;

Employee emp2 = new Employee();
emp2.Name = "Anne";
emp2.Salary = 500000.00;

Employee emp3 = new Employee();
emp3.Name = "Jane";
emp3.Salary = 900000.00;

List empList = new List();
empList.Add(emp1);
empList.Add(emp2);
empList.Add(emp3);

//Search the Employees those salaries are larger than specified value.
List newEmpList = new List(empList).FindAll(
delegate(Employee emp)
{
return emp.Salary > 400000.00;
}
);
0 comments

Use Activator.CreateInstance() to Create Objects Dynamically

You can use Activator.CreateInstance() method to create objects from String type of Assembly Name. The following code stub demonstrate how to use that method.

namespace ActivatorTest
{
public interface IActBase
{
void Execute();
}
}

namespace ActivatorTest
{
public class ActChild1 : IActBase
{
public void Execute()
{
Console.WriteLine("Activator Child 1");
}
}
}

namespace ActivatorTest
{
public class ActChild2 : IActBase
{
public void Execute()
{
Console.WriteLine("Activator Child 2");
}
}
}

namespace ActivatorTest
{
public class Program
{
static void Main(string[] args)
{
const string AssemblyName = "ActivatorTest.ActChild2";
//The AssemblyName can be loaded through the configuration based
// on your requirement. Here its hard coded.
IActBase instance = (IActBase)Activator.CreateInstance(Type.GetType(AssemblyName));
instance.Execute();
}
}
}
For More Info:- http://www.knowdotnet.com/articles/activator_createinstance.html
0 comments

Fixing SSIS Deployment Issues

SQL Server Integration Service (SSIS) is a platform for building enterprise level data integration and transformations solutions in Microsoft SQL Server. For example updating a SQL Database from an external source such as XML Source, Excel source and there are many more.

There are enough articles available on internet regarding SSIS package development and deployment, but hard to find exact solution regarding SSIS issues.

The purpose of this article is to provide solutions regarding few SSIS deployment issues in SQL Server 2005.

Issue 1:-

Error Message:-

The SQL Job fails with following Error:-
“The command line parameters are invalid. The step failed.”……

Description:-

This error occurred due to SQL Server 2005 Agent Bug. When you deployed your DTSX package as a SQL Server job and define DataSources [Ex:- DB Connection, Excel Connection] in SQL Job property window, it will automatically generate a Command Line string. [You can view it by Job Properties: - Edit "Step":- "General":- Select "Command line" Window]
When you try to run the SQL Job, the generated Command Line string cannot be recognized due to Command line String validation failure.

Solutions:-

1) The slashes should be backslashes and the quotes need to be escaped (i.e. \").

2) Use a Config file for your DTSX package. You need to parameterize all DataSource Connections and define them in a Config file. So you don’t need to edit “DataSources“under SQL Job Properties. This will avoid generating unnecessary Command Line String under SQL Job. [Recommended]

This Bug has been fixed in MS SQL Server 2008.


Issue 2 (In 64 Bit Machine):-

Error Message:-

The SQL Job fails with following Error:-
- Pre-execute (Error)
Messages
Error 0xc0202009: {F1B3B35C-FAE3-48F6-A169-4E4D8D99F9B6}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)

Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "DestinationConnectionExcel" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)……………………………………………

Description:-

The above error occurred due to limitation of 64 Bit environment since some .NET Framework data providers and some native OLE DB providers may not be available in 64-bit versions. When you try to execute DTSX Package as “SQL Server Integration Services Package” type in SQL Job window, by default it will try to use DTExec.exe available in 64 Bit environment. So it gets failed if your DTSX package contains any OLE DB/etc connections which are not available in 64 Bit environment.

Solution:-

Change SSIS package the setting to False from
Visual Studio Solution->Properties->Debug->64Bit .

Additionally you need to run you’re your DTSX package by using the 32 Bit DTExec.exe executable by default available in "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" path.

In order run it as a SQL Job you need to schedule it as an Operating System Command by changing the SQL Job Properties-> Steps -> Edit Etep-> Change the Type as “Operation System (CmdExec) ”

And the Command should be similar to following sample Command:-

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /FILE "C:\ImportClientInfoPackage.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E /CONFIGFILE "C:\ImportClientInfo.dtsConfig"
0 comments

XSLT: Useful Method implementations for XML Transformation

XSL stands for EXtensible Stylesheet Language, and is a style sheet language for XML documents. XSLT stands for XSL Transformations.

XSLT is used for the transformation of XML documents into other XML documents. In details the original xml document will not be changed; rather, a new XML document will be created according to specified schema document (XSLT Document) based on the data content of an existing Original Xml doc.

Following sample codes are very useful for you to write Common XML Transformation Component.

The following method used to create an Xml Document by Serializing the given Object in to a memory stream. The passed Object should be a XML [Serializable] class.

/// -summary
/// Used to serialize given object to a XML Document that is used for XML Transformation
/// -summary
/// "templateDto"- Serializable Object
/// "mailType"- Enum used to identify particular mail category
/// returns-Transformed HTML message
public static string GenerateMailContent(Object templateDto,MailType mailType)
{
if (templateDto == null)
{
throw new CbsException(BaseErrorCode.ArgumentIsNull);
}
XmlDocument mailContent = new XmlDocument();
//Initialize Memery stream by loading Memory stream size from Config file.
MemoryStream memoryStream =
new MemoryStream(int.Parse(CbsConfigurationManager.GetConfigurationSection
(CbsConfigurationConstants.CbsConfigurationSection, CbsConstant.MemoryStreamSize),
CbsCultureManager.NumberFormat));
XmlSerializer xmlSerializer = new XmlSerializer(templateDto.GetType());
// Serialize object to a memory stream.
xmlSerializer.Serialize(memoryStream, templateDto);
// Set seeker at begining of stream
memoryStream.Seek(0, 0);
// Load memory stream to xml document
mailContent.Load(memoryStream);

// Transform mailContent XML to a specified format.
return TransformData(mailType, mailContent);
}


The following method is used to transform given XML document according to specified XSLT document and return the newly generated document.

/// -summary
/// Used to create the XML content of the Email using XSLT template.
/// -summary
/// param name="mailType"- Email Category
/// param name="xmlContent"-XML document content.
/// returns- XML content
public static string TransformData(MailType mailType, XmlDocument xmlContent)
{
// Load XSL transformation
XslCompiledTransform xform = new XslCompiledTransform();

// Load Absolute path for the XSLT Mail Template folder.
string mailTemplatePath =
CbsConfigurationManager.GetConfigurationSection
(CbsConfigurationConstants.CbsConfigurationSection, CbsConstant.EmailTemplatesPath);

// Switch for different XSLT mail template documents.
switch (mailType)
{
case MailType.RarSwitch:
xform.Load(mailTemplatePath + PollerSwitchXslt);
break;

case MailType.DeliveryUnsucesfull:
xform.Load(mailTemplatePath + DeliveryUnsucesfullStoresXslt);
break;
case MailType.HpSwitch:
xform.Load(mailTemplatePath + RiDMSwitchXslt);
break;
}

XmlNodeReader reader = new XmlNodeReader(xmlContent);
XmlUrlResolver resolver = new XmlUrlResolver();

// Execute and Cache results.
XmlDocument resultsDoc = new XmlDocument();
XPathNavigator resultsNav = resultsDoc.CreateNavigator();

// using makes sure that we flush the writer at the end
using (XmlWriter writer = resultsNav.AppendChild())
{
xform.Transform(reader, null, writer, resolver);
}
resultsNav.MoveToChild(XPathNodeType.Element);
return resultsNav.OuterXml;
}
0 comments

WMI Queries on Win32 Environment

Windows Management Instrumentation (WMI) is the infrastructure for management data and operations on Windows-based operating systems. You can write WMI scripts or applications to automate administrative tasks on remote computers but WMI also supplies management data to other parts of the operating system and products.

Sample WMI Query to Uninstall Softwares in Win32 Environment.

ObjectQuery oq = new ObjectQuery("Select * from Win32_Product where Name like 'Microsoft Office%'");
ManagementObjectSearcher allProducts = new ManagementObjectSearcher(oq);

ManagementObjectCollection MngObjeColl = allProducts.Get();
foreach (ManagementObject product in MngObjeColl)
{
foreach (PropertyData property in product.Properties)
{
Console.WriteLine("{0} = {1}", property.Name, property.Value);
}
// Uninstall the poduct.
object res = product.InvokeMethod("Uninstall", null);
Console.WriteLine("-Successfully Uninstalled-\n");
}

You can find sample available Win32 Classes:-
http://msdn.microsoft.com/en-us/library/aa394084(VS.85).aspx
0 comments

Reading and Writing Files in C#

string writeFilePath = @"C:\PlugIn\Dimuthu.txt";
string readFilePath = @"C:\PlugIn\Nuwan.txt";

/// Used to read from one file and write to an another file using
/// Byte Array.
FileStream openFile = new FileStream(readFilePath, FileMode.Open, FileAccess.ReadWrite);
FileStream writeFile = new FileStream(writeFilePath, FileMode.Create, FileAccess.Write);
BufferedStream outFile = new BufferedStream(writeFile);

int count;
byte[] buffer;
buffer = new byte[4096];
while ((count = openFile.Read(buffer, 0, buffer.Length)) > 0)
{
outFile.Write(buffer, 0, count);
outFile.Flush();
}

/// Used to write a Large File for 20 seconds.
/// This will be helpful for performance testing.
FileStream fileStream = new FileStream(writeFilePath, FileMode.Create, FileAccess.Write);
DateTime end = DateTime.Now.AddSeconds(20.0);
StreamWriter file = new StreamWriter(fileStream);

while (DateTime.Now < end)
{
file.WriteLine(count);
file.Flush();
Console.WriteLine(count);
count++;
}
file.Close();


/// Used to create a zero byte text file.
string folderPath = @"C:\Test";
Directory.CreateDirectory(folderPath);
File.Create(folderPath + "\\Game.txt", 1);
0 comments

Base64 Encoding and Decoding

Following Base64Encode() method can be used Encode any file in to a Base64 string and write it in to a given file path. The Base64Decode() method can be used to generate original file from given Base64 String.

Basically Base64 can be used to encode a any file such as jpeg, avi, dat, txt, etc in to a String format.

private string Base64Encode()
{
string strBase64;
FileStream fileStream = new FileStream(msiFilePath, FileMode.Open, FileAccess.Read,
FileShare.Read);
byte[] buffer = new byte[fileStream.Length];
fileStream.Read(buffer, 0, buffer.Length);
strBase64 = Convert.ToBase64String(buffer);
return strBase64;
}

private void Base64Decode(string filePath, string base64String)
{
FileStream fileStream = null;
try
{
byte[] plugInObjectBytes = Convert.FromBase64String(base64String);
fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write);
fileStream.Write(plugInObjectBytes, 0, plugInObjectBytes.Length);
fileStream.Flush();
}
catch (Exception ex)
{
if (File.Exists(filePath))
{
fileStream = null;
File.Delete(filePath);
}
throw;
}
finally
{
if (fileStream != null)
{
fileStream.Close();
}
}
}
0 comments

Stored Procedures Quick Reference [SQL Server 2005]

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session.

-Get Currtent Date-
GETDATE()

-GET UTC Date
GETUTCDATE()

-Get UTC DATE WITHOUT TIME
cast(floor(cast(GETDATE() as float)) as datetime)

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

-Return a Parameter back-

@bnGroupId bigint OUT,

SET @bnGroupId = SCOPE_IDENTITY()

-SQL SURFACE to enable remote logging-

1) Select Start Menu>SQL Server 2005>Configuration Tools>Surface area Configuration
2) Click Serface area Connection
3) Select Using Both TCP/IP and named piped

Network Configuration
1)Select Start Menu>SQL Server 2005>Configuration Tools>Server Configuration Manager
2)Click Nenwork Configuration
3)Select Protocol for SQL Server
4)Enable TCP/IP

How to test Romtote Logging to SQL
SQLCMD -E -S CT-MCDDEVSQL\SQLEXPRESSCLIENT,2301

-The SQL EXIST Statement-

IF EXISTS ( SELECT * FROM [tblData]
WHERE MessageId]=@MessageId
AND [RecipId]=@vcRecipId
AND [TypeId]= @TypeId
AND @vcStatus = @vcPendingStatus
)
BEGIN
--query
END

-How to Catch Sql Errors-

DECLARE @vcErrorNumber varchar(10)-- hold the error number

BEGIN TRY
INSERT INTO [tblDestinationInfoClientInfo]/--------/

END TRY

BEGIN CATCH
SET @vcErrorNumber = ERROR_NUMBER()
-- primary key vialation error number
IF (@vcErrorNumber = '2627')
BEGIN
/--------------/
END
END CATCH

-Define Transactions-

BEGIN TRAN
COMMIT TRAN

ROLLBACK TRAN

-How to Retrieve All Specific Table Columns from SysTable-

SELECT [name] AS FieldName
FROM syscolumns
WHERE id = ( SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblEmp')

--sysobjects table contains all tables information
SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblEmp'
In SQL Server the is a unique identification number for every tables. So we retrieve specified User Defiened table ID.

--syscolumns table contains all tables column information
We can retrieve specific table Columns by passing specific table Id.

SELECT [name] AS FieldName
FROM syscolumns
WHERE id = /*Table Id*/

-Define a Cursor [Column Cursor]-

DECLARE @fieldname varchar(200)

DECLARE temptable_fields_cursor CURSOR FOR
SELECT [name] AS FieldName
FROM syscolumns
WHERE id = ( SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblClientRelationships')

OPEN temptable_fields_cursor
FETCH NEXT FROM temptable_fields_cursor INTO @fieldname

WHILE @@FETCH_STATUS = 0
BEGIN
----implementation----------
--------------
FETCH NEXT FROM temptable_fields_cursor INTO @fieldname

END --End While--

CLOSE temptable_fields_cursor
DEALLOCATE temptable_fields_cursor

-Create & Execute a Dynamic Query-

SET @SqlQuery = 'Select * From Emp'
EXEC (@SqlQuery )

-Define SQL Paging for a .NET GridView.-

declare @nPageNum INT
declare @nPageSize INT

set @nPageNum=1
set @nPageSize=10;

Note:-
--Following Select statement result will be stored in to 'Search' variable and we should need to retrieve data from 'Search' in the next statement.
--We can query 'Search' only one time.

WITH Search AS
(
SELECT rownum, Records.[Name] as PersonName , Records.[Age] as PersonAge ,Records.[ID], Records.[Bdate]
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID,Bdate) AS rownum ,
dbo.Person.[Name] , dbo.Person.[Age] ,dbo.Person.[ID]
,dbo.Person.[Bdate] FROM dbo.Person)
AS Records
)

SELECT PersonName,PersonAge,Search.[ID],Search.[Bdate],(SELECT COUNT(*) FROM Search) AS RecordCount
FROM Search
WHERE rownum BETWEEN (@nPageNum-1)*@nPageSize+1 AND @nPageNum*@nPageSize
ORDER BY Search.[Bdate]

-SQL SERVER – 2005 Locking-

ROWLOCK
Use row-level locks when reading or modifying data.

PAGLOCK
Use page-level locks when reading or modifying data.

TABLOCK
Use a table lock when reading or modifying data.

DBLOCK
Use a database lock when reading or modifying data.

UPDLOCK
UPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.

XLOCK
Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.

HOLDLOCK
Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is

no longer required.

NOLOCK
This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.

Examples:
SELECT OrderID
FROM Orders (WITH ROWLOCK)
WHERE OrderID BETWEEN 100
AND 2000

UPDATE Products (WITH NOLOCK)
SET ProductCat = 'Machine'
WHERE ProductSubCat = 'Mac'

http://msdn.microsoft.com/en-us/library/aa213026(SQL.80).aspx
http://blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/



View SQL Server Connected Users:-
sp_who or sp_who2

Get SQL users that are connected and how many sessions they have
SELECT login_name, count(session_id) as session_count
FROM sys.dm_exec_sessions
GROUP BY login_name

0 comments

ADO.NET Sample

SQL:-
CREATE PROCEDURE ShowSuppliers(
@txt varchar(50),
@Name varchar(50) output,
@Company varchar (50) output,
@Country varchar (50) output
)
AS
select @Name = ContactName, @Company = CompanyName,
 @Country = Country
from Suppliers
Where Country like "%"+ @txt +"%"
GO
--------------------------------------------------------------------------------------------------------
C#:-
// Define the command object with stored procedure name.
SqlCoommand objCommand = new Command("ShowSuppliers", objConnection);
objCommand.CommandType = CommandType.StoredProcedure;
 
// Define input Sql parameter with input value.
SqlParameter Param = objCommand.Parameters.Add("@txt", SqlDbType.Varchar, 50);
Param.Direction = ParameterDirection.Input;
Param.Value = "US";
 
// Define sql stored procedure output parameters
SqlParameter Param = objCommand.Parameters.Add("@Name", SqlDbType.Varchar, 50);
Param.Direction = ParameterDirection.Output;
SqlParameter Param = objCommand.Parameters.Add("@Company", SqlDbType.Varchar, 50);
Param.Direction = ParameterDirection.Output;
SqlParameter Param = objCommand.Parameters.Add("@Country", SqlDbType.Varchar, 50);
Param.Direction = ParameterDirection.Output;
 
// Execute the query
objCommand.ExecuteNonQuery();
 
// Retrieve Sql StoredProcedure output data.
Response.Write (objCommand.Parameters["@Name"].Value.ToString() + "<BR>");
Response.Write (objCommand.Parameters["@Company"].Value.ToString() + "<BR>");
Response.Write (objCommand.Parameters["@Country"].Value.ToString() + "<BR>");
 
=================================================================================
SQL:-
CREATE PROCEDURE ShowSuppliers (
@txt varchar(50)
)
AS
Select CompanyName, City, Country
from Suppliers
Where  Country like "%" + @txt + "%"
--------------------------------------------------------------------------------------------------------
C#:-
try{
 SqlCommand objCommand = new SqlCommand("ShowSuppliers",objConnect);
 objCommand.CommandType = CommandType.StoredProcedure;

 SqlParameter Param = objCommand.Parameters.Add("@txt",SqlDbType.VarChar, 50);
 Param.Value = "US";
 
 SqlDataReader objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection);

   while (objDataReader .Read())
     {
        // Get data from the DataReader
        Console.WriteLine("CompanyName:-", (objDataReader ["CompanyName"]);
        Console.WriteLine("City:-", (objDataReader ["City"]);
        Console.WriteLine("Country :-", (objDataReader ["Country "]);
     }
}
finally
{
     // close the reader in the face of exceptions
     if (objDataReader != null)
     {
        if (!objDataReader.IsClosed)
          objDataReader.Close();
      }
}
 
 

1 comments

Database Normalization- Quick Reference

The Normalization is used to organize data in a DB by eliminating redundant data and
ensuring all table relationships make sense
 
First Normal Form (1NF)

First Normal Form ensures there is no repeating groups
    * Eliminate duplicative columns from the same table.
    * Create separate tables for each group of related data and identify each row with a unique column
 or set of columns (the primary key).
 
============================================================================
Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of eliminating Redundant Data
    * Meet all the requirements of the first normal form.
    * Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
    * Create relationships between these new tables and their predecessors through the use of foreign keys.
 
============================================================================
Third Normal Form (3NF)

Third normal form (3NF) further addresses the concept of eliminating Columns which are Not Dependent On the Key
    * Meet all the requirements of the second normal form.
    * Remove columns that are not dependent upon the primary key.
 
============================================================================
Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:
    * Meet all the requirements of the third normal form.
    * A relation is in 4NF if it has no multi-valued dependencies.
 

0 comments

SQL Table Joins- Quick Reference

EQUI-JOIN
--------------
select *
from emp INNER JOIN dept
ON
emp.deptId = dept.id;

============================================================================
INNER JOIN
----------------
select e.empId, j.salary
from jobs j INNER JOIN emp e
ON
e.salary BETWEEN j.salary
---OR---------------------------
select e.empId, j.salary
from jobs j INNER JOIN
USING(Salary)

**Equi Join only use to retrieve data base on '='.
But Inner Join can use to retrieve data base on '=', '<', '>'
**NATURAL JOIN is same to the INNER JOIN but use NATURAL JOIN keyword and
which avoids duplicate columns.

=============================================================================
SELF JOIN
--------------
This also use INNER JOIN key word but used to retrive data from the same table.
SELECT e.first_name AS 'Employee FN', e.last_name
FROM employees AS e LEFT OUTER JOIN employees AS m
ON e.manager =m.id


LEFT OUTER JOIN
----------------------------
Select *
From emp LEFT OUTER JOIN dept
ON
emp.deptId = dept.Id

RIGHT OUTER JOIN
---------------------------
Select *
From emp RIGHT OUTER JOIN dept
ON
emp.deptId = dept.Id

FULL OUTER JOIN
---------------------------
select *
From emp FULL OUTER JOIN dept
ON
emp.eid = dept.id

**Outer joins will display all columns in both tables filled with null values for culumns which doesnt retrieve the through query.
More Info:- http://en.wikipedia.org/wiki/Join_(SQL)
0 comments
Subscribe to: Posts (Atom)