Agunahwan's Blog

SELECT [sJOB].[job_id] AS [JobID]
,[sJOB].[name] AS [JobName]
,[sJSTP].[step_uid] AS [StepID]
,[sJSTP].[step_id] AS [StepNo]
,[sJSTP].[step_name] AS [StepName]
,CASE [sJSTP].[subsystem]
WHEN 'ActiveScripting'
THEN 'ActiveX Script'
WHEN 'CmdExec'
THEN 'Operating system (CmdExec)'
WHEN 'PowerShell'
THEN 'PowerShell'
WHEN 'Distribution'
THEN 'Replication Distributor'
WHEN 'Merge'
THEN 'Replication Merge'
WHEN 'QueueReader'
THEN 'Replication Queue Reader'
WHEN 'Snapshot'
THEN 'Replication Snapshot'
WHEN 'LogReader'
THEN 'Replication Transaction-Log Reader'
WHEN 'ANALYSISCOMMAND'
THEN 'SQL Server Analysis Services Command'
WHEN 'ANALYSISQUERY'
THEN 'SQL Server Analysis Services Query'
WHEN 'SSIS'
THEN 'SQL Server Integration Services Package'
WHEN 'TSQL'
THEN 'Transact-SQL script (T-SQL)'
ELSE sJSTP.subsystem
END AS [StepType]
,[sPROX].[name] AS [RunAs]
,[sJSTP].[database_name] AS [Database]
,[sJSTP].[command] AS [ExecutableCommand]
,CASE [sJSTP].[on_success_action]
WHEN 1
THEN 'Quit the job reporting success'
WHEN 2
THEN 'Quit the job reporting failure'
WHEN 3
THEN 'Go to the next step'
WHEN 4
THEN 'Go to Step: ' + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3))) + ' ' + [sOSSTP].[step_name]
END AS [OnSuccessAction]
,[sJSTP].[retry_attempts] AS [RetryAttempts]
,[sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
,CASE [sJSTP].[on_fail_action]
WHEN 1
THEN 'Quit the job reporting success'
WHEN 2
THEN 'Quit the job reporting failure'
WHEN 3
THEN 'Go to the next step'
WHEN 4
THEN 'Go to Step: ' + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3))) + ' ' + [sOFSTP].[step_name]
END AS [OnFailureAction]
,[sSCH].[schedule_uid] AS [ScheduleID]
,[sSCH].[name] AS [ScheduleName]
,CASE [sSCH].[enabled]
WHEN 1
THEN 'Yes'
WHEN 0
THEN 'No'
END AS [IsEnabled]
,CASE
WHEN [sSCH].[freq_type] = 64
THEN 'Start automatically when SQL Server Agent starts'
WHEN [sSCH].[freq_type] = 128
THEN 'Start whenever the CPUs become idle'
WHEN [sSCH].[freq_type] IN (
4
,8
,16
,32
)
THEN 'Recurring'
WHEN [sSCH].[freq_type] = 1
THEN 'One Time'
END [ScheduleType]
,CASE [sSCH].[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 [sSCH].[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 [sSCH].[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(CAST([sSCH].[active_start_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-') AS [ScheduleUsageStartDate]
,STUFF(STUFF(CAST([sSCH].[active_end_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-') AS [ScheduleUsageEndDate]
,[sSCH].[date_created] AS [ScheduleCreatedOn]
,[sSCH].[date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysjobsteps] AS [sJSTP]
INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB] ON [sJSTP].[job_id] = [sJOB].[job_id]
INNER JOIN [msdb].[dbo].[sysjobschedules] AS [sJSCH] ON [sJOB].[job_id] = [sJSCH].[job_id]
INNER JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJSCH].[schedule_id] = [sSCH].[schedule_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP] ON [sJSTP].[job_id] = [sOSSTP].[job_id]
AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP] ON [sJSTP].[job_id] = [sOFSTP].[job_id]
AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX] ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
WHERE [sJSTP].[command] LIKE '%nama_ssis%'

<!DOCTYPE html>
<html>
<head>
<title>Print Jquery</title>
<script type="text/javascript" src="js/jquery-2.2.1.js"></script> <!-- Ini harus dideklarasikan untuk penggunaan fungsi Jquery -->
<script type="text/javascript" src="js/printThis.js"></script> <!-- Ini harus dideklarasikan untuk penggunaan fungsi printThis() -->
<script>
$(document).ready(function(){
$("#btnPrintArea").click(function(){
$('#printArea').printThis(); //Untuk Print Area tertentu bisa menggunakan code ini
});

$("#btnPrintAll").click(function(){
window.print(); //Untuk print semua area menggunakan perintah ini
});
});
</script>
</head>
<body>
<div id="printArea">
<table>
<tr>
<td>Nama :</td>
<td>Agunahwan Absin</td>
</tr>
<tr>
<td>Alamat :</td>
<td>Kebon Nanas Tangerang</td>
</tr>
<tr>
<td>Jenis Kelamin :</td>
<td>Laki-Laki</td>
</tr>
<tr>
<td>&nbsp;</td>
<td><button id="btnPrintArea">Print Area</button>&nbsp;<button id="btnPrintAll">Print All</button></td>
</tr>
</table>
</div>
Ini tidak dicetak bila menggunakan printThis()
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Library;

namespace MenuTreeView
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable dtMenu = new DataTable();
dtMenu = GetData();
DataRow[] row = dtMenu.Select("idParent IS NULL");

tvMenu.Nodes.Clear();

for (int i = 0; i < row.Length; i++)
{
TreeNode tn = new TreeNode(row[i]["menu"].ToString());
if (!row[i]["linkMenu"].ToString().Equals(""))
tn.NavigateUrl = row[i]["linkMenu"].ToString();
else
tn.SelectAction = TreeNodeSelectAction.Expand;
tn = setChildNode(tn, row[i]["idMenu"].ToString(), dtMenu);
tvMenu.Nodes.Add(tn);
}
}

private DataTable GetData()
{
SqlDataReader dr;
DataTable dt = new DataTable();
DataAccess da = new DataAccess();
dr = da.ExecuteSP("connData", "sp_GetMenuList");
dt.Load(dr);

return dt;
}

private TreeNode setChildNode(TreeNode parentNode, String idParent, DataTable data)
{
DataRow[] row = data.Select("idParent=" + idParent);
if (row.Length > 0)
{
for (int i = 0; i < row.Length; i++)
{
TreeNode treeChild = new TreeNode(row[i]["menu"].ToString());
if (!row[i]["linkMenu"].ToString().Equals(""))
treeChild.NavigateUrl = row[i]["linkMenu"].ToString();
else
treeChild.SelectAction = TreeNodeSelectAction.Expand;
treeChild = setChildNode(treeChild, row[i]["idMenu"].ToString(), data);
parentNode.ChildNodes.Add(treeChild);
}
}
return parentNode;
}
}
}

USE [MenuTreeView]
GO

/****** Object:  Table [dbo].[tblMenu]    Script Date: 1/27/2016 11:06:43 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblMenu] (
[idMenu] [int] IDENTITY(1, 1) NOT NULL
,[idParent] [int] NULL
,[menu] <a href="50">varchar</a> NULL
,[linkMenu] <a href="50">varchar</a> NULL
,CONSTRAINT [PK_tblMenu] PRIMARY KEY CLUSTERED ([idMenu] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

USE [MenuTreeView]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_GetMenuList]
AS
BEGIN
SET NOCOUNT ON;

SELECT *
FROM tblMenu
END
GO

private DataTable GetData()
{
SqlDataReader dr;
DataTable dt = new DataTable();
DataAccess da = new DataAccess();
dr = da.ExecuteSP("connData", "sp_GetMenuList");
dt.Load(dr);

return dt;
}

DataTable dtMenu = new DataTable();
dtMenu = GetData();
DataRow[] row = dtMenu.Select("idParent IS NULL");

tvMenu.Nodes.Clear();

for (int i = 0; i < row.Length; i++)
{
TreeNode tn = new TreeNode(row[i]["menu"].ToString());
if (!row[i]["linkMenu"].ToString().Equals(""))
tn.NavigateUrl = row[i]["linkMenu"].ToString();
else
tn.SelectAction = TreeNodeSelectAction.Expand;
tn = setChildNode(tn, row[i]["idMenu"].ToString(), dtMenu);
tvMenu.Nodes.Add(tn);
}

tn = setChildNode(tn, row[i]["idMenu"].ToString(), dtMenu);

private TreeNode setChildNode(TreeNode parentNode, String idParent, DataTable data)
{
DataRow[] row = data.Select("idParent=" + idParent);
if (row.Length > 0)
{
for (int i = 0; i < row.Length; i++)
{
TreeNode treeChild = new TreeNode(row[i]["menu"].ToString());
if (!row[i]["linkMenu"].ToString().Equals(""))
treeChild.NavigateUrl = row[i]["linkMenu"].ToString();
else
treeChild.SelectAction = TreeNodeSelectAction.Expand;
treeChild = setChildNode(treeChild, row[i]["idMenu"].ToString(), data);
parentNode.ChildNodes.Add(treeChild);
}
}
return parentNode;
}

WITH cte
AS (
SELECT NAME AS [ssis name]
,ssistxt = CAST(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS XML)
,folder.*
FROM msdb.dbo.sysssispackages ssis
INNER JOIN msdb.dbo.sysssispackagefolders folder ON ssis.folderid = folder.folderid
)
SELECT *
FROM cte
WHERE CONVERT(VARCHAR(MAX), ssistxt) LIKE '%Kata yang ingin dicari%'
OR [ssis name] LIKE '%Nama SSIS%'