Configure dead lock alert system in Sql server [More]

1) Create a Comma Separated String-   IF OBJECT_ID(N'tempdb..#test', N'U') IS NOT NULL DROP TABLE #test   CREATE TABLE #test ( field1 VARCHAR(5), field2 VARCHAR(5) )   INSERT INTO #test VALUES('001','AAA'),('001','BBB'), ('002','CCC'),('003','DDD'), ('004','EEE'),('004','FFF'), ('004','GGG'),('003','H')     DECLARE @Str VARCHAR(MAX) SELECT @Str = COALESCE(@Str+',' ,'') + field2 FROM #test t1 SELECT @Str 2) Create a Comma Separated String with Group By- IF OBJECT_ID(N'tempdb..#test', N'U') IS NOT NULL DROP TABLE #test   CREATE TABLE #test ( field1 VARCHAR(5), field2 VARCHAR(5) )   INSERT INTO #test VALUES('001','AAA'),('001','BBB'), ('002','CCC'),('003','DDD'), ('004','EEE'),('004','FFF'), ('004','GGG'),('003','H')     select field1, substring( ( select(', '+field2) from #test t2 where t1.field1 = t2.field1 order by field1,field2 for xml path('') ),3,1000) as commaSeprated from #test t1 group by field1

In SQL, you can generate Random DateTime,Integer ,Numeric and String value with the use of Checksum function and NEWID() function. Run the following query and analysis the result-   SELECT GETDATE() - (CheckSUM(NEWID()) / 1000000) AS RandomDate, CheckSUM(NEWID()) / 100000 AS RandomINT, CONVERT(Numeric(18,2), (CheckSUM(NEWID()) / 10000.5)) AS RandomNumeric, LEFT(REPLACE(NEWID(),'-',''),10) AS RandomString  

  The DateTime datatype is build upon Float data type so you can easily convert any DateTime in a Float value and simultaneously a float value can also be converted into a DateTime. Using this functionality we can easily get the starting DateTime and Ending DateTime of a givendate.   DECLARE @date DATETIME SET @date = GETDATE() SELECT @date GivenDate ,DATEADD(DAY, DATEDIFF(DAY, 0, @date),0) AS StartingOfDay ,DATEADD(MILLISECOND,-2,DATEADD(DAY, DATEDIFF(DAY, 0, @date), 1)) AS EndingOfDay

T-SQL provide WAITFOR clause to pause the execution of a batch, stored procedure, or transaction till it does not cross the time interval that you specified or a specified time of day is not reached.WAITFOR clause can be used with DELAY or TIME keyword. DELAY- query will wait for the given time interval. SELECT GETDATE() AS [Current Time] WAITFOR DELAY '00:00:05' SELECT GETDATE() AS [Current Time] ----EXECUTE AFTER 5 Second TIME- execution will start again at the given time. DECLARE @Time VARCHAR(8) SET @Time = CONVERT(VARCHAR(8),DATEADD(S,5,GETDATE()),108) SELECT GETDATE() AS [Current Time] WAITFOR TIME @Time SELECT GETDATE() AS [Current Time] -- EXECUTE AT CURRENT TIME + 5 SEC.

Recently I was working on a project in which i need to create a stored procedure that can call the web service directly and the result, return by web service, should be used to update a table. I was thinking how to implement such a proc that can communicate web service, then some one suggest me to create a CLR Stored Procedure. CLR Procedure , CLR Trigger , CLR Function are one of the best way to implement such type of requirement. Such type of procedure are written in .Net  Application. So lets create a demo CLR Stored  Procedure that deduct a given amount from a demo table [EmpRecord] and add an entry in an another table [EmpSaving] through web service call. Here is the script to create both the table in demo database-   CREATE DATABASE Demo GO USE Demo GO CREATE TABLE [dbo].[EmpRecord]( [EmpID] [varchar](6) NOT NULL, [EmpName] [varchar](50) NULL, [ACBalance] [decimal](11, 2) NULL, PRIMARY KEY CLUSTERED ( [EmpID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[EmpSaving]( [EmpID] [varchar](6) NOT NULL, [SavingBalance] [decimal](14, 2) NULL, [PaymentTime] [datetime] NOT NULL ) ON [PRIMARY] GO --Insert Some value in EmpRecord Table INSERT INTO [EmpRecord] SELECT 'A00022','Jon',25000.00 UNION SELECT 'A00003','Sam',65000.00 UNION SELECT 'A00004','Peter',250000.00 UNION SELECT 'B00001','Shelly',62000.00 UNION SELECT 'B00051','Chris',72450.00 UNION SELECT 'C00101','Mike',39000.00 UNION SELECT 'C00329','Ronaldo',56500.00 UNION SELECT 'D00252','David',42000.00 UNION SELECT 'E00896','Mike',94700.00   Let start to create a demo CLR Procedure- Step 1- open Visual Studio and create a SQL Server Project – (File – New – Project – Visual C# - Database - SQL Server Project) and named it DemoCLRProcedure. Step 2- Now you will see a popup indicating you to add database reference. Here you can create connection with your database in your sql server. Step 3- once you establish connection with database, you need to add a .cs (or.vb) file to write your Procedure. In Solution Explore Right Click the project and add a Stored Procedure like this- Step 4- Named it DemoProcToCallWebService.cs and click ok. Now you will find a  partial class with name StoredProcedures and a static method with procedure name (in my case it is DemoProcToCallWebService()). Write your coding here to implement the logic of your procedure and provide the parameter detail in this function to pass information to the procedure. Step 5- you can add your web service reference in your procedure like this- I have already created a demo web service with name SavingAmountDetailService.This webmethod simpaly insert a row in [EmpSaving] table with value of empID and the amount that is passed to it. In Our demo application, the CLRProcedure call this web method to make an entry in [EmpSaving] table. here is the coding of this web method- public class SavingAmountDetailService : System.Web.Services.WebService { [WebMethod] public string makePayment(string EmpID, Decimal amount) { string result = string.Empty; try { SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Demo;Integrated Security=True"); string myCmd = "INSERT INTO [EmpSaving] VALUES ('" + EmpID + "', " + amount + ", '" + System.DateTime.Now.ToString() + "')"; SqlCommand cmd = new SqlCommand(myCmd, con); con.Open(); cmd.ExecuteNonQuery(); con.Close(); result = "Payment Successful"; } catch (Exception ex) { result = "Payment Failed,Exception Message :" + ex.Message; } return result; } } Host this service and add the service reference to your stored procedure. Step 6- Now this is the time to write your business logic of the procedure. In the bellow code, I have deduct some amount from [EmpRecord] table and make an new entry in EmpSaving through web service. public static void DemoProcToCallWebService(string EmpID,decimal amount) { //Object Creation Of Web Service Class SqlPipe sp = SqlContext.Pipe; String msz = string.Empty; int rowCount = 0; SqlConnection conn = new SqlConnection("Context Connection=true"); conn.Open(); SavingAmountDetailService service = new SavingAmountDetailService(); SqlTransaction transaction = conn.BeginTransaction(); SqlCommand cmd = new SqlCommand(); cmd.Transaction = transaction; cmd.Connection = conn; try { cmd.CommandText = "UPDATE [EmpRecord] SET [ACBalance]=[ACBalance]-" + amount + " WHERE [EmpID]='" + EmpID + "' AND [ACBalance]>" + amount; rowCount += cmd.ExecuteNonQuery(); string serviceResponse = string.Empty; if (rowCount > 0) { serviceResponse = service.makePayment(EmpID, amount); } if (serviceResponse.ToUpper().Contains("SUCCESSFUL")) transaction.Commit(); else throw new Exception(serviceResponse); msz = "Amount deliver Successfully "; } catch (Exception ee) { transaction.Rollback(); if (ee.Message == null) msz = "Amount deliver Failed due to insufficient balance or invalid empID"; else msz = "Amount deliver Failed Exception: " + ee.Message; } finally { conn.Close(); sp.Send(msz); } } Step 7- This is the time to compile your procedure. But before compilation set the property to create a XmlSerializers.dll. In solution explore Right click the project and click properties. In build tab you will find a drop down indicating you “genrate Serialization assembly”. Chose “on” instead of auto. And save the project and build it. Now you will find 2 dll files in bin folder with name SqlClassLibrary.dll and SqlClassLibrary.XmlSerializers.dll., you can also create XmlSerializers dll by sgen utility. Just open visual stdio command prompt and pass the command – sgen /a: “ your  SqlClassLibrary.dll path” Step 8- after creating both assembly SqlClassLibrary.dll and SqlClassLibrary.XmlSerializers.dll,we will deploy the project. Here is the script to deploy ClrProcedure on your SQL server,( in the script , you need to change the physical path of dll files)- sp_configure 'clr enabled', 1 GO RECONFIGURE; GO ALTER DATABASE Demo SET TRUSTWORTHY ON GO CREATE ASSEMBLY DemoCLRProcAssembly FROM 'D:\Assembly\SqlClassLibrary.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS GO CREATE ASSEMBLY DemoCLRProcXmlSerializers FROM 'D:\Assembly\SqlClassLibrary.XmlSerializers.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS GO CREATE PROC DemoCLRProc @EmpID [nvarchar](6),@amount Decimal(10,2) AS EXTERNAL NAME [DemoCLRProcAssembly].[StoredProcedures].[DemoProcToCallWebService] GO After running these script, you will find that two assembly are added in Assembly folder with name DemoCLRProcAssembly and DemoCLRProcXmlSerializers and a procedure with name DemoCLRProc is also created in your database.This is your CLR Procedure. Lets Test it- Run the following script to test it EXEC dbo.DemoCLRProc 'E00896',5000.00 You will find the message Amount deliver Successfully. Now check the EmpSaving and you will find a row inserted in it.

In Sql Stored Procedure we can use 2 type of parameters 1) INPUT PARAMETERS 2) OUTPUT PARAMETERS. Input Parameter is only one way parameter while Output parameter is 2-way. Besides these two parameter  Stored Procedure also return an Integer value that is by default 0. This returning value is normally used for debugging purpose to track the executed stage of your procedure .  Here in this article we will see how to retrieve the output parameter value and returning value from procedure in a variable. The following Script will create a Demo Procedure.   CREATE PROC spTestProc @Number INT,@Result VARCHAR(50) OUTPUT AS BEGIN IF @Number % 2 =0 BEGIN SET @Result='Number is Even' IF @Number % 4 =0 BEGIN SET @Result='Number is Even and Divisible By 4' IF @Number % 8 =0 BEGIN SET @Result='Number is Even and Divisible By 8' RETURN 4 END RETURN 3 END RETURN 2 END SET @Result='Number is Odd' RETURN 1 END   The following script indicate how to retrive a return value and output parameter value of a stored procedure-   DECLARE @Number INT =12 DECLARE @Result VARCHAR(50)='' DECLARE @Level INT =0 EXEC @Level = spTestProc @Number,@Result OUTPUT SELECT @Level,@Result   In C#,You can retrieve the return value and output parameter value of a stored procedure like this-   int number = 12; string result = string.Empty; int returnValue; SqlConnection cn = new SqlConnection("Data Source=ServerName;Initial Catalog=DbName;Integrated Security=True"); string query = "spTestProc"; SqlCommand cmd = new SqlCommand(query, cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@Number", number)).Direction = ParameterDirection.Input; cmd.Parameters.Add("@Result",SqlDbType.VarChar,50,result).Direction = ParameterDirection.Output; cmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue; cn.Open(); cmd.ExecuteNonQuery(); returnValue = (int)cmd.Parameters["@ReturnValue"].Value; result = Convert.ToString(cmd.Parameters["@Result"].Value); cn.Close();

@@Identity, Scope_Identity(), and Ident_Current() are used to fetch the last inserted identity value. Most of the cases, we find that these functions return the same value indicating to an identity column. But these function are not really same, they show different behaviour at different scenario [More]

In SQL Server 2005 Cross Apply and Outer Apply operator are used to refer a table-valued function in a correlated sub query.
These operations are similar to SQL Joins operations. SQL joins are used to relate information in different tables whereas Apply clause is used to relate information in a table and a table-valued-function. [More]

Calling SSRS reports from front end in ASP.net can be acheived in few steps: Steps: 1. Create a web application project. 2. Add a report 3. Create dataset and parameters(if needed) in the report 4. Define the layout of the report....... [More]