Why do I see sp_OACreate, sp_OASetProperty in sp_WhoIsActive?

Question

I've seen this happen a few times: sp_WhoIsActive shows various sp_OA% sprocs, with my user name, but I'm certainly not running them directly. Does this reflect some kind of query rewrite process? It seems to be correlated with queries across remote servers.

Answer 1

Did you search your system catalogs for stored procedures that might be calling them? In DBs where you see this:

SELECT OBJECT_NAME([object_id]), definition
   FROM sys.sql_modules
   WHERE definition LIKE '%sp[_]OA%';

To do this for all databases at once:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CHAR(13) + CHAR(10) 
   + 'SELECT ''' + name + ''', o.name, m.definition
   FROM ' + QUOTENAME(name) + '.sys.objects AS o
   INNER JOIN ' + QUOTENAME(name) + '.sys.sql_modules AS m
   ON o.[object_id] = m.[object_id]
   WHERE m.definition LIKE ''%sp[_]OA%'';'
FROM sys.databases;

EXEC sp_executesql @sql;

You might also check job steps.

SELECT j.name, s.step_name, command
   FROM msdb.dbo.sysjobsteps AS s
   INNER JOIN msdb.dbo.sysjobs AS j
   ON s.job_id = j.job_id
   WHERE s.command LIKE '%sp_OA%';

If you don't find anything there, you will want to run a server-side trace because it must be coming from ad hoc batches.