Friday, 15 December 2017

The advantage of longRetry parameter in Azure Data Factory

We are using Azure Data Factory to load data from Azure storage blobs to SQL Server on-premises.
During data loading, we faced SQL Error: 10054 , which was due to "Connection Forcibly Closed by Remote Server".

On further analysis, it was due to some server patch activity happening on our SQL Server environment. So, this error is bound to happen once in a while, when server goes down.

The solution for solving this problem is enabling retry mechanism. But, even retry immediately will not solve the issue. So, we have to use longRetry option in the ADF activity policy.

Originally, the activity policy was set as:


"policy": {
          "concurrency": 1,
          "executionPriorityOrder": "OldestFirst",
          "style": "StartOfInterval",
          "retry": 1,      
          "timeout": "23.23:23:23"

We changed the activity policy to:


"policy": {
          "concurrency": 1,
          "executionPriorityOrder": "OldestFirst",
          "style": "StartOfInterval",
          "retry": 1,
          "longRetry": 3,
          "longRetryInterval": "00:20:00",
          "timeout": "23.23:23:23"

The number of times slice will be attempted is: Retry times x No. of Long Retry times
So, in the above case, it will be 3 x 3 = 9 times, the activity slice will try to run.

We are keeping the longRetryInterval as 20 minutes, hoping that the server patch activity will get completed within 20 minutes and retry will be successful.

We can read more about it:

Monday, 9 October 2017

Getting Sql Service Account Information

We can use below query to get information about Sql Service Account information.
SELECT value_data
FROM   sys.dm_server_registry
WHERE  value_name = 'ObjectName'
AND     registry_key = 'HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER' 
For Sql Server Agent, we can use below query.

SELECT value_data
FROM   sys.dm_server_registry
WHERE  value_name = 'ObjectName'
AND     registry_key = 'HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVERAGENT' 

There are other approaches to get this information.

SELECT DSS.servicename,
FROM   sys.dm_server_services AS DSS;  


Wednesday, 4 February 2009

Problems with unicode csv file generated by SSIS

I had the task of generating a CSV file from SSIS. With all the source columns being in nvarchar datatype, I decided to go for unicode csv file. Everything worked fine, except that all the columns in CSV file, when opened in EXCEL 2007 came under a single column. I tried many options like setting Text qualifier as "(double quotes) or '(single quote) and also dropped and recreated flat file destination. But, no change was observed.

When I myself created a csv file in unicode format in Notepad, it was opened fine by Excel 2007 without issues. Finally, after lot of googling, I found out this is due to 0xFF character inserted in the beginning of file in the forum

I had no other choice, but to use Data Conversion transform to convert all the nvarchar, ntext stream into char,text stream before inserting into ANSI flat file destination. Now, it worked without issues. Excel 2007 and SSIS being from Microsoft, and known fact that csv files are usually opened by Excel 2007 and I hoped that unicode csv file from SSIS 2005 would open fine in Excel 2007 and this problem was rather surprise to me.

Hope this problem is resolved in SQL 2008 as simplistic csv files are popular just for the reason that they are easily opened by Excel.


Issues during installing SQL Server 2008

This blog post talks about my problems with installing SQL SERVER 2008 Developer Edition in my Windows XP box.

After two failed attempts, I was finally able to install SQL SERVER 2008 in my dev box.

The below are the erros occurred in my previous attempts in installation and the root cause and corresponding solutions for them.

1. Error: “Condition:Feature dependency condition for action…… There are 10 dependant features. The feature is tested for results: ValidateResult, Result." did not pass as it returned false and true was expected.”

Root Cause: Presence of Visual Studio 2008 in my system.

Solution: The blog post of George P. Alexander Junior talks in detail about Failure of SQL Server 2008 installation due to Visual Studio 2008 presence. After reading this blog, I uninstalled all Visual Studio 2008 components to enable clean install of SQL Server 2008 in my system.

2. Error: “Access Denied”

Root Cause: Rights missing for “Debug Programs” for administrative account installing Sql Server.

Solution: The microsoft forum discussion Solution to Access Denied Error in SQL Server 2008 installation contains the solution. We have to change group policy accordingly.

After correcting the root cause issues, finally I am able to install SQL Server 2008 in my system. I am very happy about it. I think Microsoft would be including these constraints check prior to install of SQL Server 2008.


Hello World !!!

Hello World,

Through this blog, I am going to ruminate over lots of technical stuff around databases.

Hoping to provide quality content to you all.