A common scenario we come across is the requirement to implement a constraint on a table to enforce a business rule based on another table. Though this can be implemented by means of trigger its not so efficient at times…
Julian to iso date conversion function
Recently I had helped out a friend on logic for converting Julian date value to iso date value in T-SQL. Julian dates are in format CYYDDD where C will denote century YY year and DDD the day of year. So…
Display total rows with pivotting in t-sql
There were couple of occasions of late where people asked me way to display total rows along with detailed data while applying crosstabbing using PIVOT. Though this can be very easily generated in front end languages especially when you’re using…
Retrieve the status of SQL Server services programatically
One of the cool features that’s got introduced from SQL 2008 R2 is ability of getting the running status of various SQL Server services like SQL Server, Full-Text, and SQL Server Agent by means of T-SQL query. This is made possible…
Iterative queries using Common table Expressions
Common Table Expression or CTEs was a very useful feature which got added from SQL 2005. Though they’re mostly associated to recursive query scenario, they can be used for scenarios involving iterative logic also. The…
Advantages of using sys.sql_modules view over syscomments
sys.sql_modules was a new catalog view which got introduced from SQL 2005 onwards. I’ve used it quite a lot in my day to day work. Just thought of sharing some of reasons why its preferred over syscomments view a. Finding object dependencies on…
Clear cache programatically while rendering the report
Last week I was working on a SQL Reporting services report for displaying a report on financial data for one of my client. One of the requirements involved was to include ability to export this report to excel sheet.For this…
Renewed for 3rd time in New Year!
I’m just back from New year vacation and had a good news waiting for me. I got renewed third time in a row as SQL Server MVP! I take this oppurtunity to thank all of you who have been following…
Generating PDF pack reports in SSRS
One of my recent project requirements has prompted me to write this blog. The project requires generating various reports for client using SQL Reporting services tool in PDF format. We created the required reports in PDF format using SSRS and…
DTExec issues running ssis packages in 64 bit
Couple of days before, I was working on an SSIS package doing importing of some data from Excel spreadsheet to a table in SQL database. I built the package and tested it locally successfully. The intention was to create a…