Network Troubleshooting

 

• Ping relay.aei.com
• Traceroute relay.aei.com
• Telnet realy.aei.com  25

Powershell to test smtp mail

 Open powershell

Send-MailMessage -To "dexter.velasco@aei.com" -From "dexter.velasco@aei.com" -Subject "Spawn" -Body "Hell Spawn" -SmtpServer relay.aei.com

Excel Concatenate & compare rows

 Concatenate 

="select '"&B13&"' as TableName, count(*) as Ctr from "&A13&".dbo."&B13&" with (nolock) union all"

Compare rows to list

=NOT(ISERROR(MATCH(J3,$A$3:$A$46,0)))

EBS Change Password

 DECLARE

   v_user_name     VARCHAR2(30) :=  UPPER ('&USER_NAME');  -- change it

   v_new_password  VARCHAR2(30) :=  '&NEW_PASSWORD';       -- change it

  

   v_exists        PLS_INTEGER;

   v_status        BOOLEAN;

   e_user          EXCEPTION;

   e_pswd          EXCEPTION;

  

BEGIN

  

   -- Check if user exists

   BEGIN

      SELECT 1

        INTO v_exists

        FROM fnd_user u

       WHERE 1=1

         AND u.user_name = v_user_name;

        

   EXCEPTION

      WHEN NO_DATA_FOUND THEN

         RAISE e_user;

   END;

  

  

   -- Validate password

   IF (

            -- if password is less than 8 characters

            (LENGTH (v_new_password) < 8)

        OR

            -- if password does not contain any number

            (NOT REGEXP_LIKE (v_new_password, '[[:digit:]]'))

      )

   THEN

      RAISE e_pswd;

   END IF;

  

  

   -- Use API to change password

   v_status := fnd_user_pkg.ChangePassword

                     (

                        username     =>  v_user_name,

                        newpassword  =>  v_new_password

                     );

  

  

   IF v_status = TRUE THEN

      DBMS_OUTPUT.PUT_LINE ('The password has been successfully reset for ' ||

                            v_user_name);

      COMMIT;

   ELSE

      DBMS_OUTPUT.PUT_LINE ('Unable to reset password due to ' ||

                            SUBSTR (SQLERRM, 1, 100));

      ROLLBACK;

   END IF;

EXCEPTION

   WHEN e_user THEN

      DBMS_OUTPUT.PUT_LINE ('User ' || v_user_name || ' could not be found');

   WHEN e_pswd THEN

      DBMS_OUTPUT.PUT_LINE ('The password provided could not be validated');

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE ('SQLERRM: ' || SQLERRM);

END;

How to change datafile locations on always on

https://www.sqlshack.com/a-walk-through-of-moving-database-file-in-sql-server-always-on-availability-group/

Windows Server Last Restart

 systeminfo | find /i “Boot Time”

How to concat in excel

 ="select '"&B3&"' as TableName, count(*) as Ctr from "&A3&".dbo."&B3&" with (nolock) union all"

SQL Server trace

 exec sp_trace_setstatus 2,1 -- Start trace

exec sp_trace_setstatus 2,0 -- Stop trace

exec sp_trace_setstatus 2,2 -- Delete trace

SQL Server How to grant user

 USE [PS_STAGING]

GO

CREATE USER [svc_psstaging_feed] FOR LOGIN [svc_psstaging_feed]

GO

1> use ps_staging

2> go

Changed database context to 'PS_STAGING'.

1> create user svc_psstaging_feed for login svc_psstaging_feed

2> go

1> grant select on PSA_TimesheetByWeek to svc_psstaging_feed;

2> go

1>

SQL Server check if user has execute permission

 DECLARE @username nvarchar(128) = 'user01';

SELECT COUNT(*) FROM sys.database_permissions 
    WHERE grantee_principal_id = (SELECT UID FROM sysusers WHERE name = @username) 
        AND class_desc = 'DATABASE'
        AND type='EX' 
        AND permission_name='EXECUTE' 
        AND state = 'G';

Result 0 means negative answer, 1 means positive.

Robocopy

Robocopy Syntax

ROBOCOPY <source> <destination> [file…] [options]

<source> Source Directory (local or network path)

<destination> Destination Directory (local or network path) and 

[file…] Specifies the file or files to be copied. You can use wildcard characters 

(* or ?), if you want. If the File parameter is not specified, *.* is used as the default value.

 

 robocopy Z:\AEDCMSSQL05\SAC_STAGING\FULL\ F:\SQLBackups\FromAEDCSRV01 AEDCMSSQL05_SAC_STAGING_FULL_20220901_130044.bak

Better 

robocopy "D:\SQL_BKP\HKGRMA01" "Z:\HKGRMA01\HKGRMA01" /xo /Z /XA:H /W:5 /s

How to update global password on remote desktop connection manager

 https://www.bytesizedalex.com/microsoft-remote-desktop-connection-manager/

In contrast, the "Global" profiles are saved in your Windows user profile. These can be edited through Tools > Options > General tab > Default group settings > Profile Management tab..

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

SQL Server drop orphaned users

 use [master]

go

create proc dbo.sp_Drop_OrphanedUsers

as

begin

 set nocount on

 -- get orphaned users  

 declare @user varchar(max) 

 declare c_orphaned_user cursor for 

  select name

  from sys.database_principals 

  where type in ('G','S','U') 

  and authentication_type<>2 -- Use this filter only if you are running on SQL Server 2012 and major versions and you have "contained databases"

  and [sid] not in ( select [sid] from sys.server_principals where type in ('G','S','U') ) 

  and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser')  open c_orphaned_user 

 fetch next from c_orphaned_user into @user

 while(@@FETCH_STATUS=0)

 begin

  -- alter schemas for user 

  declare @schema_name varchar(max) 

  declare c_schema cursor for 

   select name from  sys.schemas where USER_NAME(principal_id)=@user

  open c_schema 

  fetch next from c_schema into @schema_name

  while (@@FETCH_STATUS=0)

  begin

   declare @sql_schema varchar(max)

   select @sql_schema='ALTER AUTHORIZATION ON SCHEMA::['+@schema_name+ '] TO [dbo]'

   print @sql_schema

   exec(@sql_schema)

   fetch next from c_schema into @schema_name

  end

  close c_schema

  deallocate c_schema   

  

  -- alter roles for user 

  declare @dp_name varchar(max) 

  declare c_database_principal cursor for 

   select name from sys.database_principals

   where type='R' and user_name(owning_principal_id)=@user

  open c_database_principal

  fetch next from c_database_principal into @dp_name

  while (@@FETCH_STATUS=0)

  begin

   declare @sql_database_principal  varchar(max)

   select @sql_database_principal ='ALTER AUTHORIZATION ON ROLE::['+@dp_name+ '] TO [dbo]'

   print @sql_database_principal 

   exec(@sql_database_principal )

   fetch next from c_database_principal into @dp_name

  end

  close c_database_principal

  deallocate c_database_principal

    

  -- drop roles for user 

  declare @role_name varchar(max) 

  declare c_role cursor for 

   select dp.name--,USER_NAME(member_principal_id)

   from sys.database_role_members drm

   inner join sys.database_principals dp 

   on dp.principal_id= drm.role_principal_id

   where USER_NAME(member_principal_id)=@user 

  open c_role 

  fetch next from c_role into @role_name

  while (@@FETCH_STATUS=0)

  begin

   declare @sql_role varchar(max)

   select @sql_role='EXEC sp_droprolemember N'''+@role_name+''', N'''+@user+''''

   print @sql_role

   exec (@sql_role)

   fetch next from c_role into @role_name

  end

  close c_role

  deallocate c_role   

      

  -- drop user

  declare @sql_user varchar(max)

  set @sql_user='DROP USER ['+@user +']'

  print @sql_user

  exec (@sql_user)

  fetch next from c_orphaned_user into @user

 end

 close c_orphaned_user

 deallocate c_orphaned_user

 set nocount off

end

go

-- mark stored procedure as a system stored procedure

exec sys.sp_MS_marksystemobject sp_Drop_OrphanedUsers

go

--USE [MyTestDB]

--GO

--EXEC sp_Drop_OrphanedUsers

Sql server open query sample

 SELECT BOOK_TYPE_CODE, BUSINESS_UNIT, DEPARTMENT, DEPARTMENT_DESCRIPTION, ASSET_NUMBER, ASSET_DESCRIPTION, MAJOR_CATEGORY, PR_NUMBER, SERIAL_NUMBER, TAG_NUMBER, PO_NUMBER, LEGACY_ASSET_NUMBER, CUSTODIAN, DETAILED_LOCATION, EQ_NUMBER, CALIBRATION_DATE, NEXT_CALIBRATION_DATE, COUNT_CYCLE, DATE_PLACED_IN_SERVICE, QUANTITY, COST, ACCUM_DEPRN, NET_BOOK_VALUE, CAPITALIZED_FLAG, Getdate()

from OpenQuery(PART1I,'select * from XXFA.XXFA_COUNT')

SQL Server. Kill runaway jobs

 select sja.*

FROM msdb.dbo.sysjobactivity AS sja

INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id

WHERE sja.start_execution_date IS NOT NULL

AND sja.stop_execution_date IS NULL

and sj.name = 'FIN_ICRP_BOM_Generate_QCHART'

kill 35

USE msdb

GO

EXEC dbo.sp_stop_job

N'FIN_ICRP_BOM_Generate_QCHART'

--use this

DECLARE @job_name VARCHAR(2000) = 'FIN_ICRP_BOM_Generate_QCHART'; 

 

DECLARE @job_id uniqueidentifier =

    (SELECT job_id

    FROM msdb.dbo.sysjobs

    WHERE NAME LIKE @job_name); 

 

UPDATE msdb.dbo.sysjobactivity

SET stop_execution_date = GETDATE()

WHERE job_id = @job_id

AND start_execution_date =

    (SELECT MAX(start_execution_date)

    FROM msdb.dbo.sysjobactivity

    WHERE job_id = @job_id

AND run_Requested_date IS NOT NULL 

AND stop_execution_date IS NULL);

 

EXEC sp_stop_job @job_id = @job_id;