What is a Trigger?

A trigger is a sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.


The Syntax for creating a trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name 

{BEFORE | AFTER | INSTEAD OF } 

{INSERT [OR] | UPDATE [OR] | DELETE} 

[OF col_name] 

ON table_name 

[REFERENCING OLD AS o NEW AS n] 

[FOR EACH ROW] 

WHEN (condition)  

BEGIN 

--- sql statements  

END; 

how to debug SP in sql server 2008

SP Debugging...

 If you have worked in SQL Server 2000 and were used to using the T-SQL debugger, you might have lamented it’s absence in SSMS in SQL Server 2005. In SQL Server 2005, one can debug the stored procedures using Visual Studio though and that works really great as well. In SQL Server 2008, MSFT has re-introduced the debugger into SSMS. In today’s post, we will pick up a stored procedure in the AdventureWorks database and will go through the features of the debugger – it is nothing different than other debuggers that you have used but it is nice to know that for folks who rely on using only SSMS for their stored procedure work, they do not need to use VS to help debug in an effective manner.

In the BOL, all of the information pertaining to the debugger is under Transact-SQL Debugger. If you have both the client tools and the server running on the same machine, then there is no configuration needed to use the debugger. However, if you are using the client tools from one machine to connect to the database server on another machine, then you need to enable port and program exceptions by using the Windows Firewall Control Panel application on both the client and the server.  We will look into those and other security related issues pertaining to the debugger in an upcoming post.  In this post, we just want to go through an example of the debugger.

So, let’s prepare an execution script for executing a stored procedure in the Adventure Works database: 

declare @x datetime
select @x = GETDATE()
exec [uspGetBillOfMaterials] 799, @x

And now, press the debug button as shown in the image below – this will launch the debugger:

Once the debugger launches, you will see the different debug options at the top – Step Into, Step Over, Step Out, breakpoints etc. and at the bottom, you will see two windows – one for the local variables/Watch and the other one a call stack/Breakpoints/Command Window and the Output – these are tabbed interfaces.  Here is the image that shows that:

And now, let’s step into the call to the procedure and you will see the local variable values and in the call stack, you will see the actual call.

This particular procedure only has a CTE which executes and returns back the records so it will exit out as we process through it and will return back the records.  Using the debugger, troubleshooting issues becomes easier especially in the case of larger procedures and the nested procedures which call other procedures or views/functions.

Diffrent Date Time format

SQL Query = SELECT CONVERT(VARCHAR(10),GETDATE(),111)
Result=2011/10/13

SQL Query =  SELECT DISTINCT CONVERT(VARCHAR(11),(date)) FROM user_log
Result=Oct 11 2011

SQL Query =SELECT Convert(char(2),DATEPART(hour, GETDATE())) + ':'+convert(char(2),datepart(mi,getdate())) + ':' + convert(char(2),datepart(s,getdate()))
Result=11:20:36

SQL Query = select substring(convert(varchar(20),date),12,8) as TheTime FROM user_log
Result=10:31PM

Automatic Backup for SQL Server

Automatic SQL Server Backup

I wanted to talk about how to setup an automatic SQL Server backup solution and also how to back it up online with SecureBackup. I am using Microsoft SQL Server 2008 Express but you should be able to use these instructions for all versions of Microsoft SQL Server 2005 and Microsoft SQL Server 2008.

In my case, the database files are located in the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA

As you may already know, you cannot backup the *.MDF and *.LDF database files directly because they are attached to the SQL Server. You could detach them, back them up, and then re-attach them but then your database will be offline for a period of time.

To solve this problem, we first need to create a SQL file that can be used later to automate our database backup. Here’s how we can do that:

CREATE AN SQL FILE

1. Login to Microsoft SQL Server Management Studio.
2. Right click on your database from the “Databases” node.
3. Select “Tasks” -> “Back up…”
4. You are now presented with the “Back up Database” window. In this window you can specify your backup settings. At a minimum, you need to create a destination for your backup. Click the “Add” button and specify a full path name for your database backup in the “File name” field. You will need to use this path name later, so let’s take note of it.
5. You can choose any other backup options that are important to you. Over on the left hand side is “Options”. I would click on that and decide whether you want to use “append to an existing backup set” or “overwrite all existing backup sets”.
6. Once you are finished with your settings, you need to create an SQL file which will be used later for the automated database backup. To create that SQL file, click on the arrow next to “Script” at the top of the window. There will be an option for “Script action to File”. Go ahead and select that option. You will then be able to save your SQL Server Script File. Take note of where you have saved this file as well.

CREATE A BATCH FILE

Now that we have created the SQL file, we need to create a batch file that can be automatically executed by the Task Scheduler. To create this file, do the following:

1. Open Notepad and enter the following:
   sqlcmd -S .\SQLEXPRESS -i "C:\Users\Administrator\Documents\Backup.sql"
   Of course, substitute the pathname with the pathname of where you saved your SQL file.
2. Save this file and take note of where you saved it.
3. Now, using Windows Explorer, go to the directory of where you created this file and rename it to have a .bat filename extension instead of .txt.

SET BATCH FILE TO RUN IN WINDOWS TASK SCHEDULER

We are now ready to add the batch file to the Task Scheduler. Rather than using the Task Scheduler GUI, I prefer to do it on the command line. You can open a command prompt as Administrator or if you are already running as Administrator, you can use Start -> Run. Either way, enter the following:

schtasks /create /sc Daily /st 03:30:00 /tn "MyTask" /tr "cmd /c C:\Users\Administrator\Desktop\Backup.bat"

Again, substitute the pathname above with that of your batch file.

Also, set the time specified to something that is desirable for you. In the above example, we run this task at 3:30 AM.

SET SECUREBACKUP TO BACKUP YOUR DATABASE

The time specified in the Task Scheduler step above is important because you need have SecureBackup automatically backup the database file after the task is complete. Since the backup took place at 3:30, you can setup SecureBackup to run the backup at say, 4:00 AM. This way you will have the most recent backup.

From within the SecureBackup software, you need to create or edit an existing  Backup Job and include the location of the backup file that you chose in step 4 of “CREATE AN SQL FILE” above. If the file isn’t listed and you can’t select it from within SecureBackup, it’s because Windows Task Scheduler has not run your batch file yet. To run the batch file, simply double click on it from within Windows Explorer. It may take anywhere from 10 seconds to, possibly, a few hours depending on how large your database is. Once complete, you will have your database backup file and can select that file from within SecureBackup. You will also be able to schedule the Backup Job around how long it took for that batch file to complete.

You now have an automated backup solution for your Microsoft SQL database complete with a secure online storage solution. I hope this post was helpful to you.

Difference between Stored Procedure and Functions

Stored Procedure:

1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won’t return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP

Functions:

1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration
5) Cannot be used with XML FOR clause
6) Cannot have transaction within function

The SQL SELECT DISTINCT Statement

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.
The DISTINCT keyword can be used to return only distinct (different) values.

SQL SELECT DISTINCT Syntax

SELECT DISTINCT column_name(s) FROM table_name

---

SELECT DISTINCT Example

The "table1" table:

P_Id	LastName	FirstName	Address	City
1	j	Balaji	Electronic city	Bangalore
2	Adhi	Subramani	BommanaHalli	Bangalore
3	Sam	Prasanth	T.Nagar	Chennai

Now we want to select only the distinct values from the column named "City" from the table above.
We use the following SELECT statement:

SELECT DISTINCT City FROM table1

The result-set will look like this:

City

Bangalore

Chennai