Read more tips about Management Data Warehouse here.
Using a query similar to the one that we used in the SQL Server Agent Job above, we can create a Custom Collection Set that uses the generic T-SQL Query Collector type.
Sometimes we need even more complex data than simple lists; you might have a time sheet application that lets a user create or edit a weekly time sheet at the client, and then you need to accept and validate an entire week of data when submitted.
A common way to pass large sets of data to a stored procedure is to use an XML or CSV character string.
This can work, but it can be complicated to encode and then decode the strings passed in, and you don’t have a clean way to validate the data as it is passed.
Starting with SQL Server 2008 Microsoft introduced Extended Events that will replace Profiler and server side traces. VARCHAR); callable Statement.register Out Parameter(4, DATE); // execute get DBUSERBy User Id store procedure callable Statement.execute Update(); String user Name = callable String(2); String created By = callable String(3); Date created Date = callable Date(4); CREATE OR REPLACE PROCEDURE get DBUSERBy User Id( p_userid IN DBUSER. Keep in mind that both of these will be deprecated in future versions of the SQL Server.This method utilizes the MERGE INTO dbo._demo_sp_exec_stats STAT USING (SELECT d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) AS proc_name, MAX( d.last_execution_time) as last_execution_time FROM sys.dm_exec_procedure_stats AS d WHERE d.database_id = DB_ID('_Demo DB') GROUP BY d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) ) AS SRC ON STAT.object_id = SRC.object_id WHEN MATCHED AND STAT.last_execution_time You can use this this solution if you already have Management Data Warehouse (MDW) setup in your environment.