Blog

SQL Server, T-SQL create Linked Server with Azure SQL Database
11-Nov-2017
SQL Server, T-SQL create Linked Server with Azure SQL Database

Today, we got a requirement to connect our Azure VM SQL (Report server) instance to access the Azure Database. Let me show you how create linked server for SQL Database on Azure from Azure VM SQL instance.

SQL Server: Use of Recursive Query with example
12-Aug-2015
SQL Server: Use of Recursive Query with example

One of the most benefit of CTE (Common Table Expressions) is that we can create recursive queries with them.  

26-Aug-2014
SQL SERVER: Clone SSIS Package

Recently, I was assigned a job to create many DTSX packages. When I review the packages, I found those packages (Information flow) are moreover same. There is just a difference of Source file connection and destination SQL table which is different in each of the package. It might take couple of hours to create each of the package, but I wanted to get it done in few minutes. To achieve the same, I have looked up the DTSX code (XML) and updated as follows to achieve get it done efficiently and save some time.  

26-Jun-2014
SQL Yoga : Certificate Expired, Mirroring Stopped

Database Administrators might face this issue while certificate gets expired and database mirroring (Non domain database mirroring) gets disconnected as two servers, Primary and Secondary cannot communicate with each other. The error message can be found in Log as follows: Message Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85.'. [CLIENT: xxx.xxx.xxx.xxx] In this post, We are going to review step by step process to resolve this issue by providing renew parameters with certificate configurations.  

19-Apr-2014
SQL SERVER: SSIS – Merge Join Transformation

Now, let’s have a look at functionality of Merge Join Transformation task in SSIS. Benefit of using Merge join is, input datasets can be combination of any two datasets from (Excel file, XML file, OLEDB table, Flat file).Output can be result of INNER, LEFT Outer, or FULL Outer Join on both the datasets. Merge Join Transformation has two inputs and one output. It does not support an error output.  

SQL SERVER T-SQL to read all attributes of the XML
16-Sep-2013
SQL SERVER T-SQL to read all attributes of the XML

We have reviewed how to read XML with SQL server in this Post, and how to read XML with Namespacein this POST. Today I would like to share how to read all attributes of the XML and have result in Table format.

SQLYoga – XML Result sets with SQL Server
14-May-2013
SQLYoga – XML Result sets with SQL Server

Recently, found that people making much complex code to have an XML in application. I have found that they return result set in Table to the application and have applied code to convert that data table to XML by Asp. Net application. When I review the process, I have suggested that why don’t you use the XML feature of the application. They surprised the simple use of it and make their life easy. Let me have sample query to generate XML result sets in SQL Server database.

SQL Yoga – Read XML with T-SQL statement in SQL SERVER
03-May-2013
SQL Yoga – Read XML with T-SQL statement in SQL SERVER

In my previous post, “XML Result sets with SQL Server”, we review to generate result sets in XML from SQL server. Then I got a comment from the team, to also have post to read XML in SQL Server.

SQL SERVER: Limit memory usage of SQL SERVER
22-Nov-2012
SQL SERVER: Limit memory usage of SQL SERVER

Recently, we have found that SQL SERVER is using most of the memory on the server, due to heavy queries are running on my different teams and it freezes the whole server. Due to time constraint team doesn’t have time to optimize those queries, so we have found a way to configure SQL SERVER to not to use all available memory. We have an option to configure how much memory can be used by SQL SERVER as below: