WCF - Error 500.21 Handler "svc-Integrated" has a bad module "ManagedPipelineHandler" in its module list"

If you  see this error you havent installed the prerequistes for ASP.NET 4.

1. Go to C:\Windows\Microsoft.NET\Framework64\v4.0.30319
2. Type the following: 
   1. aspnet_regiis.exe -i
3. Run the command
4. Try your service again...

App.Config

sAttr = ConfigurationManager.AppSettings.Get("Key0");

DB Class

using System;
using System.Data;
using System.Data.SqlClient;

public class DataManager
{
public DataManager()
{
}

public static DataTable ExecuteQuery(string query)
{
string connectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

try
{
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
DataSet ds = new DataSet();
adapter.Fill(ds);

return ds.Tables[0];
}
finally
{
if ( connection.State == ConnectionState.Open )
connection.Close();
}
}


public static void ExecuteNonQuery(string query)
{
string connectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

try
{
SqlCommand cmd = new SqlCommand();
cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;

cmd.ExecuteNonQuery();
}
finally
{
if ( connection.State == ConnectionState.Open )
connection.Close();
}
}

public static object ExecuteScalar(string query)
{
string connectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

try
{
SqlCommand cmd = new SqlCommand();
cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;

return cmd.ExecuteScalar();
}
finally
{
if ( connection.State == ConnectionState.Open )
connection.Close();
}
}
}

Remove RowGUID columns

-- drop rowguid indexes
select 'drop index ' + sysobjects.name + '.' + sysindexes.name from
sysindexes
inner join sysobjects
on sysindexes.id = sysobjects.id
where objectproperty(object_id(sysobjects.name),'IsMSShipped') = 0
and sysindexes.indid > 0 and sysindexes.indid < 255 and (sysindexes.status &
64)=0
and index_col(sysobjects.name, sysindexes.indid, 1) = 'rowguid'
order by sysindexes.indid

-- remove rowguid default constraints
select 'alter table ' + b.name + ' drop constraint ' + QUOTENAME(a.name) from
sysobjects a
inner join syscolumns on syscolumns.id = a.parent_obj
inner join sysobjects b on syscolumns.id = b.id
where syscolumns.name = 'rowguid'
and objectproperty(object_id(b.name),'IsMSShipped') = 0
and a.xtype = 'D'

-- remove rowguid columns
select 'alter table ' + sysobjects.name + ' drop column '+QUOTENAME(syscolumns.name) from
syscolumns
inner join sysobjects on syscolumns.id = sysobjects.id
where syscolumns.name = 'rowguid'
and objectproperty(object_id(sysobjects.name),'IsMSShipped') = 0

Take database to sigle user mode

ALTER DATABASE TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Take back in to multi user

ALTER DATABASE TEST SET MULTI_USER

Walk-through of CLR Integration with SQL Server

One of the project requirements is writing all the stored procedures and user-defined functions in CLR integration. Although I don't intent to give you the overview of CLR integration in this article, it's not a bad idea to go over the benefits you get from using CLR integration. Stored procedures and user-defined functions/types/aggregates authored in managed code compiles into native code prior to execution, and you can achieve significant performance increases in scenarios where complex computations or business logics are involved. When you create these CLR objects, you can leverage various functions and services CLR provides for the program execution, such as JIT, thread handling, memory allocating, type conversion etc.

The following step-by-step example will show you the common tasks performed in CLR integration projects.

* Enabling CLR integration in SQL Server
* Create a SQL Server Project in Visual Studio 2005/2008
* Create a managed stored procedure
* Deploy the CLR assembly using Visual Studio
* Execute CLR objects
* Deploy assemblies onto production environment

The Northwind database will be used in the example I created. If you do not have the Northwind database, go ahead and download it here and restore it to your SQL Server 2005/2008 instance.

Enabling CLR integration in SQL Server

CLR integration is disabled by default in the SQL Server 2005/2008. To enable it, connect to the database to which you want to deploy the CLR assembly, execute the SQL statements below and then restart the SQL server instance.

EXEC sp_configure 'clr enabled', '1'



reconfigure



Create a database project in Visual Studio 2005/2008

Creating a CLR project in Visual Studio 2005/2008 is beyond simple. Go to File --> New Project --> Visual C#, and then select "SQL Server Project". Rename the project to NorthwindOrdersCLR.

CreateProject

Click on OK. You will be prompted to add a database reference. Go ahead and select the Northwind database. If it is not already listed in the dialog, add the connection to the Northwind database.

AddDBRef

Click on Ok. The SQL Server project should be created by Visual Studio.

Create a managed stored procedure

Before we go any further, let's spend a few minutes going over the task we try to implement using the CLR integration. Suppose a business partner of Northwind is placing orders in raw XML messages and the order data needs to be parsed from the XML messages and then be populate into the Northwind..Orders table .

Let's go back to the SQL Server project we just created. Right click the project file NorthwindOrdersCLR, select Add New Item, choose Stored Procedure from the "Add new item" dialog, rename the stored procedure to ParseOrders.cs and then hit OK.

CreateSP

Visual Studio generates the stub for the stored procedure for you automatically.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;





public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void ParseOrders()

{

// Put your code here

}

};

In CLR, stored procedures are implemented as public static methods of a .NET class. The static methods can either be declared as void, or return an integer value, and the static methods must be marked with the "SqlProcedure" attribute, which is under the Microsoft.SqlServer.Server namespace.

In order to parse the XML message, we need to reference the System.Xml namesapce. Also, we are going to pass a string parameter to to our stored procedure. The string will be the raw message of orders from the Northwind's business partner. The stored procedure stub should look like this after the tweaks.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Xml;





public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void ParseOrders(string orderXML)

...

}

So far so good. Now we don't need to look at the problem from a database developer's point of view any more. We are back to what we are good at - programming in .NET. We can write this stored procedure in the way we write .NET clients to access the SQL database: create a database connection, create a SqlCommand with the SQL statement for inserting data, add parameters to the command, parse the XML message, and then call ExecuteNonQuery. In addition, we will benefit from the power of try-catch blocks so we can handle the exceptions in the .NET way.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Xml;





public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void ParseOrders(string orderXML)

{

XmlDocument xd = new XmlDocument();

using (SqlConnection sc = new SqlConnection("context connection=true"))

{

using (SqlCommand cmdInsert = new SqlCommand("INSERT INTO Orders (CustomerID, EmployeeID, OrderDate) VALUES (@OrderID, @EmployeeID, @OrderDate)", sc))

{

try

{

xd.LoadXml(orderXML);

sc.Open();

XmlNode orderNode = xd.DocumentElement;

cmdInsert.Parameters.Add("@CustomerID", SqlDbType.NChar, 5).Value = xd["CustomerID"].InnerText;

cmdInsert.Parameters.Add("@EmployeeID", SqlDbType.Int, 4).Value = Convert.ToInt32(xd["EmployeeID"].InnerText);

cmdInsert.Parameters.Add("@OrderDate", SqlDbType.DateTime, 8).Value = Convert.ToDateTime(xd["OrderDate"].InnerText);

cmdInsert.ExecuteNonQuery();

}

catch (XmlException xe)

{

SqlContext.Pipe.Send("Error parsing XML message: " + xe.Message);

}

catch (SqlException ex)

{

SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);

}

finally

{

sc.Close();

}

}

}

}

};

Although the code is pretty self-descriptive and you get the idea of exactly what this stored procedure does, I do need to mention a couple of things here.

First, the connection string I used to create the SqlConnection looks different from what we usually do in a traditional .NET application. The problem of internal data access is a fairly common scenario. That is, you wish to access the same server on which your CLR stored procedure or function is executing. One option is to create a connection using SqlConnection, specify a connection string that points to the local server, and open the connection. This requires specifying credentials for logging in. The connection is in a different database session than the stored procedure or function, it may have different SET options, it is in a separate transaction, it does not see your temporary tables, and so on. If your managed stored procedure or function code is executing in the SQL Server process, it is because someone connected to that server and executed a SQL statement to invoke it. You probably want the stored procedure or function to execute in the context of that connection, along with its transaction, SET options etc. This is called the context connection. The context connection lets you execute Transact-SQL statements in the same context that your code was invoked in the first place. In order to obtain the context connection, you must use the "context connection" connection string keyword, as in the code snippet above.

The second thing is that I used SqlContext.Pipe.Send several times in the code. For CLR database objects running in SQL Server, you can send results to the connected pipe using the Send methods of the SqlPipe object. Access the Pipe property of the SqlContext object to obtain the SqlPipe object. The SqlPipe class is conceptually similar to the Response class found in ASP.NET. This is extremely useful when you need to watch the execution steps of CLR objects. We will look at a couple of examples of how this works when we run the stored procedure.

Deploy the CLR assembly using Visual Studio

Now that we have created the managed stored procedure to handle the orders in XML format, let's deploy it into SQL server to test it out. Deploying a CLR assembly in Visual Studio cannot be easier. All you have to do is right click the NorthwindOrdersCLR project and choose "Deploy".

Deploy

You may receive an error message saying "The current database compatibility level does not support managed objects." This is because the Northwind database was created in SQL Server 2000 and CLR integration only works with SQL Server 2005/2008. We'll fix this quickly by changing the compatibility level of the database to SQL Server 2005(90). Re-deploy the CLR project after you have changed the compatibility level.

image

Visual Studio should report "Deploy succeeded" if no errors have occurred. Open SQL Server Management Studio, expand the Northwind database's Programmability node, and you should see the ParseOrders stored procedure listed under "Stored Procedures" and the NorthwindOrdersCLR assembly under the "Assemblies" node.

image

Execute CLR objects

CLR objects can be executed the same way native SQL objects are executed. Simply run the following SQL statement to test our ParseOrders.

exec ParseOrders 'FRANK12008/05/02'

If you've been following my code so far, you should receive the "Query executed successfully" message when you executed the query above. We can verify the data in the Orders table by executing a quick SELECT statement.

SELECT * FROM orders WHERE OrderID = (SELECT MAX(OrderID) FROM Orders)

The new order with CustomerID "FRANK" is shown in the result set.

image

I mentioned earlier that the SqlPipe.Send method was very useful for tracing the execution of CLR objects. We can demonstrate this by creating an exception and letting it get caught by the "catch" block, where I called the SqlContext.Pipe.Send to print out the exception message. Execute the SQL statement below.

exec ParseOrders 'FRANK123452008/05/02'

EmployeeID "12345" doesn't exist in the Northwind..Employees table, so this statement will violate the foreign key constraint on the EmployeeID column in Northwind..Orders. We expect to see the exception message coming out of the pipe when this query finishes.

image

Nice and easy!!! The query executed successfully although the insert command failed due to the constraint. The failure was caught by our SqlException catch block and we got a friendly error message.

Deploy CLR assemblies into production environment

It is easy to deploy CLR assemblies with the aid of Visual Studio. But in the production environment, you probably cannot even connect to the SQL server from your Visual Studio. You will need to use the CREATE ASSEMBLY statement in SQL to deploy the assembly. First, copy the compiled DLL file into a path that the SQL Server is able to access. For example, copy the NorthwindOrdersCLR.dll file (located in the bin\debug or bin\release folder under your project folder) to c:\temp on the production SQL server. Open the SQL Management Studio, connect to the production SQL server, and execute the following SQL statement. This will create the CLR assembly in your production database.

CREATE ASSEMBLY NorthwindOrderCLR from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE

The "WITH PERMISSION_SET" option sets the permission the assembly can have when it executes on the SQL server. SAFE is applied by default if not specified otherwise, and SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry. Another two possible values for PERMISSION_SET are EXTERNAL_ACCESS and UNSAFE. EXTERNAL_ACCESS enables assemblies to access certain external system resources such as files, networks, environmental variables, and the registry.
UNSAFE enables assemblies unrestricted access to resources, both within and outside an instance of SQL Server. Code running from within an UNSAFE assembly can call unmanaged code as well. SAFE is highly recommended by Microsoft.

CLR Triggers for SQL Server 2005

Now Microsoft SQL Server 2005 is integrated with Microsoft .NET Framework Common Language Runtime (CLR), so we can use any .NET Framework language to create database objects. The CLR provides the execution environment for all the server side objects that are created using a .NET language. This means the database developers can now perform tasks that were impossible or difficult to achieve with T-SQL alone. Especially when working with large amounts of server code, developers can easily organize and maintain their code investments.

This article covers different types of triggers supported by Microsoft SQL Server 2005 with basic ideas about them with an example. It then describes the step-by-step approach to create a CLR trigger (a DML type).

What is Trigger?

A trigger is a Database object just like a stored procedure or we can say it is a special kind of Stored procedure which fires after (/before) a specified language event executes. More specifically, it is for the object which is attached to a Table or View or Database schemas for tracking the operations on them. The main difference between a trigger and a stored procedure is that the former is attached to a table or view and is fired only when an INSERT, UPDATE, and/or DELETE occurs, while a stored procedure executes at any time when it is called.

Types of Triggers

There are some added types in SQL Server 2005 for triggering actions:

1. DML Triggers

· AFTER Triggers

· INSTEAD OF Triggers

2. DDL Triggers

3. CLR Triggers
DML Triggers

These triggers are fired when a Data Manipulation Language (DML) event takes place. These are attached to a Table or View and are fired only when an INSERT, UPDATE and/or DELETE event occurs. The trigger and the statement that fires it are treated as a single transaction. Using this we can cascade changes in related tables, can do check operations for satisfying some rules and can get noticed through firing Mails. We can even execute multiple triggering actions by creating multiple Triggers of same action type on a table. We have to specify the modification action(s) at the Table level that fires the trigger when it is created.

AFTER Triggers

As the name specifies, AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. This is the only option available in earlier versions on Microsoft SQL Server. AFTER triggers can be specified on tables only. Here is a sample trigger creation statement on the Users table.

Listing 1 (AFTER Trigger example)

------ Creating a DML trigger in T-SQL -------
SET NOCOUNT ON
CREATE TABLE UserTable (User_ID int IDENTITY, User_Name varchar(30), Type varchar(10))
go
CREATE TRIGGER tr_User_INSERT
ON UserTable
FOR INSERT
AS
PRINT GETDATE()
Go
INSERT UserTable (User_Name, Type) VALUES ('James', 'ADMIN')

------ Result ---------------
Apr 30 2007 7:04AM

INSTEAD OF Triggers

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

DDL Triggers

DDL triggers are new to SQL Server 2005. This type of triggers, like regular triggers, fire stored procedures in response to an event. They fire in response to a variety of Data Definition Language (DDL) events. These events are specified by the T-SQL statements that are start with the keywords CREATE, ALTER, and DROP. Certain stored procedures that perform DDL-like operations can also fire this. These are used for administrative tasks like auditing and regulating database operations.

CLR Triggers

A CLR triggers can be any of the above, e.g. can be a DDL or DML one or can also be an AFTER or INSTEAD OF trigger. Here we need to execute one or more methods written in managed codes that are members of an assembly created in the .Net framework. Again, that assembly must be deployed in SQL Server 2005 using CREATE assembly statement.

The Microsoft.SqlServer.Server Namespace contains the required classes and enumerations for this objective.

Steps for Creating CLR Trigger

The following are required steps for creating a CLR-Trigger of DML (After trigger) type for Insert action. This database Object is executed as the result of a user action against a table i.e. for an INSERT statement.

· Creating a .NET class of triggering action

· Making assembly(.DLL) from that Class

· Enabling CLR environment in that database.

· Registering the assembly in SQL Server

· Creating CLR Trigger using that assembly

1. Creating a .NET class

Here we can use any managed language that is supported by .Net Framework such as C++, C#, VB, J#, JScript or XAML, etc. As I am with VB, this managed code is in Visual Basic. Let us discuss the objective of this entity. According to the above example of "tr_User_INSERT" trigger, we have the UserTable for holding the user details. There is a field "Type" which explains the user role (ADMIN, End User, Register User etc.). Our objective is to check the role of the inserted User for ADMIN type and then do the action as we set.

Open the notepad, copy the following codes and save it as MyFirstAssembly.vb.

Listing 2 (.NET Class of Trigger type)

Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Partial Public Class MyFirstClrTrigger
= "checkUserRole", Target
= "UserTable", Event
= "FOR INSERT")> _
Public Shared Sub checkUserRole()

Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext()
Dim userType As String = String.Empty

If triggContext.TriggerAction = TriggerAction.Insert Then
Using connection As New SqlConnection("context connection=true")
connection.Open()
Dim sqlComm As New SqlCommand
Dim sqlPipe As SqlPipe = SqlContext.Pipe()

sqlComm.Connection = connection
sqlComm.CommandText = "SELECT Type from INSERTED"

userType = sqlComm.ExecuteScalar.ToString()

If userType.ToUpper = "ADMIN" Then

sqlPipe.Send("Hello !!! You have the Admin role.")
sqlPipe.Send("We can use e-mail codes here to inform.")

End If
End Using
End If
End Sub

End Class

Let us go into the codes. There are two major Namespaces used, System.Data.SqlClient and Microsoft.SqlServer.Server. Microsoft.SqlServer.Server provides the SqlTriggerAttribute Class, which is used to mark a method definition in an assembly as a trigger in SQL Server. The Sqltrigger attribute requires some parameters to set the created trigger properties, such as Name - the name of the Trigger, Target - the table or view to which the trigger applies (in case of DML type) and Event - the event to fire the trigger for (e.g. FOR INSERT, DELETE and/or UPDATE or INSTEAD OF etc.). Again, that method must be a Static (Shared in VB) one; here checkUserRole() is the target method.

The SqlTriggerContext class provides the required triggering properties of the Trigger for doing action. TriggerAction property of this Class is the global enumeration TriggerAction type of Microsoft.SqlServer.Server namespace which indicates what action fired the Trigger.

Here we use the INSERTED table, which is automatically created and managed by SQL Server 2005. This is used to set the conditions of DML trigger action. There is also another called DELETED, used in case of delete action. The CLR triggers can access the Inserted or Deleted tables through SqlCommand object using context connection.


2. Making assembly (.DLL)

Now we have created the MyFirstAssembly.vb file with MyFirstClrTrigger class, so we need to compile the class to create an assembly. Here I use the VB compiler "vbc.exe," found in .Net Framework library. Use the following DOS command for creating assembly.

Listing 3 (compile the .VB file)

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>vbc
/t:library /out:F:\CLR_Trigger_Test\CLR_Assembly\MyFirstAssembly
F:\CLR_Trigger_Test\MyFirstClrTrigger.vb

This command creates a DLL called MyFirstAssembly.dll at out path F:\CLR_Trigger_Test\CLR_Assembly\.

3. Enabling CLR environment

By default, the CLR functionality is trued off in SQL Server; so we need to enable it. Use the following T-SQL codes to enable the CLR functionality.

Listing 4 (enable CLR action)

------ Enabling CLR action in database -------
sp_configure 'clr enabled', 1
Go
RECONFIGURE with Override
Go

4. Registering the assembly

Our next step is to create an assembly in the Database, based on the compiled DLL (MyFirstAssembly.dll). The following T-SQL codes are useful regarding this objective.

Listing 5 (registering assembly)

------ Registering an Assembly -------
Create Assembly UserAssembly
From 'F:\CLR_Trigger_Test\CLR_Assembly\MyFirstAssembly.dll'
With Permission_Set=Safe
Go

For more information about this check my previous article.

5. Creating CLR Trigger

Now we will create an extended Trigger using CREATE Trigger statement of T-SQL. There is a new clause named EXTERNAL NAME in SQL Server 2005, which allows us to reference a method of the Registered assembly. By doing so we set the triggering action of our Trigger using that managed code method of the assembly.

Listing 6 (creating a Trigger)

------ Creating a DML trigger in CLR -------
Create Trigger tr_User_CheckRole
on UserTable
For INSERT
AS
External Name UserAssembly.MyFirstClrTrigger.checkUserRole
Go

Here we use the checkUserRole() shared method of our MyFirstClrTrigger class of the registered assembly UserAssembly.

Checking the triggering action

Finally, we need to check our Trigger. Let us test the trigger using the same INSERT Statement that we used before.

Listing 7 (Insert into UserTable)

----- Checking the DB objects.----------------
INSERT UserTable (User_Name, Type) VALUES ('James', 'ADMIN')
Go
----- Output Message -----
May 2 2007 8:54AM
Hello !!! You have the Admin role.
We can use e-mail codes here to inform.

The output is the result of two triggering actions; one is of T-SQL type and another one is of CLR type. Both are attached to the UserTable for the same type of action (i.e. after Insert statement). The following image is the output result window of SQL Server Management studio.


Conclusion

Now we have our CLR Trigger in our database with source code stored externally. Hence, externally stored code objects can be more secure than the previous one created by T-SQL. Also now we have the power and expressiveness of .NET language for our database objects. Above all, now we have a brief idea about all types of Triggers supported by SQL Server 2005.

How to Create a SQL Server 2005 Database Snapshot

Microsoft SQL Server administrators has a new feature with the SQL Server 2005 Edition which is database snapshots. Database snapshots are new with SQL Server 2005 and only available with Microsoft SQL Server 2005 Enterprise Edition. SQL Server database administration and development with SQL Server especially for sql reporting will be easier with ms sql server database snapshots.

A database snapshot can be described as a photo of a database. Snapshots are read-only so we can easily say that their main usage areas cover mostly the reporting applications. Database snaphots are one to one the same of the original database at the time when the snapshot is created. After a snapshot is created it stays as a static view of the original database at the time of snapshot creation. A snapshot can persists until it is dropped. Also multiple snapshots can be created with different names of a database called as source database.

In order to create a database snapshot, CREATE DATABASE statement is used.



How to Create a SQL Server 2005 Database Snapshot

Microsoft SQL Server administrators has a new feature with the SQL Server 2005 Edition which is database snapshots. Database snapshots are new with SQL Server 2005 and only available with Microsoft SQL Server 2005 Enterprise Edition. SQL Server database administration and development with SQL Server especially for sql reporting will be easier with ms sql server database snapshots.

A database snapshot can be described as a photo of a database. Snapshots are read-only so we can easily say that their main usage areas cover mostly the reporting applications. Database snaphots are one to one the same of the original database at the time when the snapshot is created. After a snapshot is created it stays as a static view of the original database at the time of snapshot creation. A snapshot can persists until it is dropped. Also multiple snapshots can be created with different names of a database called as source database.

In order to create a database snapshot, CREATE DATABASE statement is used.




Here is a sample database snapshot create script of the sample database AdventureWorks

First, in order to place the data files of the snapshot, we should define the physical path of the container folder

We can use the source database's data folder for the same purpose for snapshot database

select physical_name from sys.database_files WHERE file_id = 1

The above query will display where the .mdf file exists. So we can use the same folder. I'm going to use "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\"

Considering the below note from Books On Line;

When you create a database snapshot, the CREATE DATABASE statement cannot reference log files, offline files, restoring files, and defunct files.

I run the below select statement to get a list of data files that I should reference

select name from sys.database_files WHERE type_desc = 'ROWS'

The returned row set only contains the "AdventureWorks_Data"

Now, we are ready to create a snapshot of the source database.
CREATE DATABASE AdventureWorks_SS ON
(
NAME = AdventureWorks_Data,
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.ss'
)
AS SNAPSHOT OF AdventureWorks

After the above command is completed successfully, we can see the snapshot database in the Database Snapshots folder of the SQL Server Instance

In order to delete or remove a database snapshot after it is not needed any more, you can DROP it by using the DROP DATABASE command

DROP DATABASE AdventureWorks_SS

Note that if database snapshot creation fails, the snapshot is in suspect status and it should be deleted or dropped.

I believe, sql server programmers as well as database administrators will benefit more from Microsoft database snapshots. Especially when sql server performance is an issue than sql server snapshots may be a solution especially for distinguishing reporting databases from the transactional production databases. Administrators or developers do not need to backup and restore sql databases for creating a second database for sql server reporting services, for instance.

Update

string conString = GetConnectionString();
SqlConnection con = new SqlConnection(conString);
con.Open();
string sql = "UPDATE HumanResources.Employee SET ContactID = "+custObj.ContactID+", ManagerID = "+custObj.ManagerID+" WHERE EmployeeID = " + ID;
SqlCommand cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
con.Close();

Grid View Cell double click

private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
string ss = dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();

string gs = dataGridView1.Rows[e.RowIndex].Cells["EmployeeID"].Value.ToString();

Class1 test = new Class1();
DataSet ds = test.GetbyID(gs);

textBox1.Text = ds.Tables[0].Rows[0][0].ToString();
textBox2.Text = ds.Tables[0].Rows[0][1].ToString();

}

Data Retival

SqlConnection con = new SqlConnection(conString);

string sql = "Select * from HumanResources.Employee";

SqlCommand cmd = new SqlCommand(sql, con);
ds = new DataSet();
SqlDataAdapter adpt = new SqlDataAdapter(cmd);

adpt.Fill(ds);

Nested Case statements

(CASE
WHEN custid is not NULL THEN custid
WHEN accountid is not null THEN accountid
Else refID
END) AS TranNo

Get Computer Serial number..

With "GetVolumeInformation", the signature looks as follows:

[DllImport("kernel32.dll")]
private static extern long GetVolumeInformation(string PathName, StringBuilder VolumeNameBuffer, UInt32 VolumeNameSize, ref UInt32 VolumeSerialNumber, ref UInt32 MaximumComponentLength, ref UInt32 FileSystemFlags, StringBuilder FileSystemNameBuffer, UInt32 FileSystemNameSize);

Note that some of the parameters that you would think would be of type "int" must be passed as "UInt32", which corresponds to the .NET type "uint", and oftentimes a string must be passed as StringBuilder.

The actual method , with a string return value for convenience, looks like this:

public string GetVolumeSerial(string strDriveLetter)
{
uint serNum = 0;
uint maxCompLen = 0;
StringBuilder VolLabel = new StringBuilder(256); // Label
UInt32 VolFlags = new UInt32();
StringBuilder FSName = new StringBuilder(256); // File System Name
strDriveLetter+=":\\"; // fix up the passed-in drive letter for the API call
long Ret = GetVolumeInformation(strDriveLetter, VolLabel, (UInt32)VolLabel.Capacity, ref serNum, ref maxCompLen, ref VolFlags, FSName, (UInt32)FSName.Capacity);

return Convert.ToString(serNum);
}

Creating Non-Rectangular Forms

This is Taken from Net and this is only for educational purpose only

It is amazingly easy to create forms of any size and shape within Visual C#. You can even do it with VB .NET! For the most part, this can be broken down into the following steps:

1. Create a background image for the form
2. Set background of image to a color for transparency
3. Create a project in Visual Studio .NET
4. Set a couple of property on the form to set the transparency color and to turn off the title bar.

The following walks you through each of these steps.

Creating the Background Image

To create a non-rectangular form, the first step is to create an image that will be used for your background. You can use any program to create your image as long as you can save into a standard format. In general, I use Microsoft Paint since it comes with Windows.

Open Paint and draw an image for your form. I created the image shown in Figure 1 as my non-rectangular form. Be aware, I am a developer and not a graphics artist!

Figure 1: The initial drawing of the non-rectangular form.

It is by using transparency that a non-rectangular form is created. Once you've created your form in your drawing program, you will need to set the remaining background of your image to a color that will be made transparent. In Microsoft Paint, you can select and use a color that you know will not be used elsewhere within your application for forms, buttons, controls, or anything else that will be displayed.

Rather than selecting one of the existing colors, I tend to create a custom color to use. That way I'll know its exact value. To do this in page, select Colors | Edit Colors... to get the Edit Colors Dialog. You can then click on the Define Custom Colors >> button to expand out the dialog so that you can create a custom color. Within the expanded dialog (shown in Figure 2) I use the values of 100 for Red, 0 for Green, and 0 for Blue. This produces a maroon looking color.

Figure 2: Creating a custom color in Microsoft Paint.

Clicking OK saves this color. You can then use it to fill the background of your image. I've done this as shown in Figure 3.

Figure 3: The non-rectangular form with a custom background color.

Now that you have an image to use as your form, save it so that you can use it within Visual Studio .NET

Creating the Visual Studio .NET Project

Open Visual Studio .NET and create a new project. You can create either a C# Windows form or a Visual Basic Windows Form project. I use C# in this article, but the steps are identical for Visual Basic .NET.

Your project will start out with the standard rectangular Windows form. This, however, will be changed.

Select the form and then display the properties. Change the BackgroundImage property the form you created. You may need to then resize the form displayed in the Design editor so that your entire background image is displayed. I've done this in Figure 4 with my form.

Figure 4: The non-rectangular form image within the Design editor of Visual Studio .NET 2005.

In order to drop out the custom color you created, you need to set the TransparencyKey property. To set this property, click on the down arrow next to the entry box. This will display a color dialog with tabs. Select the Custom tab to ensure that the dialog displays similarly to Figure 5.

Figure 5: The Custom tab in the TransparencyKey color selection dialog box.

Right click on one of the empty color boxes in this dialog. This will bring up the dialog for defining a custom color similar to what you saw in Microsoft Paint. Set the colors to the same values you did in Paint (Red 100, Green 0, Blue 0). Click the Add Color button to save this color. This also sets the TransparencyKey property to the color you created.

Now that you've set the background color to be transparent, the remaining step is to get rid of a few items that will make your form looking goofy. Because you are using a non-rectangular form, the Title bar will no longer look good floating above your form. Additionally, your form is not set up to be resized. As such, you should set the FormBorderStyle property to the value of None.

Because you've now turned off the title bar, you will need to make sure your form has a way to exit. For the example I created in the attached zip, I simply dropped a button onto the form and put an Application.End() command into it.

You should now be able to build and run your form as I've done in Figure 6. Of course, the colors looked okay on the screen. The dithering is a result of my capturing the image.

Figure 6: The final form on Windows.

In Conclusion...

In conclusion, creating a custom form is simple. Once you've done the above process, you can drag and drop any controls and features onto your form as you'd like. Creating the rest of a form's functionality does not change from the way you've done it before.

Granted, there are a few things you may want to do such as create non-rectangular buttons, add drag&drop functionality, resize the form, and add other title button functionality back onto the form. This can all be done, but they are tasks for other articles!

How to Convert in SQL

Convert(Data Type, Field Name)

Eg: Convert(int,Qty)

How to find the number of feilds in a table

select COUNT(*) as ColumnCount from information_schema.columns
where table_name = 'table_name'

Visual Studio .NET 2003 Keyboard Shortcuts

http://www.codinghorror.com/blog/files/Visual%20Studio%20.NET%202003%20Keyboard%20Shortcuts.htm

More date formats that does not come standard in SQL Server as part of the CONVERT function

¹ To make the month name in upper case, simply use the UPPER string function.

SQL Statement	Sample Output
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]	99-01-24
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]	1999-01-24
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]	08/99
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]	12/2005
SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]	99/08
SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]	2005/12
SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]	July 04, 2006 1
SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]	Apr 2006 1
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]	February 2006 1
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]	11 September 1
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]	September 11 1
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]	19 February 72 1
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]	11 September 2002 1
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]	12/92
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]	05-2006
SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]	92/12
SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]	2006-05
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]	122506
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]	12252006
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]	240702
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]	24072002
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]	Sep-02 1
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]	Sep-2002 1
SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]	25-Dec-05 1
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]	25-Dec-2005 1

SQL Server Date Formats

SQL Statement	Sample Output
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)	Jan 1 2005 1:29PM 1
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]	11/23/98
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]	11/23/1998
SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]	72.01.01
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]	1972.01.01
SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]	19/02/72
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]	19/02/1972
SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]	25.12.05
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]	25.12.2005
SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]	24-01-98
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]	24-01-1998
SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]	04 Jul 06 1
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]	04 Jul 2006 1
SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]	Jan 24, 98 1
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]	Jan 24, 1998 1
SELECT CONVERT(VARCHAR(8), GETDATE(), 108)	03:24:53
SELECT CONVERT(VARCHAR(26), GETDATE(), 109)	Apr 28 2006 12:32:29:253PM 1
SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]	01-01-06
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]	01-01-2006
SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]	98/11/23
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]	1998/11/23
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]	980124
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]	19980124
SELECT CONVERT(VARCHAR(24), GETDATE(), 113)	28 Apr 2006 00:34:55:190 1
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]	11:34:23:013
SELECT CONVERT(VARCHAR(19), GETDATE(), 120)	1972-01-01 13:42:24
SELECT CONVERT(VARCHAR(23), GETDATE(), 121)	1972-02-19 06:35:24.489
SELECT CONVERT(VARCHAR(23), GETDATE(), 126)	1998-11-23T11:25:43:250
SELECT CONVERT(VARCHAR(26), GETDATE(), 130)	28 Apr 2006 12:39:32:429AM 1
SELECT CONVERT(VARCHAR(25), GETDATE(), 131)	28/04/2006 12:39:32:429AM

Forcing console logins when the server exceeds its terminal services connections

MsTSC /Console