Create stored procedure if doesn't exists in sysobjects

if not exists(Select * from sysobjects where name = 'ON_TIME_DELIVERY_TELCON2006')

begin

EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE ON_TIME_DELIVERY_TELCON2006 AS

SELECT * FROM ACCOUNTS'

end

Convert Multiple Rows into single Row

Method 1:

DECLARE @str varchar(4000)
SET @str = (SELECT CONTACT_EMAIL + ';' FROM table FOR XML PATH(''))
SET @str = SUBSTRING(@str,1,LEN(@str)-1)
SELECT @str

Method 2:

DECLARE @str varchar(4000)
SELECT @str = COALESCE(@str + ';', '') + CONTACT_EMAIL FROM table
SELECT @str

Method 3:

DECLARE @str varchar(4000)
SELECT DISTINCT STUFF( (SELECT CONTACT_EMAIL + ';' from table FOR XML PATH('')),1,1,'')
SELECT @str

Automatically Create Install Scripts with Sql Server

Problem: How can I QUICKLY create an install script automatically in sql server every time I change my database? Creating an install script in sql server is pretty easy thanks to the "Script Database As..." command. However, this is generally a long winded process that doesn't give you too much control.

In this post, I propose a solution to create build scripts with the following requirements

1. Don't need to write any code
2. Easily maintainable
3. Can script schema AND data
4. Can specify which objects to script
5. Can run automatically before an MSI setup/deployment project is built
6. It has to be FREE

The first thing you need to do is download Microsoft's Database Publishing Wizard (http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en)

Next, in your development sql server, add two new accounts named: ScriptSchema and ScriptData. Make sure they are created as a sql login. At this point, don't give them any other access than public access to the database you want to script.

Open the database you want to script. For each table/view/stored proc etc that you want to include in the install, grant view definition access to the ScriptSchema user as follows:

grant view definition on .

Team Deployment

http://www.tfsbuild.com/Default.aspx?Page=TeamDeploy&AspxAutoDetectCookieSupport=1

Memory leak configuration

http://blogs.msdn.com/tess/archive/2009/05/12/debug-diag-script-for-troubleshooting-net-2-0-memory-leaks.aspx

http://msdn.microsoft.com/en-us/library/ms954593.aspx#dbgch04_scenariounexpected

http://msdn.microsoft.com/en-us/library/ms954591.aspx

http://www.red-gate.com/products/ants_memory_profiler/index.htm


http://support.microsoft.com/?id=911816


http://social.msdn.microsoft.com/Forums/en-US/netfxsetup/thread/ed3d8886-459f-4f13-8a8e-ec86b7f6f4a6


http://social.msdn.microsoft.com/forums/en-US/netfxbcl/thread/9fc91efe-5a5c-42da-a0b3-101752ce92f6/

http://social.msdn.microsoft.com/forums/en-US/netfxbcl/thread/297cc644-00ec-412f-a417-9e8ad1f070f7/

Sort DropdownList

Introduction

I found a big problem in ASP.NET is there was no direct method for sorting the items in the Dropdown list. Sometimes this sorting functionality is required in the applications.

Here I gave a small and easy technique of sorting the Dropdown list in ASP.NET
Using the code

I assumed that, you have a Dropdown list in your ASPX page with name "DropDownList1". I have added few items to this Dropdown list in the Page_Load event with disorder manner.
Collapse

protected void Page_Load(object sender, EventArgs e)
{
this.DropDownList1.Items.Add("Orange");
this.DropDownList1.Items.Add("Grapes");
this.DropDownList1.Items.Add("Apple");
this.DropDownList1.Items.Add("Mango");
this.DropDownList1.Items.Add("Lemon");
this.DropDownList1.Items.Add("Banana");

SortDDL(ref this.DropDownList1);
}

In the Page_Load event, I have called a function "SortDDL" for sorting the Dropdown list. In this function we have to pass the reference of the required Dropdown list. Then system will automatically sort the items in the given Dropdown list.
Collapse

private void SortDDL(ref DropDownList objDDL)
{
ArrayList textList = new ArrayList();
ArrayList valueList = new ArrayList();


foreach (ListItem li in objDDL.Items)
{
textList.Add(li.Text);
}

textList.Sort();


foreach (object item in textList)
{
string value = objDDL.Items.FindByText(item.ToString()).Value;
valueList.Add(value);
}
objDDL.Items.Clear();

for(int i = 0; i < textList.Count; i++)
{
ListItem objItem = new ListItem(textList[i].ToString(), valueList[i].ToString());
objDDL.Items.Add(objItem);
}
}

We should need to import the following namespace for getting ArrayList class.