ITCanopy

Source Below query will helps you find the queries which have taken a lot of time in milliseconds. To confirm that you have query performance issues on your SQL Server instance start by examining queries by their execution time. SELECT     req.session_id     , req.total_elapsed_time AS duration_ms     , req.cpu_time AS cpu_time_ms     , req.total_elapsed_time - req.cpu_time AS wait_time     , req.logical_reads     , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1,        ((CASE statement_end_offset            WHEN -1            THEN DATALENGTH(ST.text)              ELSE req.statement_end_offset          END - req.statement_start_offset)/2) + ...

When using " Communications Outbound Manager " business service Siebel create a job record in " S_SRM_REQUEST " table. You can easily check the status of these jobs by using below query which is written for MS SQL Manager Studio. SELECT STATUS ,CREATED,ROW_ID,DESC_TEXT,ENTERPRISE_NAME,EXEC_SRVR_NAME,PARAM_VAL,RESP_TEXT,COMPLETION_TEXT FROM S_SRM_REQUEST WHERE 1=1 AND DATEPART ( YEAR ,CREATED) = '2022' AND DATEPART ( MONTH ,CREATED) = '04' AND DATEPART ( DAY ,CREATED) = '06' AND ( PARAM_VAL LIKE '%SendMessage%' OR PARAM_VAL LIKE '%CreateRequest%' ) ORDER BY CREATED DESC

When you run SELECT query and copy the result to notepad or excel instead of one row columns with new line character shifts to new row. char(13) is carriage return (is a control character or mechanism used to reset a device's position to the beginning of a line of text) and char(10) is line feed (new line). Below query will eliminate carriage return and new lines from a column of a table. The inner replace replaces line feed and the outer replace replace carriage return SELECT Replace ( Replace( X_DIVISION, CHAR (10), '' ), CHAR (13), '' ) FROM S_CASE_X WHERE PAR_ROW_ID = '1-8I4G98D' Source: Stackoverflow 28628342, Wikipedia See Also: How to remove Line Breaks from a string using Siebel eScript

This Article will cover below points: How to create DB View in Microsoft SQL Server. How to provide permissions to DB View. How to export DB View to .sql file. How to create DB View in Microsoft SQL Server: Connect MS SQL Server to Siebel DB. Expand the Siebel DB and navigate to Views Right click on Views and Select New View.. This might take few minutes on first use and it will open below new window from where you can create db view by selecting Table, Views etc. But I prefer to write query and directly use.Click on Close button. On Close you can see a new query window with three sections.  1st section will show the table/ view relational diagram. 2nd section will show the Columns and their details. 3rd window will have the SQL query. Copy and Paste the SQL query in 3rd section and click on other section, MS SQL Server will auto create the relation diagram and populate the Column details. From the top tool bar click on Save button and MS SQL Server will ask for DB View name, pr...

Data Defination Language (DDL) Create  Alter  Rename Truncate  Drop  Data Query Language (DQL) Select  Data Manipulation Language (DML) Insert Update Merge Delete Data Control Language (DCL) Grant Revok

You have two options for concatenating strings and column values in Oracle: CONCAT Using || CONCAT example: select CONCAT(ROW_ID,'text') from s_contact; Using || example: select ROW_ID || 'text' from s_contact; Source: StackOverflow

With INSERT ... SELECT , you can quickly insert many rows into a table from the result of a SELECT statement, which can select from one or many tables. For example: INSERT INTO tbl_temp2 (fld_id)   SELECT tbl_temp1.fld_order_id   FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;   Source: Oracle Docs   You can also insert record and set column values from multiple selects insert into S_CON_EDU (CON_ID,SCHOOL_ID,CLASS_GRADE,CREATED_BY,LAST_UPD_BY,DATE_DT,ROW_ID) ( select ( select con.row_id from s_contact con where con.IDN = '123654789'), ( select sch.row_id from S_ORG_EXT sch where sch.IDN like ( select '%'||SUBSTR('1082AB', 1, 4)||'%' from dual)), ( select lov.name from S_LST_OF_VAL lov where lov.type = 'EDU_LEVEL' and lov.active_flg = 'Y' and lov.val = 'Grade Six' ), '0-1', '0-1', SYSDATE, '1-EDU101' from dual)

To see what services are available in the database use following queries: select name from V$SERVICES ; select name from V$ACTIVE_SERVICES ; The V$ views are mainly dynamic views of system metrics. They are used for performance tuning, session monitoring, etc. So access is limited to DBA users by default The easiest way of finding the database name is: select * from global_name ; This view is granted to PUBLIC, so anybody can query it.

 About DB Views: Views are customized presentations of data in one or more tables or other views. You can think of them as stored queries. Views do not actually contain data, but instead derive their data from the tables upon which they are based. These tables are referred to as the base tables of the view. Similar to tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view. Views can provide an additional level of security by restricting access to a predetermined set of rows and columns of a table. They can also hide data complexity and store complex queries. Create Views: Open SQL Developer and connect the connection (DEV, UAT, Prod), Write the desired query. Now expand the db and find the Views and right click and select the "New View" A new window will appear. Paste the query your have prepared before and provide the appropriate new and click on OK button....

 ROUND(months_between(TRUNC( sysdate ),to_date( CON.BIRTH_DT ))/12, 2)

SELECT u.LOGIN as "User ID" ,res.NAME as "Responsibility" FROM S_USER u left join S_PER_RESP ri on ri.PER_ID = u.ROW_ID left join S_RESP res on res.ROW_ID = ri.RESP_ID WHERE  u.LOGIN IN (' USER.NAME ') ORDER BY u.LOGIN

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