Saturday 13 August 2016

Connecting to an MS SQL Instance using NodeJS (Fixing ConnectionError: Port for SQLServer not found in ServerName & Failed to connect to localhost:undefined in 15000ms)

After few debates and discussions on new technologies in the market and how to adapt to them, during the weekend I thought of exploring NodeJS and it applications. Since I spent most of my time designing data-centric applications at office, as the first step I thought of connecting to MS SQL Server using NodeJS.
As a newbie to NodeJS, I went through the official documentation and managed to achieve it. However, during the course, I faced many difficulties and by referring to many of the articles, I was managed to resolve all these hurdles.
So I thought of including these problems which I faced and how to overcome them. So it would be a great help to anyone who’s exploring or trying to achieve this more easily.
For this, I will be using SQL Server 2014 on an Instance (.\SQL2K14).

1. First you need to download and install NodeJS. (https://nodejs.org/en/)
2. Install MSSQL package for Node, using the following syntax: (Use windows command prompt)
npm install mssql
3. Create a file named ‘connecttosql.js’ and include the following code:

//We require mssql package for this sample
var sqlcon = require('mssql');

function GetSQLData(queryCallback){        //A callback function is taken as an argument. Once the operation is completed we will be calling this
   
    //SQL Configuration
    var config = {
        user:'###'            //SQL User Id. Please provide a valid user
        ,password:'######'    //SQL Password. Please provide a valid password
        ,server:'localhost\\SQL2K14'   
        /*
            Since my SQL is an instance I am using 'localhost\\Instance'.
            If you have SQL installed on the default instance, it should be server:'localhost'
        */
        ,database: 'master'        //You can use any database here
    }

    var connection = new sqlcon.Connection(config,function(err){
        //In case of an error print the error to the console. You can have your customer error handling
        if (err) console.log(err);
       
        //Query Database
        var dbQuery = new sqlcon.Request(connection);
        //Purposely we are delaying the results
        dbQuery.query("WAITFOR DELAY '00:00:05';SELECT * FROM INFORMATION_SCHEMA.TABLES",function(err,resultset){
            //In case of an error print the error to the console. You can have your customer error handling
            if (err) console.log(err);
           
            //Passing the resultset to the callback function
            queryCallback(resultset);
        })
    });
}

function callback (resultset){
    console.dir('Results returned and printed from the call back function');
    console.dir(resultset);
   
    //Exit the application
    console.dir('Exiting the Application');
    process.exit(0);
}

//Calling the function
console.dir('Calling GetSQLData');
GetSQLData(callback);
/*
    Once we call this function even there's a delay to return the results
    you will see the next line printing 'Waiting for callback function to get invoked...'
*/
console.dir('Waiting for callback function to get invoked...');

I have provided the relevant information as comments.
Before running the program please make sure the following configurations on the SQL server is already done:

1. Enable TCP/IP Protocols in SQL Server Configuration Manager for both server and client.

image

Or else when running it will result an error shown below:
{ [ConnectionError: Port for SQL2K14 not found in ServerName;[YourPCName];InstanceName;SQL2K14;IsClustered;No;Version;12.0.2000.8;np;\\[YourPCName]\pipe\M
SSQL$SQL2K14\sql\query;;]
  name: 'ConnectionError',
  message: 'Port for SQL2K14 not found in ServerName;[YourPCName];InstanceName;SQL2K14;IsClustered;No;Version;12.0.2000.8;np;\\\\[YourPCName]\\pipe\\MSSQL
$SQL2K14\\sql\\query;;',
  code: 'EINSTLOOKUP' }
{ [ConnectionError: Connection is closed.]
  name: 'ConnectionError',
  message: 'Connection is closed.',
  code: 'ECONNCLOSED' }

2. In SQL Server Configuration Manager under SQL Server Services make sure that ‘SQL Server Browser’ service is running.

image

Or else when running the script it will result an error shown below:
{ [ConnectionError: Failed to connect to localhost:undefined in 15000ms]
  name: 'ConnectionError',
  message: 'Failed to connect to localhost:undefined in 15000ms',
  code: 'ETIMEOUT' }
{ [ConnectionError: Connection is closed.]
  name: 'ConnectionError',
  message: 'Connection is closed.',
  code: 'ECONNCLOSED' }

if the aforementioned issues are already addressed execute the above file using the following syntax in a Windows Command Window:
node connecttosql.js
You should get a similar result which is shown below:
'Calling GetSQLData'
'Waiting for callback function to get invoked...'
'Results returned and printed from the call back function'
[ { TABLE_CATALOG: 'master',
    TABLE_SCHEMA: 'dbo',
    TABLE_NAME: 'spt_fallback_db',
    TABLE_TYPE: 'BASE TABLE' },
  { TABLE_CATALOG: 'master',
    TABLE_SCHEMA: 'dbo',
    TABLE_NAME: 'spt_fallback_dev',
    TABLE_TYPE: 'BASE TABLE' },
  { TABLE_CATALOG: 'master',
    TABLE_SCHEMA: 'dbo',
    TABLE_NAME: 'spt_fallback_usg',
    TABLE_TYPE: 'BASE TABLE' },
  { TABLE_CATALOG: 'master',
    TABLE_SCHEMA: 'dbo',
    TABLE_NAME: 'spt_values',
    TABLE_TYPE: 'VIEW' },
  { TABLE_CATALOG: 'master',
    TABLE_SCHEMA: 'dbo',
    TABLE_NAME: 'spt_monitor',
    TABLE_TYPE: 'BASE TABLE' },
  { TABLE_CATALOG: 'master',
    TABLE_SCHEMA: 'dbo',
    TABLE_NAME: 'MSreplication_options',
    TABLE_TYPE: 'BASE TABLE' } ]
'Exiting the Application'

I hope this will help anyone who’s using node to connect to SQL and facing the aforementioned issues.

Wednesday 9 March 2016

String or binary data would be truncated / Arithmetic overflow error converting numeric to data type numeric – Workaround

 

There’s nothing more annoying than getting the error ‘String or binary data would be truncated’ or ‘Arithmetic overflow error converting numeric to data type numeric’, when you need to insert data to a table using a SELECT statement. To make it more interesting, the SQL won’t be providing us the name of the column (or columns) which is causing this issue. (This is due to the SQL architecture on how it executes queries)

To illustrate this I will use a small sample.

Suppose we have a table to store some Customer details:

CREATE TABLE Customer_Data(
    CustId        TINYINT
    ,CustFName    VARCHAR(10)
    ,CustLName    VARCHAR(10)
    ,MaxCredit    NUMERIC(6,2)
)

We will try to insert details to the above table. (In reality the SELECT statement will be very complex and could fetch lots of rows)


INSERT INTO dbo.Customer_Data(
    CustId
    ,CustFName
    ,CustLName
    ,MaxCredit
)

SELECT 1 AS CustId,'John' AS CustFName,'Doe' AS CustLName,1000.00 AS MaxCredit UNION ALL
SELECT 2,'Jane','Doe',1000.00 UNION ALL
SELECT 3,'James','Whitacker Jr.',15000.00

 

This will result the following error:

Msg 8152, Level 16, State 14, Line 48
String or binary data would be truncated.

The statement has been terminated.

The challenge here is to find out actually which columns are having this issue. (As mentioned in reality number of columns could be very large)

However there is a small workaround which we can use to find out the columns which is causing the insertion to fail. You need to do the following in order to find out these columns.

1. First create a table using the same select statement. (You can either create a temporary table or an actual table based on the environment and your need). I will create two tables, one actual and one temporary to illustrate both the options.

SELECT A.*
INTO Temp_Customer_Data
FROM(
    SELECT 1 AS CustId,'John' AS CustFName,'Doe' AS CustLName,1000.00 AS MaxCredit UNION ALL
    SELECT 2,'Jane','Doe',1000.00 UNION ALL
    SELECT 3,'James','Whitacker Jr.',15000.00
) AS A


SELECT A.*
INTO #Customer_Data
FROM(
    SELECT 1 AS CustId,'John' AS CustFName,'Doe' AS CustLName,1000.00 AS MaxCredit UNION ALL
    SELECT 2,'Jane','Doe',1000.00 UNION ALL
    SELECT 3,'James','Whitacker Jr.',15000.00
) AS A

2. Use the following query to identify the issue columns

Actual Table:

;WITH Cte_Source AS (
SELECT
    C.COLUMN_NAME
    ,C.DATA_TYPE
    ,C.CHARACTER_MAXIMUM_LENGTH
    ,C.NUMERIC_PRECISION
    ,C.NUMERIC_SCALE
FROM
    INFORMATION_SCHEMA.TABLES AS T
    JOIN INFORMATION_SCHEMA.COLUMNS AS C
        ON C.TABLE_CATALOG = T.TABLE_CATALOG
        AND C.TABLE_NAME = T.TABLE_NAME
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE
    T.TABLE_NAME = 'Temp_Customer_Data'        -- Source Table
    AND T.TABLE_SCHEMA = 'dbo'
)
,Cte_Destination AS (
SELECT
    C.COLUMN_NAME
    ,C.DATA_TYPE
    ,C.CHARACTER_MAXIMUM_LENGTH
    ,C.NUMERIC_PRECISION
    ,C.NUMERIC_SCALE
FROM
    INFORMATION_SCHEMA.TABLES AS T
    JOIN INFORMATION_SCHEMA.COLUMNS AS C
        ON C.TABLE_CATALOG = T.TABLE_CATALOG
        AND C.TABLE_NAME = T.TABLE_NAME
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE
    T.TABLE_NAME = 'Customer_Data'        -- Destination Table
    AND T.TABLE_SCHEMA = 'dbo'
)
SELECT
    S.COLUMN_NAME
   ,S.DATA_TYPE
   ,S.CHARACTER_MAXIMUM_LENGTH
   ,S.NUMERIC_PRECISION
   ,S.NUMERIC_SCALE

   ,D.COLUMN_NAME
   ,D.DATA_TYPE
   ,D.CHARACTER_MAXIMUM_LENGTH
   ,D.NUMERIC_PRECISION
   ,D.NUMERIC_SCALE
FROM
    Cte_Source AS S
    JOIN Cte_Destination AS D
        ON D.COLUMN_NAME = S.COLUMN_NAME
WHERE
    S.CHARACTER_MAXIMUM_LENGTH > D.CHARACTER_MAXIMUM_LENGTH
    OR S.NUMERIC_PRECISION > D.NUMERIC_PRECISION

 

Temporary Table:

;WITH Cte_Source AS (
SELECT
    C.COLUMN_NAME
    ,C.DATA_TYPE
    ,C.CHARACTER_MAXIMUM_LENGTH
    ,C.NUMERIC_PRECISION
    ,C.NUMERIC_SCALE
FROM
    tempdb.sys.objects so
    JOIN tempdb.INFORMATION_SCHEMA.TABLES AS T
        ON so.name = T.TABLE_NAME
        AND so.[object_id] = OBJECT_ID('tempdb..#Customer_Data')
    JOIN tempdb.INFORMATION_SCHEMA.COLUMNS AS C
        ON C.TABLE_CATALOG = T.TABLE_CATALOG
        AND C.TABLE_NAME = T.TABLE_NAME
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
   
WHERE
    T.TABLE_SCHEMA = 'dbo'
)
,Cte_Destination AS (
SELECT
    C.COLUMN_NAME
    ,C.DATA_TYPE
    ,C.CHARACTER_MAXIMUM_LENGTH
    ,C.NUMERIC_PRECISION
    ,C.NUMERIC_SCALE
FROM
    INFORMATION_SCHEMA.TABLES AS T
    JOIN INFORMATION_SCHEMA.COLUMNS AS C
        ON C.TABLE_CATALOG = T.TABLE_CATALOG
        AND C.TABLE_NAME = T.TABLE_NAME
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE
    T.TABLE_NAME = 'Customer_Data'        -- Destination Table
    AND T.TABLE_SCHEMA = 'dbo'
)
SELECT
    S.COLUMN_NAME
   ,S.DATA_TYPE
   ,S.CHARACTER_MAXIMUM_LENGTH
   ,S.NUMERIC_PRECISION
   ,S.NUMERIC_SCALE

   ,D.COLUMN_NAME
   ,D.DATA_TYPE
   ,D.CHARACTER_MAXIMUM_LENGTH
   ,D.NUMERIC_PRECISION
   ,D.NUMERIC_SCALE
FROM
    Cte_Source AS S
    JOIN Cte_Destination AS D
        ON D.COLUMN_NAME = S.COLUMN_NAME
WHERE
    S.CHARACTER_MAXIMUM_LENGTH > D.CHARACTER_MAXIMUM_LENGTH
    OR S.NUMERIC_PRECISION > D.NUMERIC_PRECISION

 

Both the aforementioned queries will return the following result.

image

The reason to return the above three columns as follows:

1. CustId ==> In our destination table CustId’s data type is TINYINT. Even the select query is returning the results within the boundary, the data type which our insertion query is returning is an INT. So there could be a possibility that there could be large numbers that the destination table could not hold.

2. CustName ==> ‘Whitacker Jr.’ is exceeding the maximum length of 10 which is in the destination table.

3. MaxCredit ==> In the destination table the size of the column is numeric (6,2). Which means it can hold values up to 9999.99. But our insertion query contains a record which consists of 15000.00.

 

Hope this might be helpful to you.

Thursday 3 March 2016

Extracting Date (Excluding Time) from a DateTime value in SQL Server

 

SQL Server supports many data types where we can store the Date along with the time, such as

  • DateTime
  • SmallDateTime
  • DateTimeOffset
  • DateTime2

But in some cases it’s required only to fetch only the date portion from an aforementioned type of field.

There are few ways which we can achieve this task easily using T-SQL.

The easiest of the method is to CAST the DateTime value directly to a DATE type.

SELECT CAST(GETDATE() AS DATE)                                    --==> 2016-03-03

Also you can achieve this by using the CONVERT function providing different styles as per your requirement.

SELECT CONVERT(VARCHAR(24),GETDATE(),101)                        --==> 03/03/2016
SELECT CONVERT(VARCHAR(24),GETDATE(),102)                        --==> 2016.03.03

Please refer to the following URL (https://msdn.microsoft.com/en-sg/library/ms187928.aspx) for more details on the CONVERT function and supported styles:

But if your requirement is to return a DateTime type but having only the date portion you can use the following syntax:

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)        --==> 2016-03-03 00:00:00.000
SELECT CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS DATETIME)      --==> 2016-03-03 00:00:00.000

Tuesday 16 February 2016

Index REBUILD vs. REORGANIZE in SQL SERVER

Couple of days back there was an interesting statement (or rather a question) was brought up by one of the colleagues in the company. Ultimately the initial stement left us with one simple question, which is the difference between Index REBUILD and REORGANIZE and when should be exactly use it.

If you google the aforementioned you can find numerous posts/blogs regarding this. Therefore I will keep things very simple and easier way to understand.

Rebuilding an index or Reorganizing is required when index fragmentation has reached to a considerable percentage. The fragmentation percentage can be identified using the Dynamic Management View - sys.dm_db_index_physical_stats in SQL Server.

You may get more details on the view on the following link: https://msdn.microsoft.com/en-us/library/ms188917.aspx

You can get a list of fragmented indexes using the following query:

SELECT
    OBJECT_NAME(Stat.object_id)
    ,I.name
    ,Stat.index_type_desc
    ,Stat.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) AS Stat
JOIN sys.indexes AS I
        ON Stat.index_id = I.index_id
        AND Stat.object_id = I.object_id
WHERE
    Stat.avg_fragmentation_in_percent > 30

Executing the above query will give you a list of fragmented indexes which has more than 30% fragmentation. ‘index_type_desc’ will give you a hint what sort of index is it. (clustered, non-clustered, heap etc…)

As per the guidlines provided by Microsoft, it’s the best practice to Reorganize the index if the fragmentation is less than or equal to 30% (more than 5%) and Rebuild it if it’s more than 30%

 

Rebuilding Indexes

  • Should perform this if the fragmentation is more than 30%
  • Operation can be done online or offline

Index rebuilding can be done useing the following syntax:

In order to build all the indexes on a specific table:

USE <Database_Name>
GO

ALTER INDEX ALL ON <Table_Name> REBUILD
GO

 

In order to build only a specific index:

USE <Database_Name>
GO

ALTER INDEX <Index_Name> ON <Table_Name> REBUILD
GO

 

Reorganizing Indexes

  • Should perform this if the fragmentation is more than 5% but less than or equal to 30%
  • Operation is always online

Index reorganizing can be done using the following syntx:

In order to reorganize all the indexes on a specific table:

USE <Database_Name>
GO

ALTER INDEX ALL ON <Table_Name> REORGANIZE
GO

In order to reorganize only a specific index:

USE <Database_Name>
GO

ALTER INDEX <Index_Name> ON <Table_Name> REORGANIZE
GO

 

Optionally you can set many attributes during the Rebuild or Re-Organize process (Eg: FILLFACTOR, SORT_IN_TEMPDB etc..). Please check on the following link for more details on the REBUILD options: https://msdn.microsoft.com/en-us/library/ms188388.aspx

How ever REBUILD or REORGANIZE will not have an effect on the HEAP fragmentation. In order to remove the heap fragmentation you can use the followng syntax (*** NOT THE BEST PRACTICE):

USE <Database_Name>
GO

ALTER TABLE <Table_Name> REBUILD
GO

** Eventhough the aforementioned syntax will remove the HEAP fragmentation, it is considered as bad as creating and dropping a clustered index, which will leave behind lots of fragmentation on non clustered indexes. The best practise would be to create a clustered index on the table to remove the HEAP fragmentation. You can find more details on this on the following blog post by Paul. S Randal which he had illustrated nicely.

Wednesday 20 January 2016

Error accessing Oracle Database Objects via Linked Server in SQL Server (The OLE DB provider "OracleOLEDB.Oracle" for linked server reported an error. Access denied.)


In one of the project we are working these days, there was a requirement to fetch some details, directly from the Oracle Database via VIEWS. Initially everything was setup correctly on the Oracle Database & Server side so that we can access the relevant schemas and fetch data without any issue. And once the oracle client is setup and the configurations are correctly setup (“tnsnames.ora”), we were able to fetch the details using .Net Code. And when we checked using the Oracle SQL Developer UI, it was evident that the details were easily fetched.
How ever we faced an issue when we were asked to access and fetch the same set of details from SQL objects using OPENQUERY. Even when we try a simple query such as retrieving “sysdate”, we got an ‘Access Denied’ error.


SELECT FROM OPENQUERY ([LINKED_SERVER], 'SELECT sysdate FROM DUAL')

The OLE DB provider "OracleOLEDB.Oracle" for linked server reported an error. Access denied. Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server "<Linked_Server>"

image_thumb[2]

After spending some time with the configurations on both SQL and Oracle side, we were able to rectify this issue by allowing “Allow inprocess” option in linked server providers in SQL side.

image_thumb[4]

I am sharing this hoping that it would help someone to resolve the similar kind of issue without any hassle.





Wednesday 25 November 2015

Analyzing SQL Server Error Logs / Agent Logs using T-SQL

Even though you design our SQL Scripts with the best methods using best practices, or configure the SQL Server to perform correctly and in the optimized manner, you cannot prevent things going wrong. Luckily SQL Server does a great job on logging all the issues which we will be encountering during the course. Things could have been worse if you need to go through the error log file using only a text editor like the ‘Note Pad’ application (Favorite text editor of majority people). But fortunately SQL Server had provided us some help when you need to dig deep into Error Log.

image

But things could get more complicated if the Error Log contains lots of records and in those records if you require to swim for the issue which you are looking for.

image

Even though it provides you some searching and filtering capabilities, it could still be very challenging and time consuming.

image

image

However we do have another workaround which might come in handy. That’s to query the Error Logs using T-SQL. This can be done using the system procedure ‘sys.sp_readerrorlog’. This consists with few parameters.
USE [master]
GO
/****** Object:  StoredProcedure [sys].[sp_readerrorlog]    Script Date: 24/11/2015 7:11:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER proc [sys].[sp_readerrorlog](
    @p1        int = 0,
    @p2        int = NULL,
    @p3        nvarchar(4000) = NULL,
    @p4        nvarchar(4000) = NULL)
as
begin

    if (not is_srvrolemember(N'securityadmin') = 1)
    begin
       raiserror(15003,-1,-1, N'securityadmin')
       return (1)
    end
    
    if (@p2 is NULL)
        exec sys.xp_readerrorlog @p1
    else 
        exec sys.xp_readerrorlog @p1,@p2,@p3,@p4   
end



  1. @p1 –> This represents the error log which you need to inspect (0 ~ Current | 1 ~ Archive #1 etc..)
  2. @p2 –> Type of the error log which you want to inspect (NULL or 1 ~ Error Log | 2 ~ SQL Agent Log)
  3. @p3 –> 1st Search Parameter (A value which you want to search the contents for)
  4. @p4 –> 2nd Search Parameter (A value which you want to search to further refine the result set)

**Please note: Aforementioned parameters are optional. Therefore if you don’t provide any parameters, it will return the whole contents of the current/active Error Log.

Few Examples

1. This will return all entries in the current Error Log
EXEC sys.xp_readerrorlog @p1 = 0


2. This will return all the entries in the current SQL Agent Log
EXEC sys.xp_readerrorlog @p1 = 0, @p2 = 2


3. This will return all the entries in the current SQL Error log where ever the value ‘CLR’ consist.
EXEC sys.sp_readerrorlog @p1=0, @p2=1, @p3='CLR'


image


4. This will return the entries in the current SQL Error log when the value ‘CLR’ and ‘Framework’ exist.
EXEC sys.sp_readerrorlog @p1=0, @p2=1, @p3='CLR', @p4='Framework'


image

When we execute the stored procedure ‘sys.sp_readerrorlog’, inside it will call an extended stored procedure which will accept 7 parameters, which is  ‘sys.xp_readerrorlog’. The parameter details are as follows:

Param #ParameterDetails
1Log Number0 – Current / 1 – Archive #1 / 2 – Archive #2 etc…
2Log Type1 – SQL Error Log / 2 – SQL Agent Log
3Search Text 1Search term which will be searched on the Text column
4Search Text 2Search term which will be searched on the Text column. **If both search texts are supplied it will return rows containing both texts.
5Start DateLog entries which the ‘Log Date’ is newer than the date provided. (including the date provided)
6End DateLog entries which is between the Start Date and End Date
7Sort OrderASC – Ascending / DESC - Descending

Eg:

EXEC sys.xp_readerrorlog 0,1,N'',N'', '20151124','20151125','DESC'    


I hope this information will help you when you need to query the Error Log in order to troubleshoot an issue.

Sunday 22 November 2015

Enabling Instant File Initialization in SQL Server

Technorati Tags:

Every time the SQL data file or log file expands, it fills the newly allocated (expanded) space with zero. There are few good and bad having this feature (Zeroing the allocated space). One downside of this is, this process will block all the sessions which are writing to these files (data and log files), during this initialization period. One might debate this time period will be very small, but it could be an extremely critical one for some process.

However enabling the Instant File Initialization behavior will make sure that the aforementioned issue will not have any effects when SQL Data file is expanded. But there will be a security risk enabling this feature. When this is enabled, it could be a possibility that unallocated part of the SQL data file could contain information related to previously deleted files (OS related information). There are tools which can examine this data and people who will be having access to the data file (most probably DB Administrators) can easily see the underlying data of these unallocated areas.

Before enabling this we will check how SQL will behave with this option disabled:

DBCC TRACEON(3004,3605,-1)
GO

CREATE DATABASE Sample_Database
GO

EXEC sys.sp_readerrorlog
GO

DROP DATABASE Sample_Database
GO

DBCC TRACEOFF(3004,3605,-1)
GO


 


image


You can clearly see that SQL had an operation to zero out both data and the log file. (I have only highlighted the data file [.mdf])


Enabling Instant File Initialization can be done by adding a ‘SA_MANAGE_VOLUME_NAME’ permission (also know as ‘Perform Volume Maintenance Task’) to the SQL Server Startup account. This can be done as follows:


Open the Local Security Policy Management Application. (execute secpol.msc from the run command or from command line). Double click on the ‘Perform Volume Maintenance Tasks’ which can be found In Security Settings –> Local Policies –> User Rights Assignment.


image 


And add the SQL Server start up account to the list.


image


 


SQL Server startup account can be found in ‘Log On’ Tab in ‘SQL Server <Instance>’ in the Services.


image


 


*** Please note: SQL will check whether this feature is enabled or disable during start up. Therefore we need to restart the service once it’s enabled or disabled. Also we can only enable this for SQL data files only. We cannot enable this for log files.


After restarting the SQL Service we will execute the code snippet which we executed earlier. You will be able to see that SQL is only zeroing the log file now.


image

Thursday 19 November 2015

Capturing and Analyzing deadlocks in SQL Server

No matter how perfect your code and how optimized the way you have written is, facing a situation of getting a deadlock is inevitable. And it would make the situation even more complex when you know that a deadlock had occurred but you have no clue which the victim and perpetrator was.

There are few ways to capture and analyze a deadlock. I will explain two ways in this post.

  1. Enabling trace flags to capture/log deadlock information into SQL Log
  2. Use SQL Profiler to capture a deadlock.

 

Enabling trace flags to capture/log deadlock information into SQL Log

This method will make sure that the deadlock details are logged in the SQL log file (not the transaction log). Even though the details which are captured via this method is textual  it gives you the option of inspecting the details at a later time.

First you need to enable the trace flags using the following syntax:

DBCC TRACEON (1222, 1204, -1)


 

Afterwards we will simulate a deadlock : (in reality you don’t need to do this if you are facing such issue)

First create two sample tables and insert few records which we will use to produce a deadlock.


-- Create a Sample Tables --

CREATE TABLE SampleDeadLock_1(
Item_Code INT
,Item_Desc VARCHAR(100)
,Qty INT
)

CREATE TABLE SampleDeadLock_2(
Item_Code INT
,Item_Desc VARCHAR(100)
,Qty INT
)

-- Insert couple of records to each table --

INSERT INTO dbo.SampleDeadLock_1(
Item_Code
,Item_Desc
,Qty
)
VALUES
(1,'CPU', 10)
,(2, '20 GB - Hard Disk', 20)

INSERT INTO dbo.SampleDeadLock_2(
Item_Code
,Item_Desc
,Qty
)
VALUES
(3,'Monitor', 15)
,(4, 'Keyboard & MOuse', 25)


 


Now open two query windows in SQL Server Management Studio (Window 1 & Window 2) and paste the following code to ‘Window 1’


BEGIN TRAN

UPDATE dbo.SampleDeadLock_1 SET Qty = 100 WHERE Item_Code = 1

WAITFOR DELAY '00:00:30'

UPDATE dbo.SampleDeadLock_2 SET Qty = 200 WHERE Item_Code = 3


And paste the following code to ‘Window 2’


BEGIN TRAN

WAITFOR DELAY '00:00:10'

UPDATE dbo.SampleDeadLock_2 SET Qty = 300 WHERE Item_Code = 3
UPDATE dbo.SampleDeadLock_1 SET Qty = 400 WHERE Item_Code = 1


 


Afterwards execute the code in Window 1 and immediately execute the code in Window 2. After few seconds (30+) you will see a deadlock error message in Window 1.


image


And when you checked on the ERRORLOG file, you will be able to find out the relevant details which relates to the deadlock. (I have highlighted the deadlock victim details in Red and the perpetrator details in blue)


image


 


Using SQL Profiler to capture a deadlock (Deadlock Graph)


One of the issues we have in the aforementioned method is that we need to look into lots of textual information in order to extract the details which is relevant for the deadlock. In this example it’s somewhat easier since we had chosen a simple situation. But things could get real hard during an actual situation where the processes are bit complex.


In such case (any case), we can use the SQL Profiler to detect the deadlock in a more user friendly manner.


** Please Note: Down-side of this method is you need to have the profiler running during the time that the deadlock had occurred. And this isn’t the best way if you need to troubleshoot in a Production Environment.


How ever for the sake of understanding we will see how we can achieve this.


Open the SQL Profiler and connect to the relevant SQL Server. And in the ‘Trace Properties’ window choose ‘TSQL_Locks’ as the template.


image


And in the ‘Event Selection’ tab, please choose the following options.



  • Deadlock graph
  • Lock:Deadlock
  • Lock:Deadlock Chain

image


Before click on running the profiler, Rollback the transaction which is running from the first example (The one which didn’t become the Deadlock Victim)


Start the profiler by clicking the ‘Run’ button. And once the profiler is running, execute the code on Query Window 1 and afterwards execute the code in Query Window 2.


Once the deadlock occurs the Profiler will display the relevant information in a graphical manner. This will contain all the relevant details which is required in order to troubleshoot the deadlock. And if you hover the cursor over the processes in the graph (shown in ellipses) a tooltip will be displayed along with the statement which has been executed, which caused the deadlock to occur.


image


I hope this will help you in order to troubleshoot a deadlock situation.

Monday 26 October 2015

Elevate permission on a SQL login having read only access to Execute Stored Procedures and to View Database Schemas

Due to an unavoidable situation, it was decided that all the write access to a particular server (SQL Server) was required to be removed immediately. And only few people should be able to access the server with full privileges. How ever this wasn’t sound very friendly to the developers, since they were using this server for various kinds of activities during their development and testing. Once the write access was removed we ran into various kinds of trouble since majority couldn’t view the contents of the database objects such as Stored Procedures, Views etc.

So a requirement came to allow them to access the schemas so that they can view the contents, also to execute the procedures (required when debugging a flow using Profiler) without allowing anyone to change or add any new database objects nor any data updates to existing tables directly.

The easiest workaround was to create a SQL user login with read permission and grant execution access to that login.

1.  Create a SQL Login.

image

 

2. Only keep the ‘public’ server role selected for this SQL Login.

image

 

2. Select ‘db_datareader’ role membership.

image

 

Afterwards if you connect to the SQL Server using the above created login, you can see that only read only operations are allowed. You will be able to see the tables, views (cannot see the underlying SQL Code). Stored procedures will be hidden

 

image

 

image

I have created the following stored procedure in my sample database (AdventureWorks) for testing purpose.

CREATE PROCEDURE ReadOnlyUserProc
AS 
BEGIN
    SELECT GETDATE() AS CurrentDate
END


And when the aforementioned procedure is executed as the ‘readonlyuser’ we will get the following error:



EXEC ReadOnlyUserProc

image


Now lets just provide the necessary access to this login so that it can be use to execute the procedures and to see the schemas of database objects. Use the following code to grant the execution access to the previously created user.



USE AdventureWorks
GO
 
GRANT EXECUTE TO readonlyuser
GO

 


And now when we execute the stored procedure we will get the desired result.



EXEC ReadOnlyUserProc

image


However still we are unable to see the schema of the SQL Objects, which is a part of what we want in this exercise.


image


If you try to generate the script by right clicking the object you will get an error message:


image


We will try to fix that issue as well. Please follow these steps:


1. Right click the login and go to the properties window.


2. Go to the ‘Securables’ tab and check/tick the ‘Grant’ check box for ‘View any definition’.


3. Click ‘OK’


image


Now you can see the definitions of the database objects.


image


image


image













Hope this might be useful to you as well.

Saturday 17 October 2015

Elevate permission on a SQL login having read access to run the SQL Profiler

Sometime it’s necessary to allow SQL logins only to have read access to the respective SQL Servers. But it becomes a necessity to allow the developers to run the SQL Profiler when it comes to troubleshoot or to debug a flow and to find the exact point where the problem is generating. How ever it’s a very common opinion that a login having a read only access cannot run the profiler unless it’s included into the ‘sysadmin’ SQL role.
Well this isn’t entirely true. We could allow a SQL login to start and monitor SQL activity using the SQL Profiler and yet keeping the read only access to the SQL schemas. Follow these steps to achieve this:
1. Connect to the SQL Server and expand the Security->Logins node

image

2. Right click the desired login and go to ‘Properties’ from the context menu

image

3. Go to the ‘Securables’ tab and check/tick the Grant checkbox on ‘Alter Trace’. And click ok to save the details.

image

Now you can run the profiler and monitor details using that SQL Login.

image

image