To overcome the listagg issue ORA-01489: result of string concatenation is too long Use XMLagg instead of listagg

If you using listagg sometimes we will face the error "ORA-01489: result of string concatenation is too long"

Then you can modify your query with below sample

SELECT replace(dbms_lob.substr( rtrim(XMLAgg( XMLElement(a, ',',your_data_column) order by your_data_column ).getClobVal()),3999,1),',','') alias_name from table_name


Example: SELECT replace(dbms_lob.substr( rtrim(XMLAgg( XMLElement(a, ',',info) order by info ).getClobVal()),3999,1),',','') combined_info from bab


For more query please leave your comments

Find all objects containing specific text in Oracle or all resource text

SELECT * FROM ALL_source WHERE UPPER(text) LIKE upper('%SUM(PROV_GR_AMT)%')

Easy way to change/update the oracle table column datatype without pain

ALTER TABLE es.response ADD approval_amount1 number(12,2); UPDATE es.response SET approval_amount1 = approval_amount; ALTER es.response MODIFY approval_amount (12,2); UPDATE es.response SET approval_amount=approval_amount1; ALTER TABLE es.response DROP COLUMN approval_amount1;

Find all objects containing specific text in SQL Server or all resource text

SELECT DISTINCT a.[name] FROM sysobjects a INNER JOIN syscomments b on a.id = b.id WHERE b.[text] LIKE '%[search-term]%'

Inner join Vs Cross Apply

Follow the below links

http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join

http://technet.microsoft.com/en-us/library/ms175156.aspx

http://explainextended.com/2009/07/16/inner-join-vs-cross-apply/
 

How to Find Currently Running Query in SQL Server and Killing the session

The below query returns currently running query in SQL Server

Session Query

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
where DB_NAME(req.database_id) = 'northwind'

Kill the session

KILL [session_id] (get the session id from the above query)

Example

Kill 82

The unattended Service Account Application ID is not specified or has an invalid value

Rule          :   The Unattended Service Account Application ID is not specified or has an invalid value.

Summary  : The Unattended Service Account Application ID setting stores an application identifier (ID) in the registered Secure Store Service. The application ID is used to reference the Unattended Service Account credentials. The Unattended Service Account is a single, low-privileged account that Visio Graphics Service impersonates when it connects to data sources external to SharePoint 2013, such as SQL Server. This account is required to connect to these external data sources. For more information about Visio Graphics Service, see Plan for Visio Services in SharePoint Server 2013 and Plan Visio Services security in SharePoint Server 2013.

Resolution:   Specify a valid application ID value

1. Verify that the user account that is performing this procedure is an administrator of the Visio Graphics Service service application and the Secure Store Service service application.
   
2. In Central Administration, on the Home page, in the Application Management section, click Manage service applications.
   
3. On the Service Applications page, click the Secure Store Service service application.
   
4. On the Secure Store Service page, record the application ID from the Target Application ID column.
   For more information about the Secure Store Service service application, see Plan the Secure Store Service in SharePoint Server 2013.
   
5. On the Service Applications page, click the Visio Graphics service application.
   
6. On the Manage the Visio Graphics Service page, click Global Settings.
   
7. On the Visio Graphics Service Settings page, in the External Data section, in the Unattended Service Account text box, type the application ID that you recorded in step 4 of this procedure.
   
8. Click OK.

Further Clarification refer below links

http://sharepointwings.blogspot.in/2012/11/sharepoint-2010-unattended-service.html
http://sharepointshah.blogspot.in/2012/08/the-unattended-service-account.html

SharePoint Health Analyzer rules reference

Before the applying health analyzer rules please refer below link. 

http://technet.microsoft.com/en-us/library/ff686816%28v=office.14%29.aspx

Event 5586 after applying SP1 to SharePoint 2010

After installed the SP1 in share point 2010 we found the below error event viewer

Error:

Unknown SQL Exception 2812 occurred. Additional error information from SQL Server is included below.

Could not find stored procedure 'proc_UpdateStatisticsNVP'.


This is known bug of the Microsoft share point server no need to worry about that.The error will fixed in future Microsoft releases.

Please refer below link

http://support.microsoft.com/kb/2635071

If you want fix this error you can refer the below link. risk is not measured.

http://seth.killey.me/?p=462

Event ID 6398 due to CEIP Data Collection for SharePoint Foundation

Please Refer below link :

http://support.microsoft.com/kb/2616940