September 2013 Entries

Which DMVs are most helpful? Is there a list of them?
As with most questions asked about monitoring/assessing/admini... MS SQL Server installations, the answer is "it depends".There are many other blogs/resources/etc. online that can get into the specifics--think of this mini-blog as a snake-bit kit! :) Overall, to get to a list of objects that fall under the DMV (Dynamic Management Views) category, type this:-- This will show you the views and the functions of all dmv objectsSELECT name, type, type_descFROM sys.system_objects WHERE name LIKE 'dm_%'-- ......

Posted On Friday, September 27, 2013 7:23 PM | Comments (0)

How can I tell when a stored proc (or function) in sql server was last modified?
You're really looking of the modify_date in the sys.objects table where the type is either a stored proc or a function. This works on a database by database level -- to elevate this search to the server level, you'll have to use the loop sproc sp_msforeachdb SELECT name, create_date, modify_date FROM sys.objectsWHERE type = 'P' -- change 'P' to 'FN' if you're looking for a function and modify_date between cast('2013-09-10' as date) and cast('2013-09-20' as date) ......

Posted On Friday, September 20, 2013 5:22 PM | Comments (0)

How do I change the value of a column in a CASE statement and eliminate NULLs?
In t-sql/mssql, if you have a known NULL value you can issue a statement like:SELECT ISNULL(BirthDate,'1/1/1970 12:00:00 AM') as BirthDateFROM DrviersThe ISNULL function is replacing any nulls found in the BirthDate column to a default value of 1/1/1970.In other situations, you may not have just NULL values, but other possibilities as well. SELECT ISNULL(BirthDate,'1/1/1970 12:00:00 AM') as BirthDate, CASE WHEN BusinessPhone IS NULL OR BusinessPhone = '' THEN 'Unknown' ELSE BusinessPhone END as BusinessPhoneFROM ......

Posted On Thursday, September 19, 2013 9:57 AM | Comments (0)

I get the error "Can't connect to SSISServer" . How do I fix this?
If you're seeing this error, or a similar access denied error you'll need to have your windows account given DCOM permissions. DBAs generally have the ability to add you.If you're a DBA and want to add a developer to be able to connect to the SSIS services to deploy packages here's a quick punch list:Logon to the target SQL Server. Go to Start->Run type lusrmgr.mscIn the Local Users and Groups dialog box, click Groups->Ditributed COM usersOnce in the Distributed COM users properties dialog ......

Posted On Wednesday, September 18, 2013 8:55 AM | Comments (0)

How do I do a SELECT TOP in SQL for DB2 on an iSeries (Power7) ?
This answer depends on what version of SQL you're running. A FETCH clause will work for all. Here's an example:SELECT FLBILC, FLDATE, FLTCK#, FLCST# FROM PRDMETRO.FLHISTAF WHERE FLBILC = 'CMD' ORDER BY FLBILC FETCH FIRST 10 ROW ONLY;The equivalent in T-SQL would be:SELECT TOP 10 FLBILC, FLDATE, FLTCK#, FLCST# FROM PRDMETRO.FLHISTAF WHERE FLBILC = 'CMD'; ......

Posted On Wednesday, September 11, 2013 5:19 PM | Comments (0)