In my most recent adventure, I was tasked with creating a real-time push subscription to our companies distributed publication server. This effort kicked off a lot of ideas, foremost being the “Asynchronous trigger” promoted by the service broker. I used the example found in the back of the book Pro SQL Server 2008 Service Broker. While this asynchronous trigger is asynchronous in respect to “writing to an internal SQL Server object” (i.e. a table) it still takes a bit of time to execute (and thus return the initial transaction). I was disappointed to now associate the word asynchronous to “timely”. Service broker has (and will) bring about many advances in the near-term future, however the speed of submission and true asynchrony will hopefully be revisited sometime soon. The second option for performing the real-time push is to use a synchronous trigger, or an “after update” trigger. The after update trigger would push the record to a common table which would then be polled by a job. Initially I thought there was a way to insert to this common table then execute another trigger on this table to publish the record. However I soon learned that all triggers that call triggers contribute to the same transaction. When one trigger inserts into a table and that table also contains a trigger, this is a “nested trigger”. The reason that nested triggers are a concern is because the first call that performs the initial insert does not return until the last trigger in […]
Continue reading ...
If you landed on this article, then you most likely know that temp tables can cause recompilation. This happens because temp tables are treated just like regular tables by the SQL Server Engine. When the tables (in which underlying queries rely on) change significantly, SQL Server detects this change (using auto update statistics) and marks the dependent queries to be recompiled so the next execution can create an optimal execution plan. A similar sort of phenomenon also happens in temp tables. With SQL Server 2005+, recompilation now occurs at the individual statement level rather than stored procedure level. This is advantageous because when input parameters change in a stored procedure the majority of the stored procedure can still utilize the cached execution plan. This is especially beneficial to stored procedures that contain many statements. In this example, we will create an example showing recompilation, how to detect it, and how to prevent it. First, we’ll create a procedure that selects a parameterized number of rows from a table. We’re going to use a temp table for purposes of this example. [cc lang=”sql”] CREATE PROCEDURE spGetPresident ( @Rows int ) AS BEGIN SET ROWCOUNT @Rows SELECT * INTO #Presidents FROM dbo.Presidents_Tmp SELECT * FROM #Presidents SET ROWCOUNT 0 END [/cc] Next, before we execute the procedure, we will set a trace up to determine if we are recompiling. In management studio, go to tools -> SQL Server Profiler Connect to your server then choose the ‘Events Selection’ Tab. Unselect all the […]
Continue reading ...
The following SQL Server script works to determine if a particular job is currently running. This feature is nice to have if you do not want jobs to step over one another. I frequently will use this script as the first step of the job. If it is currently running, then the step will raise an error and not proceed to the next step. [cc lang=”sql”] sp_configure ‘Ad Hoc Distributed Queries’,1 RECONFIGURE WITH OVERRIDE GO DECLARE @ExecutionStatus INT DECLARE @JobName varchar(100) = ‘%yourjobnamehere%’ SELECT name, current_execution_status, job_id INTO #Jobs FROM OPENROWSET(‘SQLNCLI’, ‘server=(local);trusted_connection=yes’, ‘set fmtonly off exec msdb.dbo.sp_help_job’) SELECT @ExecutionStatus = current_execution_status FROM #Jobs WHERE job_id = (SELECT sj.job_id FROM msdb.dbo.sysjobs sj WHERE name like @JobName) IF @ExecutionStatus != 1 BEGIN RAISERROR (‘Job Is Not Running’, 16, 1 ) END [/cc] The only caveat to this method, which is the only one I know, is that you need to enable ‘Ad Hoc Distributed Queries’. While I don’t consider this a harmful change, it may not be allowed unless you have dbo permissions on the database.
Continue reading ...
Generating a date table on the fly is nice to have for many reasons. The most recent of my adventures required determining when employees did not enter their time in their timesheets. I know I had a tattle tale job, the ironic part is that I ended up getting in trouble the next day for not inputting my hours. So, as for determining the dates when no one entered their time, the only way to determine this, was to cross join against a table of complete sequential dates. Yes it is an expensive query, however it’s the only way I think it can be done. In this exercise, I needed to create a date table containing all dates for the last month. I needed to do this in order to cross join against all the days the employee had logged in order to retrieve a summary of hours. If an employee did not enter any hours for a particular day, then the day itself would not be represented on the report in order to show zero hours. [cc lang=”sql”] — Create our date table using a — Common Table Expression (CTE) DECLARE @EndDate datetime DECLARE @StartDate datetime — 30 days ago SET @StartDate = GETDATE() – 30 — today SET @EndDate = GETDATE() ;WITH Dates(DATEPARAM) AS ( SELECT @StartDate AS datetime UNION ALL SELECT DATEADD(DAY, 1, DATEPARAM) FROM Dates WHERE DATEPARAM < @EndDate ) SELECT * FROM Dates OPTION (MAXRECURSION 10000) [/cc] Now we join this table on the actual […]
Continue reading ...
