How to in SQL Server, C#.Net, BizTalk Server, BizTalk RFID, and SharePoint Server

Modal popup dialog box using JQuery

2011-10-12T07:28:00.000-07:00

Before going into the implementation let me first share the screen shot how the popup will look like (and obviously you can customize it as per your branding needs)

Just download the css, images and Java script files from below link

http://simplemodal.googlecode.com/files/simplemodal-demo-basic-1.4.1.zip

Above download will give you a demo html page, you can just take a look and copy pastes the code on your page. This works great!!

For more detail on the JQuery modal dialog box you can visit to http://www.ericmmartin.com/projects/simplemodal-demos/

Modifying Web.Config file and IIS Reset

2011-06-04T02:02:00.001-07:00

What do you think changing in web.config file reset the IIS? Are you sure??

When you say IIS RESET do you know IIS Reset stops and restarts the entire web server (including non-ASP.NET apps).

Lets take a look at IIS.

In IIS you can create multiple websites, application pools. You can run multiple applications under one application pool. Now think in your IIS you have so many applications running, and you just modified one of your application web.config file and if you are thinking modifying web.config file reset the IIS then that means you have impacted all the applications running on the IIS server, is that really making sense??

The Answer to the above question is NO, it doesn’t reset the IIS when you modify web.config file of your application.

Editing the web.config in a web application only affects that web application (recycles just that app), so it doesn’t require to reset the IIS, it just impact that particular application and reload the app domain. And if you are recycling an app pool that will only affect applications running in that app pool. But Editing the machine.config on the machine will recycle all app pools running.

Another question is when you change/update dlls/drop new dlls in the bin directory of your web application, is that will reset IIS?? Again the answer is NO, it doesn’t reset the IIS. IIS monitors the /bin directory of your application. Whenever a change is detected in those dlls, it will recycle the app and re-load those new dlls.

How to copy an image from an URL to own server and resize it

2011-05-19T08:20:00.001-07:00

First: Download the image with Url.

Second: Resize the image and save it into disk.

<! 1) For the first requirement, you can use WebClient class. Refer to the following code

private void Page_Load(object sender, System.EventArgs e)

{

WebClient wc = new WebClient();

byte[] data = wc.DownloadData("http://www.google.cn/intl/en-us/images/logo_cn.gif");

MemoryStream ms = new MemoryStream(data);

System.Drawing.Image img = System.Drawing.Image.FromStream(ms);

GetThumbnailImage(img);//Resize Image

}

-> 2) For the second requirement refer to the code below:

private void GetThumbnailImage(System.Drawing.Image img)

{

float iScale = img.Height > img.Width ? (float)img.Height / 100 : (float)img.Width / 100;

img = img.GetThumbnailImage((int)(img.Width / iScale), (int)(img.Height / iScale), null, IntPtr.Zero);

MemoryStream memStream = new MemoryStream();

img.Save(Server.MapPath("att.jpeg"), System.Drawing.Imaging.ImageFormat.Jpeg);

memStream.Flush();

}

How to Know Country Location of a user - asp.net C#

2011-05-19T07:35:00.000-07:00

You can use below code snippet to get the region description of the current user.

using System.Globalization;

string name = RegionInfo.CurrentRegion.DisplayName;

For more information visit below link

http://www.msdn.microsoft.com/en-us/library/system.globalization.regioninfo(vs.71).aspx

SMTP Server Relay setting and sending email from c# code

2011-05-19T07:17:00.000-07:00

Use below steps to set up your SMTP server first

1. Start>>Run>inetmgr

2. Now check if SMTP server already installed with IIS (if not then open Control Panel>>Add Remove Programs >> click on add remove windows components, your will get a window , here you can select Internet Information Services and double click on this, now you will get list IIS of components, here you can select SMTP Server) install it.

2. Once SMTP Server is installed, then right click on SMTP Server >> click properties, it will open a window, now select Access Tab on the top, click on Relay button, here you will get radio button option (1. Only list below, 2.All except the list below), select only list below and add your ip address and mask (127.0.0.1) in the list

Click OK, you are done,

Now in your application, you can specify your SMPTP Server IP address and credentials for sending emails. You can use below sample code for sending emails from c# code.

static public void SendMail(string fromEmail, string fromName, string toAddress,

string toName, string ccAddress, string subject,

string body

)

{

SendMail(new System.Net.Mail.MailAddress(fromEmail, fromName), toAddress, ccAddress, subject, body);

}

static public void SendMail(System.Net.Mail.MailAddress fromAddress, string toAddress, string ccAddress,

string subject, string body)

{

//Read SMTP Server Name or IP from Config xml file

string SmtpServer = ConfigSettings.GetProperty(Constants.SMTP_SERVER);

//Read User Name from Config xml file

string SmtpUserName = ConfigSettings.GetProperty(Constants.SMTP_USERNAME);

//Read User Password from Config xml file

string SmtpUserPass = ConfigSettings.GetProperty(Constants.SMTP_PASSWORD);

//Read port setting from Config xml file

string smtpPort = ConfigSettings.GetProperty(Constants.SMTP_PORT);

System.Net.Mail.SmtpClient smtpSend = new System.Net.Mail.SmtpClient(SmtpServer);

using (System.Net.Mail.MailMessage emailMessage = new System.Net.Mail.MailMessage())

{

emailMessage.To.Add(toAddress);

if (!string.IsNullOrEmpty(ccAddress))

emailMessage.CC.Add(ccAddress);

emailMessage.From = fromAddress;

emailMessage.Subject = subject;

emailMessage.Body = body;

emailMessage.IsBodyHtml = true;

if (!Regex.IsMatch(emailMessage.Body, @"^([0-9a-z!@#\$\%\^&\*\-=_\+])", RegexOptions.IgnoreCase) ||

!Regex.IsMatch(emailMessage.Subject, @"^([0-9a-z!@#\$\%\^&\*\-=_\+])", RegexOptions.IgnoreCase))

{

emailMessage.BodyEncoding = Encoding.Unicode;

}

if (SmtpUserName != null && SmtpUserPass != null && smtpPort != null)

{

smtpSend.Port = Convert.ToInt32(smtpPort);

smtpSend.UseDefaultCredentials = false;

smtpSend.Credentials = new System.Net.NetworkCredential(SmtpUserName, SmtpUserPass);

}

try

{

smtpSend.Send(emailMessage);

}

catch (Exception ex)

{

AppException.HandleException(ex);

}

AJAX AutoComplete Feature Implementation Example

2011-05-19T07:01:00.000-07:00

1.       Add AJAXControltoolkit dll in your web site reference
2.       Drag n drop script manager on the top of the page (if already included inmaster page then no need)
3.     Place folowing html in your html page

<ajaxToolkit:AutoCompleteExtender ID="AutoCompletePeople" runat="server" TargetControlID="txtUsers "

DelimiterCharacters="," MinimumPrefixLength="1" ServicePath="NewQuestion.aspx"

ServiceMethod="GetUsersList" UseContextKey="True"

EnableCaching="true"

CompletionSetCount="5"

CompletionInterval="1000"

BehaviorID="autoCompleteBehavior1">

4. Write following code in aspx.cs class file

#region AutoComplete Users

//Specify this method as ServiceMethod for Company Completion List

[System.Web.Services.WebMethod]

public static string[] GetUsersList(string prefixText, int count)

{

string[] result = selectUsers(prefixText);// this method returns all the users

return result;

}

#endregion AutoComplete Users END

Here is the link for AJAX Auto complete features and more

http://www.asp.net/ajax/ajaxcontroltoolkit/Samples/AutoComplete/AutoComplete.aspx

Custom Rating Control, create your own simple rating control

2011-05-19T06:45:00.001-07:00

I have implemented rating control in one of my application by using two images (blue star and white star), and written c# code for displaying the rating selected by the user, you can store the selected rating in the database also and display accordingly.

You might be wondering if rating controls are already available then why build from scratch, your thinking is absolutely right; however I wanted a simple light weight control for my application so that I can change anything I want.

First what is does it display 5 rating white stars and when user click on any star let’s say click on 3rd then you have to replace the image URL of previous 2 images and the clicked one to blue (whatever color you like), so it will become 3 blue star.

Lets have a look at the sample code. You just need to pass the rating value to the setFeebackScore method; it will set the images and display the score description accordingly. You can create this control as a component and use it wherever you want in your application.

private const string IMAGE_FEEDBACK_BLUE = "/_layouts/images/neon_FeedbackBlue.JPG";

private const string IMAGE_FEEDBACK_WHITE = "/_layouts/images/neon_FeedbackWhite.JPG";

private void setFeedbackScore(float rating)

{

if (rating == float.Parse("1"))

{

imgPoor.ImageUrl = IMAGE_FEEDBACK_BLUE;

imgFair.ImageUrl = IMAGE_FEEDBACK_WHITE;

imgGood.ImageUrl = IMAGE_FEEDBACK_WHITE;

imgVeryGood.ImageUrl = IMAGE_FEEDBACK_WHITE;

imgExcellent.ImageUrl = IMAGE_FEEDBACK_WHITE;

lblScoreDescription.Text = "(Poor)";

}

else if (rating == float.Parse("2"))

{

imgPoor.ImageUrl = IMAGE_FEEDBACK_BLUE;

imgFair.ImageUrl = IMAGE_FEEDBACK_BLUE;

imgGood.ImageUrl = IMAGE_FEEDBACK_WHITE;

imgVeryGood.ImageUrl = IMAGE_FEEDBACK_WHITE;

imgExcellent.ImageUrl = IMAGE_FEEDBACK_WHITE;

lblScoreDescription.Text = "(Fair)";

}

else if (rating == float.Parse("3"))

{

imgPoor.ImageUrl = IMAGE_FEEDBACK_BLUE;

imgFair.ImageUrl = IMAGE_FEEDBACK_BLUE;

imgGood.ImageUrl = IMAGE_FEEDBACK_BLUE;

imgVeryGood.ImageUrl = IMAGE_FEEDBACK_WHITE;

imgExcellent.ImageUrl = IMAGE_FEEDBACK_WHITE;

lblScoreDescription.Text = "(Good)";

}

else if (rating == float.Parse("4"))

{

imgPoor.ImageUrl = IMAGE_FEEDBACK_BLUE;

imgFair.ImageUrl = IMAGE_FEEDBACK_BLUE;

imgGood.ImageUrl = IMAGE_FEEDBACK_BLUE;

imgVeryGood.ImageUrl = IMAGE_FEEDBACK_BLUE;

imgExcellent.ImageUrl = IMAGE_FEEDBACK_WHITE;

lblScoreDescription.Text = "(Very Good)";

}

else if (rating == float.Parse("5"))

{

imgPoor.ImageUrl = IMAGE_FEEDBACK_BLUE;

imgFair.ImageUrl = IMAGE_FEEDBACK_BLUE;

imgGood.ImageUrl = IMAGE_FEEDBACK_BLUE;

imgVeryGood.ImageUrl = IMAGE_FEEDBACK_BLUE;

imgExcellent.ImageUrl = IMAGE_FEEDBACK_BLUE;

lblScoreDescription.Text = "(Excellent)";

}

else

{

imgPoor.ImageUrl = IMAGE_FEEDBACK_WHITE;

imgFair.ImageUrl = IMAGE_FEEDBACK_WHITE;

imgGood.ImageUrl = IMAGE_FEEDBACK_WHITE;

imgVeryGood.ImageUrl = IMAGE_FEEDBACK_WHITE;

imgExcellent.ImageUrl = IMAGE_FEEDBACK_WHITE;

lblScoreDescription.Text = string.Empty;

}

JavaScript to auto increase height of multiline text box

2011-03-25T03:59:00.000-07:00

Here is the Java script code for auto resizing multiline text box. What it does is when user keeps typing in the text box and reach to the end line then text box height automatically increases.

// this function takes text box as input parma which need to grow in height upon typing

function AutoResizeMultilineTextBox(txtBox) {

nCols = txtBox.cols; // find total columns set to the text box

sVal = txtBox.value; // total characters

nVal = sVal.length; // total lenth of the character

nRowCnt = 1; // new row count variable reset to 1

// this loop is written to find out total new row count to be added depending upon the character lenght

//and new line found in the character

for (i = 0; i < nVal; i++) {

if (sVal.charAt(i).charCodeAt(0) == 13) {

nRowCnt += 1;

}

// check if new row count is less than the rown count covered by the character

if (nRowCnt < (nVal / nCols)) { nRowCnt = 1 + (nVal / nCols); }

txtBox.rows = nRowCnt+2;

}

</script>

Example text box

<asp:TextBox ID="txtMsg" runat="server" TextMode="MultiLine" onkeyup="AutoResizeMultilineTextBox(this)" Rows="3" Columns="50" />

Above code will cover all scenarios

- User Continuous typing

- User keep hitting new line (Enter Button)

- User hit back button

Event Validation Error when switching between aspx pages

2011-03-25T03:54:00.000-07:00

In .Net web applications when you switch between pages very frequently sometimes you might observe exceptions (page breaks).

Exception:

“Invalid postback or callback argument. Event validation is enabled using <pages enableeventvalidation="true" /> in configuration or <%@ page enableeventvalidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.”

Solution:

This problem occurs when post back happen before completely loading the previous page, to avoid this problem you need add following code under page element in web.config file

This will solve your problem; you don’t need to add these setting codes in all your aspx pages, if you have added it under page element in web.config then it will work for all the pages throughout the application.

How to keep session alive in asp.net application

2010-08-03T14:25:00.001-07:00

Friends, many times we get requirements to increase the session timeout expiry time on the application server, so what we do basically we go in web.config file and set session time out to some time period, by default its 20 mins, but remember sometime you increase time period in web.config file and it doesn't work why?? The reason is you also have to check your application pool recycling time period it should be more than the session expiry period which you are doing in web.config file.

Yo! But anyways you did not avoid session expiration in your application, session will still expire in your application let's say if user filling a long form in your application or kept a page open and gone out for coffee. So now what will happen? Session will expire right? By the time use will be back and resume with the work.

Some people do session resetting in code behind by checking if session is null then read User ID () from cookies and reset it. Or if it is intranet application and AD authentication then you get the current logged in user in system and set it in session.Now if you don't wanna do all above crap In your application, what all you wanna do is don't let session to get expire unless user close the browser. To implement this behavior in your applications simply follow below steps.

First of all let me give you some brief about working session state in asp.net. By default it is set to 20 min, so if user sends a request to the server (in simple language browse a page) then server stores the time when request came and extend the session time out to 20 min, so if after 10 min user send again a request so it keeps extended to 20 min, if user don't send any request to server till 20 min then session get expires. This is basic working of session state.
From first step you know that, server need to get any request from client to keep session alive, so now we can think of some methodology to keep pinging the server, below is the script which will keep sending request to the server and will keep session alive.

Create a SessionCheck.aspx page in your application; you can keep this page blank. So that if it gets called from the browser then it should not be over loaded.

Using Java Scrip:
Image will be used to keep session alive by changing image src, assigning this property to some
<img id="imgSessionCheck" width="1" height="1" />

<script type="text/javascript" >
//Variable used to prevent caching on some browsers and knowing the count how many times user sends request to server.
    var counter;
    counter = 0;

    function KeepSession() {
// Increase counter value
    counter++;

    // Gets reference of image
    var img = document.getElementById("imgSessionCheck");

    // Set new src value, which will send a request to the server
img.src = "http://servername:port/appName/SessionCheck.aspx?count=" + counter;

    // now schedule this process to happen in some time interval, in this example its 1 min
    setTimeout(KeepSession, 60000);
}

    // Call this function for a first time
    KeepSession();
    </script>

Using JQuery:
<script language="javascript" type="text/javascript" src="http://code.jquery.com/jquery-latest.js"></script>
<script language="javascript" type="text/javascript">

    function KeepSession() {
    // A request to server
    $.post("http://servername:port/appName/SessionCheck.aspx");

    //now schedule this process to happen in some time interval, in this example its 1 min
    setInterval(KeepSession, 60000);
}

    // First time call of function
    KeepSession();
</script>

So it's simple just you need to put this script in your page.

Thanks for ready this post.

Happy Coding!!!

Controlling SQL Server 2005 behavior by setting database options regarding NULL values

2010-05-26T10:54:00.001-07:00

To access SQL Server there are many Tools and interfaces available, these interfaces can have some default settings to control the database behavior from front end. Right now I am going to talk about most familiar database front end interface SQL Server Management Studio.

To open database Options,

Right Click your database >> Properties >> Options, you will get below screen shot.

ANSI NULL Default:

The default option ANSI NULL DEFAULT corresponds to two session settings ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF. When ANSI null default database option is false, then the new columns created with the ALTER TABLE and CREATE TABLE statements are by default NOT NULL if Nullability for the column is not explicitly defined.

When this option is set to ON, columns comply with the ANSI SQL-92 rules for column nullability. That is if you don't specifically indicate whether a column in a table allows NULL values, NULLs are allowed. When this option is set to OFF, newly created columns do not allow NULLs if no nullability constrains is specified.

ANSI_NULLS:

Database option ANSI Nulls corresponds to the session settings SET ANSI_NULLS. When this option is set to true, all comparison to a null value evaluate to false. When it is set to false, comparison of non-Unicode values to a null evaluate to true if both values are NULL.

In addition if this option is set to true then your code must use the function IS NULL to determine whether a column has a NULL value. When this option is set to false, SQL Server allows=NULL as a synonym for IS NULL and <> NULL as a synonym for IS NOT NULL.

Below is the code snippet to demonstrate this behavior.

I have a user table in which Email column is having NULL values,

-- This query returns all the rows where email = null, so column to null comparison works

set
ANSI_NULLS
OFF

GO

select
*
from Users where email =
NULL

-- This query doesnt return any row, colum to NULL comparison doesnt work

set
ANSI_NULLS
ON

GO

select
*
from Users where email =
NULL

ANSI_PADDING:

When this option is set to ON, string being compared with each other are set to the same length before the comparison take place. When this option is OFF, no padding takes place.

ANSI_WARNING:

When this option is set to ON, errors or warnings are issued when conditions such as division by zero or arithmetic overflow occurs.

CONCAT_NULL_YEILDS_NULL:

When this option is set to ON, concatenating two strings results in a NULL string if either of the string is NULL. When this option is set to OFF, a NULL string treated as an empty (zero-length) string for the purpose of concatenation.

Saving changes is not permitted. How to save the changes in table design, column ordering in SQL Server 2005 / 2008

2010-05-17T11:39:00.001-07:00

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that requires the table to be re-created.

Screen shot:

While designing database or during initial development mode I keep changing the database schema (sometimes adding more fields or re-arranging the column positions) I got this popup many times and that makes me to go and write alter script to do the changes in table schema but through alter script I dont know how to change the column position so at last I have to fix this popup and do my changes easily through table designer.

Below are few steps to fix this popup and it will never appear

Step1: in SSMS, go to Tools>>Options…

Step 2: Drill down to Designers >> Table and Database Designers

Step 3: You will see bunch of check boxes there is one which is highlighted in above screen shot "Prevent saving changes that requires table re-creation" uncheck this check box,

Hit OK, that's all you are done, now go to your table designer and add columns, rearrange column position now it will not prompt any popup.

Cheers!!!!

Table Variable Vs Temp table in SQL Server

2010-05-13T02:27:00.001-07:00

In many scenarios we need some temporary table for the processing of data. Now you have two option 1) Table Variable, 2) temp Table. Which one do you choose? Let's talk about differences between these two and make sure our decision to use one of them is best for our requirement.

Table Variable	Temp Table

Performance differences
Table variables don't participate in transactions, logging or locking. This means they're faster as they don't require the overhead, but conversely you don't get those features.	Temporary Tables are real tables so you can do things like CREATE Indexes, etc. If you have large amounts of data for which accessing by index will be faster than temporary tables are a good option
You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (e.g. make a function to split a string into a table of values on some arbitrary delimiter).	You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing data types over time, since you don't need to define your temp table structure upfront.
A table variable can only have a primary index,	A temp table can have indexes
If speed is an issue Table variables can be faster	But if there are a lot of records, or the need to search the temp table of a clustered index, then a Temp Table would be better.
A table variables don't have column statistics, This means that the query optimizer doesn't know how many rows are in the table variable (it guesses 1), which can lead to highly non-optimal plans been generated if the table variable actually has a large number of rows	Whereas temp tables do have column statistics so the query optimizer can choose different plans for data involving temp tables
You cannot alter a Table variable with DDL statement (so you cannot create a non clustered index on a table variable). That makes every table variable a heap, or at best a table with a single, clustered index, and every table variable access a table scan (or clustered index scan)	Temp tables can be altered with DDL statements
User table variable if there less records for processing	Use temp table if you have huge record for processing
Syntactical difference
-To create table variable declare @T table (firstColumn varchar(100)) -insert operation insert into @T select 'some value' -select statement select * from @T	-You can create temp table create table #T (firstColumn varchar(100)) -Insert Operation insert into #T select 'some value' -Select statement select * from #T

So now I think you can make wise decision which one to use when. Have fun -J

Happy Coding!!!

Script for Setting Database Mail and Sending Email From SQL Server 2008

2010-05-11T14:32:00.001-07:00

Step1: First of all you need to enable and setup Data mail profile and account. Below is the script just copy and paste to your SQL Server query window and change the parameters accordingly.

/*

    Run this script after setting proper configurations

*/

-- ENABLE DATABASE MAIL FEATURE IN SYSTEM CONFIGURATION

use
master

go

sp_configure
'show advanced options',1

go

reconfigure
with
override

go

sp_configure
'Database Mail XPs',1

go

reconfigure

go

-- DECLARE DATABASE MAIL ACCOUNT PARAMETERS

Declare @qmail_account_name as
varchar(64);

Declare @qmail_account_description as
varchar(64);

Declare @qmail_account_email_address as
varchar(64);

Declare @qmail_account_display_name as
varchar(64);

Declare @qmail_account_username as
varchar(64);

Declare @qmail_account_password as
varchar(64);

Declare @qmail_account_mailserver_name as
varchar(64);

-- DECLARE PROFILE PARAMETERS

declare @qmail_profile_profile_name as
varchar(64);

declare @qmail_profile_description as
varchar(64);

-- DECLARE PRINCIPLES

declare @qprincipal_name as
varchar(16);

-- SET SMTP ACCOUNT DETAILS, USERNAME AND PASSWORD HERE

set @qmail_account_mailserver_name =
'smtp.bizmail.yahoo.com'
-- your smtp address

set @qmail_account_username='something@something.com'
-- username

set @qmail_account_email_address =
'myEmailName@something.com'
-- email address

set @qmail_account_password='password for user (something@something.com)'
-- password for username

set @qmail_account_name =
'YourAccountName (e.g MyAppName_EmailAccount)'

set @qmail_account_description =
'Mail account for administrative e-mail.'

set @qmail_account_display_name =
'MyApplicationName Automated Mailer'

-- PROFILE PARAMETERS ASSIGNMENT

set @qmail_profile_profile_name =
'MyAppNameEmailServiceProfile'

set @qmail_profile_description =
'Profile used for database mail jobs'

--PROFILE TYPE

set @qprincipal_name=
'public'

if ( @qmail_account_mailserver_name =
''
or @qmail_account_username=''
or @qmail_account_email_address =
''
or @qmail_account_password=''
)

begin

        Select
'Please enter SMTP details'
as [Comments]

end

else

Begin

            if
exists(select
*
from msdb.dbo.sysmail_account where [name]=@qmail_account_name)

            begin

            exec msdb.dbo.sysmail_delete_account_sp
@account_name = @qmail_account_name

            end

            EXECUTE msdb.dbo.sysmail_add_account_sp

             @account_name = @qmail_account_name,

                @description = @qmail_account_description,

                @email_address = @qmail_account_email_address,

                @display_name = @qmail_account_display_name,

                @username=@qmail_account_username,

                @password=@qmail_account_password,

                @mailserver_name = @qmail_account_mailserver_name

            if
exists(select
*
from msdb.dbo.sysmail_profile where [name]=@qmail_profile_profile_name)

            begin

            exec msdb.dbo.sysmail_delete_profile_sp
@profile_name = @qmail_profile_profile_name

            end

            EXECUTE msdb.dbo.sysmail_add_profile_sp

                 @profile_name = @qmail_profile_profile_name,

                 @description = @qmail_profile_description

            EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

                @profile_name = @qmail_profile_profile_name,

                @account_name = @qmail_account_name,

                @sequence_number = 1

            EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

                @profile_name = @qmail_profile_profile_name,

                @principal_name = @qprincipal_name,

                @is_default = 1 ;

end

Step2: Create a Sproc which will send email notification. Below is the script for that only you need to change your email To/CC, Subject, Body from your application tables or you can set anything you want. Please make sure Email profile name is proper it should be same as you have configured during Database Email Setup.

if
exists
(select 1 from
sys.objects
where name =
'p_SendEmailNotification'
and
type
=
'p')

drop
procedure p_SendEmailNotification

go

Create
Procedure [dbo].[p_SendEmailNotification]

as

Begin

Declare @ErrorCode as
int

BEGIN
TRANSACTION

declare @MailPriority varchar(8);

declare @sendAddressTo varchar (256);

declare @sendAddressCC varchar(256);

declare @MailBodyFormat varchar(8);

declare @DBMailProfile varchar(32);

set @MailBodyFormat ='HTML'

set @DBMailProfile =
'Email Profile Name which you have created during DB Mail setup (e.g MyAppNameEmailServiceProfile)'

set @MailPriority =
'Normal'

select @sendAddressTo =
'someone@someone.com,abc@abc.com'

select @sendAddressCC ='someone@someone.com,xyz@xyz.com'

EXEC msdb.dbo.sp_send_dbmail

                @profile_name = @DBMailProfile,

                @recipients=@sendAddressTo,

                @copy_recipients = @sendAddressCC,

                @importance = @MailPriority,

                @subject =
'Email Subject',

                @body =
'Email Body....hi this is test email',

                @body_format = @MailBodyFormat ;



set @ErrorCode =
@@ERROR

if(@ErrorCode <> 0)

begin

goto ERR_HANDLER

end

COMMIT
TRANSACTION

Set @ErrorCode =
@@Error

if @ErrorCode<>0 begin
goto ERR_HANDLER end

ERR_HANDLER:

if @ErrorCode<>0

Begin

ROLLBACK
TRANSACTION

End

End

You are done, now if you just need to call this sproc and this will send the email, if you want to Automate this email as some alert then better you create a job and within that job call this sproc as per your needed schedule.

Disaster Recovery Plan in SQL Server

2010-04-13T14:38:00.001-07:00

Abstract
In today's world every single data is very important, data keep growing day by day, managing the data becoming challenging, thanks we have got machines which can store our data, what all our brain have to do is to manage those machines and make sure data is safe. The question is how you will make sure that the machine which is storing our data is going to be safe, if tomorrow something happens disaster and machine destroyed then where about your data which was stored in the machine since long time, is that gone ??.

We don't want to lose our data so is the disaster recovery plan comes into the picture, you can restore your data even your current database machine is dead. Busy database administrators can easily push this task onto the back burner with promises to themselves that they'll "take care of backups right after I finish this big project." However, take a moment and consider the value of your data to your organization? What would be the result if an hour's worth of database changes were lost? A day's worth? What about a complete loss of the database?
In this article we are going to learn if your data storage machine is Microsoft SQL Server then what are the ways you can plan your disaster recovery.
Table of content

Introduction
1. Disaster Recovery
2. Disaster recovery in SQL Server
Disaster Recovery Plans
1. Timely Database Backup Strategies
2. Database Replication
3. Log shipping
4. Database Mirroring
Conclusion

Introduction
Disaster Recovery:
Disaster Recovery plan is your business continuity plan. Disaster recovery is a process that you can use to help recover information systems and data, if a disaster occurs
Suppose you have a database system storing financial application data, an organization whole business is running on some applications and data is stored in database. Lets your database system machine is placed in the same organization, now if tomorrow database machine crashed then???, all your data gone, what about that organization business records, is that organization will be able to continue business?
So if you had solid Disaster Recovery plan for your organization data then your organization business will not stop and it can continue, for the organization only it will be a matter of one system crash. So in disaster recovery what you do is configure some backup mechanism on some other machine to keep the same piece of information what you have on your current database machine. So if your current database machine dead then you can recover your all data from backup machine.
The first step in developing a solid disaster recovery plan is to develop an idea of what constitutes an acceptable loss for your organization. First, consider the impact of losing data stored in your database. Would you be able to recover from the loss of an hour's worth of data? If you're managing a human resources database, chances are that you could deal with this situation by instructing your personnel to reenter data entered during that period. If you're running the database supporting a financial institution, the loss of an hour's data could bring the instant wrath of clients and industry regulators along with significant monetary losses. Protection against these types of losses is provided by backups of the database and use of transaction logging. We'll discuss these in further detail.
Disaster recovery planning is the work that is devoted to preparing all the actions that must occur in response to a disaster. The planning includes the selection of a strategy to help recover valuable data. The selection of the appropriate disaster recovery strategy depends on your business requirements.
Disaster recovery in SQL Server

Microsoft SQL Server database system provides multiple ways to configure disaster recovery.

Timely database Backup strategies
Database Replication
Log shipping
Database Mirroring

Disaster Recovery Plan
Timely Database Backup Strategies
If your disaster recovery plan you are planning for timely databases backup then all what you have to do is:

Take backup of all database from the database server (you can schedule a job in sql server to do take backup of all databases)
Copy all back up files to your file server or some other directory
You can write schedule daily/weekly backup plan in sql server
Write your daily backup / weekly back up in disk and store the disk at some safe place

Database Replication

SQL Server replication, a software package included in Microsoft SQL Server, is used to move data from server to server in a transitionally consistent state from one instance to another.

Replication topology consists of three servers – the subscriber, publisher and distributor. The subscribers are the servers receiving the data. The publisher is the server that holds the initial set of data being made available to the subscribers. The distributor is the server that contains many of the settings. It also holds the data as it is moved from the publisher to the subscribers.

Snapshot Replication

Snapshot replication is the easiest replication type to understand because it is conceptually similar to full backup and restore. With snapshot replication the server copies entire set of data to the subscriber at scheduled time, rewriting the data at subscriber with each copy of operation. However snapshot replication does not operate on the complete the database as backup and restore does. Snapshot replication copies only specified articles from publisher to the subscribers. Keep in mind because snapshot replication copies entire set of data every time it runs, you should use this replication type only when the amount of data is small and rather static.

Transactional Replication

Peer-to-peer transactional replication is designed for applications that might read or might modify the data in any database that participates in replication. Additionally, if any servers that host the databases are unavailable, you can modify the application to route traffic to the remaining servers. The remaining servers contain identical copies of the data.

Log shipping
Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. But this is not all, the key feature of log shipping is that it will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers in sync. Should the production server fail, all you have to do is point the users to the new server. Well, it's not really that easy, but it comes close if you put enough effort into your log shipping setup
Log shipping involves one Primary Server and one or many secondary server and one monitor server. First transaction log backup of database on primary server is taken and then the backup files are moved to the secondary servers and transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restores operations and, optionally, raises alerts if these operations fail to occur as scheduled.
Log shipping consists of three operations:

Back up the transaction log at the primary server instance.
Copy the transaction log file to the secondary server instance.
Restore the log backup on the secondary server instance.

The primary server instance runs the backup job to back up the transaction log on the primary database. This server instance then places the log backup into a primary log-backup file, which it sends to the backup folder. In this figure, the backup folder is on a shared directory—the backup share.
Each of the three secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder.
Each secondary server instance runs its own restore job to restore the log backup from the local destination folder onto the local secondary database.
Database mirroring
The primary goal of Database Mirroring technology is to increase the database availability. When enabled, every update to the principal database is immediately reproduced onto the mirror database
Database mirroring is a primarily software solution for increasing database availability. You can only implement mirroring on a per-database basis. Mirroring only works with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, all bulk operations are always fully logged. It also offers failover capabilities, which may be made automatic depending on how you configure mirroring. The mirrored copy is a standby copy that cannot be accessed directly. It is used only for failover situations
Principal Server: In a database mirroring configuration, there are two copies of a single database, but only one copy is accessible to the clients at any given time. The copy of the database that the applications connect to is called the principal database. The server that hosts the principal database is known as the principal server.
Mirror Server: The mirror is the copy of the principal database. The mirror is always in a restoring state; it is not accessible to the applications. To keep this database up-to-date, the log records are transferred from the principal and applied on the mirror database. The server that hosts the mirror database is known as the mirror server.
Witness Server: The optional witness is an SQL Server instance in a database mirroring configuration. It is separate from the principal and mirror instances. When database mirroring is used in synchronous mode, the witness provides a mechanism for automatic failover
Conclusion
In this article we had discussed about disaster recovery and disaster recovery plan in sql server. Disaster recovery plan is very important for an organization running business by using software application, so data recovery plan you can say is the business continuity plan. There are many ways you can configure disaster recovery for your database, in sql server database you can use Database Replication, Timely Backup Strategies, Log Shipping, and Database Mirroring.

SQL server best practices and code review check list

2010-03-04T01:54:00.000-08:00

Quite frankly let me share with you that i learned T-SQL programming from one of the worst database design which was designed by me. I never realized my database design is going to work for me or not/...what i did was started with development and created bunch of stored procedures, functions, views.... Every sproc having huge data processing logic, table joins, function calls, so i almost used all the syntax of T-SQL programming (including Transaction handling, temp Tables, Cursors, derived table, sub queries.....etc. ).
I never forget one of my sproc in which i used cursor inside cursor and two temp table for each cursors, and processing huge records in the cursor now you can imagine performance of my sproc. Anyhow first phase of my application was developed and deployed successfully.

Now Time to start with next phase....in the beginning i realized that the current database design is not going to support implementation of a small functionality, so what should i do..create new tables...maintain same data in multiple table..oh no..how bad design!!!!!!

Thank God it was not a customer project it was internal application for training purpose.

Then i started looking at my existing database design and started tweaking..... it took me around two weeks to stabilize my existing database ..changing the sprocs..code and making sure that existing functionality does not breaks.

At the end DB was stabilized...

Now let me make sure you do not make such mistakes....so here are some tips which you can take a look before you finalize your database design.

So guys SQL Server code review is very important...review you database design carefully..below are the steps which will help to ensure a good database design...

Database Design Considerations

Database Model - Build tables that balance the number of joins (normalization) versus a reasonable amount of denormalization. If you want the application to be highly normalized for the opportunity to extend it for more generic needs, be sure you understand the trade-off of having to join a potentially large number of tables. On the other side of the coin, if your database only has a handful of tables that are 200+ columns with a great deal of duplicate data, you may want to strike a balance.
- SQL Server 2005 Exposed = Data Modeling Tools
Primary Keys - Ensure a column in each table is designated as the primary key and select an appropriate data type such as an integer.
- Finding primary keys and missing primary keys
Foreign Keys - Validate all of the foreign keys are established to maintain referential integrity among your tables to ensure the proper relationships are maintained.
- The Importance of Foreign Keys
- Identify all of your foreign keys in a database
Auto Increment - If a column needs an incremental value, use an identity for that column as opposed to a custom solution.
- Managing and maintaining identity values
- Retrieving identity column properties for all tables in a database
Correct Data Types - Be sure to select the correct data types for each column. For example, if a varchar column can be used as opposed to a BLOB data type, in general the performance implications will be far less. So choose the data type wisely.
- Identify All Tables with Columns of a BLOB Data Type
Indexing - As new queries are written, columns are added or removed, it is a good time to validate that your indexes will support your performance needs. Validate indexes are not unnecessarily duplicated and validate as indexes are added that the intended queries take advantage of the query. If not, think twice about building a new index that SQL Server will need to use, but your new query is not using.
- Clustered Tables vs Heap Tables
- Index Analysis Script - All Indexes for All Tables
Statistics - Validate sufficient statistics are available on a per column basis or validate based on the database configurations that the indexes will be created.
- The NORECOMPUTE option of UPDATE STATISTICS
Lookup Tables - As opposed to being forced to hard code values in T-SQL code, lookup the values in a table with the proper referential integrity. Over time the code will be much easier to understand and simpler to maintain.
Defaults - As applicable, create defaults for the columns to have a meaningful value for the column.
NULL Usage - Review the NULL usage on a per-column basis and determine if NULL is an applicable value or if a business specific value will be used instead which can be enforced via a lookup table.

General T-SQL Considerations

Testing Results - Validate the functional testing was completed successfully to ensure you will not face functional issues once the code is deployed and be in a situation where you are asked to change the code on the fly.
- Testing Options with SQL Server 2000 and 2005
Response Time Requirements - Validate the performance testing yielded acceptable performance for the new code that is going to be released as well as not negatively impact the existing platform. To me, the only situation worse than having to change code on the fly is the need to firefight a performance issue.
- Testing Options with SQL Server 2000 and 2005
Scope of Data - Only process (SELECT, INSERT, UPDATE or DELETE) the needed data. I have seen in a few different applications where SQL Server is flooded with SELECT statements that return unneeded data that is unnecessarily filtered by the front end application. A much more efficient process is to just select the data needed. On another occasion, I have observed duplicate data being unnecessarily inserted into the table. The resolution in this specific scenario is duplicate data that needs to be filtered on the front end.

Stored Procedure Considerations

General Review - As you review the code, make sure you do not see any of the following:
- Unnecessary JOINs
- Inaccurate calculations or functions
- Unnecessary data
- Cartesian product
General Technique - Make sure the general coding practice is reasonable. If possible, use SET based logic as opposed to a cursor or a while statement. In addition, avoid logic that processes data in a temp table as opposed to processing the data directly. For example, there is no reason to load data into a temporary table, update the data in the temporary table, delete the data from the base table and then load the data from the temporary table. Just update the needed data in the base table.
Query Plan - Review the query plan to validate it is efficient. Validate the following items:
- Table scanning is not occurring
- Bookmark lookups are not occurring
- Code or indexes can be changed to convert index scans to index seeks
Optimizer Overrides - Review the code to determine if index hints or NOLOCK clauses are really necessary or if a portion of the code can be changed to not try to out smart the optimizer. These hints could be beneficial in the short term, but as the database or code changes as well as when new versions of SQL Server are released, these overrides can be an unexpected exercise when it comes time to upgrade.
- Notes from the field - Post SQL Server 2000 Service Pack 4 Performance Issues
Comments - Be sure valuable comments are included with the code; if not, it is rare that comments are added once the code is deployed.
- Commenting SQL Server T-SQL Code

Read this full article on SQL Server Code Review Checklist on

http://www.mssqltips.com/tip.asp?tip=1303

How to remove an object with specific property value from the List of Objects using LINQ

2010-03-03T03:43:00.000-08:00

Suppose you have an object call Employee (having properties EmployeeID and EmployeeName) and you have List of Employee object (List lstEmployee, now if you want to remove a particular Employee object which has EmployeeID lets say 10 from the list lstEmployee, below is the source code which will give some feel of LINQ.

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Collections.Generic;

public partial class _Default : System.Web.UI.Page

{

class Employee

{

public int EmployeeId { get; set; }

public string EmployeeName { get; set; }

}

protected void Page_Load(object sender, EventArgs e)

{

List<Employee> lstEmployee = new List<Employee>();

Employee emp = new Employee();

emp.EmployeeId = 1;

emp.EmployeeName = "Name1";

lstEmployee.Add(emp);

emp = new Employee();

emp.EmployeeId = 2;

emp.EmployeeName = "Name2";

lstEmployee.Add(emp);

emp = new Employee();

emp.EmployeeId = 10;

emp.EmployeeName = "Name10";

lstEmployee.Add(emp);

//Below LINQ is used to get all the employee which is not having employee id 10

lstEmployee = lstEmployee.Where(x => x.EmployeeId != 10).ToList();

}

Happy Coding !!!!!!!

Script for taking backup of all the databases in the SQL Server

2010-02-08T06:23:00.000-08:00

Hello, do you want to take back up of all the databases from you DB server, ok below is the script for that.
What all you have to do is provide the path where you want to store all the backups and exclude some database which you dont want to take the backup.

Here you go .........

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'E:\AllDBBackups\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb','MyTestDBIdontWantToTakeBackupOfThisDB')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

BACKUP DATABASE @name TO DISK = @fileName with init

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

Now go to your folder location and check you will have all the databases .bak file which will have current date postfix in the file name.

Clear cache before generating the execution plan in SQL Server

2009-09-06T08:00:00.000-07:00

Hi,

Are you doing SQL query tuning ???, so you must be generating execution plan...... Ok ..so before generating execution plan of your SQL query you shoud flush the proc out of your database and clear cache/buffers...below are the script for that.

1. Command to flush the proc out of database
DBCC FLUSHPROCINDB (db_id)

here db_id you can find by using following query in you database
Select db_id() -- this will return int number that number you can pass as db_id

2. Clear Cache

DBCC FREEPROCCACHE

2. Clear Buffers
DBCC DROPCLEANBUFFERS

After running above 3 commands in your database, you can generate execution plan,

now you will  get exact execution plan.

How to generate data script for a table in SQL Server

2009-09-05T10:54:00.000-07:00

Hi,

In your SQL Server database you might be having some master tables which contains master data entries of your application,

schema of these tables you can easily generate from SQL Server table properties and generate schema options, now if you want to generate data script of all the existing data in the table then you can use below SQL script to do that work

Say AppRoles table contains following data

AppRoleId	RoleDescription	Status
1	Admin	1
2	User	1
3	Guest	1
4	Demo User	1

select 'insert into tAppRoles (AppRoleID, RoleDescription, Status) values (''' + AppROleId + ''',''' + RoleDescription + ''',' + convert(varchar(1),status) + ')' from tAppRoles

Now when you will run above script in your database it will give you following result set

insert into tAppRoles (AppROleId, RoleDescription, Status) values (1, 'Admin', 1)
insert into tAppRoles (AppROleId, RoleDescription, Status) values (2, 'User', 1)
insert into tAppRoles (AppROleId, RoleDescription, Status) values (3, 'Guest', 1)
insert into tAppRoles (AppROleId, RoleDescription, Status) values (4, 'DemoUser', 1)

So this way you can get insert script of all the records, now just save it ....you are done !!!

SQL Reporting Services 2008 FAQs

2009-08-25T09:51:00.000-07:00

Q:What is SQL Reporting Services?
A: SQL Server Reporting Services is a comprehensive, server-based solution that enables the creation, management, and delivery of both traditional, paper-oriented reports and interactive, Web-based reports. An integrated part of the Microsoft business intelligence framework, Reporting Services combines the data management capabilities of SQL Server and Microsoft Windows Server™ with familiar and powerful Microsoft Office System applications to deliver real-time information to support daily operations and drive decisions. (text from http://www.microsoft.com/sql/reporting/productinfo/overview.asp)

Q:How do I create a report?
A:There are 2 common ways of creating a report for use by SQL Reporting Services. Reports in SQL Reporting Services use the Report Description Language (RDL) which is an XML based standard for defining reports. Since it is simply XML, a report can, theoretically, be written using any text editor. This method, however, is not an approach favored by most since implementing RDL by hand can be laborious in addition to having a steep learning curve.

By far, the most common way of creating a report is by using the SQL Report Designer which is available to those owning licenses for both SQL Server and Visual Studio.NET 2008. The Report Designer is and Visual Studio.NET 2008 add-in packaged with SQL Reporting Services and, when installed, makes available a Report project type in Visual Studio.NET 2008.

Also on the horizon are numerous third party tools that are able to generate RDL. At the time of this writing, many of the available tools are still in beta testing but there are a handful that offer full products for consumer purchase and/or evaluation.

Q: Is the ReportViewer control included in SQL Server 2005/2008?
A: No. Report Server is a component of SQL Server 2005/2008, but the ReportViewer control is not. ReportViewer is only included in Visual Studio 2005/2008.

Q : Does SQL Server have to be installed in order to use the ReportViewer control?
A: No. The only prerequisite of the ReportViewer control is .NET Framework 2.0.

Q : Is a SQL Server license required to use the ReportViewer control?
A: No. The ReportViewer control is freely redistributable. It can work independently of SQL Server ('local mode') and a SQL Server license is not required. In remote mode the ReportViewer control talks to a Report Server. The Report Server does require a SQL Server license.

Q : Does the data for the report have to come from a SQL Server database?
A: No. Data can come from any source. It is the host application's responsibility to collect data from whatever source it needs to come from, and supply it to the control in the form of ADO.NET DataTables or a collection of business objects. The ReportViewer control does not know or care where the data is coming from.

Q: Can the ReportViewer view reports hosted on a SQL Server 2000 Report Server?
A: No. In remote mode the ReportViewer control can only view reports hosted on a SQL Server 2005 Report Server. Note that this has nothing to do with where the data for the report can come from. Data for the report can come from any source, including SQL Server 2000, Oracle, DB2 and non-database datasources.

Q: Is the ReportViewer control part of the .NET Framework?
A: No. The ReportViewer control is distributed with Visual Studio 2008, but it is not part of the .NET Framework.

Q: What editions of Visual Studio 2008 include ReportViewer?
A: ReportViewer is included in Standard and up. (i.e., all editions except Express.) An add-in is available for Visual Web Developer Express. (See below.)

Q8: What is the difference between RDL and RDLC formats?
A: RDL files are created by the SQL Server 2005 version of Report Designer. RDLC files are created by the Visual Studio 2008 version of Report Designer.
RDL and RDLC formats have the same XML schema. However, in RDLC files, some values (such as query text) are allowed to be empty, which means that they are not immediately ready to be published to a Report Server. The missing values can be entered by opening the RDLC file using the SQL Server 2005 version of Report Designer. (You have to rename .rdlc to .rdl first.)
RDL files are fully compatible with the ReportViewer control runtime. However, RDL files do not contain some information that the design-time of the ReportViewer control depends on for automatically generating data-binding code. By manually binding data, RDL files can be used in the ReportViewer control.
Note that the ReportViewer control does not contain any logic for connecting to databases or executing queries. By separating out such logic, the ReportViewer has been made compatible with all data sources, including non-database data sources. However this means that when an RDL file is used by the ReportViewer control, the SQL related information in the RDL file is simply ignored by the control. It is the host application's responsibility to connect to databases, execute queries and supply data to the ReportViewer control in the form of ADO.NET DataTables.

Q: What does the C in RDLC stand for?
A: The C stands for Client-side processing. RDL stands for Report Definition Language.

Q10: Why doesn't the ReportViewer control support parameter prompting in local mode?
A: The ReportViewer control does not prompt for parameters when in local mode. It prompts for parameters when it is connected to a Report Server.
In local mode it does not make sense for ReportViewer to prompt for parameters. The rationale is as follows: The most common use of report parameters is to pass to queries as values of query parameters. But unlike the Report Server, the ReportViewer control does not execute queries itself. Rather, queries are executed by the host application, and the result is passed to the ReportViewer control. So the ReportViewer control does not have the opportunity to set query parameters. Applications should take advantage of the parameterization features of Visual Studio data wizards instead.

Q: I installed Visual Studio 2008 and started Report Designer. I can't find the Preview tab. Where is the Preview tab that existed in SQL Server 2000 Report Designer?
A: There are two editions of Report Designer. One comes in the Visual Studio 2008 box. The other comes in the SQL Server 2005 box. If you are using the Report Designer included in SQL Server 2005, the Preview tab is still available. If you are using the Report Designer included in Visual Studio 2008, you have to run your application in order to preview the report.
Why is the Preview feature missing in the VS 2008 edition of Report Designer? Because the ReportViewer control does not contain any logic for connecting to databases or executing queries. Since it is your application's responsibility to supply data—and your application can supply data that originates anywhere, including objects that are internal to your application—data for previewing the report cannot be obtained without running your application.

Q: Why can't I just hand an RDL file to the report control and have it do everything and show me the report?
A: When the ReportViewer control is used in remote processing mode you can simply supply the url of the Report Server and the path to the report, and ReportViewer control will do the rest.
In local processing mode, the ReportViewer control is designed to integrate well with the host application. That includes being able to generate reports based on business objects that are internal to your application.
The ReportViewer control does not contain any logic for connecting to databases or executing queries. By separating out such logic, the ReportViewer has been made compatible with all kinds of data sources, including non-database data sources.
Rather than building in the logic to connect to databases and execute queries, ReportViewer is designed to integrate well with Visual Studio data wizards and the classes they generate, such as the TableAdapter class that knows how to connect to data sources, execute queries and fetch data.

Q: I dragged and dropped a field from the Data Sources window into the report. When I run the application the report only shows one row of data. How can I display all rows?
A: To see all rows, first drag and drop a Table or a List to the report, then drag and drop the field into the Table or List.

Q14: I am using the WebForms ReportViewer control. ReportViewer displays the message "Report is being generated" but the report is not displayed when this progress message goes away.
A: If you have set ReportViewer's height to a percentage, then in the .aspx file delete the line that sets doctype to xhtml. Why?

Q15: Does xls export require Excel to be installed?
A: No. The xls export feature is independent of Excel. Excel is only required to view the xls file.

Q16: Does PDF export require an Adobe license?
A: No. The ReportViewer control does not use any Adobe code to generate PDF files. PDF is an open format, and anyone can create PDF files without requiring a special license.

Q: What are the limitations of ReportViewer control?
A: Unlike the Report Server the ReportViewer control does not connect to databases or execute queries. Also, in local mode the only export formats available are Excel and PDF. (In remote mode all formats supported by the Report Server are available.) The ReportViewer control cannot be extended by adding custom renderers or custom report items.
Besides additional export formats, the Report Server offers better scalability, central storage, management and access control of reports, caching, subscriptions, etc.

Q: How can I learn more about Reporting Services?
A: Plenty of books and training are available.

Reference: http://www.gotreportviewer.com/

How to Install CRM Adapter for BizTalk Server 2009

2009-08-24T09:23:00.000-07:00

Installing CRM for BizTalk Server 2006 is quite straight forward. The CRM adapter for BizTalk requires that BizTalk 2006 be installed, but this is not entirely correct.

What i realized while installing CRM adapter for BizTalk 2009, it is just checking if Biztalk 2006 installed on the system. So i started digging about and discoved that CRM installation is simply looking for the registry key thats says BTS 2K6 is installed. So here is a small step in order to get the adapter to install on BTS 2009.

Your action items before installing CRM adapter for BizTalk 2009:

Before installing the CRM Biztalk adapter, make a backup of the registry (good practice) and then modify the following key, then install the adapter. After the adapter is installed, set the key back to its original value.

For 64 bit machine:

Start run >> type regedit then hit enter, navigate to

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\BizTalk Server\3.0

change ProductName = "Microsoft BizTalk Server 2009" to ProductName = "Microsoft BizTalk Server 2006"

For 32 bit machine:

Start run >> type regedit then hit enter, navigate to

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server\3.0

change ProductName = "Microsoft BizTalk Server 2009" to ProductName = "Microsoft BizTalk Server 2006"

You are done, now install CRM adapter, once installation is done successfully make sure you revert back the registry prodcut name to its original value.

Stay Hungry Stay Foolish …. I think this speech just changed my life

2009-08-24T08:52:00.000-07:00

If you know you can love work, you're in the home stretch, and if you know what work you love, you're practically there.

People just listen this speech it has changed my entire life infact the most inspiring speech i ever heard.

Steve: "Stay Hungry. Stay Foolish" ...When I was young, there was an amazing publication called The Whole Earth Catalog, which was one of the bibles of my generation...It was sort of like Google in paperback form, 35 years before Google came along: it was idealistic, and overflowing with neat tools and great notions...(publisher) Stewart (Brand) and his team put out several issues of The Whole Earth Catalog, and then when it had run its course, they put out a final issue. It was the mid-1970s, and I was your age. On the back cover of their final issue was a photograph of an early morning country road, the kind you might find yourself hitchhiking on if you were so adventurous. Beneath it were the words: "Stay Hungry. Stay Foolish." It was their farewell message as they signed off...And I have always wished that for myself. And now, as you graduate to begin anew, I wish that for you. Stay Hungry. Stay Foolish."

SQL Server Reporting Service (SSRS) – 401 Unauthorized Access Issue in Production Environment

2009-08-19T10:11:00.000-07:00

Let me share with you my experience on one of the issue i had with sql server reporting services in production environment.

Problem Description:
I am using sql server 2008 reporting services for reports in a .Net 3.5 application.

Production Environment:
Application Server: IM-APP1 (this is IIS machine and application is deployed here) has following windows server 2003, .Net 3.5, IIS 6.0

Database and Reporting Server: IM-SQL1 (This server has the database and sql reporting services), windows server 2003, .Net 3.5, SQL Server 2008, SQL 2008 Reporting Services

When i am browsing the application from desktop client, application works fine it fetches the data from database server, but when i navigate to a page, enter the report criteria and hit the button Generate Report then application throws 401-unauthorized access exception.

Now when i host database, reporting services and application on the same server (IM-APP1) then everything works fine.

below is the stack trace of the exception:

Exception Message : The request failed with HTTP status 401: Unauthorized.
Stack Trace : at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.IsSecureMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.SetConnectionSSLForMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.LoadReport(String Report, String HistoryID)
at Microsoft.Reporting.WebForms.ServerReport.GetExecutionInfo()
at Microsoft.Reporting.WebForms.ServerReport.SetParameters(IEnumerable`1 parameters)
at Pages_Reports_LogFormReports.DisplayReport(ReportViewer reportViewer, String reportName, ReportParameter[] parmList)

DisplayReport() is the method name which is responsible for generating the report and display it in report viewer object

I reviewed the DisplayReport() method and found that report server user credentials are not passed to the report server, so here what Application server does is when it calls the reports and if the report is hosted on the same server it doesn't need any credential for generating report so the report is working fine, now in other scenario where reports are hosted on different server then Application Server should pass report server user credential to generate the report.

Now the question is how to pass report server user credential from the application.

Here is the solution:

First We need to Create one sealed class to perform this action. This class need to be inherited from IReportServerCredential interface.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.Reporting.WebForms;
using Microsoft.ReportingServices;
using System.Security.Principal;
using RA.FRIMS.Settings;

/*****************************************************************************
* Created By : Nizam
* Created Date : Aug 17, 2009
* Description: This is sealed and serializable class and implementing IReportServerCredentials interface.
* This class Provides Network credentials to be used to connect to the report server.
* *****************************************************************************/

[Serializable]
public sealed class ReportServerNetworkCredentials : IReportServerCredentials
{
#region IReportServerCredentials Members
public bool GetFormsCredentials(out System.Net.Cookie authCookie, out string userName,
out string password, out string authority)
{
authCookie = null;
userName = null;
password = null;
authority = null;

return false;
}

// Specifies the user to impersonate when connecting to a report server.
//A WindowsIdentity object representing the user to impersonate.
public WindowsIdentity ImpersonationUser
{
get
{
return null;
}
}

// Returns network credentials to be used for authentication with the report server.
//A NetworkCredentials object.
public System.Net.ICredentials NetworkCredentials
{
get
{
//you can place below settings in configuration xml file
string userName = "ReportServerUserName";
string domainName = "ReportServerUserInDomainName";
string password = "ReportServerUserPassword";

return new System.Net.NetworkCredential(userName, password, domainName);
}
}

#endregion

}

Now all what you have to do in your display report method is:

//This method display reports in the report viewer object
private void DisplayReport(ReportViewer reportViewer, string reportName, ReportParameter[] parmList)
{
try
{
//For Reports with some input parameters.
reportViewer.ShowCredentialPrompts = false;
reportViewer.ShowParameterPrompts = false;
reportViewer.ShowDocumentMapButton = false;
reportViewer.ShowPageNavigationControls = true;
reportViewer.ShowFindControls = false;
reportViewer.ShowPrintButton = true;

reportViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;

// Here we are going to pass the ReportServerCredentials to the Report Viewer.
reportViewer.ServerReport.ReportServerCredentials = new ReportServerNetworkCredentials();

reportViewer.ServerReport.ReportServerUrl = "http:port//ReportServerMachineName/reportserver";
reportViewer.ServerReport.ReportPath = "ReportFolderName" + reportName;

reportViewer.ServerReport.SetParameters(parmList);
reportViewer.ServerReport.Refresh();

}
catch (Exception ex)
{
//handle exception
}
}

That's all you are done!!! Now run the report.

Please feel free to comment on this post. Critics are highly appreciated. :-)

401 Unauthorized access error when accessing web service (Which uses SharePoint-80 application pool and domain user identity) from client machine

2009-07-22T07:08:00.000-07:00

PROBLEM Description:

1. I have deployed web service, on the production server, web service that utilizes the MOSS objects / namespaces. In order that the service should be able to access the information i have deployed the service in the same application pool as SharePoint - 80 and provided the website folder with same permissions as the SharePoint site. The end result is that the information is accessible through the service on the local machine.

When the same methods are invoked from any other machine on the network we get the unauthorized 401 error.

If I change the application pool to the default application pool then the methods are accessible but not the information from MOSS as it is not within the same application pool.

The differences between the two application pools are the identities that are used for the services. Default utilizes the network service account while MOSS uses a custom service account.

We need to identify the error for the first scenario and be able to deploy without error the web service in the SharePoint application pool.

Environment:

Product: Visual Studio 2008, MOSS 2007

OS: Windows Server 2003

RESOLUTION:

I am providing you here the troubleshooting steps which I performed to resolve the issue.

[INFORMATIVE AND IMPORTANT]

We are having a web service which is consuming services from SharePoint also and running with application pool of SharePoint and identity of the domain user.
Everything works fine on the server.
But from client machine when we are trying to access the service, it is throwing 401 unauthorized errors.
We have set the identity of domain user (say ‘devportal’ user).
Now to get the actual issue, we enabled the auditing on the application server.
We enabled “Audit account logon events” and “Audit logon events” for failure.
Then ran the application again from client machine.
After that we saw failure audit in security event log and with event ID of 529,533.
This gave me an idea that this is a failure due to Kerberos.
So I started troubleshooting from that perspective.
I created a sample website (just an html page) and ran it same application pool and identity.
Here also I am able to reproduce the issue.
Then I made two changes on Active Directory machine:

a. For the server, set the “Trust this computer for delegation to any service (Kerberos only).”

b. For the user, set the “All computers”

I also set the NTLM authentication on IIS with the following command:

cscript adsutil.vbs set w3svc/NTAuthenticationProviders "NTLM"

After that everything worked fine.
But it was popping up the dialog box for authentication.
I ran the command:

cscript adsutil.vbs set w3svc/NTAuthenticationProviders "Negotiate,NTLM"

Also I configured the SPN with the following commands:

Setspn –A HTTP/NETBIOS_NAME_OF_IIS_SERVER domain\username

Setspn –A HTTP/FQDN_OF_IIS_SERVER domain\username

Now when I browse the web service from client machine then it works fine, problem is resolved -J

But problem still persist on the application server which deployed an application in IIS and that application is consuming this web service

Problem description:

Till now when I browse the web service from server machine or client machine then it works fine

1. I have an application [say DocSytems] deployed on another server [say APPSERVER]

2. DocSytems consumes the web service which is deployed on share point server

3. Now if I access the web service from DocSytems from APPSERVER server machine then it works fine

4. If I call the web service from DocSytems from other machine in the network and login with a domain user name then it doesn’t work

5. But interestingly if I impersonate the domain user (devportal) name which is set in the Identity of the SharePoint-80 app pool and DocSytems application then it works fine

Solution:

• I found that application server’s IIS was not set to Kerberos, i.e. with “Negotiate, NTLM”

• I set the IIS for “Negotiate, NTLM”

To set:

cscript adsutil.vbs set w3svc/WebSite/root/NTAuthenticationProviders "Negotiate,NTLM" ( In yellow color, you need the change it with site Id, in our case it was ‘1’).

To get:

cscript adsutil.vbs get w3svc/WebSite/root/NTAuthenticationProviders

• Then I also found that IE browser (of client) was not able to recognize the windows integrated authentication setting.

• In Internet explorer, I enabled the settings from Toolsà Internet Options à Advanced à Security à “Enabled Integrated Windows Authentication”

• Once I enabled the setting on client machine, everything works as expected.

So the problem is resolved

References on the issue which we resolved:

http://technet.microsoft.com/en-us/library/cc739740(WS.10).aspx

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

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

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

http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/d3df4bc9-0954-459a-b5e6-7a8bc462960c.mspx?mfr=true

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

http://technet.microsoft.com/hi-in/library/bb742516(en-us).aspx

Additional references:

For your future reference, I am mentioning here few articles related to IIS. Please note that these articles may or may not be related to the current case, but good pointers for keeping them in record book and future study.

1. This article talks about "How to configure applications in IIS 6.0" - http://support.microsoft.com/default.aspx?id=814867

2. This article talks about "How to configure security by using IIS 6.0" - http://support.microsoft.com/default.aspx?id=814874

3. This article talks about "How to performance-tune your applications in IIS 6.0" - http://support.microsoft.com/default.aspx?id=814876

4. This link talks about troubleshooting IIS - http://technet2.microsoft.com/windowsserver/en/technologies/featured/iis/default.mspx

5. IIS Authentication - http://msdn.microsoft.com/en-us/library/aa292114.aspx

Recommended 10 Skills for a good developer

2009-06-03T09:57:00.000-07:00

DO U DARE TO BE A DEVELOPER ??????????.............THINK TWICE........DO U KNOW LIFE OF A DEVELOPER????.........Ohh U Dare...I think you don't know life of a developer..............OK.........If you really decided to be a developer WHY NOT be a good Developer with good skill set and.........

Here is the Kick........

Web development? Pretty sure that's been established as important for the last 7+ years now. Today if you look at the market, there are so many technologies going on and you will find most of them are in demand.

SAP, Oracle, CRM, Share point, BizTalk Server…etc
On the other hand you have JAVA, PHP, .Net…..etc

Here I will talk about the core technology and supporting technologies.

1: One of “Big Three” (.NET, Java, PHP)

Choose any one of THREE, all are HOT/DEMANDING/BOOMING
Are you looking for plate form independent life, open source, then go for JAVA or PHP.
Radical shift in the development world (akin to an asteroid hitting Redmond), most developers will need to know at least one of the Big Three development systems — .NET (VB.NET or C#), Java, or PHP — for the near future. It’s not enough to know the core languages, either. As projects encompass more and more disparate functionality, you’ll need to know the associated frameworks and libraries more deeply.

2: Rich Internet Applications (RIAs) – THE WEB 2.0

Today you can count how many people don’t use internet but how many use you cannot. Today everyone wants things to be more personalized, users don’t have time to stay on your page, so how your application will interact with user, how you will represent yourself to the user, so that user can stay and spend a minute on your page. The Internet giants are more focusing on usability, Usability..Usability..Usability.

A lot of applications are there on the internet, and they have implemented mind blowing ideas, only few of them are getting the huge traffic and rest are just running apps, the reason is usability.

The solution is RIAs. Love it or hate it, in the last few years, Flash is suddenly being used for more than just animations of politicians singing goofy songs. Flash has also sprouted additional functionality in the form or Flex and AIR. Flash’s competitors, such as JavaFx and Silverlight, are also upping the ante on features and performance. To make things even more complicated, HTML 5 is incorporating all sorts of RIA functionality, including database connectivity, and putting the formal W3C stamp on AJAX. In the near future, being an RIA pro will be a key resume differentiators.

3: Web development

[JavaScript, JQuery, CSS, HTML, XML, XSLT]
Web development is not going away anytime soon. Many developers have been content to lay back and ignore the Web or to just stick to “the basics” their framework provides them with. But companies have been demanding more and more who really know how to work with the underlying technology at a “hand code” level. So bone up on JavaScript, CSS, and HTML to succeed over the next five years.

4: Web services

[Remoting >> RPC >> Web Services >> SOA]
REST or SOAP? JSON or XML? While the choices and the answers depend on the project, it’s getting increasingly difficult to be a developer (even one not writing Web applications) without consuming or creating a Web service. Even areas that used to be ODBC, COM, or RPC domains are now being transitioned to Web services of some variety. Developers who can’t work with Web services will find themselves relegated to legacy and maintenance roles.

5: Soft skills

Always hard for developers -:)
One trend that has been going for quite some time is the increasing visibility of IT within and outside the enterprise.

Developers are being brought into more and more non-development meetings and processes to provide feedback. For example, the CFO can’t change the accounting rules without working with IT to update the systems. And an operations manager can’t change a call center process without IT updating the CRM workflow. Likewise, customers often need to work directly with the development teams to make sure that their needs are met. Will every developer need to go to Toastmasters or study How to Win Friends and Influence People? No. But the developers who do will be much more valuable to their employers — and highly sought after in the job market.

6: One dynamic and/or functional programming language

Languages like Ruby, Python, F#, and Groovy still aren’t quite mainstream – but the ideas in them are. For example, the LINQ system in Microsoft’s .NET is a direct descendent of functional programming techniques. Both Ruby and Python are becoming hot in some sectors, thanks to the Rails framework and Silverlight, respectively. Learning one of these languages won’t just improve your resume, though; it will expand your horizons.

"Every top-flight developer I’ve met recommends learning at least one dynamic or functional programming language to learn new ways of thinking, and from personal experience, I can tell you that it works."

7: Agile methodologies

[Ideas changes, requirement changes, customer wants a real view on his/her idea, less time to market, so SDL changed and came the Agile Methodologies]

When Agile first time I heard this word, I was a skeptic, along with many other folks I know. It seemed to be some sort of knee-jerk reaction to tradition, throwing away the controls and standards in favor of anarchy. But as time went on, the ideas behind Agile became both better defined and better expressed. Many shops are either adopting Agile or running proof-of-concept experiments with Agile. While Agile is not the ultimate panacea for project failure, it does indeed have a place on many projects. Agile benefits both Vendors as well as customers, it involve customers in every stage of development.
Developers with a proven track record of understanding and succeeding in Agile environments will be in increasingly high demand over the next few years.

8: Domain knowledge

Hand-in-hand with Agile methodologies, development teams are increasingly being viewed as partners in the definition of projects. This means that developers who understand the problem domain are able to contribute to the project in a highly visible, valuable way. With Agile, a developer who can say, “From here, we can also add this functionality fairly easily, and it will get us a lot of value,” or “Gee, that requirement really doesn’t match the usage patterns our logs show” will excel.
As much as many developers resist the idea of having to know anything about the problem domain at all, it is undeniable that increasing numbers of organizations prefer (if not require) developers to at least understand the basics.

Domain knowledge allows you to add value by guiding the client and by proactively anticipating business.

9: Development “hygiene”

I remember the days when I started my career in Software Development; it was very hard for me to get my code sync with the other team member’s code. I was not aware of using any source repository, One day I developed some code and forgot to take backup, I thought coding is completed and then thought to optimize the code, I started with code optimization, finally I realized that my code is totally changed and it becomes buggy, then I thought to revert back to previous code but by that time I forgot what code I had written previously -:), at the end there was no option for me and again started coding from scratch, this is one scenario you can understand the importance of development hygiene.

A few years ago, many (if not most) shops did not have access to bug tracking systems, version control, and other such tools; it was just the developers and their IDE of choice. But thanks to the development of new, integrated stacks, like the Microsoft Visual Studio Team System, and the explosion in availability of high quality, open source environments, organizations without these tools are becoming much less common. Developers must know more than just how to check code in and out of source control or how to use the VM system to build test environments. They need to have a rigorous habit of hygiene in place to make sure that they are properly coordinating with their teams. “Code cowboys” who store everything on a personal USB drive, don’t document which changes correspond to which task item, and so on, are unwelcome in more traditional shops and even more unwelcome in Agile environments, which rely on a tight coordination between team members to operate.

10: Mobile development

The late 1990s saw Web development rise to mainstream acceptance and then begin to marginalize traditional desktop applications in many areas. In 2008, mobile development left the launch pad, and over the next five years, it will become increasingly important. There are, of course, different approaches to mobile development: Web applications designed to work on mobile devices, RIAs aimed at that market, and applications that run directly on the devices. Regardless of which of these paths you choose, adding mobile development to your skill set will ensure that you are in demand for the future.

!!!!!!!! Hey if you have all above skills , you are a good developer then....you can share your experience here by putting few lines of comments !!!!!!!!!!!

Get the key facts on a wide range of technologies, techniques, strategies, and skills with the help of the concise need-to-know, Please click here and keep abreast with upcoming technologies.

HAPPY CODING!!!!!!!!!!!!!!!!!!!!!