Putting Data Management in its Proper Place

The Intersection of Technology and Commerce
Business and technology are more interwoven today than at any other time in human history. It’s clear that the data driven nature of modern business is the result of an inexorable fusion of information technology and commerce. To paraphrase Bill Gates, for the first time ever, we can meaningfully discuss neither business nor technology independently of the other. In other words, Information Technology is the glue that binds together the manifold and disparate business functions of nearly every company.

Think about your own company; if you work for a large or mid-sized company, ask yourself what your Sales, Marketing, Purchasing, Logistics, Manufacturing, Accounting, Finance, and Human Resources departments have in common. If your company is successful, then your answer is most likely Information Technology. That’s because IT is the thread of commonality that runs through all the various—and otherwise unique—functional areas of most large and mid-sized companies. And while org charts generally show these departments as vertical silos that converge only at the top, today’s most successful companies are those best able to leverage IT to bring about meaningful convergence throughout their entire organizations—organizational convergence through effective Data Management.

The Progeny of Data and the Supremacy of its Management
The noted author Alvin Toffler famously said, “Data is not information, information is not knowledge, knowledge is not understanding, and understanding is not wisdom.” But organizational convergence allows a company to turn Toffler’s maxim on its head by presenting a “single version—single vision” of the enterprise where every department works from a single version of the company’s truth and toward a single vision of the company’s future. Wisdom comes from understanding, understanding from knowledge, knowledge from information, and information from data. IT departments are tasked with many diverse projects and activities like Systems and Network Engineering, Applications Development and Maintenance, and End User Support; while each of these is important, it is my strongly held opinion that so long as information, knowledge, understanding, and wisdom remain the progeny of data, Data Management will remain the sine qua non of Information Technology.


—James Sizemore

Resync Usernames and Logins after Database Move

Use sp_change_users_login to accomplish this. Here is a screen shot from my workstation with one way to do it (the way I do it). Any other methods or suggestions are welcome.

Finding Dependencies with Sysdepends

We are working on a a database "rethinking" project. As we go about the change process we want to make sure that as we "fix stuff" we don't break other "stuff." Here is a quick query I wrote using sysdepends to help ensure that we don't leave anything orphaned during our database revamping:

select b.name as ObjectName,
c.name as DependantObjectName,
c.xtype as DependantObjectType
from sysdepends a
inner join sysobjects b
on a.id = b.id
inner join sysobjects c
on a.depid = c.id
where b.name = 'YourStoredProcedureNameHere'
group by b.name, c.name, c.xtype

View Extended SQL Agent Job History

Use this query to generate job and job step history; you can see this information in the SSMS GUI, but this query will allow you to better play with (read: analyze) the information. Also, if you need to configure SQL Server 2005 to keep more history than the default of 100 records per job, you can modify the history tab under SQL Agent properties in SSMS. Here is the query:


select b.name as job,
a.step_name as step,
convert(varchar(10), cast(str(a.run_date,8, 0) as datetime), 111) as date,
stuff(stuff(right('000000' + cast (a.run_time as varchar(6 ) ) ,6),5,0,':'),3,0,':') as [time],
a.run_duration as duration_seconds,
a.run_duration / 60 as duration_minutes,
case         a.run_status
when 0 then 'failure'
when 1 then 'success'
when 2 then 'retry'
when 3 then 'cancelled'
when 4 then 'active'
end as disposition,
a.message as details
from         msdb.dbo.sysjobhistory a
inner join         msdb.dbo.sysjobs b
on b.job_id = a.job_id
-- {optionally, to filter by job name} where b.name = 'MyJobName'
order by         b.name,
a.run_date,
a.run_time