SQL Procedures = More Free Time!
Filed under:
Asset Management Solution
Automation, Cleanup, Database, Scripting
Submitted by cnpalmer75 on 3 December, 2007 - 11:27
After you've slaved over a tough SQL script, wouldn't it be nice just to have it run on its own, without worry and interaction?
This article will walk you through the steps on how to create a stored procedure and schedule it through SQL so that you can go crack open a nice cold one (for those of you that are of age.)
It's Miller Time!
How to Create & Schedule a Stored Procedure
Often times, once you have a great script that runs the way you want it, you want to automate the execution of it. This article will walk you through the steps to create the script as a SQL stored procedure and then create a SQL job to execute the stored procedure.
The following steps describe how to create a stored procedure of a SQL script.
- Make sure you have a SQL script that functions correctly by testing it first using query analyzer. Below is a SQL script that merges computers that have identical serial numbers & MAC addresses.
/* This will merge resource pairs with matching Serial Number + MAC It will not merge resources where the same Serial Number + MAC is found on more than two resources. It is recommended that instances > 2 be investigated and merged manually. */ DECLARE @FromGuid uniqueidentifier DECLARE @ToGuid uniqueidentifier DECLARE @RC int DECLARE merge_Cursor CURSOR FOR SELECT TOP 500 minned.[_ResourceGuid] AS 'FromGuid', maxed.[_ResourceGuid] AS 'ToGuid' FROM ( /* Returns _ResourceGuid of MIN duplicate record by Serial Number + MAC */ SELECT AC.[_ResourceGuid], mindup.[SN_MAC], mindup.[Client Date] FROM Inv_AeX_AC_Identification AC INNER JOIN Inv_AeX_AC_TCPIP IP ON IP.[_ResourceGuid] = AC.[_ResourceGuid] INNER JOIN Inv_AeX_HW_Serial_Number SN ON SN._ResourceGuid = AC.[_ResourceGuid] INNER JOIN ( /* Returns MIN Serial Number + MAC by Client Date */ SELECT SN.[Serial Number] + IP.[MAC Address] as 'SN_MAC', MIN (AC.[Client Date]) as 'Client Date' FROM Inv_AeX_AC_Identification AC INNER JOIN Inv_AeX_AC_TCPIP IP ON IP.[_ResourceGuid] = AC.[_ResourceGuid] AND IP.[_id] = (SELECT TOP 1 t1.[_id] FROM Inv_AeX_AC_TCPIP t1 WHERE AC.[_ResourceGuid] = t1.[_ResourceGuid] AND t1.[MAC Address] IS NOT NULL AND t1.[IP Address] IS NOT NULL AND t1.[MAC Address] NOT IN ('','00-00-00-00-00-00', 'FF-FF-FF-00-00-00','00-00-64-65-73-74','00-50-56-C0-00-08','00-50-56-C0-00-01','44-45-53-54-42-00','20-4C-4F-4F-50-20','00-53-45-00-00-00','44-45-53-54-00-00') AND t1.[Device] NOT LIKE 'VM%' AND t1.[Device] NOT LIKE 'VS%' AND t1.[Device] NOT LIKE '%Extranet%' AND t1.[Device] NOT LIKE '%IPSEC%') INNER JOIN Inv_AeX_HW_Serial_Number SN ON SN._ResourceGuid = AC.[_ResourceGuid] AND SN.[_id] = (SELECT TOP 1 s1.[_id] FROM Inv_AeX_HW_Serial_Number s1 WHERE AC.[_ResourceGuid] = s1.[_ResourceGuid] AND s1.[Serial Number] NOT IN ('','Not Found','No Asset Tag','not yet supported','Data not accessible','GENERIC HP FAILURE') --AND s1.[Serial Number] NOT LIKE 'VMWARE%' AND s1.[Serial Number] IS NOT NULL) GROUP BY SN.[Serial Number] + IP.[MAC Address] HAVING COUNT(*) = 2 ) mindup ON mindup.[Client Date] = AC.[Client Date] AND mindup.[SN_MAC] = SN.[Serial Number] + IP.[MAC Address] ) minned, ( /* Returns _ResourceGuid of MAX duplicate record by Serial Number + MAC */ SELECT AC.[_ResourceGuid], maxdup.[SN_MAC], maxdup.[Client Date] FROM Inv_AeX_AC_Identification AC INNER JOIN Inv_AeX_AC_TCPIP IP ON IP.[_ResourceGuid] = AC.[_ResourceGuid] INNER JOIN Inv_AeX_HW_Serial_Number SN ON SN._ResourceGuid = AC.[_ResourceGuid] INNER JOIN ( /* Returns MAX Serial Number + MAC by Client Date */ SELECT SN.[Serial Number] + IP.[MAC Address] as 'SN_MAC', MAX (AC.[Client Date]) as 'Client Date' FROM Inv_AeX_AC_Identification AC INNER JOIN Inv_AeX_AC_TCPIP IP ON IP.[_ResourceGuid] = AC.[_ResourceGuid] AND IP.[_id] = (SELECT TOP 1 t1.[_id] FROM Inv_AeX_AC_TCPIP t1 WHERE AC.[_ResourceGuid] = t1.[_ResourceGuid] AND t1.[MAC Address] IS NOT NULL AND t1.[IP Address] IS NOT NULL AND t1.[MAC Address] NOT IN ('','00-00-00-00-00-00', 'FF-FF-FF-00-00-00','00-00-64-65-73-74','00-50-56-C0-00-08','00-50-56-C0-00-01','44-45-53-54-42-00','20-4C-4F-4F-50-20','00-53-45-00-00-00','44-45-53-54-00-00') AND t1.[Device] NOT LIKE 'VM%' AND t1.[Device] NOT LIKE 'VS%' AND t1.[Device] NOT LIKE '%Extranet%' AND t1.[Device] NOT LIKE '%IPSEC%') INNER JOIN Inv_AeX_HW_Serial_Number SN ON SN._ResourceGuid = AC.[_ResourceGuid] AND SN.[_id] = (SELECT TOP 1 s1.[_id] FROM Inv_AeX_HW_Serial_Number s1 WHERE AC.[_ResourceGuid] = s1.[_ResourceGuid] AND s1.[Serial Number] NOT IN ('','Not Found','No Asset Tag','not yet supported','Data not accessible','GENERIC HP FAILURE') --AND s1.[Serial Number] NOT LIKE 'VMWARE%' AND s1.[Serial Number] IS NOT NULL) GROUP BY SN.[Serial Number] + IP.[MAC Address] HAVING COUNT(*) = 2 ) maxdup ON maxdup.[Client Date] = AC.[Client Date] AND maxdup.[SN_MAC] = SN.[Serial Number] + IP.[MAC Address] ) maxed WHERE minned.[SN_MAC] = maxed.[SN_MAC] AND minned.[_ResourceGuid] <> maxed.[_ResourceGuid] OPEN merge_Cursor FETCH NEXT FROM merge_Cursor INTO @FromGuid, @ToGuid WHILE @@FETCH_STATUS = 0 BEGIN /* COMMENTED THE PRINT STATEMENT. UNCOMMENT TO VIEW RESULTS OF THE SCRIPT. PRINT 'Now merging ' + CAST(@FromGuid AS VARCHAR(40)) + ' into ' + CAST(@ToGuid AS VARCHAR(40)) */ EXEC @RC = spResourceMerge @FromGuid, @ToGuid /* Set merged asset to Active status */ UPDATE ResourceAssociation SET childresourceguid = '{0A0203A5-D2B6-49F1-A53B-5EC31A89437C}' WHERE ParentResourceguid = @ToGuid AND ResourceAssociationTypeGuid LIKE '302816%' FETCH NEXT FROM merge_Cursor INTO @FromGuid, @ToGuid END CLOSE merge_Cursor DEALLOCATE merge_Cursor - Open SQL Enterprise Manager.
- Navigate to the Stored Procedures folder under the Altiris database.
- Right-click the Stored Procedures folder and select "New Stored Procedure" from the drop-down list.
- The "New Stored Procedure" dialog appears.
- Change the [OWNER].[PROCEDURE NAME] to the dbo.<name> where <name> is the name to be given the store procedure. In this example "dbo.sp_Merge_Duplicate_Serial_Numbers_and_MACs"
- Cut and paste the SQL script under AS.
- Click OK. The dialog box will close and a new stored procedure will be listed for the one just created.
- In Enterprise Manager, navigate to the server.
- Right-click on the server and select Properties.
- The SQL Server Properties (Configure) dialog appears.
- In the Autostart policies area, verify all options are selected.
- In Enterprise Manager navigate down to Management - SQL Server Agent.
- Right-click the SQL Server Agent and select Start.
- Under the SQL Server Agent, right click the Jobs icon and select "New Job .".
- A New Job Properties dialog appears.
- Give the job a descriptive name.
- Specify the Owner of the job which needs to have dbo permissions to the database.
- Specify the target.
- Click the Steps tab.
- Click the New button. The "Edit Job Step" dialog appears.
- Give the step a name.
- Verify the type is "Transact-SQL Script (TSQL)".
- 24 .Specify the database as Altiris.
- Enter the command "Exec <storedprocedurename>". In this example it is sp_Merge_Duplicate_Serial_Numbers_and_MACs.
- The completed window should look like this. Click Ok.
- Click the Schedules tab.
- Click the New Schedule button. The "New Job Schedule" dialog appears.
- Add in a Name.
- Click the Change button. The "Edit Recurring Job Schedule" dialog appears. Adjust the settings as necessary and click OK.
- Verify the schedule is correct and click OK.
- Once that is done the job should look like this.
- When finish click OK to complete the job. The job will appear in the Jobs list.
(34 votes)
A HUGE Thank You...
I have to add in huge props to Ian Corcoran for helping me lay this out. A lot of his work in the past few weeks led up to the whole resource merging and cleanup book as well as this article here. Thanks Ian!
Benjamin Z. Palmer
Architect | Workspace Design | The Hartford | Simsbury, CT 06082