Adwords PPC India

We Adwords Voucher in India & UK. Price for 100$ adwords Voucher is 8$ or 400 INR.
We also run your ads on Google with 100$ budget only in 20$ or 1000 INR.

IF you need Facebook Coupons & Bing Coupons also then contact us at 9136075049 / 8586875020.

We accept Netbanking / Cash Deposit / LR / Paypal / Moneybookers  etc

Email - ceo@speakmeme.com
Skype- Speakmeme

Permission set of CLR

Introduction

In my previous article named "CLR Integration with MS SQL Server" lot of my reader asking for permission set. But I told them that in my first article related to CLR are an introductory article to grow interest of new developer. In this article I am trying discuss about permission set of CLR in SQL Server. Hope it will be informative and enjoyable.

Assembly are basically managed DLL that contains meta data information With the support of manage code inside the MS SQL server database Microsoft has developed special security settings to protects CLR.

Please note that as because the assemblies are stored in the database they also get backed up and restored with the database.

CREATE ASSEMBLY DDL statement  

 The CREATE ASSEMBLY statement registers a .NET assembly on the DB Server. After registration, the methods contained in the assembly can be referenced by user-defined CLR procedures and functions. CREATE ASSEMBLY uploads an assembly that was previously compiled as a .dll file from managed code for use inside an instance of SQL Server.

As per MSDN

"Creates a managed application module that contains class metadata and managed code as an object in an instance of SQL Server. By referencing this module, common language runtime (CLR) functions, stored procedures, triggers, user-defined aggregates, and user-defined types can be created in the database."

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

The syntax are mentioned bellow.

CREATE ASSEMBLY assembly_name

[ AUTHORIZATION owner_name ]

FROM { <client_assembly_specifier> | <assembly_bits> [ ,...n ] }

[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]

[ ; ]

<client_assembly_specifier> :: =

        '[\\computer_name\]share_name\[path\]manifest_file_name'

  | '[local_path\]manifest_file_name'

<assembly_bits> :: =

{ varbinary_literal | varbinary_expression }

assembly_name

It is the name of the assembly. The name must be unique within the database and a valid identifier.

AUTHORIZATION owner_name

Specifies the name of a user or role as owner of the assembly. owner_name must either be the name of a role of which the current user is a member, or the current user must have IMPERSONATE permission on owner_name. If not specified, ownership is given to the current user.

<client_assembly_specifier>

Specifies the local path or network location where the assembly that is being uploaded is located, and also the manifest file name that corresponds to the assembly. <client_assembly_specifier> can be expressed as a fixed string or an expression evaluating to a fixed string, with variables. CREATE ASSEMBLY does not support loading multimodule assemblies. SQL Server also looks for any dependent assemblies of this assembly in the same location and also uploads them with the same owner as the root level assembly. If these dependent assemblies are not found and they are not already loaded in the current database, CREATE ASSEMBLY fails. If the dependent assemblies are already loaded in the current database, the owner of those assemblies must be the same as the owner of the newly created assembly.

<assembly_bits>

Is the list of binary values that make up the assembly and its dependent assemblies. The first value in the list is considered the root-level assembly. The values corresponding to the dependent assemblies can be supplied in any order. Any values that do not correspond to dependencies of the root assembly are ignored.

varbinary_literal

Is a varbinary literal.

varbinary_expression

Is an expression of type varbinary.

PERMISSION_SET

There is three of permission associated with CREATE ASSEMBLY DDL statement

§  SAFE
this is the default permission set, but it's highly restrictive. With the SAFE setting, we can access only data from a local database to perform computational logic on that data.

§  EXTERNAL_ACCESS
this is the next step in the permissions hierarchy. This setting lets us access external resources such as the file system, Windows Event Viewer, and Web services. This type of resource access isn't possible in SQL Server 2000 and earlier. This permission set also restricts operations such as pointer access that affect the robustness of your assembly.

§  UNSAFE
this permission set assumes full trust of the assembly and thus imposes no "Code Access Security" limitations. This setting is comparable to the way extended stored procedures function—we assume all the code is safe. However, this setting does restrict the creation of unsafe assemblies to users who have sysadmin permissions. Microsoft recommends that you avoid creating unsafe assemblies as much as possible.

Example

--create an assembly

CREATE ASSEMBLY myAssembly

AUTHORIZATION dbo

FROM 'C:\projects\mySolution\UserDefinedFunctions.dll'

WITH PERMISSION_SET = SAFE

GO

SELECT dbo.fnDispalyName()

References

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

Related tropic

http://sqlknowledgebank.blogspot.in/2012/10/clr-integration-with-ms-sql-server.html

Hope you like it.

Posted by: MR. JOYDEEP DAS

CLR Integration with MS SQL Server

Introduction

My friend told me that the CLR Integration is quite complex. I think that nothing is complex if we understand it properly. So in this article I am trying to discuss about MS SQL server CLR integration. I try to demonstrate it as simple as possible by an example that everyone can understand it properly.

Point in focus

1.    What is CLR

2.    Advantage of CLR

3.    How we can make the CLR Integration

What is CLR

The full form of CLR is Common Language Runtime. The CLR Integration of MS SQL Server starts from MS SQL Server version 2005 and later version.

The database objects such as Stored Procedure (SP), Function, Triggers can be coded in CLR. The main purpose of the CLR is to complete the tasks that are not possible or complex to make in T-SQL and it is faster than the T-SQL in many cases.

Now we look what MSDN tell about CLR.

"The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

Managed code uses Code Access Security (CAS), code links, and application domains to prevent assemblies from performing certain operations. SQL Server 2005 uses CAS to help secure the managed code and prevent compromise of the operating system or database server."

Advantage of CLR

    1.    The CLR Integration layer provides some facility that is not directly available from T-SQL.  

          It offers to access .NET framework libraries.

2. Provide the better result in complex logic for intense string operation or string manipulations, cryptography, accessing system resources and file management, etc.
3. CLR are managed codes so ensures type safety and memory management.
   
   
4. It is quite convenient for programmer as CLR Stored Procedures can be written in C#, VB or any other language that the .NET Framework supports.
   
   

How we can make the CLR Integration

In this example we are calling .NET CLR code from MS SQL Server. The demonstration example was implemented in MS Visual Studio 2010 and MS SQL Server 2008 and use .NET frame work 3.5.

I am going to demonstrate this simple example in to 2 steps.

1.    Using .NET Frame work for CLR Function creation

2.    Calling the CLR Function from MS SQL Server 2008

Using .NET Frame work for CLR Function creation

Step-1

Open the MS Visual Studio 2010 and then open the File menu. From File menu open the new projects.

Step-2

From New Projects dialog box, in the Installed Templates select

DatabaseàSQL ServeràVisual C# SQL CLR Database projects.

Step-3

From New Database References provide the SQL Server name in Server name and then choose the Authentication mode. Then choose the Database from Select or Enter database name.

  

Step-4

In Add new Item choose the User-Define Function

Step-5

In Function1.cs or class file I am just going to change the function name to fnDispalyName and change the code within the function. As it is a sample example I am not adding any code here just provide "Hello World".

return new SqlString("Hello World");

Sample code:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString fnDispalyName()

    {

        // Put your code here

        return new SqlString("Hello World");

    }

};

Step-6

Now Build the solutions. If any error came then rectify it and re-build the solution and then Deploy the solution from Build menu.

Step-7

Open the MS SQL Server management studio and type the connect to the TEST_DB and then provide the bellow SQL Script to test.

SELECT dbo.fnDispalyName()

Output:

Hello World

Hope you like it.

Posted by: MR. JOYDEEP DAS

DATETIME in WHERE Clause

Introductions

I saw that a lot of developer makes some common mistake when they are working with DATETIME data type in T-SQL statement. In this article I am trying to gather some common tips and tricks related to DATETIME data type when used in WHERE clause of SQL Query.

Point in focus

1.    Understanding the DATETIME data type

2.    Understanding current session date format

3.    SET DATE FORMAT

4.    Is my input string is supported DATETIME data type.

5.    Best SQL Statement to compare DATETIME data type

6.    Related tropics

Understanding the DATETIME data type

Before using the DATETIME data type in WHERE clause of SQL query, it is better to understand the data type properly.

DATETIME data type contains DATE + TIME of day with fractional seconds that is based on a 24-hour clock.

DATE range: January 1, 1753, through December 31, 9999

TIME range: 00:00:00 through 23:59:59.997

Element ranges:

1.    YYYY is four digits from 1753 through 9999 that represent a year.

2.    MM is two digits, ranging from 01 to 12, that represent a month in the specified year.

3.    DD is two digits, ranging from 01 to 31 depending on the month that represent a day of the specified month.

4.    hh is two digits, ranging from 00 to 23, that represent the hour.

5.    mm is two digits, ranging from 00 to 59, that represent the minute.

6.    ss is two digits, ranging from 00 to 59, that represent the second.

7.    n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.

Character length: 19 positions minimum to 23 maximum

Storage size: 8 bytes

Understanding current session date format

To understand what date format my current session is supported, please run the query mentioned bellow.

SELECT date_format

FROM   sys.dm_exec_sessions

WHERE  session_id=@@SPID

Output:

date_format

mdy

So my current session supports the date format mdy that means Month-Day-Year

SET DATE FORMAT

SET DATEFORMAT is used to Sets the order of the date parts (month/day/year).

For example:

SET DATEFORMAT DMY

GO

SELECT date_format

FROM   sys.dm_exec_sessions

WHERE  session_id=@@SPID

GO

Output:

date_format

dmy

Is my input string is supported DATETIME data type

When we supply some parameter in WHERE condition of SQL server to compare with DATETIME data type, we must check that is the input string parameter supports the DATETIME data type. This is done by ISDATE().Returns 1 if the expression is a valid DATE, TIME, or DATETIME value; otherwise, 0.

For example:

SELECT ISDATE('22-01-2012')

It returns 0 as the default date format is mdy.

Now use this.

SET DATEFORMAT DMY

GO

SELECT ISDATE('22-01-2012')

GO

It returns 1.

Best SQL Statement to compare DATETIME data type

To understand it properly, I am going to demonstrate an example.

Step-1 [ Create the Base Table ]

CREATE TABLE my_DATE

 (ID    INT      NOT NULL IDENTITY(1,1) PRIMARY KEY,

  VAL   DATETIME NOT NULL);

Step-2 [ Inserting some value on it ]

INSERT INTO  my_DATE

       (VAL)

VALUES('01-22-2012 10:00:00'),

      ('01-22-2012 11:00:00'),

      ('01-22-2012 12:00:00'),

      ('01-22-2012 12:02:00'),

      ('01-22-2012 12:05:00'),

      ('01-22-2012 12:07:00') 

Please look at the Insert statement carefully as the value contains DATE + TIME

Step-3 [ Now make a query to retrieve data for date 01-22-2012 ]

If we make this query like this

SELECT * FROM my_DATE WHERE VAL='01-22-2012'

It will not return any thing as TIME part is added with date part. The above query try to retrieve only the date part and 00:00:00 as time part.

We can individually retrieve a date with time by this

SELECT * FROM my_DATE WHERE VAL='01-22-2012 10:00:00'  

Output:

ID            VAL

1              2012-01-22 10:00:00.000

Step-4 [ So How to retrieve data for date 01-22-2012 ]

This can be done by

SELECT * FROM my_DATE WHERE DATEDIFF(day, val, '01-22-2012')=0

Output:

ID            VAL

1              2012-01-22 10:00:00.000

2              2012-01-22 11:00:00.000

3              2012-01-22 12:00:00.000

4              2012-01-22 12:02:00.000

5              2012-01-22 12:05:00.000

6              2012-01-22 12:07:00.000

Related tropics

1.    BETWEEN Clause Vs [ >= AND <= ]

http://sqlknowledgebank.blogspot.in/2012/10/between-clause-vs-and.html

Hope you like it.

Posted by: MR. JOYDEEP DAS

BETWEEN Clause Vs [ >= AND <= ]

Introduction

In this article I am trying to discuss related to BETWEEN clause and >= AND <= comparisons operators and which one is best.

Most of the junior developer has some bad concepts related to BETWEEN clause and they told, not to use it. In this article I am demon staring the BETWEEN clause and how good it is.

What is the Difference

Let's take an example to understand it properly.

Step-1 [ Create the Base Table ]

CREATE TABLE my_TestTab

       (ID    INT      NOT NULL IDENTITY(1,1) PRIMARY KEY,

        VAL   DATETIME NOT NULL);

Step-2 [ Inserting Records ]

INSERT INTO my_TestTab

      (VAL)

VALUES('05-01-2012'),

      ('05-07-2012'),

      ('05-11-2012'),

      ('05-15-2012'),

      ('05-22-2012'),

      ('05-23-2012'),

      ('05-25-2012'),

      ('05-27-2012'),

      ('05-28-2012');

Step-3 [ Now use Between Clause ]

SELECT *

FROM   my_TestTab

WHERE  VAL BETWEEN  '05-01-2012' AND  '05-28-2012';

Output:

ID            VAL

1              2012-05-01 00:00:00.000

2              2012-05-07 00:00:00.000

3              2012-05-11 00:00:00.000

4              2012-05-15 00:00:00.000

5              2012-05-22 00:00:00.000

6              2012-05-23 00:00:00.000

7              2012-05-25 00:00:00.000

8              2012-05-27 00:00:00.000

9              2012-05-28 00:00:00.000

Step-4 [ Now using >= AND <= ]

SELECT *

FROM   my_TestTab

WHERE  VAL >='05-01-2012' AND VAL<='05-28-2012'; 

Output:

ID            VAL

1              2012-05-01 00:00:00.000

2              2012-05-07 00:00:00.000

3              2012-05-11 00:00:00.000

4              2012-05-15 00:00:00.000

5              2012-05-22 00:00:00.000

6              2012-05-23 00:00:00.000

7              2012-05-25 00:00:00.000

8              2012-05-27 00:00:00.000

9              2012-05-28 00:00:00.000

To observe the difference between Step-3 and Step-4 just execute the SQL of Step-3 Again and see the actual execution plan.

SELECT *

FROM   my_TestTab

WHERE  VAL BETWEEN  '05-01-2012' AND  '05-28-2012';

If we see the execution plan we find that

SELECT * FROM  my_TestTab WHERE  [VAL] >= @1 AND [VAL]<=@2

So there is no difference between Step-3 and Step-4. Actually internally the BETWEEN clause is converted to >= and <= logical operators.

Summary

As per me using BETWEEN clause is much easier then the >= and <= operators and it looks great in complex query.

It actually depends on developer and there writing style of T-SQL.

Posted by: MR. JOYDEEP DAS