You can use Microsoft Visual Studio 2010 to generate XSD from this XML file.
1. Create a Biztalk project (myTestProject.Schema)
2. Right click on the project and select Add –> Add Generated Items …
3. Select Generate Schema from Add Generated Items and select Generate Schema from the right pane
4. Select Well-Formed XML in the document type from Generate Schema window
5. Provide XML file in the input file or select it from the BROWSE
6. Finally press ok and the XSD file will generate.
Use following query to get running backup jobs:
--Get Details of running backup jobs
SELECT r.session_id AS [Session_Id]
,r.command AS [command]
,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete]
,GETDATE() AS [Current Time]
,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time]
,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min]
,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated Hours]
,CONVERT(VARCHAR(1000), (
SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE
WHEN r.statement_end_offset = - 1
THEN 1000
ELSE (r.statement_end_offset - r.statement_start_offset) / 2
END) 'Statement text'
FROM sys.dm_exec_sql_text(sql_handle)
))
FROM sys.dm_exec_requests r
WHERE command like 'RESTORE%'
or command like 'BACKUP%'Recently I was assigned a task to create a Jobs Inventory file containing the details of SQL Agent jobs and tasks scheduled in Windows Scheduler on each of the servers. I wanted to extract this information from the system through a script rather than having to use the GUI and going through each individual item to extract the required information.
The DOS command schtasks holds the key for working with Windows scheduler jobs. The command has a number of parameters, but for the purpose of accomplishing my task, the /query parameter will be used.
Open the command prompt, and type the following command:
schtasks /query
It returns a list of all the scheduled tasks, including Task Name, Next Run Time and Status. The /query parameter can be used in a number of ways. It has its own list of parameters that it can be used along with. To see the complete list of parameters for /query, use the following command:
schtasks /query /?
Since for my task I wanted a bit more detail about the scheduled tasks, specifically the file path of what was scheduled, so I used the command as follows:
schtasks /query /v /fo LIST
This provided a detailed list of all the scheduled tasks. It included the additional information that I required, such as the task being run, schedule information, etc. But the resultant information was in list format and it is difficult to apply any kind of filter on it.
To use the data in a better way and to filter it as per my requirements, I needed to get the output in CSV format. For this, I used the following command:
schtasks /query /v /fo CSV
This produced the same result set in a columnar format and saved it as a CSV file at the given path. Using the CSV file with MS Excel, I was able to manipulate data easily using Excel’s different features, like sorting and filtering etc.
schtasks /query /v /fo CSV > D:\tasks.csv
Finally, if you want to extract similar data for a remote machine, you can do so by using the command as follows:
schtasks /query /S SERVERNAME /U username /P password /FO CSV /V > exportedjobs.csv
In this section, I will show how to obtain similar information for SQL Agent jobs.
SQL Server allows creation of jobs for performing various tasks in an automated fashion like Replication, running SSIS Packages, Stored Procedures, Batch Commands, etc. These jobs can be created either using SSMS GUI or using T-SQL queries. The details of these jobs are stored in different tables of MSDB system database.
The MSDB is a SQL Server system database which is used to store the Configuration, Processing, & Metadata information related to the SQL Server features, including: SQL Server Agent Jobs, Job Steps, Job schedules, Alerts, Operators, database backups and restore information, Maintenance Plan and Database Mail etc.
Script for retrieving SQL Server Agent Job data
The following query gives us the Job Level Setup and Configuration information for all SQL Server Agent Jobs. This information can also be found in the SQL Server Agent Job Properties window in SSMS.
| SELECT HOST_NAME() as ServerName
,j.[Name] AS [JobName] , CASE j.[enabled] WHEN 1 THEN ‘Yes’ WHEN 0 THEN ‘No’ END AS [IsJobEnabled] ,CASE WHEN c.name = ‘[Uncategorized (Local)]’ THEN ” ELSE c.name END ‘Category’ , CASE j.[description] WHEN ‘No Description available.’ THEN ” ELSE [description] END AS ‘Description’ , s.[name] AS [ScheduleName] , CASE s.[enabled] WHEN 1 THEN ‘Yes’ WHEN 0 THEN ‘No’ END AS [IsScheduleEnabled] , CASE WHEN [freq_type] = 64 THEN ‘Start automatically when SQL Server Agent starts’ WHEN [freq_type] = 128 THEN ‘Start whenever the CPUs become idle’ WHEN [freq_type] IN (4,8,16,32) THEN ‘Recurring’ WHEN [freq_type] = 1 THEN ‘One Time’ END [ScheduleType] , CASE [freq_type] WHEN 1 THEN ‘One Time’ WHEN 4 THEN ‘Daily’ WHEN 8 THEN ‘Weekly’ WHEN 16 THEN ‘Monthly’ WHEN 32 THEN ‘Monthly – Relative to Frequency Interval’ WHEN 64 THEN ‘Start automatically when SQL Server Agent starts’ WHEN 128 THEN ‘Start whenever the CPUs become idle’ END [Occurrence] , CASE [freq_type] WHEN 4 THEN ‘Occurs every ‘ + CAST([freq_interval] AS VARCHAR(3)) + ‘ day(s)’ WHEN 8 THEN ‘Occurs every ‘ + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ‘ week(s) on ‘ + CASE WHEN [freq_interval] & 1 = 1 THEN ‘Sunday’ ELSE ” END + CASE WHEN [freq_interval] & 2 = 2 THEN ‘, Monday’ ELSE ” END + CASE WHEN [freq_interval] & 4 = 4 THEN ‘, Tuesday’ ELSE ” END + CASE WHEN [freq_interval] & 8 = 8 THEN ‘, Wednesday’ ELSE ” END + CASE WHEN [freq_interval] & 16 = 16 THEN ‘, Thursday’ ELSE ” END + CASE WHEN [freq_interval] & 32 = 32 THEN ‘, Friday’ ELSE ” END + CASE WHEN [freq_interval] & 64 = 64 THEN ‘, Saturday’ ELSE ” END WHEN 16 THEN ‘Occurs on Day ‘ + CAST([freq_interval] AS VARCHAR(3)) + ‘ of every ‘ + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ‘ month(s)’ WHEN 32 THEN ‘Occurs on ‘ + CASE [freq_relative_interval] WHEN 1 THEN ‘First’ WHEN 2 THEN ‘Second’ WHEN 4 THEN ‘Third’ WHEN 8 THEN ‘Fourth’ WHEN 16 THEN ‘Last’ END + ‘ ‘ + CASE [freq_interval] WHEN 1 THEN ‘Sunday’ WHEN 2 THEN ‘Monday’ WHEN 3 THEN ‘Tuesday’ WHEN 4 THEN ‘Wednesday’ WHEN 5 THEN ‘Thursday’ WHEN 6 THEN ‘Friday’ WHEN 7 THEN ‘Saturday’ WHEN 8 THEN ‘Day’ WHEN 9 THEN ‘Weekday’ WHEN 10 THEN ‘Weekend day’ END + ‘ of every ‘ + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ‘ month(s)’ END AS [Recurrence] , CASE [freq_subday_type] WHEN 1 THEN ‘Occurs once at ‘ + STUFF( STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) WHEN 2 THEN ‘Occurs every ‘ + CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Second(s) between ‘ + STUFF( STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) + ‘ & ‘ + STUFF( STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) WHEN 4 THEN ‘Occurs every ‘ + CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Minute(s) between ‘ + STUFF( STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) + ‘ & ‘ + STUFF( STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) WHEN 8 THEN ‘Occurs every ‘ + CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Hour(s) between ‘ + STUFF( STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) + ‘ & ‘ + STUFF( STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) END [Frequency] ,STUFF( STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) as active_start_time ,STUFF( STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ‘:’) , 6, 0, ‘:’) as active_end_time , STUFF( STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, ‘-‘) , 8, 0, ‘-‘) AS [ScheduleUsageStartDate] , STUFF( STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, ‘-‘) , 8, 0, ‘-‘) AS [ScheduleUsageEndDate] , s.[date_created] AS [ScheduleCreatedOn] , s.[date_modified] AS [ScheduleLastModifiedOn] ,STUFF( STUFF(CAST(h.Last_Run_Date AS VARCHAR(8)), 5, 0, ‘-‘) , 8, 0, ‘-‘) AS [Last_Run_Date]
FROM msdb..sysjobs j LEFT JOIN msdb..sysJobschedules js ON j.job_id = js.job_id LEFT OUTER JOIN [msdb].[dbo].[sysschedules] s ON js.Schedule_id = s.Schedule_id LEFT JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id LEFT OUTER JOIN (SELECT Job_Id, MAX(Run_Date) as Last_Run_Date FROM msdb..sysjobhistory GROUP BY Job_Id) h On h.Job_id = j.Job_id ORDER BY j.Name
|
1. First of all we need to create a connection “SSISLogging” with the database which will hold logging information in SQL Server. For this example, we selected the “SSISLogging” database which exists in DEV server.
2. To configure SSIS logging, open the package in BIDS and go to SSIS menu and select logging option or right click the package designer and select Logging option.
3. On the Configure SSIS Logs: follow the below steps to configure this dialog box,
4. In the detail tab, select the events on which you want to log information to the database. In the below example, whenever there is an error in the package execution it will record it to the log table. You may need to select other events according to your requirements.
5. Following is the sample package execution. I have mentioned invalid table name in the query in “Execute SQL Task”. So that package execution fail and we can find the error in the log table.
6. Following is the output of log table dbo.sysssislog. I have only displayed the columns id, event, source and message. There are many other columns in the table. You can notice that records with ids 2 and 3 contain the error information logged.
Introduction to Transact-SQL Querying
Understanding the Logical Order of Operations in SELECT statements
Executing Basic SELECT Statements
Executing queries which filter data using predicates
Executing queries which sort data using ORDER BY
Querying Multiple Tables
Understanding Joins
Querying with Inner Joins
Querying with Outer Joins
Querying with Cross Joins and Self Joins
Using DML to Modify Data
Inserting Data
Modifying and Deleting Data
Using Built-In Functions
This module introduces the use of functions that are built in to SQL Server, and will discuss
some common usages including data type conversion, testing for logical results and nullability.
Writing Queries with Built-In Functions
Using Conversion Functions
Using Logical Functions
Using Functions to Work with NULL
Grouping and Aggregating Data
This module introduces methods for grouping data within a query, aggregating the grouped data and
filtering groups with HAVING. The module is designed to help the student grasp why a SELECT clause
has restrictions placed upon column naming in the GROUP BY clause as well as which columns may be
listed in the SELECT clause.
Using Aggregate Functions
Using the GROUP BY Clause
Filtering Groups with HAVING
Using Subqueries
This module will introduce the use of subqueries in various parts of a SELECT statement.
It will include the use of scalar and multi-result subqueries, and the use of the IN
and EXISTS operators
Writing Self-Contained Subqueries
Writing Correlated Subqueries
Using the EXISTS Predicate with Subqueries
Using Table Expressions & Temp Tables
This module introduces T-SQL expressions which return a valid relational table, typically for
further use in the query. The module discusses views, derived tables, common table expressions and
inline table-valued functions
Using Derived Tables
Using Common Table Expressions
Using Views
Using Inline Table-Valued Functions
Executing Stored Procedures
Querying Data with Stored Procedures
Passing Parameters to Stored Procedures
Creating Simple Stored Procedures
Working with Dynamic SQL
Cursor
Indexes
Triggers
Tayyab Siddiqui 