September 2013 Entries

As with most questions asked about monitoring/assessing/administering 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 objects
SELECT name, type, type_desc
FROM sys.system_objects
 WHERE name LIKE 'dm_%'
-- and type = 'V'
  ORDER BY name

Here are a few useful DMVs to get started with...

-- Query Plans
-- stats about cached query plans
select * from sys.dm_exec_query_stats

-- identifies query plans that are cached
select * from sys.dm_exec_cached_plans

-- I/O
select * from sys.dm_io_pending_io_requests
select * from sys.dm_io_virtual_file_stats(null,null)  -- //
stats for all data and log files

-- Reqs/Executions

-- lists active user/internal task connects
select * from sys.dm_exec_sessions

-- established connects
select * from sys.dm_exec_connections

-- lists info about all reqs executing
select * from sys.dm_exec_requests

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.objects
WHERE 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) 
In t-sql/mssql, if you have a known NULL value  you can issue a statement like:

  ISNULL(BirthDate,'1/1/1970 12:00:00 AM') as BirthDate
FROM Drviers

The 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. 

 ISNULL(BirthDate,'1/1/1970 12:00:00 AM') as BirthDate,
   WHEN BusinessPhone IS NULL OR BusinessPhone = ''
        THEN 'Unknown'
   ELSE BusinessPhone
 END as BusinessPhone
FROM Drviers

The case statement is evaluating the value found for the BusinessPhone column. If the value is NULL or set to '', then the default value is Unknown, else if the value isn't null or set to '', then retain the value for the  BusinessPhone column found.
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:

  1. Logon to the target SQL Server. Go to Start->Run  type lusrmgr.msc
  2. In the Local Users and Groups dialog box, click Groups->Ditributed COM users
  3. Once in the Distributed COM users properties dialog box, click Add
  4. Select the User click OK
  5. Go back to Start->Run type dcomcnfg
  6. In the Component Services dialog box, drill down to Component Services->Computers->MyComputer
  7. Under DCOM Config, right click MsDtsServer, click Properties
  8. In MsDtsServer100 Properties dialog box, click Security tab
  9. Under Launch and Activate Permissions, go to Customize->Edit
  10. Under Launch Permissions click Add
  11. Under Select Users or Groups, type the user name, click OK
  12. Under Permissions for the user, Allow or check the checkbox for all 4 (local,remote launch/activation)
  13. Click OK. Do steps 11-12 for Access Permissions.

This answer depends on what version of SQL you're running. A FETCH clause will work for all. Here's an example:


The equivalent in T-SQL would be: