How to write some text in file from Sql Server

alter PROCEDURE spWriteStringToFile
(
@String Varchar(max), --8000 in SQL Server 2000
@Path VARCHAR(255),
@Filename VARCHAR(100)

--
)
AS
DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@fileAndPath varchar(80)

set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT

Select @FileAndPath=@path+'\'+@filename
if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,2,True

if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream


for more information please CLICK HERE

How to read a file from sql server

Create FUNCTION [dbo].[uftReadfileAsTable]
(
@Path VARCHAR(255),
@Filename VARCHAR(100)
)
RETURNS
@File TABLE
(
[LineNo] int identity(1,1),
line varchar(8000))

AS
BEGIN

DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@String VARCHAR(8000),
@YesOrNo INT

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT


if @HR=0 Select @objErrorObject=@objFileSystem, @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',@command=@path+'\'+@filename

if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'OpenTextFile'
, @objTextStream OUT, @command,1,false,0--for reading, FormatASCII

WHILE @hr=0
BEGIN
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='finding out if there is more to read in "'+@filename+'"'
if @HR=0 execute @hr = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT

IF @YesOrNo<>0 break
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='reading from the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Readline', @String OUTPUT
INSERT INTO @file(line) SELECT @String
END

if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='closing the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'


if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
insert into @File(line) select @strErrorMessage
end
EXECUTE sp_OADestroy @objTextStream
-- Fill the table variable with the rows for your result set

RETURN
END

Parallel Tasks in .NET 4.0 – Methods that Return value

For methods that return a value, you would need to use the Task(TResult) class which represents an asynchronous operation that can return a value. We will explore how to use this class in this article.

For Example:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Threading;
using System.Threading.Tasks;

namespace URLRoutingDemoApp.Parallel_For
{
public partial class ParallelInvokeWithReturnValue : System.Web.UI.Page
{
static string stValues = string.Empty;
string strTaskResult = string.Empty;
string strTaskResult1 = string.Empty;
protected void Page_Load(object sender, EventArgs e)
{
stValues = "";
strTaskResult = "";
strTaskResult1 = "";
//Two Approaches
//Approach 1-> First Initialize the task and then execute
Task Task Task
Task1.Start();
Task2.Start();
Task3.Start();

strTaskResult += "Task1=" + Task1.Result.ToString() + "</br>";
strTaskResult += "Task1=" + Task2.Result.ToString() + "</br>";
strTaskResult += "Task1=" + Task3.Result.ToString() + "</br>";

lblparallelInvokewithreturnvalue.Text = stValues + "::::</br>" + strTaskResult;

//Approach 2-> Initialize and execute task at the same time
stValues = "";
var TaskApp1=Task var TaskApp2 = Task var TaskApp3 = Task
strTaskResult1 += "Task1=" + TaskApp1.Result.ToString() + "</br>";
strTaskResult1 += "Task1=" + TaskApp2.Result.ToString() + "</br>";
strTaskResult1 += "Task1=" + TaskApp3.Result.ToString() + "</br>";

lblparallelInvokewithreturnvalue1.Text = stValues + "::::</br>" + strTaskResult1;
}

static int GenerateNumbers()
{
int i;
for (i = 0; i < 10; i++)
{
stValues += "Method1 - Number " + i.ToString() + " </br>";
Thread.Sleep(1000);
}
return i;
}

static string PrintCharacters()
{
string str = "blog";
for (int i = 0; i < str.Length; i++)
{
stValues += "Method2 - Character " + str[i].ToString() + " </br>";
Thread.Sleep(1000);
}
return str;
}

static int PrintArray()
{
int[] arr = { 1, 2, 3, 4, 5, 6, 7, 8 };
foreach (int i in arr)
{
stValues += "Method3 - Array " + i.ToString() + " </br>";
Thread.Sleep(1000);
}
return arr.Count();
}
}
}

Used the Task(TResult) class to call methods that returned a value.

Parallel Tasks in .NET 4.0

In .NET 4.0, we have a set of new API's to simplify the process of adding parallelism and concurrency to applications. This set of API's is called the "Task Parallel Library (TPL)" and is located in the System.Threading and System.Threading.Tasks namespaces.

For Example:
using System.Threading;
using System.Threading.Tasks;

public partial class ParallelInvoke : System.Web.UI.Page
{
static string stValues = string.Empty;

protected void Page_Load(object sender, EventArgs e)
{
stValues = "";
Parallel.Invoke(new Action(GenerateNumbers), new Action(PrintCharacters), new Action(PrintArray));
parallelInvokelbl.Text = stValues;
}

static void GenerateNumbers()
{
for (int i = 0; i < 10; i++)
{
stValues += "Method1 - Number " + i.ToString() + "
";
Thread.Sleep(1000);
}
}

static void PrintCharacters()
{
string str = "blog";
for (int i = 0; i < str.Length; i++)
{
stValues += "Method2 - Character " + str[i].ToString() + "
";
Thread.Sleep(1000);
}

}

static void PrintArray()
{
int[] arr = { 1, 2, 3, 4, 5, 6, 7, 8 };
foreach (int i in arr)
{
stValues += "Method3 - Array " + i.ToString() + "
";
Thread.Sleep(1000);
}
}
}

What is the difference between For(C#) and Parallel.For(.NE 4.0) Loop.

The difference is that with the C# for statement, the loop is run from a single thread. However the Parallel class uses multiple threads. Moreover the order of the iteration in the parallel version is not necessarily in order.

Let us see an example:
1) Add one web page with Name "ParallelForVSFor"
2) Add two label controls on the page "ParallelForVSFor.aspx" file

<body>
<form id="form1" runat="server">
<div>
<table width="100%">
<tr>
<td><asp:Label ID="parallelfor" runat="server" Text=""></asp:Label></td>
<td><asp:Label ID="csharpfor" runat="server" Text=""></asp:Label></td>
</tr>
</table>
</div>
</form>
</body>

3) Add the following code in "ParallelForVSFor.aspx.cs" file
using System;
using System.Threading;
using System.Threading.Tasks;

protected void Page_Load(object sender, EventArgs e)
protected void Page_Load(object sender, EventArgs e)
{
parallelfor.Text += "Using Parallel.For \n";
Parallel.For(0,10,i =>
{
parallelfor.Text += "i="+i.ToString()+"\n";
Thread.Sleep(10);
});

csharpfor.Text += "Using C# For Loop \n";
for (int i = 0; i < 10; i++)
{
csharpfor.Text += "i=" + i.ToString() + "\n";
Thread.Sleep(10);
}
}

As you can see, the Parallel.For method is defined as Parallel.For Method (Int32, Int32, Action(Of Int32)). Here the first param is the start index (inclusive), the second param is the end index (exclusive) and the third param is the Action delegate that is invoked once per iteration. Here’s the output of running the code:

Using Parallel.For i=0 i=5 i=1 i=6 i=2 i=7 i=3 i=8 i=4 i=9
Using C# For Loop i=0 i=1 i=2 i=3 i=4 i=5 i=6 i=7 i=8 i=9

As you can see, with the C# for loop statement, the results are printed sequentially and the loop is run from a single thread. However with the Parallel.For method uses multiple threads and the order of the iteration is not in order.

The Parallel.For() construct is useful if you have a set of data that is to be processed independently. The construct splits the task over multiple processor.

SqlCacheDependency

following Steps those are required are given below:

Prepare the database:
In order to use the SqlCacheDependency class, the database has to be configured to use the service broker (notification services).
First check if the service broker is enabled:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'Northwind'

When this query returns 1 the broker is enabled and we are done with SQL Server.
If not, enable the broker by using this alter table statement:

ALTER DATABASE Northwind SET ENABLE_BROKER

Two problems I have encountered using this statement:
1. It keeps running.
2. It returns error 9772: The Service Broker in database 'Northwind' cannot be enabled because there is already an enabled Service Broker with the same ID.

This problems can be solved by using resp.:
1. Change the statement to ALTER DATABASE Northwind SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
2. First execute statement ALTER DATABASE Northwind SET NEW_BROKER and then again try to enable the broker.

Prepare the web application:
In the Global.asax file add the following statement to the Application_Start subroutine:
System.Data.SqlClient.SqlDependency.Start("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True")
And allmost the same statement in Application_Stop:
System.Data.SqlClient.SqlDependency.Stop("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True")

Using the Cache:
1) in Global.asax file under Application_Start method
SqlDependency.Start(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
2) in Global.asax file under Application_Stop method
SqlDependency.Stop(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
3)Code

DataSet objDS = new DataSet();

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
SqlCommand cmd = new SqlCommand("select top 100 ID,CRN_ID from C1", con);
cmd.Notification = null;
cmd.NotificationAutoEnlist = true;
SqlCacheDependencyAdmin.EnableNotifications(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
if(!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString).Contains("C1"))
{ SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString, "C1");
}

SqlCacheDependency dependency = new SqlCacheDependency(cmd);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(objDS);

_cache.Insert("C1", objDS, dependency);


for more information please visit:
http://www.dotnetcurry.com/ShowArticle.aspx?ID=263&AspxAutoDetectCookieSupport=1

.NET,ASP.NET: Application level data caching (File dependency) with callbacks

state management(cache management)

Now let's discuss different aspects of cache management (or state management) in ASP.NET.

Although cache management is not an issue in Windows applications, it has always been a challenge in the web environment. Since HTTP is a stateless protocol and a web server doesn't recognize users between different requests, it becomes very important for us to recognize a particular user between different requests and also store data so that it can be re-used between different requests. ASP.NET provides many features for storing data both in the client (browser) and the server (web server) sides, but sometimes we get confused with when to use what. In ASP.NET we come across features like Session, Application and Cache objects, but it is important for us to understand the difference between them in order to effectively use them in web applications.

Caching helps us to achieve three important aspects of QoS (Quality Of Service):

•Performance - Caching improves application performance by minimizing data retrieval and formatting operations.
•Scalability - Since caching minimizes data retrieval and formatting operations, it reduces the load on server resources thus increasing the scalability of the application.
•Availability - Since the application uses data from a cache, the application will survive failures in other systems and databases.

Server side cache management

ASP.NET session state is used to cache data per user session. It means that the data cannot be shared across multiple users and the data usage is limited to the user session it is created in. ASP.NET Session object is used for this purpose.

ASP.NET session state can be managed in three different ways:

•InProc - Stored in the aspnet_wp.exe process area. The session data is lost when the process or the application domain is recycled.
•StateServer - Session state is stored in a separate process (aspnet_state.exe) which can be stored in a different machine. Since it can be stored in a different machine, this option will work in a web farm scenario.
•SQLServer - Session state is stored in a SQL Server database. This option will also work in a web farm scenario.

ASP.NET application object
ASP.NET provides an object called Application object to store data that is accessible to all user requests. The life span of this object is tied to the application and it is re-created every time the application starts. Unlike ASP.NET Session object this object is accessible to all user requests. Since this storage is created and maintained in an application domain space, this should not be used for data storage in a web farm scenario. This option is very useful to store data like the application metadata (CONFIG files data) that can be loaded to the Application object during application start up and can be used during the life of the application without reloading it every time for each user request. But if there is a requirement to invalidate the cached data whenever there is any change to the CONFIG files while the application is running, this option should not be used as it doesn't provide any feature to expire the cached data. So in this case other options like the ASP.NET Cache object should be used, which is explained below.

ASP.NET cache object
ASP.NET provides a key-value pair object - the Cache object which is available in the System.Web.Caching namespace. The scope of it is the application domain and the life span is tied to the application. Unlike the ASP.NET Session object, this object is accessible to all user requests.

Although both Application and Cache objects look the same, the key difference between them is the added features provided by the Cache object like the expiration policies and dependencies. It means that the data stored in the cache object can be expired/removed based on some predefined time limit set by the application code or when the dependent entity gets changed whereas this feature is not available in the Application object.

Let us discuss the different expiration policies and the dependencies that are supported.

Dependency
Dependency means that an item can be removed from the cache when a dependent entity gets changed. So a dependent relationship can be defined on an item whose removal from the cache will depend on the dependent. There are two types of dependencies supported in ASP.NET.

•File dependency - This provides an option to remove an item automatically from the cache whenever a disk file changes. Let's say in my application I am using an XML file to store error details (error number and error message mapping) which is used to retrieve an error message for a given error number at runtime. So instead of reading the file from the disk each time I need an error message, let's say I decide to load it once at application startup and store it in the ASP.NET cache for further use. So, if I need to change the CONFIG file to add new error messages or change some of the existing error messages while the application is running, then what will happen to my cached data? Do I need to stop the application and start it again to reflect the file changes in the application? The answer is no. The cache data can be invalidated whenever the file changes by using the File dependency option.

Example: in Global.asax file
void Application_Start(object sender, EventArgs e)
{
// Code that runs on application startup
//Common.ServiceManger.constant.LoadConstants();
_cache = HttpContext.Current.Cache;
FillConstantCache();
}

private void FillConstantCache()
{
//if (errorconstant == null)
//{
DataSet objDS = new DataSet();
string constantFile = string.Empty;
constantFile = "D:/DOTNETPROJECTS/TFSPROJECTS53/Test/AppConstants.xml";
CacheDependency dependency = new CacheDependency(constantFile);
objDS.ReadXml(constantFile);

//SqlConnection con = new SqlConnection("connectionString");
//SqlCommand cmd = new SqlCommand("select key,value from table", con);
//SqlCacheDependency dependency = new SqlCacheDependency(cmd);
//SqlDataAdapter da = new SqlDataAdapter(cmd);
//da.Fill(objDS);

errorconstant = new Hashtable();
foreach (DataRow dr in objDS.Tables[0].Rows)
{
if (!errorconstant.Contains(dr[0].ToString()))
errorconstant.Add(dr[0].ToString(), dr[1].ToString());
}
_cache.Insert("appconstant", objDS, dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Default, new CacheItemRemovedCallback(this.CacheItemRemoved));
//}

}

//this method used for callBack when you modified an AppConstants.xml
private void CacheItemRemoved(string key, object val, CacheItemRemovedReason reason)
{
FillConstantCache();
}

You call this from any web page like that:
lblname.Text = Global.errorconstant["keyname"].ToString();

for more information visit the following links:
http://weblogs.asp.net/kwarren/archive/2004/05/20/136129.aspx
http://www.codeproject.com/KB/web-cache/cachemanagementinaspnet.aspx

Difference between Implicit and Explicit Interface

There are 4 basic difference between Implicit and Explicit interface. To understand all four of them see the below example first:

There is a class C1 which implements two interfaces I1 and I2. and Both the class have method with same name i.e. sum().

public class C1 : I1, I2
{
//Explicit Implementation of interface
void I2.sum()
{
}

//Implicit Implementation of interface
public void sum()
{
}
}

public interface I1
{
void sum();
}
public interface I2
{
void sum();
}

The Interface I1 is implemented implicitly and I2 is explicitly. Below are the difference based on above example.

1) In Implicit implementation, no need to specify Interface name with its method.
e.g. public void sum() { }
Where as in Explicit implementation, you need to specify Interface name with its method
e.g. void I2.sum() { }

2) In implicit, access specifier is required with method name and it must be public, even protected will not work.
Where as in Explicit, no access specifier will be required.

3) You can not make virtual method in explicit implemention and same will work in implicit implemention.

4) You can not make abstract method in explicit implemention and same will work in implicit implemention.

Practical Example

Now you wonder, by considering above scenario, what is the use of these two implementation because only Implicit implementation will also work for all type of method then why these two has been introduced by Microsoft. or what should be the practical scenario where we need to use either Implicit or Explicit.

Suppose you need to implement 2 interface and both interface will have method with same name. At that time you can not create one method in your class for both interfaces, rather you must implement one interface Implicit and one Explicit (or both Explicit) for showing two different method in your class. (Check above example).

Hiding Interface Details:

If you implement interface explicitly then it will not show in class Intellisense list. So, if you want to hide your method name to display when creating object of class then you need to go for Explicit interface implementation and in this case you need to create object of your interface to know the method name.

E.g.: In our above example if I want to invoke method of I2 interface:
I2 obj = new C1();
obj.sum();

NOTE: for more please click http://developerssolutions.wordpress.com/2010/07/22/difference-between-implicit-and-explicit-interface/

Is it possible to declare multiple Interfaces with same name and method?

Yes, You can have same name for all the interfaces if you declare with partial keyword.

But they can have same method but with different signature.

partial interface AAA
{
void TestAAA();
}

partial interface AAA
{
string TestAAA(parameter);
}

Implementing Two interface having the same method signature in the same class

There can be scenario when we would have two interface with the same method name and same type of arguments, same number of arguments and even the return type can be same and we need to implement both the interface in the same class. How to implement the interface in our class? Most of you will be thinking whats so tough in this, just implement the interface and the method and move on. If the signature of the methods in the interface were different then there would have been no problem but here the signature of the methods in two different interface are same and both the interfaces are gonna be implemented in the same class. The two interface are as below.
public interface IA
{
string PrintName();
}
public interface IB
{
string PrintName();
}
From the above code we can infer that we have two interface with names IA and IB and both have a single method named “PrintName”. The signature of both the methods are same and we need to implement the interfaces in our class say “A”. One way of impelementing the interface is as shown below i.e. just having a “public” implementation of the interface method only once.
public class A : IA, IB
{
public A()
{
}
public string PrintName()
{
return this.GetType().Name;
}
}
The above implementation has got a limitation i.e the method “PrintName” is considered to be a common method for all i.e commong method for the class, and for the interfaces IA and IB. If you are writing a code shown below
A a = new A();
IA ia = new A();
IB ib = new A();
Console.WriteLine(a.PrintName());
Console.WriteLine(ia.PrintName());
Console.WriteLine(ib.PrintName());
all the calls to method “PrintName” will give you the same result, the name of the class i.e. “A”. This is because all call to the method goes to the same definition. Now if you want to give different implementation to the methods in interface IA and IB, what will you do? Its also simple, just have two impelementation of the same method and prefix the method names with the interface name as shown below.
public class A : IA, IB
{
public A()
{
}
string IA.PrintName()
{
return “IA PrintName Method”;
}
string IB.PrintName()
{
return “IB PrintName Method”;
}
}
Now the below code will give you different output.
IA ia = new A();
IB ib = new A();
Console.WriteLine(ia.PrintName());// Will print "IA PrintName Method"
Console.WriteLine(ib.PrintName());// Will print "IB PrintName Method"
So this is how two interfaces having the same method signature can be given different implementation in the same class.

For more information please visit : http://sandblogaspnet.blogspot.com/2008/05/implementing-two-interface-having-same.html

Convert a Dynamic Entity to a System Entity

This sample code shows how to convert a dynamic entity instance into a strongly typed account entity instance. The code first creates a dynamic entity, populates its properties with account attributes and values, and then converts the dynamic entity to an account entity.
using System;
using System.Reflection;

using CrmSdk;
using Microsoft.Crm.Sdk.Utility;

namespace Microsoft.Crm.Sdk.HowTo
{
public class ConvertDynamicToCore
{
static void Main(string[] args)
{
// TODO: Change the server URL and organization to match your Microsoft
// Dynamics CRM Server and Microsoft Dynamics CRM organization.
ConvertDynamicToCore.Run("http://localhost:5555", "CRM_SDK");
}

public static bool Run(string crmServerUrl, string orgName)
{
// Create an account dynamic entity.
DynamicEntity accountDynamicEntity = new DynamicEntity();

//Set a few account properties.
StringProperty name = new StringProperty();
name.Name = "name";
name.Value = "Fabrikam Inc.";

StringProperty accountnumber = new StringProperty();
accountnumber.Name = "accountnumber";
accountnumber.Value = "AZ1200";

Picklist plist = new Picklist();
plist.name = "UPS";
plist.Value = 2;

PicklistProperty shippingmethodcode = new PicklistProperty();
shippingmethodcode.Name = "address1_shippingmethodcode";
shippingmethodcode.Value = plist;

accountDynamicEntity.Properties = new Property[] { name, accountnumber, shippingmethodcode };
accountDynamicEntity.Name = EntityName.account.ToString();

//Create a strongly typed account entity to copy the dynamic entity into.
account coreAccount = new account();

//Convert the dynamic entity to a strongly typed business entity.
coreAccount = (account)Convert(accountDynamicEntity);

Console.WriteLine("\n|Core Account Attributes\n|=======================");
Console.WriteLine("|name: {0}", coreAccount.name);
Console.WriteLine("|accountnumber: {0}", coreAccount.accountnumber);
Console.WriteLine("|address1_shipmethodcode: {0}", coreAccount.address1_shippingmethodcode.Value);

return true;
}

///

/// Convert a dynamic entity into a strongly typed business entity.
///

public static BusinessEntity Convert(DynamicEntity entity)
{
string coreEntityName = entity.Name;

Type entType = (new account()).GetType();
ConstructorInfo init = entType.GetConstructor(new Type[] { });
object ent = init.Invoke(new object[] { });

foreach (Property p in entity.Properties)
{
PropertyInfo entProp = entType.GetProperty(p.Name);
if (null == entProp)
{
Console.WriteLine("Could not find attribute {0} on entity {1}.", p.Name, coreEntityName);
}
else
{
entProp.SetValue(ent, GetAttribute(entity, p.Name), null);
}
}
return (BusinessEntity)ent;
}

///

/// This method returns the value of a dynamic entity attribute.
///

public static object GetAttribute(BusinessEntity entity, string attribute)
{
if (entity.GetType() == typeof(DynamicEntity))
{
DynamicEntity de = (DynamicEntity)entity;
foreach (Property prop in de.Properties)
{
if (prop.Name == attribute)
{
PropertyInfo propInfo = prop.GetType().GetProperty("Value");
return propInfo.GetValue(prop, null);
}
}
return null;
}
else
{
PropertyInfo propInfo = entity.GetType().GetProperty(attribute);
return propInfo.GetValue(entity, null);
}
}
}
}

PublishXml Message (CrmService)

Publish the customizations for the specified entities.
The relevant classes are: 1) PublishXmlRequest 2) PublishXmlResponse
Remarks
To use this message, pass an instance of the PublishXmlRequest class as the request parameter in the Execute method.
For a list of required privileges, see PublishXml Privileges.
Example
The following code example shows how to use the PublishXml message.
// Set up the CRM service.
CrmAuthenticationToken token = new CrmAuthenticationToken();
// You can use enums.cs from the SDK\Helpers folder to get the enumeration for Active Directory authentication.
token.AuthenticationType = 0;
token.OrganizationName = "AdventureWorksCycle";

CrmService service = new CrmService();
service.Url = "http://<servername>:<port>/mscrmservices/2007/crmservice.asmx";
service.CrmAuthenticationTokenValue = token;
service.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Create the request.
PublishXmlRequest request = new PublishXmlRequest();

request.ParameterXml = @"<importexportxml>
<entities>
<entity>account</entity>
<entity>contact</entity>
</entities>
<nodes/>
<securityroles/>
<settings/>
<workflows/>
</importexportxml>";

// Execute the request.
PublishXmlResponse response = (PublishXmlResponse)service.Execute(request);

Create Custom Entity Message (MetadataService)

The relevant classes: 1) CreateEntityRequest 2) CreateEntityResponse

Remarks
To perform this action, the caller must be a user in the organization for which metadata is requested and must haveCreate Entity and Read Entity privileges.

The entity created will have a property value of true for the EntityMetadata.IsCustomEntity property.

You must publish the changes to the metadata before this change will be visible in the application. For more information see Publishing the Metadata.

Example with Source Code:

// Create an authentication token.
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.OrganizationName = "AdventureWorksCycle";

// You can use enums.cs from the SDK\Helpers folder to get the enumeration for Active Directory authentication.
token.AuthenticationType = 0;

// Create the metadata Web service.
MetadataService metadataService = new MetadataService();
metadataService.Url = "http://<servername>:<port>/MSCRMServices/2007/MetadataService.asmx";
metadataService.CrmAuthenticationTokenValue = token;
metadataService.Credentials = System.Net.CredentialCache.DefaultCredentials;
metadataService.PreAuthenticate = true;

// Create the entity.
EntityMetadata timesheetEntity = new EntityMetadata();
timesheetEntity.SchemaName = "new_timesheet";
timesheetEntity.OwnershipType = new CrmOwnershipTypes();
timesheetEntity.OwnershipType.Value = OwnershipTypes.UserOwned;
timesheetEntity.DisplayName = Microsoft.Crm.Sdk.Utility.CrmServiceUtility.CreateSingleLabel("Timesheet", 1033);
timesheetEntity.DisplayCollectionName = Microsoft.Crm.Sdk.Utility.CrmServiceUtility.CreateSingleLabel("Timesheets", 1033);
timesheetEntity.Description = Microsoft.Crm.Sdk.Utility.CrmServiceUtility.CreateSingleLabel("Employee Timesheet", 1033);

// Create the primary attribute.
StringAttributeMetadata primaryAttribute = new StringAttributeMetadata();
primaryAttribute.SchemaName = "new_name";
primaryAttribute.RequiredLevel = new CrmAttributeRequiredLevel();
primaryAttribute.RequiredLevel.Value = AttributeRequiredLevel.None;
primaryAttribute.MaxLength = new MetadataServiceSdk.CrmNumber();
primaryAttribute.MaxLength.Value = 100;
primaryAttribute.DisplayName = Microsoft.Crm.Sdk.Utility.CrmServiceUtility.CreateSingleLabel("Name", 1033);
primaryAttribute.Description = Microsoft.Crm.Sdk.Utility.CrmServiceUtility.CreateSingleLabel("Employee Name", 1033);

// Create the entity request.
CreateEntityRequest createEntity = new CreateEntityRequest();
createEntity.Entity = timesheetEntity;
createEntity.HasActivities = false;
createEntity.HasNotes = true;
createEntity.PrimaryAttribute = primaryAttribute;

// Execute the request.
CreateEntityResponse entityResponse = (CreateEntityResponse )metadataService.Execute(createEntity);

// Publish the new custom entity.
// Note that the custom entities cannot be used until they have been published.
PublishAllXmlRequest publishAllRequest = new PublishAllXmlRequest();
PublishAllXmlResponse publishAllResponse = (PublishAllXmlResponse)metadataService.Execute(publishAllRequest);

Maximum Number of Index per Table in SQL SERVER

SQL Server 2005:
1 Clustered Index + 249 Nonclustered Index = 250 Index

SQL Server 2008:
1 Clustered Index + 999 Nonclustered Index = 1000 Index

Temporary Tables vs. Table Variables

There are three major theoretical differences between temporary tables:

create table #T (…)

And table variables:

declare @T table (…)

The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism, as is clearly visible from this example:

create table #T (s varchar(128))
declare @T table (s varchar(128))
insert into #T select 'old value #'
insert into @T select 'old value @'
begin transaction
update #T set s='new value #'
update @T set s='new value @'
rollback transaction
select * from #T
select * from @T

s
---------------
old value #

s
---------------
new value @

After declaring our temporary table #T and our table-variable @T, we assign each one with the same "old value" string. Then, we begin a transaction that updates their contents. At this point, both will now contain the same "new value" string. But when we rollback the transaction, as you can see, the table-variable @T retained its value instead of reverting back to the "old value" string. This happened because, even though the table-variable was updated within the transaction, it is not a part of the transaction itself.

The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

Finally, table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement.

For more Details please Visit:http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

Temporary tables in SQL Server

Introduction

Temporary tables are a useful tool in SQL Server provided to allow for short term use of data. There are two types of temporary table in SQL Server, local and global.

Local temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed. Global temporary tables are available to any connection once created, and are dropped when the last connection using it is closed.

Both types of temporary tables are created in the system database tempdb.

Creating Temporary Tables

Temporary tables can be created like any table in SQL Server with a CREATE TABLE or SELECT..INTO statement. To make the table a local temporary table, you simply prefix the name with a (#). To make the table a global temporary table, prefix it with (##).

Example
-- Create a local temporary table
CREATE TABLE #LocalTempTable
(
Field1 INT,
Field2 VARCHAR(20)
)

CREATE TABLE ##GlobalTempTable
(
Field1 INT,
Field2 VARCHAR(20)
)
* you can do all the DML operation on Temporary table.

Derived Table in SQL SERVER

Introduction
With SQL Server you have the ability to create derived tables on the fly and then use these derived tables within your query. In very basic terms, a derived table is a virtual table that's calculated on the fly from a select statement.

In concept this is similar to creating a temporary table and then using the temporary table in your query, but the approach is much simpler, because it can all be done in one step.

Boost Performance
The biggest benefit of using derived tables over using temporary tables is that they require fewer steps, and everything happens in memory instead of a combination of memory and disk. The fewer the steps involved, along with less I/O, the faster the performance.

Here are the steps when you use a temporary table:

Lock tempdb database
-CREATE the temporary table (write activity)
-SELECT data & INSERT data (read & write activity)
-SELECT data from temporary table and permanent table(s) (read activity)
-DROP TABLE (write activity)
-Release the locks
Compare this with the number of steps it takes for a derived table:

-CREATE locks, unless isolation level of "read uncommitted" is used
-SELECT data (read activity)
-Release the locks
As is rather obvious from this example, using derived tables instead of temporary tables reduces disk I/O and can boost performance.


example

without derived table
SELECT C.CustomerID, C.CompanyName, COUNT(O.OrderID) AS TotalOrders
FROM Customers C LEFT OUTER JOIN Orders O ON
C.CustomerID = O.CustomerID
WHERE YEAR(O.OrderDate) = 1996
GROUP BY C.CustomerID, C.CompanyName

with derived table
SELECT C.CustomerID, C.CompanyName,
COUNT(dOrders.OrderID) AS TotalOrders
FROM Customers C LEFT OUTER JOIN
/* start our derived table */
(SELECT * FROM Orders WHERE YEAR(Orders.OrderDate) = 1996) AS dOrders
/* end our derived table */
ON
C.CustomerID = dOrders.CustomerID
GROUP BY C.CustomerID, C.CompanyName

Updating a derived table
Now the question of updating a derived table. I don't think that it is a big issue, but still it's a question asked about derived tables. So the answer is as follows,

- MS SQL 2000 - Not possible

- MS SQL 2005 - Possible

So, updating a derived table is not possible in SQL Server 2000. If you try to do so, then SQL Server will give you the following error.

Update T SET Id=0 FROM (SELECT * FROM tt1) AS T
Error:
Server: Msg 4421, Level 16, State 1, Line 1
Derived table 'T' is not updatable because a column of the derived table is derived or constant.

To Know more please visit: http://www.sqlservercentral.com/articles/DerivedTables/61388/

Map or Virtual Earth 6.2 Integrating with Dynamics CRM 4.0

It's very simple to add geo capabilities in Dynamics CRM using Virtual Earth.Use the following steps.

1) Create an ContactLocation.htm file with the following code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Select zip/postal code and city</title>
<script type="text/javascript" src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2"></script>
<script type="text/javascript">
var map = null;
var location = null;
function GetMap() {
// Open the comments to read values from contact entity page in CRM
var street = "10861 Weyburn Ave";//parent.document.forms[0].all.address1_line1.DataValue;
var state = "California";//parent.document.forms[0].all.address1_stateorprovince.DataValue;
var city = "Los Angeles";//parent.document.forms[0].all.address1_city.DataValue;
var postcode = "90024";//parent.document.forms[0].all.address1_postalcode.DataValue;
var country = "United state";//parent.document.forms[0].all.address1_country.DataValue;

var location = street + ', ' + city + ', ' + state + ', ' + postcode + ', ' + country;
map = new VEMap("contactMap");
map.LoadMap();
map.Find(null, location, VEFindType.Businesses, null, null, null, true, true, true, true, onfound);
}

//This function used to show the Pin Icon at the searched location
function onfound(layer, resultsArray, places, hasMore, veErrorMessage) {
if (places) {
var shape = new VEShape(VEShapeType.Pushpin, places[0].LatLong);
shape.SetTitle(location);
map.AddShape(shape);
}
}
</script>
</head>
<body onload="GetMap();">
<form id="form1" runat="server">
<div>
<div id="contactMap" style="position:relative; width:600px; height:400px;"></div>
</div>
</form>
</body>
</html>

2) Host this file on IIS of the same server of the Dynamics CRM.
3) Open Dynamics CRM and customize the Contact entity’s Main Form:
4) Add a Tab called Map (or whatever you prefer).
5) Add a Section and Iframe called Map in the Location tab.
6) Set the IFrame source as the URL of the hosted html file. Uncheck the Restrict cross-frame scripting option.
7) Save, close and Publish, and is ready.

Now when you view a Contact, go to the Map tab and will be displayed a map as defined address.

How to Hide a Tab in an Entity MS CRM 4.0

Sometime user required to hide a tab in an Entity then use the following line of code:

crmForm.all.tab3Tab.style.display = "none";

Override Custom Tool Tips On-Mouse-Over in MS CRM 4.0

Some time users give more information into what they should enter into a field? You can either make the label of a given field really long, put a field in a section on it's own, and write the description out in the section title, or you could use scripts to override the tool tip of the field.
Here's the script:
var oPopup = window.createPopup();
var oPopupBody = oPopup.document.body;
oPopupBody.style.fontFamily = 'Tahoma';
oPopupBody.style.border = '1px solid black';
function My_OrgName_showRatingPopup ()
{
oPopupBody.innerHTML = "<div><table style='font-size:xx-small'>";
oPopupBody.innerHTML += "<tr><td><b></b></td><td> Custom Tool Tip Text Here</td></tr></table></div>";
var x_coord = -100;
var y_coord = 20;
var width = 350;
var height = 90;
var documentElement = event.srcElement;
oPopup.show(x_coord, y_coord, width, height, documentElement);
}
//Be sure to modify the field name here so that you attach your OnMouseOver event
//to the correct field. The "_c" indicates the label of the field.
crmForm.all.new_customfield_c.attachEvent('onmouseover', My_OrgName_showRatingPopup);
Now just hover over your field label and you'll see you new custom tooltip pop up.

To read in detail <a href="http://blog.expertsoftware.co.uk/?tag=/mscrm&page=2">Click Here</a>. Original Post: <a href="http://blog.expertsoftware.co.uk/?tag=/mscrm&page=2">expertsoftware </a>

Calling a workflow of MS CRM 4.0 from c# code (ExecuteWorkflow Message (CrmService)).

Executes a workflow.

To perform this action, the caller must have access rights on the workflow entity instance.

// Set up the CRM Service.
CrmAuthenticationToken token = new CrmAuthenticationToken();
// You can use enums.cs from the SDK\Helpers folder to get the enumeration for Active Directory authentication.
token.AuthenticationType = 0;
token.OrganizationName = "AdventureWorksCycle";

CrmService service = new CrmService();
service.Url = "http://:/mscrmservices/2007/crmservice.asmx";
service.CrmAuthenticationTokenValue = token;
service.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Create an ExecuteWorkflow request.
ExecuteWorkflowRequest request = new ExecuteWorkflowRequest();

//Assign the ID of the workflow you want to execute to the request.
request.WorkflowId = new Guid("b050f053-6968-dc11-bb3a-0003ffbad37a");

//Assign the ID of the entity to execute the workflow on to the request.
request.EntityId = new Guid("1DCDEE97-35BB-44BE-8353-58BC36592656");

// Execute the workflow.
ExecuteWorkflowResponse response = (ExecuteWorkflowResponse)service.Execute(request);

How to compare more than one column in where clouse using "NOT IN" in SQL Server

answer is: CHECKSUM

CHECKSUM computes a hash value, called the checksum, over its list of arguments. The hash value is intended for use in building hash indices. If the arguments to CHECKSUM are columns, and an index is built over the computed CHECKSUM value, the result is a hash index, which can be used for equality searches over the columns.

CHECKSUM satisfies the properties of a hash function: CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator. For the purpose of this definition, NULL values of a given type are considered to compare as equal. If one of the values in the expression list changes, the checksum of the list also usually changes. However, there is a small chance that the checksum will not change.

BINARY_CHECKSUM and CHECKSUM are similar functions: they can be used to compute a checksum value on a list of expressions, and the order of expressions affects the resultant value. The order of columns used in the case of CHECKSUM(*) is the order of columns specified in the table or view definition, including computed columns.

CHECKSUM and BINARY_CHECKSUM return different values for the string data types, where locale can cause strings with different representation to compare equal. The string data types are char, varchar, nchar, nvarchar, or sql_variant (if its base type is a string data type). For example, the BINARY_CHECKSUM values for the strings "McCavity" and "Mccavity" are different. In contrast, in a case-insensitive server, CHECKSUM returns the same checksum values for those strings. CHECKSUM values should not be compared against BINARY_CHECKSUM values.
Examples
Using CHECKSUM to build hash indices

The CHECKSUM function may be used to build hash indices. The hash index is built by adding a computed checksum column to the table being indexed, then building an index on the checksum column.

-- Create a checksum index.
SET ARITHABORT ON
USE Northwind
GO
ALTER TABLE Products
ADD cs_Pname AS checksum(ProductName)
CREATE INDEX Pname_index ON Products (cs_Pname)

The checksum index can be used as a hash index, particularly to improve indexing speed when the column to be indexed is a long character column. The checksum index can be used for equality searches.

/*Use the index in a SELECT query. Add a second search
condition to catch stray cases where checksums match,
but the values are not identical.*/
SELECT *
FROM Products
WHERE checksum(N'Vegie-spread') = cs_Pname
AND ProductName = N'Vegie-spread'

Creating the index on the computed column materializes the checksum column, and any changes to the ProductName value will be propagated to the checksum column. Alternatively, an index could be built directly on the column indexed. However, if the key values are long, a regular index is not likely to perform as well as a checksum index.

LIVE EXAMPLE:

SELECT TBL1.* FROM TABLE1 TBL1
INNER JOIN TABLE2 TBL2 ON TBL1.COLUMN_NAME = TBL2.COLUMN_NAME
WHERE CHECKSUM(TBL1.COLUMN1,TBL1.COLUMN2) NOT IN (SELECT CHECKSUM(COLUMN1,COLUMN2) FROM ANOTHERTABLE)

What is Microsoft Dynamics CRM External Connector License?

The External Connector License is only necessary when external users access
data. The term “external users” means that users are not:
• The organization's employees
• The organization's contractors or agents
• Employees of a company affiliated to the organization
• Contractors or agents of a company affiliated with the organization
For example, an organization cannot set up an external connector license for
users in a call center environment.

Example A - License required
An organization must have an External Connector License if it wants to create a
web site where the organization's customers can view and maintain their contact
information that is stored in Microsoft Dynamics CRM.

Example B - License not required
An External Connector License is not required if an organization wants to create
a web site that only displays a form that allows the public to enter information
that will be saved in Microsoft Dynamics CRM. The key difference is that in
Example A, the external users have access to Microsoft Dynamics CRM data. In
Example B, external users will enter data that is saved in Microsoft Dynamics
CRM, but they do not have access to Microsoft Dynamics CRM data.

Creating Additional Organizations

When Microsoft Dynamics CRM is installed, the Microsoft CRM Server Setup
wizard prompts the Administrator for the default organization information. The
wizard, in turn, creates the organization record and adds it into the configuration
database.
Following installation of the Microsoft Dynamics CRM Enterprise Edition, any
additional organizations must be added by the CRM Administrator through a new
Organizations tool in Deployment Manager.

Perform the following steps to create a new organization using the Deployment
Manager's Organizations tool:

1. On your desktop, click the Start menu, click All Programs, click
the Microsoft Dynamics CRM program group, then click
Deployment Manager.
2. Click on the Organizations folder to view the existing
organizations.
3. To add a new organization, either right click on the Organizations
folder and select New Organization, or click Action on the menu bar
and select New Organization.
4. In the New Organization Wizard, on the Specify the Organization
Name window, enter the Organization name. The name cannot
include blank spaces or any unsupported characters.
5. When you tab out of the Organization name field, the same name is
prefilled in the Friendly name field. Change this value if required.
Note: You can add blank spaces back in to the Friendly name if
necessary. For example, if adding an organization record for
Northwind Traders, the organization name might be
“NorthwindTraders”, but the Friendly name could be “Northwind
Traders”.
6. The ISO currency code field specifies the base currency for the new
organization. Click the Browse button and select the new
organization's base currency. Click OK.
7. The Currency name and Currency symbol are prefilled based on the
selected base currency. Click Next.
8. On the Help Us Improve the Customer Experience window, select
whether this organization is willing to participate in the Customer
Experience Improvement program. Click Next.
9. On the Select SQL Server window, select from the drop down list the
Microsoft SQL Server that contains the CRM database. Click Next.
10. On the Specify Reporting Services Server window, the report server
URL is prefilled for you. Change this value if necessary. Click Next.
11. On the System Requirements window, review the results of the
verification tasks. If any errors occur, click Details for more
information. If necessary, click Back to correct any previously
entered information. If no errors occurred, click Next.
12. On the Ready to Create window, verify the organization information.
If anything requires change, click Back to correct the information. If
all the information is correct, click Create.
13. Click Finish once the New Organization is successfully created.
14. The new organization will appear in the Organizations folder.

Microsoft Dynamics CRM uses Active Directory for storing information
regarding:
• Users
• Client computers
• The Organizational Unit
• Security groups

How to delete organizations in CRM?

As per many peoples, Once organization created in CRM it will be disabled but never deleted.
It's wrong,If required to delete existing organizations then read the following paragraph.

When an administrator creates a new organization in the Enterprise Edition of
Microsoft Dynamics CRM, the organization information is stored in the
configuration database. Similarly, the administrator can remove an existing
organization from the configuration database, although the organization must be
disabled first before it can be deleted. And if there are no more organizations left
in the configuration database, the administrator can uninstall Microsoft Dynamics
CRM from the server(s).

Update page size in CRM 4.0

As defined in CRM you can increase your page size maximum of 250 records, but if you need to resize it for 1000-2000 records then you are unable to do it, you can do it in unsupported way after using the following code.

update usersettings set PagingLimit=1900 where ModifiedByName='user name'

Refresh your CRM panel, if not reflecting then reset application Pool in IIS.

What is CredentialCache Class in CRM SDK

The CredentialCache class stores credentials for multiple Internet resources. Applications that need to access multiple resources can store the credentials for those resources in a CredentialCache instance that then provides the proper set of credentials to the Internet resource when required. When the GetCredential method is called, it compares the Uniform Resource Identifier (URI) and authentication type provided with those stored in the cache and returns the first set of credentials that match.

The DefaultCredentials property contains the system credentials of the current security context. For client applications, these represent the user name, password, and domain of the user who is currently logged in. For ASP.NET applications, the default credentials are the user credentials of the logged-in user or the user being impersonated.

How to cancel Save Operation in MS CRM4.0

Pass this line of code to prevent saving the current items in CRM.
// Cancel the save operation.
event.returnValue = false;

Read-Only fields Values don’t get saved in the database.

After changing the value of a read-only field with JavaScript the new value will not be saved into the database.

To save the newvalue set the ForceSubmit property of the field to true.

crmForm.all.new_customfield.ForceSubmit = true;

Attach an Event for a Lookup in JavaScript MS CRM 4.0

Some time we required to call a JavaScript function after selection the Lookup value for this use the below line of code to attach event.

crmForm.all.objectid.attachEvent("onafterselect", javaScriptFunctionName);

Attaching to Processes

Now that you have the plug-in registered, you are ready to start debugging. You need to attach to W3WP.exe(for synchronous services) to attach to plug-ins since they run within IIS. To attach to workflows you need to attach to the CRM async service which is Crmasyncservice.exe(for asynchronous services).

Hiding System defined Views in CRM 4.0

Some time user required to hide some system defined views from any entity in CRM 4.0,
to do this we need to create a plug-in.

For Example: hare you can hide "All My Leads" and "My Closed Leads" view's from my lead entity.

Use the following code:

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.Sdk.Metadata;
using Microsoft.Crm.Sdk.Query;
using Microsoft.Crm.SdkTypeProxy;

namespace HideViewsPlugin
{
public class HideViews : IPlugin
{
public void Execute(IPluginExecutionContext context)
{
// Query the SavedQueryBase table to retrieve the Query Id for an attribute savedqueryid
Guid[] Views = new Guid[] {
new Guid("5F001E93-3C6D-DE11-991A-0019B9F5ED8F"), //All My Leads
new Guid("36AA1011-4F6C-DE11-991A-0019B9F5ED8F"), //My Closed Leads
};

if (context.InputParameters != null && Views.Length > 0)
{
if (context.InputParameters.Properties.Contains(ParameterName.Query))
{
QueryExpression qe = (QueryExpression)context.InputParameters.Properties[ParameterName.Query];

//only apply this action if the query is for 'views' or saved queries
if (qe.EntityName == "savedquery")
{
if (qe.Criteria != null)
{
if (qe.Criteria.Conditions != null)
{
//Append more condition logic to the default query that's used by CRM. In this case, I filtered on the savedqueryid to exclude the views from the Ids identified above.
//This conditional expression hide only two defined Views
ConditionExpression queryCondition = new ConditionExpression("savedqueryid", ConditionOperator.NotIn, Views);

OR

//If you required to hide all the Views starting from a specific string then use the below defined ConditionExpression

/*The query is edited to look at views not starting with "Hidden" at the begining of the View Name*/

//ConditionExpression queryCondition = new ConditionExpression("name", ConditionOperator.NotLike, "Hidden%");

qe.Criteria.Conditions.Add(queryCondition);
context.InputParameters.Properties[ParameterName.Query] = qe;
}
}
}
}
}
}
}
}

Now register this plug-in and register one more Step for this plug-in,Parametters those required for step are given below:

Meggage: RetrieveMultiple
Primary Entity: savequery
Eventing Pipline: Pre Stage
Execution Mode: Synchronous
Step Deployment: Server
Triggering Pipeline: Parent Pipeline

Create a task through JavaScript after using Web Service in MS CRM 4.0

Some times we required to create a task for a user through JavaScript, you can do this by the following code

Here you can pass your Description ,Owner, Regarding,subject and created By parametters.

function CreateTask(MangerID,CreatedByID)
{
var CurrentQuoteID = crmForm.all.quotenumber.DataValue;
var xml = "" +
"<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +
GenerateAuthenticationHeader() +
" <soap:Body>" +
" <Create xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
" <entity xsi:type=\"task\">" +
" <description>Your Message "+ CurrentQuoteID + "</description>" +
" <ownerid>" + MangerID + "</ownerid>" +
" <regardingobjectid type=\"quote\">" + crmForm.ObjectId + "</regardingobjectid>" +
" <subject>Your Subject "+ CurrentQuoteID + "</subject>" +
" <createdby>" + CreatedByID + "</createdby>" +
" </entity>" +
" </Create>" +
" </soap:Body>" +
"</soap:Envelope>" +
"";

var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");

xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xmlHttpRequest.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/Create");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
xmlHttpRequest.send(xml);

var resultXml = xmlHttpRequest.responseXML;
}

How can we use LookUpControl in my Custom page in CRM 4.0

I'm trying to replicate the functionality of this CRM 4.0 in my custom aspx page

<nobr>
<asp:TextBox ID="lookupresult" runat="server"></asp:TextBox>
<img src="/_imgs/btn_off_lookup.gif" onclick="ShowOwnerLookupDialog()" />

<script language="javascript">
function ShowOwnerLookupDialog()
{
var lookupItems = showModalDialog( '/_controls/lookup/lookupsingle.aspx?class=BasicOwner&objecttypes=8,2020&browse=0&ShowNewButton=1&ShowPropButton=1&DefaultType=0' , null, '' );
document.all.lookupresult.value = lookupItems.items[0].id;
}
</script>
</nobr>

Update Filtered lookup data in CRM 4.0

For this We have to pass fetch xml string using one of the parameters "search" recognised by the server otherwise exception is thrown (there is a way to disable parameters check through the registry setting
DisableParameterFilter, but it's easier without yet another undocumented setting).
Since we want to disable search functionality it makes sense to re-use search parameter.

Use the following code:

This line of code using for single entity
//The attribute where lookup is attached
var field = crmForm.all.pricelevelid;

// Ensure that search box is not visible in a lookup dialog
field.lookupbrowse = 1;
// Pass fetch xml through search value parameter
field.AddParam("search",
"<fetch mapping='logical'><entity name='pricelevel'>"
+ "<filter><condition attribute='name' operator='eq' value='pricelist name' /></filter></entity></fetch>");


This line of code using for multiple entity
//Lookup will show all the Child users of current loggedIn user

//Return all child users
var targetUserUnits = GetChildusers() //for eg: <value>"+Value1+"</value><value>"+Value2+"</value>
//The attribute where lookup is attached
var field = crmForm.all.new_userid;
//Ensure that search box is not visible in a lookup dialog
field.lookupbrowse = 1;

field.AddParam("search",
"<fetch mapping='logical'><entity name='systemuser'>"
+"<all-attributes/>"
+"<link-entity name='new_nesteduser' from='new_userid' to='systemuserid'>"
+"<filter type='and'><condition attribute='new_lft' operator='between'>"
+targetUserUnits
+"</condition></filter></link-entity></entity></fetch>");

Unsupported
You can manage it from lookup form means if this parameter contains any value then apply this filter otherwise use as origional.
to do this open the page "C:\Program Files\Microsoft Dynamics CRM\CRMWeb\_controls\lookup\lookupsingle.aspx " and change it accordingly as required.

For Eg.

void crmgrid_PreRender( object sender , EventArgs e )
{
// As we don't want to break any other lookups, ensure that we use workaround only if
// search parameter set to fetch xml.
<strong>if (crmGrid.Parameters["search"] != null && crmGrid.Parameters["search"].StartsWith("<fetch"))
{
crmGrid.Parameters.Add("fetchxml", crmGrid.Parameters["search"]);
// searchvalue needs to be removed as it's typically set to a wildcard '*'
crmGrid.Parameters.Remove("searchvalue");
// Icing on a cake - ensure that user cannot create new contact outside of the account
// and then select it.
this._showNewButton = false;
} </strong>
}

Expose the toggling function at the window level in CRM 4.0

After create a function in page load event of any entity in CRM and tried to call it from onSave or any attribute OnChange event
then you don't required to type the same function again and again just toggle this function at the bottom of pageLoad
event, to do this use the following code

//Expose the toggling function at the window level
this.functionName = functionName;

Determine Space Available/Used for Entities

Determine Space Available for your Entity

If you decide to add a custom attribute to an entity, you can determine if there is
space available for the attribute in the entity's extension table by running this
Transact-SQL query on the _CRM database.Use this following query:

select EntityName=e.name,Bytes_Remaining=8060-(Sum(a.length))
from entity e join attribute a on e.entityid=a.entityid
where a.iscustomerfield=1 and a.islogical=0
group by e.Name

Determine Space Used for your Entity

Alternatively, you can determine the number of bytes already used by custom
attributes for an entity by running this Transact-SQL query on the
_CRM database.Use this following query:

select EntityName=e.Name,Physical_size=Sum(a.length)
from entity e join attribute a on e.entityid=a.entityid
where a.iscustomerfield=1 and a.islogical=0
group by e.Name

Increase the Tab limit in MSCRM 4.0

By default the max number of tabs allowed in CRM Form is 8.

The max tab limit is defined in JavaScript of formeditor.aspx. This page can be found at the following location “\Microsoft Dynamics CRM\CRMWeb\Tools\FormEditor”.
You can change the count specified in the _iMaxTabs to increase the count.

Create and Sending Email in CRM 4.0

Use the following code to send an email to multiple customers in CRM4.0

SendMailToMembers(ICrmService crmService, ArrayList members, String msgBody, Guid ownerID, Guid userID,string subject)
{

// create an email
email emailCreate = new email();
emailCreate.subject = subject;
emailCreate.description = msgBody;

//specify the owner for the mail
emailCreate.ownerid = new Owner();
emailCreate.ownerid.type = EntityName.systemuser.ToString();
emailCreate.ownerid.Value = ownerID;

//create an activityparty array holding all the guids specified in the members array list
activityparty[] ap = new activityparty[members.Count];
// creating as many activity party as the no of users or members in a team
int i = 0;
foreach (String memberID in members)
{
ap[i] = new activityparty();
ap[i].partyid = new Lookup();
ap[i].partyid.type = EntityName.contact.ToString();
ap[i].partyid.Value = new Guid(memberID);
i++;
}

// specify to part of the email
emailCreate.to = ap;

// specify the from part of the email
activityparty from = new activityparty();
from.partyid = new Lookup();
from.partyid.type = EntityName.systemuser.ToString();
from.partyid.Value = userID;
emailCreate.from = new activityparty[] { from };

// finally create the email and get the guid of the email
Guid emailId = crmService.Create(emailCreate);

// Create an SendEmailRequest object
SendEmailRequest req = new SendEmailRequest();
req.EmailId = emailId;
req.TrackingToken = string.Empty;
req.IssueSend = true;

// Finally Send the email message.
SendEmailResponse res = (SendEmailResponse)crmService.Execute(req);
}

Updating custom entities icons (Upload a new image for a Custom Entity in CRM)

For all our projects, we are always customizing Microsoft Dynamics CRM 4.0. To achieve that, we create many custom entities and one of the worst aspect of that heavy customization is to have a lot of custom entities with the same icon. So, it is difficult to identify them just with their icon.

In order to customize further the application, it is a good thing to add different icon to each custom entity.

The standard approach

This approach consists of using the standard feature of Microsoft Dynamics CRM 4.0 to update custom entites icons. In the customization view, just click on the menu “More actions” and click on “Update icons…”.

You then need to provide a file for each required icons:

*
icon in web application (16x16 / gif / less that 10kb)
*
Shortcut icon in Outlook (32x32 / ico / less than 10kb)
*
Icon in entity form (66x48 / gif / less than 10kb)

For more Details Click Here

Plug-ins vs. Workflows in MS CRM 4.0

First of all, remember than both workflows and plug-ins can attach to exactly the same events. Well, plug-ins have available a couple of more events but essentially both work on top of the same event model.Remember also that workflows always run asynchronous and hence, the Asynchronous Processing Service must be running on the server in order to run.

Workflows are more suitable if:

1)you want to achieve simple tasks faster, such as sending an e-mail or creating / updating assigning records. These actions can be set up very quickly with a workflow without any need of writing code.

2)you want to easily scale things to managers (if they were setup for user records), as it is possible to assign records to them.

you want to allow an advanced user to make changes to logic. As using the integrated workflow designer is user-friendly, an advanced user would be able to edit an existing workflow and change some rules according to business changes.

3)the logic should be available to be run on demand. I mean, when you are within an entity and navigates to “workflows” option in the left pane, all workflows marked as available to run on demand can be executed making them independent of an event trigger.

4)you want to send emails making use of templates and attaching files.

Workflows also allow running child workflows which may make a lot of sense in some scenarios. Nevertheless, be careful if you need the child workflow results in order to make decisions on your main workflow, as child workflows always run asynchronous, which means that it will trigger the child workflow and continue. If you need your primary workflow to wait until child ends, you will need to write a custom activity.

On the other hand, plug-ins are more suitable if:

1)you need to manipulate data before is saved.

2)you need to make validations before submitting the operation.

3)you want to be able to cancel an operation based on your validations.

4)immediate response to the user is needed.

5)you need retrieve values and/or take actions after operation has been completed (i.e. getting and autogenerated id)

It is important to note that since Dynamics CRM 4, plug-ins can also be configured to run asynchronous (Mode attribute while registering plug-in). Nevertheless, pre-event asynchronous plug-ins are not supported. In this case, you will have to set it up as synchronous mode.

Another important thing about plug-ins is the Deployment option which says if the plug-in is going to be executed on the server and/or Outlook client. If both executions are set up and client goes offline and online, plug-in calls are triggered after synchronization so be prepared in this case to execute your code twice!

Regarding to security:

1)Workflows triggered automatically will run under the security context of the workflow owner. On the contrary, if executed on demand, the security context of the user who executed the workflow will be used.

2)Plug-ins execute under the security context of the CRM Web application pool identity (typically NETWORK SERVICE). As this account typically maps to generic CRM SYSTEM user this typically works fine.

However, within plug-ins you can make use of impersonation to work under the credentials of the user who is making the request. For doing so, you just need to pass True to the CreatCrmService method under the context object.If you need to always impersonate with a specific user, you can do that by passing True as above and setting impersonatinguserid attribute while registering the plug-in.It is important to know that plug-in impersonation does not work offline. The logged on user credentials are always used in this case.

referred from Here

How to get currently authenticated user in MSCRM SQL queries?

During developing SQL queries for MSCRM, it’s often required to fetch the currently authenticated user.
SUSER_SNAME() is a special function in SQL that returns the currently authenticated user. If your code doesn’t authenticate to SQL Server by using Windows authentication, your queries will never return any data, because all of the filtered views perform an inner join using systemuserid. Here is the query to fetch currently authenticated user in SQL server.

select SystemUserId, FullName from SystemUserBase
where SystemUserBase.DomainName = SUSER_SNAME()

above code referred from http://ayazahmad.wordpress.com/page/5/

Get Current User Roles through JavaScript in CRM

If you need to find businessunitid and name from role entity of current logged in user in JavaScript then for this you need to access WebService of CRM through JavaScript and privide logged in user id through system variable "EqualUserId".

In the below code you can access role entity attribute after liked other two entities with name "systemuserroles" and "systemuser" with "role" entity.

var xml = "" +
"<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +
GenerateAuthenticationHeader() +
" <soap:Body>" +
" <RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
" <query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\">" +
" <q1:EntityName>role</q1:EntityName>" +
" <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
" <q1:Attributes>" +
" <q1:Attribute>businessunitid</q1:Attribute>" +
" <q1:Attribute>name</q1:Attribute>" +
" </q1:Attributes>" +
" </q1:ColumnSet>" +
" <q1:Distinct>false</q1:Distinct>" +
" <q1:LinkEntities>" +
" <q1:LinkEntity>" +
" <q1:LinkFromAttributeName>roleid</q1:LinkFromAttributeName>" +
" <q1:LinkFromEntityName>role</q1:LinkFromEntityName>" +
" <q1:LinkToEntityName>systemuserroles</q1:LinkToEntityName>" +
" <q1:LinkToAttributeName>roleid</q1:LinkToAttributeName>" +
" <q1:JoinOperator>Inner</q1:JoinOperator>" +
" <q1:LinkEntities>" +
" <q1:LinkEntity>" +
" <q1:LinkFromAttributeName>systemuserid</q1:LinkFromAttributeName>" +
" <q1:LinkFromEntityName>systemuserroles</q1:LinkFromEntityName>" +
" <q1:LinkToEntityName>systemuser</q1:LinkToEntityName>" +
" <q1:LinkToAttributeName>systemuserid</q1:LinkToAttributeName>" +
" <q1:JoinOperator>Inner</q1:JoinOperator>" +
" <q1:LinkCriteria>" +
" <q1:FilterOperator>And</q1:FilterOperator>" +
" <q1:Conditions>" +
" <q1:Condition>" +
" <q1:AttributeName>systemuserid</q1:AttributeName>" +
" <q1:Operator>EqualUserId</q1:Operator>" +
" </q1:Condition>" +
" </q1:Conditions>" +
" </q1:LinkCriteria>" +
" </q1:LinkEntity>" +
" </q1:LinkEntities>" +
" </q1:LinkEntity>" +
" </q1:LinkEntities>" +
" </query>" +
" </RetrieveMultiple>" +
" </soap:Body>" +
"</soap:Envelope>" +
"";
var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");

xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xmlHttpRequest.setRequestHeader("SOAPAction"," http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
xmlHttpRequest.send(xml);

var resultXml = xmlHttpRequest.responseXML;

if (resultXml != null)
{
var entityNode = resultXml.selectSingleNode("//RetrieveMultipleResult/BusinessEntities/BusinessEntity");
var RoleName = entityNode.selectSingleNode("q1:name").text;
var BUentityNode = resultXml.selectSingleNode("//RetrieveMultipleResult/BusinessEntities/BusinessEntity/q1:businessunitid");
var BUName = BUentityNode.getAttribute("name");
}

Get Regarding Activity Status from activitypointer Entity

Here you can find activityid and statecode from activitypointer entity through WebService in JavaScript after passing one parameter "crmForm.ObjectId", parameter return current entitt GUID.

var xml = "" +
"<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +
GenerateAuthenticationHeader() +
" <soap:Body>" +
" <RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
" <query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\">" +
" <q1:EntityName>activitypointer</q1:EntityName>" +
" <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
" <q1:Attributes>" +
" <q1:Attribute>activityid</q1:Attribute>" +
" <q1:Attribute>statecode</q1:Attribute>" +
" </q1:Attributes>" +
" </q1:ColumnSet>" +
" <q1:Distinct>false</q1:Distinct>" +
" <q1:Criteria>" +
" <q1:FilterOperator>And</q1:FilterOperator>" +
" <q1:Conditions>" +
" <q1:Condition>" +
" <q1:AttributeName>activitytypecodename</q1:AttributeName>" +
" <q1:Operator>Equal</q1:Operator>" +
" <q1:Values>" +
" <q1:Value xsi:type=\"xsd:string\">Task</q1:Value>" +
" </q1:Values>" +
" </q1:Condition>" +
" <q1:Condition>" +
" <q1:AttributeName>regardingobjectid</q1:AttributeName>" +
" <q1:Operator>Equal</q1:Operator>" +
" <q1:Values>" +
" <q1:Value xsi:type=\"xsd:string\">"+crmForm.ObjectId+"</q1:Value>" +
" </q1:Values>" +
" </q1:Condition>" +
" </q1:Conditions>" +
" </q1:Criteria>" +
" </query>" +
" </RetrieveMultiple>" +
" </soap:Body>" +
"</soap:Envelope>" +
"";

var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");

xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xmlHttpRequest.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
xmlHttpRequest.send(xml);

var objResult = xmlHttpRequest.responseXML;

var buNodes = objResult.selectNodes("//BusinessEntity/q1:statecode"); // CRM 4.0

if(buNodes != null )
{
/*get values*/
for( i = 0; i < buNodes.length; i++)
{
switch(buNodes[i].text)
{
//Put your logic here
}
}
}

Include Javascript files in CRM (load custom javascript file)

Some time user required to do same type of work at multiple place in JavaScript, for this user have two options either place a same type of code at all the places or store the same code at one place in JavaScript file and name it with "xyz.js", now the question is how to use this JS file in different entities from CRM.

Some steps are there:
1) create one folder with name "myscripts" in ISV in CRM server.
2) place the "xyz.js" file in above named folder.
3) use the following JavaScript code at the OnLoad event of the entity.

var script = document.createElement('script');
script.language = 'javascript';
script.src = '/ISV/myscripts/xyz.js';
document.getElementsByTagName('head')[0].appendChild(script);

var f = function()
{
if (event.srcElement.readyState == "loaded")
{
//perform onload scripts
FunctionfromJavaScriptFile();
// some function from your custom js file
}
}
script.attachEvent("onreadystatechange", f);

After using above three defined steps you are ready to call custom JavaScript file.

Disable Menu Buttons from any entity in CRM

Some time user is not interested to show some buttons from menu items, for this user required to hide these buttons. You can do this if you have ID of that button, but in CRM the ID of these buttons not standard these are created at the time of loading, but some part of the ID is unique every time, so find this unique id name from source control of entity.After this use following JavaScript to Hide these buttons.

function DisableButtons()
{
//Get all of the List Elements
var lis = document.getElementsByTagName('LI');
var i = 0;
//Loop through the list items
while (i < lis.length)
{
if (lis[i].getAttribute('id').indexOf( 'provide unique id name')!=-1)
{
var lnkmenuApproveDiscount = lis[i].getAttribute('id');
var menuApproveDiscountButton = document.getElementById(lnkmenuApproveDiscount);
menuApproveDiscountButton.style.display='none';
}
if (lis[i].getAttribute('id').indexOf( 'provide unique id name')!=-1)
{
var lnkmenuRejectDiscount = lis[i].getAttribute('id');
var menuRejectDiscountButton = document.getElementById(lnkmenuRejectDiscount);
menuRejectDiscountButton.style.display='none';
}
i = i + 1;
}
}

Hide associated view buttons(Buttons those are showing in related entity)

Some time user not required some buttons to be shown in associated (related entity view) grid menu.To hide associated(related entity view) buttons we are required to Add JavaScript on the form OnLoad event of entity, in which this assicated view is shown.

For example:- I have add entity with name Audit in CRM and associate it with Account, Now I tried to hide Audit assiciated button's Add New and Add Existing Item from Audit entity, but I saw ID's of these buttons not exist in Account entity source becuase associated entity open in Iframe, so for this we requried to add following JavaScript on Account form load event.

function HideAssociatedViewButtons(loadAreaId, buttonTitles) {
var navElement = document.getElementById('nav_' + loadAreaId);
if (navElement != null) {
navElement.onclick = function LoadAreaOverride() {
// Call the original CRM method to launch the navigation link and create area iFrame
loadArea(loadAreaId);
HideViewButtons(document.getElementById(loadAreaId + 'Frame'), buttonTitles);
}
}
}

function HideViewButtons(Iframe, buttonTitles) {
if (Iframe != null) {
Iframe.onreadystatechange = function HideTitledButtons() {
if (Iframe.readyState == 'complete') {
var iFrame = frames[window.event.srcElement.id]; var liElements = iFrame.document.getElementsByTagName('li'); for (var j = 0; j < buttonTitles.length; j++) {
for (var i = 0; i < liElements.length; i++) {
if (liElements[i].getAttribute('title') == buttonTitles[j]) {
liElements[i].style.display = 'none';
break;
}
}
}
}
}
}
}

HideAssociatedViewButtons function requires loadAreaId, buttonTitles as a parameters.
We can find this loadAreaID, by viewing source code of Account form. and buttonTitles means ToolTip of that button.

for Example:- HideAssociatedViewButtons('new_lead_custom_audit', ['Add existing Audit to this record', 'Add a new Audit to this record']);

Content refereed from http://mahadeomatre.blogspot.com/2009/07/hide-associated-view-buttons.html