SQL Server – Simple Example to Create Database Role and grant permission to role

As sql server dba, you come cross number of request from developer asking about access to execute, select , drop and create permission on database. By creating database role and grant them appropriate permission is good example of  standard practice for sql server on database level. You can make changes at database role, it will ...

SQL Server Error 64 A connection was successfully established with the server

We got email from developer saying they are getting error while connecting to database server as shown below A connection was successfully established with the server, but then an error occurred during the login process ( provider: TCP provider, error:0 – the specified network name is no longer available) Microsoft Sql server, Error: 64) Checked ...

SQL Server – How to use WHILE loop with Break and Continue keyword

  Most of developer know, how to use WHILE loop in sql server. You can control WHILE loop using CONTINUE and BREAK keyword. Whenever you need to stop the execution of while loop on certain condition, make use of BREAK keyword. It will exit from WHILE loop and control moved to next statement. CONTINUE keyword will skips ...

SQL Server- Get date and time from current system datetime with example

How to find out current date and time in sql server? Using GETDATE() function it is possible to retrieve system datetime. GETDATE() is system in sql server Syntax : SELECT GETDATE() Examples: SELECT GETDATE() as CurrentDate Example with sql server 2000 and 2005, you can use below gatedate() function to get hours and minutes from ...

Event Notification in sql server to track the change with example

Similar to DDL trigger, event notification also execute in response to transact sql ddl statements as well as sql trace events by sending information about these events to a service broker service. Event notification Event notification can be used to log and review changes or activity occurring on the database, and they respond to the event ...

Monitor sql server services using xp_servicecontrol

In Sql Server, you can use different tools to manage sql server services like sql server configuration manager or windows services. using xp_servicecontrol undocumented stored procedure, you can monitor, start and stop the sql server services. xp_servicecontrol All sql server services can be monitored by using xp_servicecontrol extended stored procedure using SSMS, it will give ...

Change SQL Server Agent Service Account start-up type to Automatic

SQL Server Agent SQL Server Agent helps to perform various administrative tasks such as responding to predefined events, assisting in the automation of routine tasks, and processing alerts. Steps to change the start-up type of SQL Server Agent. SQL Server Configuration manager will help you to change the start-up type of SQL Server Agent to ...

SQL Server – Transparent Data encryption – Steps to enable TDE with Example

In sql server 2008 r2, you can use Transparent Data Encryption (TDE) to encrypt an entire user database. TDE is used to encrypt the data and log files of the database in real time, as well as encrypting backup of the database. TDE can be used while still adhering to laws, regulations, and standards that ...

SQL Server – sp_procoption system stored procedure

Is it possible to run stored procedure whenever sql server starts? Answer is yes. SQL Server provides sp_procoption system procedure that will make you to run stored procedure automatically each time sql server service is started. sp_procoption Using sp_procoption system stored procedure help you to execute stored procedure when sql server service is started. This ...

Ranking function in sql server 2008 with example

Ranking Function You would like to identify and group the information available in the table based on condition, after grouping. You may wish to rank the people in each group based on their category. This result can be obtained by ranking grouped data Type of Ranking function in sql server 2008 Using Rank function, you ...