Run Query on Multiple Servers

The ability to query multiple servers instantaneously is a welcome addition to SQL 2008. You achieve this by adding the target servers as a group of Registered Servers. Once this has been done, you right click on the group and select ‘New Query’. This action will open a new query window which is ready to execute across all the servers in your registered servers group.

I find this particularly useful after I arrive in the office as the execution of a single script can interrogate the environments contained within your group and give you immediate results.

For example running the following script on a single server returns jobs that failed on the last occasion they were run.

select h.server as [Server],

j.[name] as [Name],

h.message as [Message],

h.run_date as LastRunDate,

h.run_time as LastRunTime

from msdb.dbo.sysjobhistory h

inner join msdb.dbo.sysjobs j on h.job_id = j.job_id

where --j.enabled = 1 and

h.instance_id in

(select max(h.instance_id)

from msdb.dbo.sysjobhistory h group by (h.job_id))

and h.run_status = 0

When executing using this method of querying multiple servers, the results are returned from each server union-ed into one result set. The result set begins with an additional column which lists the ‘Server Name’, before returning the other columns as normal.

Another example is to immediately identify the versions of multiple servers by using the following server properties:

SELECT

SERVERPROPERTY('productversion'),

SERVERPROPERTY ('productlevel'),

SERVERPROPERTY ('edition')

Run this over multiple servers using query window and the results are returned as follows (note the additional Server Name column and how the results appear as subject of a UNION).

Of course the query window which is spread across multiple servers can also be used for any other ad-hoc action you would perform on a single. A very useful feature indeed!

Oh what job was it? A procedure to search your jobs for a particular string

After regularly querying job data from the system tables I decided to write a very simple procedure that could be used to reduce the time taken rewriting the same TSQL multiple times a day.

The following procedure allows you to pass in string of text which well then be used to return any jobs that contain an instance of that particular string. The procedure will interrogate the job name, the job steps, and the contents of the step.

The results are in order of the job name, followed by each of the job steps that contain the string. Please feel free to amend/improve/expand the procedure, but if possible, let me know what you have done (for my own interest!).

/****** Object: StoredProcedure [dbo].[sp_SearchInJobs] Script Date: 12/08/2009 09:35:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[sp_SearchInJobs]

@texttofind nvarchar(255)

/************************************************************************************************************

**************************************************IMPORTANT**************************************************

** Purpose: Searches names and content of jobs and

** job steps for a provided string of text.

** Notes: from http://www.sqlservercentral.com/blogs/james_howards_sql_blog/

** Created By: James Howard

** Created On: 08th December 2009

************************************************************************************************************/

AS

SET NOCOUNT ON

SET @texttofind='%'+@texttofind+'%'

SELECT j.name AS JobName

,case j.enabled

when 0 then 'No'

when 1 then 'Yes'

else 'UNKNOWN'

END as enabled

,js.step_id AS StepNum

,js.step_name AS StepName

,js.subsystem AS Type

,js.command AS StepContent

,js.database_name AS db

,js.last_run_date AS LastRun

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobsteps js

ON j.job_id=js.job_id

WHERE (j.name like @texttofind

OR js.step_name like @texttofind

OR js.command like @texttofind)

ORDER BY j.job_id,js.job_id

GO

Data Collection Job - ‘collection_set_3_upload’ failing…. its a bug!

 

The past couple of hours have seen me frustrated as one of the jobs related to the great Data Collection functionality packed into SQL Server 2008 keeps failing without much error information. Unfortunately the issue appears unanswered on many of the forums in which I searched.

 

The failing job is named ‘collection_set_3_upload’ and it fails on each of the servers on which it has been applied.

 

Following some investigation it seems Microsoft have identified and fixed the bug so can install Cumulative Update 5 for SQL Server 2008 SP1.

 

Information on the problem can be found here:

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/e1aa9f08-2239-4c0b-b902-b19b8693a799

 

While the actual update can be located here:

http://support.microsoft.com/default.aspx/kb/975977/en-us

 

Right, I’m off to update some forums!

Please, Sir, I want some more… orphaned database users.

Permissions on our development servers are considerably less stringent than elsewhere with developers afforded the freedom to restore backup’s ad-hoc. Occasionally (or more accurately, often) there are redundant credentials in the form of Database users not attached to any logins. This can be incredibly irritating.

I wanted to write something that would identify these orphaned users across the server, quickly and accurately, perhaps even have it scheduled for a periodic review.

The following script will interrogate the users in each database on the server. I select all records from sysusers where the record contains a SID and I then use suser_sname(SID), which returns the login name associated with a security identification number (SID). If the returned value is null, the database user is not attached to a valid login.

I then applied various filters to exclude records I do not want to see eg database and application roles.

A temporary table is then populated with data from each database using the sp_MSforeachdb procedure.

/*************************************************

** Purpose: To retun database users (for each db) orphaned from any login.

** Created By: James Howard

** Created On: 03 DEC 09

*************************************************/

--create a temp table to store the results

CREATE TABLE #temp (

DatabaseName NVARCHAR(50),

UserName NVARCHAR(50)

)

--create statement to run on each database

declare @sql nvarchar(500)

SET @sql='select ''?'' as DBName

, name AS UserName

from ?..sysusers

where (sid is not null and sid <> 0x0)

and suser_sname(sid) is null and

(issqlrole <> 1) AND

(isapprole <> 1) AND

(name <> ''INFORMATION_SCHEMA'') AND

(name <> ''guest'') AND

(name <> ''sys'') AND

(name <> ''dbo'') AND

(name <> ''system_function_schema'')

order by name

'

--insert the results from each database to temp table

INSERT INTO #temp

exec SP_MSforeachDB @sql

--return results

SELECT * FROM #temp

DROP TABLE #temp

I can now use this to schedule the retrieval of the results and will be able to immediately identify orphaned users.

Hopefully someone will find this useful. I’d be interested to hear from anyone who has a more efficient method to achieve the same results.

She Sells PowerShell on The Sea Shore

 

The frequency with which I interact with PowerShell 2 is ever increasing, and the product has become a useful method of reducing the time taken by particular tasks. I use PowerShell to rollout the latest versions of our applications, to create and amend both the structure and data of the database. PowerShell scripts can also be used towards monitoring of your SQL Servers.

 

 

You can find a variety of examples on PowerShell.com, the community for PowerShell People (http://powershell.com/cs/media/g/sql-server/default.aspx). I recommend having a look through the scripts as it will give you an idea of the potential of PowerShell.

 

 

Also well worth a read is an excellent overview piece by Robert Sheldon entitled Creating Windows PowerShell scripts to manage SQL Server 2008 instances (http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1344850_mem1,00.html?track=sy41)

 

 

Finally, there is another simple explanatory piece by Muthusamy Anantha Kumar which, as the title suggests, shows you how to ‘Check your SQL Server using Windows Powershell‘. http://www.databasejournal.com/features/mssql/article.php/3752866/Check-your-SQL-Server-using-Windows-PowerShell--Part-1.htm

 

 

I suggest becoming at least familiar with PowerShell, I can see its rise in popularity and increased usage continuing for some time to come.

Intellisense isn’t working? An IntelliSensible list of Common Causes

Having been a fan of SQL Server 2008's Intellisense feature, I was disappointed to find it not working on a new installation of SQL Server 2008 (SP1-10.0.2531.0). I checked the Intellisense options (Tools>Options>Text Editor>Transact-SQL>Intellisense ) and it was showing as being enabled. I reviewed the other common cause of failure which is being connected to a database with a lower compatibility level than SQL Server 2008 (10) but this wasn’t the case.

I had a dig around and found the option to refresh Intellisense ’s local cache (edit>Intellisense >Refresh Local Cache), which worked for me and Intellisense sprang into life.

Curiosity persisted though, and I found the following guide to common causes of Intellisense failure. Hope it saves you some time in your investigations…

 

From MSDN:

There are certain cases when the Intellisense options might not work as you expect.

Conditions That Affect Intellisense

The following conditions might affect the behavior of Intellisense :

• There is a code error above the cursor.
  If there is an incomplete statement or other coding error above the location of the insertion point, Intellisense may be unable to parse the code elements, and therefore will not work. You can comment out the applicable code to enable Intellisense again.
• The insertion point is inside a code comment.
  Intellisense options are not available when the insertion point is within a comment in your source file.
• The insertion point is inside a string literal.
  Intellisense options are not available when the insertion point is inside the quotation marks around a string literal, for example:
  WHERE FirstName LIKE 'Patri%|'
• The automatic options are turned off.
  Many Intellisense features work automatically by default, but you can disable any feature.
  Even when automatic statement completion is disabled, you can use an Intellisense feature. For more information, see How to: Modify Intellisense Options.

Database Engine Query Intellisense

The following issues apply to the Database Engine Query Editor:

• The Intellisense functionality of the Database Engine Query Editor does not support all Transact-SQL syntax elements. Parameter help does not support the parameters in some objects, such as extended stored procedures. For more information, see Transact-SQL Syntax Supported by Intellisense .
• Intellisense is only available when the Database Engine Query Editor is connected to an instance of the SQL Server 2008 Database Engine. Intellisense is not available when the Query Editor is connected to earlier versions of the Database Engine.
• Intellisense is turned off in the Database Engine Query Editor when the SQLCMD mode is set on.
• Intellisense functionality does not cover database objects created by another connection after your editor window connected to the database. You can choose one of these three mechanisms to refresh the cache of objects for your editor window:
• Select the Edit menu, select Intellisense , then select Refresh Local Cache.
• Use the CTRL+Shift+R keyboard shortcut.
• Disconnect your editor window from the instance of the Database Engine and reconnect.
• Completion lists do not include database objects for which you do not have permissions. Intellisense flags references to objects for which you do have permissions. For example, if you open a script that is written by someone else, any references to objects for which that person has permissions and you do not are flagged as incorrect.
• Completion lists might stop working if you lose the connection to the instance of the SQL Server Database Engine. Reconnect to the instance.

Common Forum Queries: Calculate age from the date of birth

Some questions reappear on forums more than others, my experience suggests none more so than how one should work out an individual’s age based on their date of birth.

I have been using the following logic within a procedure, as I didn’t require specific months and days as part of the returned result.



DECLARE @DOB as DateTime

Set @DOB='29 Jun 1980' -- Date Of Birth

select year(getdate())

- year(@DOB)

- case when month(@DOB) > month(getdate()) then 1

when month(@DOB) < month(getdate()) then 0 when day(@DOB) > day(getdate()) then 1

else 0

end as age



Well now the requirements have changed with the application now expecting the Month and Day part of the calculated age.

The following tsql will fulfil these new criteria:

DECLARE @date datetime,

@tmpdate datetime,

@years int,

@months int,

@days int

SELECT @date = '6/29/80'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN MONTH(@date) > MONTH(GETDATE()) THEN 1 ELSE 0 END

SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)

SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END

SELECT @tmpdate = DATEADD(m, @months, @tmpdate)

SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years as Yr, @months as Mnths, @days as [Days]



This seems to do the trick but I’d be interested to hear from anyone with a different (better) way of producing the additional columns for the latter query.