SQL Server Developer

Finding Connections for A Single Database

During trouble-shooting you may want to find all the connections relevant to one database. You can use sp_who2 but the problem with that is it can return a lot of results for all connections and you need to scroll through entire result set. The following script will return connections relevant to one database with other […]

Verify Whether a SQL Server Agent Job is Running

When I posted an faq about Finding Running SQL Server Agent Jobs I had several few emails and queries on how verify whether a job is running by using only a TSQL script. The below script will verify if the job exists and whether or not it is running. DECLARE @jobname sysname =’Running Job’ — […]

How to unzip a File in SSIS?

For SSIS packages, most of the times you will get zip, rar or tar etc files and need to extract them during the extraction operation in SSIS. Let us see how we can do this directly from SSIS. You can use the Execute Process Task in the Control Flow task. In BIDS Simply drag and […]

SQL Server Reporting Server (SSRS) service is failing to start

After a server reboots the SQL Server Reporting Server (SSRS) service may fail to start and the following errors are logged in the event log. The SQL Server Reporting Services (MSSQLSERVER) service failed to start due to the following error: The service did not respond to the start or control request in a timely fashion. Timeout (30000 milliseconds) waiting […]

How to Import Excel 2007 Version to SQL Server using SSIS

Importing Excel files to SQL Server is a frequent task you need to carry out in your day to day life. Most probably you are using SSIS to import Excel files to SQL Server. If you are asked to import an Excel file, you can use Excel Source from the Data Flow Sources SQL Server […]

Users are able to connect to report manager but not able to connect to report server.

You need to register IIS before installing SQL Server Reporting Services. However, there can be situations where you need to re-register it.  In order to re-register ISI follow the steps below: 1. Open the Command Prompt 2. Change directory as follows: For 32-bit machines change to: <WindowsDir>Microsoft.NETFramework<version number> For 64-bit machines change to: <WindowsDirMicrosoft.NETFramework64<version number> […]

How to Display Server Name or IP Address in a Reporting Services Report

It is possible that users can receive miss-leading data when you are running several reporting severs. You may have a test and a production environment and you do not know from which server a report has been generated. An obvious way you can tackle this issue is by displaying the report server name in the report. […]

How to schedule SSAS Database backup?

SQL Server Analysis Services (SSAS) database backups can be taken using SQL Server Management Studio (SSMS). However, you cannot schedule a SSAS database backup from the SSAS backup option. To achieve this, you can create SQL Server Agent Job and schedule that job as per your requirement. 1.  Right click the Job folder of the SQL […]

SSIS Lookups are Case Sensitive

Lookup’s are a key component in SQL Server Integration Services (SSIS). Let’s say we want to lookup data from the personalookup table. In this example, we assume that we have a record in the personlookup table with the name jim. In our source we have JIM, jim and JiM. Ideally all of these records should match […]

Convert Number to Words in SSRS

A question that is often asked is how to convert a number to words in SSRS. For example 1 => One. Let’s take a look at an example: SELECT Sales.SalesPerson.SalesPersonID AS SalesPerson, SUM(Sales.SalesOrderDetail.OrderQty * Sales.SalesOrderDetail.UnitPrice) AS Amount FROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID INNER JOIN Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonID GROUP BY […]