SQL Server Jobs

In this post we are going to set up a SQL Job to monitor daily the sales table and produce statistical data in other table

We have the next SQL schema

And the next Stored proc to generate statistics

     
CREATE PROCEDURE P_GENERATE_STATISTICS
AS BEGIN

    DECLARE @HighestSale INT
    DECLARE @MostSoldProd INT
    DECLARE @SalesAverage MONEY
    
    SET @HighestSale =
        (SELECT TOP 1 IdSale
         FROM B_SALES WHERE CAST(SALEDATE AS DATE) = CAST({fn NOW()} AS DATE)
         ORDER BY Price DESC)
    
    SET @MostSoldProd =
        (SELECT TOP 1 IdProduct
         FROM B_SALES
         WHERE CAST(SALEDATE AS DATE) = CAST({fn NOW()} AS DATE)
         GROUP BY IdProduct
         ORDER BY COUNT(IdProduct) DESC)
        
    SET @SalesAverage =
        (SELECT AVG(Price)
         FROM B_SALES
         WHERE CAST(SALEDATE AS DATE) = CAST({fn NOW()} AS DATE))

    INSERT B_STATISTICS
    VALUES (DEFAULT, @HighestSale, @MostSoldProd, @SalesAverage)

END
GO

The first step to set up the Job is run the New Job Dialog at the SQL Server Agent folder. The main component of a job definition is the unique name that will be used to refer to the job

Go to the Steps tab and click New

In the Advanced tab, stablish the actions to perform in case of success and failure

In the Schedules tab, set up the triggering properties. In our case it's a recurring schedule type, with daily frequency

As a last step, we are going to set up a Notification to be sent when the Job completes with success, writing the execution information to Windows Application Event Log

The result of the Job is the next

The notification sent to Event Log can be checked through Event Viewer

<METHOD SOFTWARE © 2012>

DLLs

As a first post, we are going to treat a theme which can save us a lot of time when we have different applications to execute the same functionality, I´m talking about dynamic linking libraries, which also make our code redistributable to any other language

As a brief example, we will develop a Class Library for calculating Biorhytms

Byorhytm theory states that our lives are affected by three primary cycles: Physical (23 days), Emotional (28 days) and Intellectual (33 days) and the cycle starts at the midpoint at birth

     
	public class Biorhytms
    {
        public static double[] getBiorhytms(DateTime birthDate)
        {
            double[] rhytms = new double[3];

            double daysOnEarth = ((TimeSpan)(DateTime.Now - birthDate)).TotalDays;

            double PhysicalCycle = ((daysOnEarth/23) - ((int)daysOnEarth/23))*23;
            double EmotionalCycle = ((daysOnEarth/28) - ((int)daysOnEarth / 28))*28;
            double IntellectualCycle = ((daysOnEarth/33) - ((int)daysOnEarth/33)*33;

            rhytms.SetValue(PhysicalCycle, 0);
            rhytms.SetValue(EmotionalCycle, 1);
            rhytms.SetValue(IntellectualCycle, 2);

            return rhytms;
        }
    }
    

When we have coded our library, we should sign it with strong name key and register it in the GAC, for this simply drop it into the c:\windows\assembly folder and it will be added automatically to cache, or if your scenario needs more requisites to add your assembly to the global cache, you can use the gacutil.exe utility, type gacutil for display all options

The process is the next

Keep in mind that we have added to the GAC a .NET 4.0 assembly, so it will be displayed in the folder c:\windows\microsoft.net\assembly, instead of c:\windows\assembly . If you want to make it accessible from your .NET tab for comfort, you can distribute your assembly to the next folder, depending on the version of your .NET framework, 4.0 in this case

C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\Profile\Client

The next step is adding our assembly to other project, written for example in Visual Basic.
A DLL is linked to your program at run time, so it will be part of the whole of our source code when we deploy our application

     
Imports Biorhytms.Biorhytms

Module Module1
    Sub Main()

        Dim bios As Double()
        bios = getBiorhytms(New Date(1999, 7, 4))

        For Each bio As Double In bios

            Console.WriteLine(String.Format("{0:#.###}", bio))

        Next

        Console.ReadKey()

    End Sub

End Module

We have exposed the case in you are the distributor of your code packed in a dll, but if you are the library consumer, and want to know about the guts of the dll you are implementing, you can use tools like Reflector .NET, and decompile it for browsing its source code

<METHOD SOFTWARE © 2012>