How to export SSIS packages from SQL server

 Run this on SSMS and then run the results on the command prompt.

USE MSDB

SELECT 'DTUTIL /SQL "\' + F.foldername + '\' + P.NAME +'" /ENCRYPT FILE;"F:\FileTransfer\DJPegging\GYRPACKAGES\' + F.foldername + '\'+ P.NAME+'.DTSX";0'

FROM SYSSSISPACKAGES P

LEFT JOIN sysssispackagefolders  F ON P.FolderID = F.folderid

WHERE F.foldername IN

(

'PH DWH',

'PH DWH STG',

'PHAPPCVTSQL04',

'PHAPPLAGSQL07'

)

diskspd

 https://github.com/Microsoft/diskspd/releases/latest/download/DiskSpd.zip (aka https://aka.ms/getdiskspd).

Diskspd.exe -b8K -d60 -h -L -o2 -t4 -r -w30 -c100M c:\test\io.dat > c:\test\output.txt

SQL SERVER How to create a select only user on a database

 use etrace

CREATE USER svc_dwhread FOR LOGIN svc_dwhread;

EXEC sp_addrolemember 'db_datareader'', 'svc_dwhread'

Link Server Query Example

 SELECT ORG_ID, SUBINVENTORY, SUBINV_TYPE

FROM OPENQUERY(CAROL,

'select to_number(substr(flv.lookup_code,1,4)) org_id,

flv.description subinventory,

flv.tag subinv_type

from apps.fnd_lookup_values_vl flv

where flv.lookup_type = ''EMR_ETR_MRB_SUBINV''

and flv.tag = ''MRB''')

select * from openquery(PUMA,'SELECT * FROM DWH_CALENDAR_DIM');

How to clear performance Cache in SQL SERVER

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);   

How to change oracle EBS smtp

 System Administrator responsibility > Oracle Application Manager > Workflow > Click on Notification Mailer > Click on Workflow Notification Mailer > Click on Edit >Advanced > Steps 1 - 8

Configure override email

Workflow Override email address - erpSchools

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries'

 Msg 15281, Level 16, State 1, Procedure sp_04_TransferData_From_FD_to_FTS_Per_PRNo, Line 19

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

Solution:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE with override
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE with override
GO