How to find SQL Server Version,Edition,Server Name?

How to find sql server version,edition,Server Name?

SQL Server provides a System Defined function SERVERPROPERTY(propertyname) .

By using this function you can find a number of things

Property Name	Description	syntax
Edition	Return SQL Server edition installed on machine.	select ServerProperty('edition')
EditionID	return Edition ID	select ServerProperty('editionid')
InstanceName	Return instance name if it is not default.In case of default return Null.	select ServerProperty('InstanceName')
ProductVersion	return Product version	select ServerProperty('ProductVersion')
BuildClrVersion	return version of the .NET framework Common Language Runtime (CLR)	select ServerProperty('BuildClrVersion')
EngineEdition	return 1 = Desktop 2 = Standard 3 = Enterprise 4 = Express 5 = SQL Azure	select ServerProperty('EngineEdition')
IsClustered	Server instance is configured in a failover cluster. 1 = Clustered. 0 = Not Clustered. NULL = Input is not valid, or an error.	select ServerProperty('IsClustered')
MachineName	Return machine name	select ServerProperty('MachineName')
ResourceLastUpdateDateTime	Returns the date and time that the Resource database was last updated	select ServerProperty('ResourceLastUpdateDateTime')
ProductLevel	Returns Level of the version of SQL Server instance 'RTM' = Original release version 'SPn' = Service pack version 'CTP', = Community Technology Preview version	select ServerProperty('ProductLevel')

When and why you should use 1=1 in WHERE clause?

One Interviewer ask this question during interview of my friend.It is easy to answer but need some specific and to the point answer.So I try to relate it with C#.

 If you don't know  the list of conditions at compile time and it will built at run time, Then you can made a condition with “where 1=1”. and for other conditions that will affect run time, use

and  <condition>.

Example

StringBuilder sb = new StringBuilder();
         sb.Append("SELECT * FROM Products");  // Your query
         sb.Append(" WHERE 1=1"); // always true condition
 
         // append query's where clause
 
         if (catID != 0)
         {
             sb.Append(" AND categoryID= {0}", catID);
         }
         if (minPrice > 0)
         {
             sb.Append(" AND itemPrice >= {0}", minPrice);
         }
 
         SqlCommand cmd = new SqlCommand(sb.ToString(), cnn);
         SqlDataReader dr = cmd.ExecuteReader();
         // your code to read data from dr.

.

.Net Components(CLR) Integration with SQL Server

Microsoft provide a very powerful feature to SQL Server 2005 or later to integrate .Net components with SQL Server that is “CLR Integration”.CLR integration means that you can create database objects like stored procedures, triggers, user-defined types ,functions and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#.By using CLR integration,You can make complex tasks easier.

For Example: In SQL Server Express edition,there is no database mail functionality to create mail profile and sent mail.To achive this you can use CLR function.A good demostration of this example is given by Greg Robidoux.

Sending mail from SQL Server Express edition

In simple word if you are familiar with .Net application development ,then it is very easy to understand concept of CLR Integration.To implement this,You need to create a class library(dll) and register with SQL Server.

By default,CLR integration is disable.So you need to enable it.

1. by using this query

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

OR

2.  By you can set by using Surface Area Configuration Tool
   
   

After enabling CLR integration services,You can create and integrate CLR stored procedure,functions,triggers etc.For this, you need to create CLR code in any .Net compatible language.Visual Studio 2008/2010 already provided template for creating CLR database objects.

Steps to Create CLR database objects:

1. Go to File =>New Project
2. select DataBase=> SQL Server
3. Select Visual C# SQL CLR Database Object

4. On ok,New window appears and asking for database reference.Select relevant one or add new reference.
5. Go to Solution explorer and add new item
   
   
6. Select that you want to work with
7. Add functionality to hellofunction.cs
   
   
   
   

4. Build your project
   
   
5. Right click solution explorer=> Deploy your project(it may ask for server credentials)
6. The below error may occured

Beginning deployment of assembly clrproject.dll to server localhost: TestDb

C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): error : Could not connect to server localhost TestDb : Login failed for user 'sa'.

11. Provide connection string
12. Rebuilt and deploy
13. If your Project .Net Framework and SQL Server Supporting .Net framework not compatible, the below error occurred

The following error might appear if you deploy a SQL CLR project that was built for a version of the .NET Framework that is incompatible with the target instance of SQL Server: "Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification". To resolve this issue, open the properties for the project, and change the .NET Framework version.

Deployment script generated to:D:\learning\clrproject\clrproject\bin\Debug\clrproject.sql

Try to deploy by changing Build Framework in Project Proprties.

14. If the error not resolved till now then it may be you mark your assembly as UNSAFE/External and database is TRUSTWORTHY.So change permission level to Safe.

15. Now Deploy your solution.I hope it will deployed successfully.
16. After successful deployment,The CLR database object will appears in Object Explorer of SQL Server Mgmt. Studio.
    
    
17. Now you can use this function as SQL Server User defined functions.

                     

select dbo.hellofunction()

Output:

                         

18. In the same way you can create CLR stored procedure,types,triggers etc.

Advantages of CLR integration:

1. As you know,T-SQL does not support arrays, collections, for-each loops, bit shifting, or classes.It is specifically designed for direct data access and manipulation in the database.But if you are using Managed code then these can be supported. CLR allows these constructs.
2. CLR has a built in RegEx object.
3. you can consume an external Webservice from a SQLCLR method.
4. Potential for improved performance and scalability:

How to check SQL Server instance Target Framework

When you uses .Net components in SQL server.The component build framework should be compatible with SQL server target framework.If it is not the below error occurred

The following error might appear if you deploy a SQL CLR project that was built for a version of the .NET Framework that is incompatible with the target instance of SQL Server.

So you need to check SQL Server target framework.

select * from sys.dm_clr_properties 

To resolve this error, set target framework of your projects compatible  with this result accordingly.