SQL Server – System function to access system table information

SYSTEM FUNCTION System function is basically use to access system tables of SQL Server, system functions starts with @@ sign which sometime refer to as global system variable.  It doesn’t follows variable functionality as it is more similar to function behavior. Some commonly used system functions in sql server are as follows DB_ID(name) and DB_NAME(ID) ...

SQL Server – User defined table valued function and Scalar function

Two types of functions supported by sql server, User defined function and Built-in or system defined function. User defined function You can create your custom function to perform any activity in sql server that known as User defined function. Scalar and table valued function is another type of user defined function. Scalar Functions In Scalar ...

Sql Server – Fix – Cannot open user default database. Login failed for user ‘testlogin’. (Microsoft SQL Server, Error: 4064)

We created login and password for user to access database in read only mode.  But after creating login name when user tries to login, it failed to access the sql server. Error: Cannot open user default database. Login failed. Login failed for user ‘testlogin’. (Microsoft SQL Server, Error: 4064) As error suggests, there is some issue ...

SQL Server – Cursor with simple example

SQL Server – Cursor Sql server can provide the complete result set using cursor that can be process one row at time. Cursor used to retrieve data from tables and stores in variable for any modification. Cursor Syntax Declare @variablename varchar(10) DECLARE [Cursorname] CURSOR FOR [select_query] OPEN [Cursorname] FETCH INTO [@variablename] WHILE @@fetch_status = 0 ...

SQL Server – On Update and on delete casade

Today we are going to understand sql server on update and delete casade properties that updates child table automatically while updating records in parent table. Child table takes reference  of parent table based on primary and FOREIGN KEY concept. Any records inserted in child table should present in parent table otherwise it will throw foreign key error. ...

SQL Server – How to restore database with old full backup and transaction log

It is possible to recover database with any full database and all subsequent transaction log if LOG chain is intact. SQL Server Transaction log chain A continous sequence of log bcakup is called a log chain. Whenever you create database backup for first time its generate log chain or if you change the recovery model of ...

SQL Server 2008 r2 dynamic views

There are two new server related dynamic management views (dmv) that are associated with the sql server fulltext, and sql server agent services in sql server 2008 r2. These dmv are only available in sql server 2008 r2 sp1 and later versions: sys.dm_server_services sys.dm_server_registry sys.dm_server_services The sys.dm_server_services dmv returns information about the sql server and sql ...

How to Use Synonyms in sql server

How to Use Synonyms in sql server First question comes in the mind, what are synonyms and how to use synonyms in sql server? Well in simple words we can say it is alias for referring remote objects which is on different server and location If I want to some data from different server where ...

Script out of Database Users in Sql Server 1

Using below code you can script out of Database Users, Roles, Role-members, Schema Permission, Object Permission Grants         — DECLARE @DatabaseUserName [sysname]  SET NOCOUNT ON DECLARE @errStatement [varchar](8000), @msgStatement [varchar](8000), @DatabaseUserID [smallint], @ServerUserName [sysname], @RoleName [varchar](8000), @MEmberName [varchar](800), @ObjectID [int], @ObjectName [varchar](261) PRINT ‘——— CREATE USERS ————————————–‘ DECLARE _users CURSOR LOCAL FORWARD_ONLY ...

How to unlock sql server login without changing the password

Issue : sql server login account got locked due to that production jobs are started to fail so user created ticket to unlock the login without changing the password. Error output ===========        ========        ======= Error: SQLSTATE = 42000 Microsoft OLE DB Provider for SQL Server Login failed for user ...