Blog Archives

SQL Tip: Change owner on all Agent Jobs – TSQL code

Quick note. After creation of maintenance plans and agent jobs to be careful always replace owner with SA or any other more permanent account. This will reduce the chances of jobs failing due to any issues with accounts. DECLARE @name_holder VARCHAR(1000) DECLARE

Tagged with:
Posted in SQL Tips, tsql

SQL Tip – Comma delimited list from a table column

If you want to extract query result of a column to a variable as Comma separated values list use the following example and modify accordingly. declare @Dblist varchar (max) set @Dblist = ” select @Dblist = case when @Dblist =

Tagged with:
Posted in sql, SQL Server, tsql

SQL Tip: Changing Database names along with file names (logical and physical)

Sometimes you need to rename a databases. Although it seems pretty easy to rename it on the surface. However for long term maintainability it is crucial that the underlying logical files and physical files also reflect the changes. Without the

Tagged with:
Posted in security, sql, SQL Server, tsql

SQL Tip: Read + Write (or any) role on databases, all existing and new ones

Trying to avoid the double hop issue which configuring linked servers, I opted for local users. After setting them up I asked the developer who requested this as to which databases he wanted to be read across the servers /

Tagged with:
Posted in SQL Server, tsql, Uncategorized

SQL Tip: How to find SQL Server installation date and time

Getting right down to business… heres the script SELECT create_date FROM sys.server_principals WHERE sid = 0x010100000000000512000000 And for Eval versions — Evaluation Version Expire Date SELECTcreate_date ASInstallationDate, DATEADD(DD, 180, create_date) AS’Expiry Date’ FROMsys.server_principals WHEREsid = 0x010100000000000512000000 GO Reference: Pinal Dave (http://blog.sqlauthority.com)

Tagged with:
Posted in sql, SQL Server, tsql, Uncategorized

Usage data for SSRS reports to decide on reports to consider for re-engineering & retiring

You have a SSRS instance on either live or production and you want to find out which reports and most / least used. Luckily SQL Server maintains by default SSRS usage info in audit logs it maintains. Although you can

Tagged with: , , ,
Posted in sql, SQL Server, ssrs, tsql

SQL Server Index and Statistics maintenance: Importance and a good solution with explanation (Ola hallengren’s IndexOptimize)

INTRODUCTION Index and Statistics need to be maintained regularly for optimum performance of a SQL Server database engine. The SQL Server core engine within the Query Optimizer part of it, uses Indexes and statistics to create a query plan to

Tagged with: , ,
Posted in sql, SQL Server, tsql

Running Diagnostic TSQL scripts remotely within and without SCOM- 1

Requirement: To be able to run a generic diagnostic information gathering script remotely via System Center Operations Manager on a SQL Server instance without having to start a separate session of SSMS and / or RDP and executing script. There

Tagged with: , , , ,
Posted in Development, Monitoring, SCOM, sql, SQL Server, tsql

Add local windows Administrators group to SQL Logins

Adding Windows groups (local or domain) based security is best practice as it can help you in a situation where you loose credentials potentially. You want to add Local Windows Administrators group to have SA or any other permissions in

Tagged with: , , , ,
Posted in Active Directory, security, sql, SQL Server, tsql

SSRS reports & URL / query string parameters passing

You have a parametrised report and you want to run it with a simple URL with non default parameter values. Here’s how you do it.Get the default URL of the report in report viewer. http://<ssrs server>/ReportServer/Pages/ReportViewer.aspx?%2fFolder1%2fFolder2%2fReport+File+Name&Param1=Value1&Param1=Value2&Param2=Value3

Tagged with: , , , , ,
Posted in sql, SQL Server, tsql