ITCanopy

SELECT TYPE AS 'Type' ,VAL AS 'Display Value' ,NAME AS 'Language-Independent Code' ,LANG_ID AS 'Language Name' ,ORDER_BY AS 'Order' ,ACTIVE_FLG AS 'Active' ,TRANSLATE_FLG AS 'Translate' ,MULTI_LINGUAL_FLG AS 'Multilingual' FROM S_LST_OF_VAL WHERE 1=1 AND TYPE = ' LOV_TYPE '

Get Row Id of Column from Siebel Tools and replace below: SELECT ROW_ID, NAME AS 'Column Name', LENGTH AS 'Column Length' FROM S_COLUMN WHERE ROW_ID = ' 1-6D23-JMI0 ' --UPDATE S_COLUMN SET LENGTH = '255' WHERE ROW_ID = ' 1-6D23-JMI0 '

Replace values in highlighted with lime color according to your configuration. BACKUP DATABASE [SiebelDB] TO DISK = N' E :\\ FolderName \\ MSSQL11.MSSQLSERVER \\ MSSQL \\ Backup \\SiebelDB- bk-date .bak' WITH NOFORMAT, NOINIT, NAME = N'SiebelDB-Full Database Backup- Date ', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 GO

Background: For working on an object in Siebel tools you need to lock it and usually developer forget to unlock after working on the object. So you need to ask and wait for current lock owner to unlock. There is simple way to unlock any object in Siebel, use SQL Query. Below are the simple steps to achieve this. Process: Get Row Id of Workflow from Siebel Tools and replace below and run the query:   Open tools select the workflow and from top menu bar go to "Help" and click on "About Record"   A new window will open click on "Details>>" button. Copy the Row# and replace in where clause. UPDATE S_WFR_PROC SET OBJ_LOCKED_FLG='N' WHERE ROW_ID=' 1-OOYOU ' If you using Oracle SQL Developer make sure to commit. In tools query the workflow and lock. Now query the workflow again in Siebel Tools and lock it by right click menu.

Below is the query for getting workflow instance monitor values from table: select wfdl.NAME as 'WF Name' ,wfdl.DEPLOY_STATUS_CD as 'Status' ,wfdl.VERSION as 'WF Version' ,wfil.END_TS as 'Instance EndDate' ,wfil.STATUS_CD as 'Instance Status' ,wfsil.STEP_NAME as 'Step Name' ,wfsil.STATUS_CD as 'Step Status' ,wfsl.NAME as 'Process Name' ,wfsl.PROP_VAL as 'Process Value' from S_WFA_DEFN_LOG wfdl join S_WFA_INST_LOG wfil on wfdl.ROW_ID = wfil.DEFINITION_ID  --Process Instance join S_WFA_INSTP_LOG wfsil on wfil.ROW_ID = wfsil.INST_LOG_ID --Step Instance join S_WFA_STPRP_LOG wfsl on wfsil.ROW_ID = wfsl.STEP_LOG_ID  --Process Properties where wfdl.NAME = 'Workflow name with instance monitor level 3' --and wfil.STATUS_CD = 'COMPLETED' --and wfsil.STEP_NAME = 'step name' --and wfsl.NAME <> 'Error Message'

1. Open MS SQL Management Studio copy paste below query in query window. SELECT Prof.NAME AS 'Profile Name' ,ParNam.NAME AS 'Parameter Name' ,Par.VALUE AS 'Parameter Value' FROM S_CM_PROF Prof LEFT JOIN S_CM_PROF_PARM Par ON Par.CM_PROF_ID = Prof.ROW_ID LEFT JOIN S_CM_CNCTR_PARM ParNam ON ParNam.ROW_ID = Par.CM_CNCTR_PARM_ID WHERE 1=1 AND Prof.NAME = 'Email  Profile Name' 2. Go to site map in Siebel Client search for Communication Drivers and Profile, copy the name of desired Email profile and add it in above query. Run the query and you will have all parameters and their values of that profile.

--All Outbound Requests-- SELECT SCR.REQ_NUM AS 'Request #' ,SCR.STATUS_CD AS 'Status' --,SCR.CM_SRVR_COMP_NAME AS 'Component Name' --,SCR.NAME AS 'Description' ,SCR.STATUS_MSG AS 'Status Message' --,SCR.DFLT_MEDIUM_CD AS 'Default Preference' --, AS 'Recipient Group' ,SCR.PROC_START_DT AS 'Start Time' ,SCR.PROC_END_DT AS 'End Time' ,SCR.CREATED AS 'Created' ,SCRU.LOGIN AS 'Created By' --,SCR.COMMENTS AS 'Comments' --Comm Request Source-- ,SCRS.SRC_ROW_ID AS 'Id' --Comm Package-- ,SDCP.NAME AS 'Name' ,SDCP.MEDIA_TYPE_CD AS 'Channel Type' --Request Recipient Source --,RCS.ROW_ID ,RCS.SR_NUM AS 'SR No' --Customer Record ,(ISNULL(CUS.FST_NAME, '') + ' ' + ISNULL(CUS.MID_NAME, '') + ' ' + ' ' + ISNULL(CUS.LAST_NAME, '')) AS 'Name ENU' FROM S_COMM_REQ SCR --Comm Parent Request LEFT JO...

For checking table fragmentation use below: DBCC SHOWCONTIG ('S_CONTACT') DBCC SHOWCONTIG (tablename) DBCC SHOWCONTIG (tablename, indexname) Response: DBCC SHOWCONTIG scanning 'S_CONTACT' table... Table: 'S_CONTACT' (1353212021); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 13919 - Extents Scanned..............................: 1742 - Extent Switches..............................: 1742 - Avg. Pages per Extent........................: 8.0 - Scan Density [Best Count:Actual Count].......: 99.83% [1740:1743] - Logical Scan Fragmentation ..................: 0.02% - Extent Scan Fragmentation ...................: 6.89% - Avg. Bytes Free per Page.....................: 956.4 - Avg. Page Density (full).....................: 88.18% DBCC execution completed. If DBCC printed error messages, contact your system administrator. ----- For fragmentation use below: DBCC DBREINDEX ('S_CONTAC...

Below query will return column details: SELECT COL.ROW_ID AS 'Column Id' ,COL.NAME AS 'Column Name' ,COL.LENGTH AS 'Column Length' ,TAB.NAME AS 'Table Name' FROM S_COLUMN COL LEFT JOIN S_TABLE TAB ON TAB.ROW_ID = COL.TBL_ID WHERE 1=1

Add below in your SQL query and it will get date difference and convert into years: (DATEDIFF(MONTH,TABLE.BIRTH_DT,CURRENT_TIMESTAMP)/12) The DATEDIFF() function returns the difference between two dates. DATEDIFF(Interval, date_1, date_2) The CURRENT_TIMESTAMP() function returns the current date and time. SELECT CURRENT_TIMESTAMP()

Add below in your SQL query and it will subtract date column from today's date and convert into days: CONVERT(INT, GETDATE() - TABLE.CREATED_DATE) AS 'Age' OR DATEDIFF(DAY,TABLE.CREATED,GETDATE()) The GETDATE() function returns the current database system date and time. SELECT GETDATE() The CONVERT() function converts a value (of any type) into a specified datatype. CONVERT(data_type), expression/ value) SELECT CONVERT(INT, 1.1) The DATEDIFF() function returns the difference between two dates. DATEDIFF(Interval, date_1, date_2)

SQL : Structured Query Language SQL is used for storing, retrieving and manipulating (managing data) data in databases (MySql, Oracle, Sybase, MS Access). SQL is a query language, not a programming language. The SQL language is divided into queries, clauses and statements etc. It helps consumer (user) to describe and manipulate the data. Importance SQL is used for the query, insert, collect and manages data from the database. It store and fetch data from database immediately. It helps to find the needed data (information) easily. You can easily write commands almost same as we write English as it is a query language not a programming language. Almost every database system needs SQL for processing. Reasons to learn Easy to learn It doesn't require coding (SQL manages the database system by using standard SQL). You can run SQL on laptop, servers, e.t.c. SQL Standard (Databases are used by established standards. ANSI in 1986 and ISO in 1987). SQL defines foll...