Monday, March 10, 2014

Extended stored procedures (Un-Documented)

xp stands for extended stored procedure which is DLL, programmed using the SQL Server Open Data Services API. These extend the capabilities of SQL Server.

sp_MSgetversion
To get the current version of MSSQL Server.
EXEC master..sp_MSgetversion

Alternative way to retrieve version related detailed information.
SELECT @@version
-----------------------------------

xp_dirtree
To get a list of all the folders.
To get a list of all the folders in the C:\WINDOWS folder, run:
EXEC master..xp_dirtree 'C:\WINDOWS'
-----------------------------------

xp_enum_oledb_providers
This is used to list of all the available OLE DB providers. It returns Provider Name, Parse Name and Provider Description. To get a list of all OLE DB providers for your SQL Server, run:
EXEC master..xp_enum_oledb_providers
-----------------------------------

xp_enumerrorlogs
This  returns the list of all error logs with their last change date. To get the list of error logs, run:
EXEC master..xp_enumerrorlogs
-----------------------------------

xp_enumgroups
This returns the list of Windows NT groups and their description. To get the list of the Windows NT groups, run:
EXEC master..xp_enumgroups
-----------------------------------

xp_fileexist
You can use this extended stored procedure to determine whether a particular file exists on the disk or not. Run following example:
EXEC master..xp_fileexist D:\DSC01568.JPG'
-----------------------------------

xp_fixeddrives
This  returns the list of all hard drives and the amount of free space in Mb for each hard drive. To see the list of drives, run:
EXEC master..xp_fixeddrives
-----------------------------------

xp_getnetname
This returns the WINS name of the SQL Server that you're connected to. To view the name, run:
EXEC master..xp_getnetname
-----------------------------------

xp_readerrorlog
This extended stored procedure returns the content of the errorlog file. To see the text of the errorlog file, run:
EXEC master..xp_readerrorlog
-----------------------------------

xp_regdeletekey
This will delete an entire key from the registry. You should use it very carefully.
Syntax:  EXECUTE xp_regdeletekey [@rootkey=]'rootkey',  [@key=]'key'

e.g.  to delete the key 'SOFTWARE\Test' from 'HKEY_LOCAL_MACHINE', run:
EXEC master..xp_regdeletekey @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\test'
-----------------------------------

xp_regdeletevalue
This will delete a particular value for a key in the registry. You should use it very carefully.
Syntax:
EXECUTE xp_regdeletevalue [@rootkey=]'rootkey',
                          [@key=]'key',
                          [@value_name=]'value_name'

e.g.  to delete the value 'TestValue' for the key 'SOFTWARE\Test' from 'HKEY_LOCAL_MACHINE', run:
EXEC master..xp_regdeletevalue
       @rootkey='HKEY_LOCAL_MACHINE',
       @key='SOFTWARE\Test',
       @value_name='TestValue'
-----------------------------------

xp_regread
This is used to read from the registry.
Syntax:
EXECUTE xp_regread [@rootkey=]'rootkey',
                   [@key=]'key'
                   [, [@value_name=]'value_name']
                   [, [@value=]@value OUTPUT]

e.g., to read into the variable @test from the value 'testvalue' from the key 'SOFTWARE\test' from the 'HKEY_LOCAL_MACHINE', run:

DECLARE @test varchar(20)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\test',
  @value_name='testvalue',
  @value=@test OUTPUT
SELECT @test
-----------------------------------

xp_regwrite
This  is used to write to the registry.
Syntax:
EXECUTE xp_regwrite [@rootkey=]'rootkey',
                    [@key=]'key',
                    [@value_name=]'value_name',
                    [@type=]'type',
                    [@value=]'value'

e.g. to write the variable 'Test' to the 'TestValue' value, key 'SOFTWARE\Test', 'HKEY_LOCAL_MACHINE', run:
EXEC master..xp_regwrite
     @rootkey='HKEY_LOCAL_MACHINE',
     @key='SOFTWARE\Test',
     @value_name='TestValue',
     @type='REG_SZ',
     @value='Test'
-----------------------------------

xp_subdirs
This is used to get the list of folders for the folder named in the xp. In comparison with xp_dirtree, xp_subdirs returns only those directories whose depth = 1.

This is the example:
EXEC master..xp_subdirs 'D:\MULTIMEDIA'




Note: These extended stored procedure tested in MSSQL-2012 but it’s not standard and supported by Microsoft, can be discontinue in upcoming version of MSSQL.

Saturday, February 22, 2014

How can I use ROW_NUMBER() if the column name is not identified?

I got a question from my friend.

Question: How can I use ROW_NUMBER() if the column name is not identified?
Answer: There is many ways to achieve it practically.

Example1: 
--Normal Behavior Row number generation
SELECT ROW_NUMBER() OVER(ORDER BY @@ROWCOUNT), * FROM myTable

Example2: 
--Normal Behavior Row number generation
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), * FROM myTable

Example3:  
--Random Behavior Row number generation
SELECT ROW_NUMBER() OVER(ORDER BY NEWID()), * FROM myTable

Saturday, February 1, 2014

WHILE LOOP with CONTINUE and BREAK keywords

WHILE loop enables to execute any SQL statement or block of statement repeatedly as long as the specified condition is true. The execution can be controlled by two keywords BREAK and CONTINUE by putting inside the loop as per user's need.

BREAK keyword: This keyword is use to stop execution if user's specified condition got true.
CONTINUE keyword: This keyword is use to skip execution of below statement. User can specify condition as per need.

Example1: WHILE loop

DECLARE @val INT = 100;
WHILE (@val <= 500)
BEGIN
       PRINT @val
       SET @val = @val + 100

END

ResultSet:
100
200
300
400
500

Example2: WHILE loop with BREAK

DECLARE @val INT = 100;
WHILE (@val <= 500)
BEGIN
       PRINT @val
       SET @val = @val + 100
       IF @val>350
       BREAK;
END

ResultSet:
100
200
300

Example3: WHILE loop with CONTINUE 

DECLARE @val INT = 100;
WHILE (@val <= 500)
BEGIN
       PRINT @val
       SET @val = @val + 100
       CONTINUE;
       IF @val>350
       BREAK;
END

ResultSet:
100
200
300
400
500


Friday, October 4, 2013

Extract Query Result in A Flat File (BCP command)

Please consider following script given below.

EXEC sp_configure 'show advanced options',1;
RECONFIGURE;

EXEC sp_configure 'Ad Hoc Distributed Queries',1;
RECONFIGURE;

EXEC sp_configure 'xp_cmdshell','1'
RECONFIGURE;
DECLARE @CMD VARCHAR(500) = 'BCP "SELECT col1, col2 FROM [MY_DB].[DBO].[MY_TABLE]" queryout C:\TestData.txt -t, -c -S' + @@ServerName+ ' -U user1 -P pwd@123 -T';


EXEC xp_cmdshell @CMD

Monday, August 12, 2013

Find Dependency of an Object in SQL Server.

If we want to know dependency of Object in MSSQL it is very easy to find out by using following query.
It is very useful because of if we want to modify/delete any object like Function/Procedure then it is necessary to know where actually it is used.

USE TEST_DB
SELECT * FROM SYS.DM_SQL_REFERENCING_ENTITIES ('DBO.MY_FUNCTION', 'OBJECT');




Thursday, May 16, 2013

Sequence in SQL Server 2012

A sequence is a user-defined object which is use to generate a sequence of numeric value based on specified value at the time of declaration. Sequence can generate ascending or descending numeric values at a specified interval and can be recycle (restart). Sequence is independent object that’s why it is not dependent on any table unlike IDENTITY. Sequence is much faster in generating number compared to IDENTITY.


--Creating a sequence
CREATE SEQUENCE [DBO].[seq_mySequence]
AS [BIGINT]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999
GO

--Generating sequence
SELECT NEXT VALUE FOR [DBO].[seq_mySequence] AS Seq
GO



----Alter or Reset sequence----
ALTER SEQUENCE [DBO].[seq_mySequence]
RESTART WITH 101
INCREMENT BY 1
MINVALUE 101
MAXVALUE 999
GO

--Calling sequence----
SELECT NEXT VALUE FOR [DBO].[seq_mySequence] AS Seq
GO



Wednesday, November 28, 2012

TRIGGER in SQL Server (T-SQL)

A trigger is a special kind of a store procedure that fire implicitly when certain event occurs in database server. We can’t invoke triggers explicitly. It can be used to perform administrative tasks in database such as regulating database operation.

Types of TRIGGER:
1. DDL TRIGGER:

DDL triggers execute in response to a variety of Data Definition Language (DDL) events such as CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS statements and certain system stored procedures that perform DDL like action. DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures.
  • DDL triggers do not support INSTEAD OF feature.
  • DDL triggers do not have feature of inserted and deleted tables like DML triggers.
Example :
CREATE TRIGGER PreventDropTable
ON DATABASE            
FOR DROP_TABLE  --DROP_TABLE is DDL Event See link DDL events 
          
AS
PRINT 'Drop table action not allowed.'
ROLLBACK;
GO

Droping table to test trigger action
DROP TABLE [EMPLOYEE]It will not drop table due to triggering action

We can disable or enable trigger
--Disabling database trigger
DISABLE TRIGGER PreventDropTable ON DATABASE;


--Enabling database trigger
ENABLE TRIGGER PreventDropTable ON DATABASE;

2. DML TRIGGER:
DML trigger is performed on DML operation (INSERT, UPDATE, DELETE) to enforce rule and regulation on table. Trigger can prevent incorrect, unauthorized, inconsistent changes to data. 

A). AFTER TRIGGER (FOR TRIGGER)
After triggers run after INSERT, UPDATE or DELETE operation on table. They do not supported for views. 
AFTER TRIGGERS can be classified further into three types as: 

     i). AFTER INSERT Trigger
This trigger is fired after an INSERT on the table. Let’s create the trigger as:-

--Creating a sample table
CREATE TABLE empProfile
(id INT IDENTITY(1,1),
name VARCHAR(50),
gender VARCHAR(6),
create_date DATETIME,
update_date DATETIME
)



--Creating trigger to automatic update log
CREATE TRIGGER setCreateDate ON [dbo].[empProfile]
FOR  INSERT
AS
      --AUTOMATIC populate create_date
      UPDATE empProfile SET create_date = GETDATE() WHERE id IN (SELECT ID FROM inserted)
      PRINT 'AFTER INSERT trigger fired.'
GO

Inserting value in database to test trigger action
INSERT INTO empProfile (name,gender)
SELECT 'MDF', 'MALE'
UNION ALL
SELECT 'ZTE', 'FEMALE'
UNION ALL
SELECT 'John', 'MALE'
UNION ALL
SELECT 'Abc', 'MALE'

SELECT * FROM empProfile

     ii). AFTER UPDATE Trigger
This trigger is fired after an update on the table.
Example:
--Creating trigger to automatic update log
CREATE TRIGGER setUpdateDate ON [dbo].[empProfile]
FOR  UPDATE
AS
--AUTOMATIC populate create_date
UPDATE empProfile SET update_date = GETDATE() WHERE id IN (SELECT ID FROM inserted)
      PRINT 'AFTER Update trigger fired.'
GO
UPDATE empProfile SET name='XYZ' WHERE id=1
UPDATE empProfile SET name='MDF' WHERE id=3

     iii). AFTER DELETE Trigger
This trigger is fired after a delete on the table. 
Example:
CREATE TRIGGER onDeleteTest ON [dbo].[empProfile]
AFTER  DELETE
AS
      PRINT 'AFTER DELETE trigger fired.'
GO

Testing trigger action
DELETE FROM empProfile WHERE ID=2

A). Instead Of Triggers
Instead of trigger override default action of DML operation on table or view. After defining INSTEAD OF trigger if we want to delete a row then Delete operation trying to delete but actually row will not get deleted unless you issue another delete instruction with in trigger.

Advantage of INSTEAD OF trigger:
  • It can enable views that would not be updatable to support updates. A view based on multiple base tables must use an INSTEAD OF trigger to support inserts, updates, and deletes that reference data in more than one table.
  • It can enable you to code logic that can reject DML operation according to condition.
  • It enable to ignoring parts of a batch.

Note: INSTEAD OF DELETE and INSTEAD OF UPDATE trigger cannot be defined on table if a table has foreign key defined by using DELETE or UPDATE cascading action then INSTEAD OF DELETE and INSTEAD OF UPDATE cannot be defined.

INSTEAD OF trigger can be classified further into three types: 

     i). INSTEAD OF INSERT Trigger. 
Example :
CREATE TRIGGER onInsertTest ON [dbo].[empProfile]
INSTEAD OF  INSERT
AS
IF ((SELECT gender FROM inserted)='FEMALE')
RAISERROR('Can not INSERT FEMALE employee',16,1);
ELSE
INSERT INTO empProfile (name, gender)
SELECT name, gender FROM inserted
GO

Testing trigger action
INSERT INTO empProfile (name, gender)
SELECT 'DEVA', 'FEMALE'

     ii). INSTEAD OF UPDATE Trigger. 
Example :
CREATE TRIGGER onUpdateTest ON [dbo].[empProfile]
INSTEAD OF  UPDATE
AS
IF ((SELECT gender FROM inserted)='FEMALE')
RAISERROR('Can not update FEMALE employee',16,1);
ELSE
UPDATE empProfile SET name = (SELECT name FROM inserted) WHERE ID =(SELECT id FROM inserted)
GO

Testing trigger action
UPDATE empProfile set NAME='INDRA' WHERE ID = 2

     iii). INSTEAD OF DELETE Trigger. 
Example :
CREATE TRIGGER onDeleteTest ON [dbo].[empProfile]
INSTEAD OF  DELETE
AS
IF EXISTS( SELECT 1 FROM deleted WHERE gender ='FEMALE')
RAISERROR('Can not delete FEMALE employee',16,1);
ELSE
DELETE FROM empProfile WHERE ID IN (SELECT D.ID FROM deleted D)
GO

Testing trigger action
DELETE FROM empProfile WHERE ID IN (3,4)

3. LOGON TRIGGER
 Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established. 
Example :

CREATE TRIGGER logging_logon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO logHistoryTbl (sys_user, loginTime)
SELECT SYSTEM_USER,GETDATE()
END
GO