11-Nov-2017SQL 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.
22-Nov-2017SQL Server Reporting Services, List out all the data sources with Report Name and Path
In Enterprise application, we can have many reports hosted. Sometimes there are few customized reports too. Over period of time, there may be many Data Sources used by the different reports. To find out Report and its data source, we can use the following query: (Execute on ReportServer Database)
26-Jun-2015SQL Server, avoid encoding special character when using FOR XML PATH
From SQL 2005, we are using FOR XML PATH('') for the string concatenation. I have also mentioned the same in very old blog: Generate Comma Separated List with SELECT statement.
20-Apr-2015SQL SERVER: Find Current Location of Log File(s) with File Size of All the Database
As we are mostly engaged in SQL Assignments, we come to the place where we found that there are lots of ldf files which occupies lots of the space on the DISK. To continue my work, I have to make a room on the same DISK. I found that there are lots of log files (LDF) on the disk which occupies more than 1 TB (1024 GB). This is the development server and I found and confirmed that we can have “Recovery Option” as “SIMPLE” too. The tricky part over here is, these databases are already created and Log file names are not same as database name. So, first of all I need to find out the database name to which I can make Recovery Option as Simple. Finally, I got following query which helps me to continue my assignment as follows:
10-Sep-2014SQL SERVER: SSIS - Rename and move files from source folder to destination folder
With SSIS, most of the time we need to process all the files from the folder. Once file has been processed, we need to move the file to archive folder, so we should know that file has been processed and we have the file in archive folder.
18-Sep-2014SQL SERVER: SSIS - Conditional Split Data Flow Transformation Task
Conditional Split transform use split the source row in easy to multiple groups in data flow and which Destination table populated. Lets review the same by reading a Sample text file and separate out the data in two groups.
26-Aug-2014SQL 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-2014SQL 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: