A helpfull SAP PI transaction listing

SAP XI TCODES

1.SXMB_IFR -> Start Integration Builder
2.SXMB_MONI -> Integration Engine - Monitoring
3.SXI_MONITOR -> XI: Message Monitoring
4.SXI_CACHE -> To Access IS runtime cache
5.sxi_cache -> XI data directory cacheidx1 -> idoc adapter
6.idx2 -> idoc metadata
7.idx5 -> monitocr idoc adapter
8.sm21 -> log di sistema
9.st22 -> dump abap (analisi di eccezioni)
10.smq1 -> messages inbound e outbound queue
11.smq2 -> messages inbound e outbound queue
12.smicm -> J2EE administration
13.st06 -> stato della macchina cpu memory filesystem machine status - cpu, 14.memory and file system.
15.st03 -> workload.
16.scc4 -> visualizza mandanti del sistema
17.we20 -> partner profiles
18.sale - ale settings
19.rz10 - edit system profile
20.sm59 ->mantain rfc destinations
21.bd64 -> mantain distribution model (trasformazioni)
22.SU01 -> users
23.AL08 -> list all logged users(user login logon)
24.SE10 -> Change Request
25.SE09 -> Change Request
26.WE05 -> All idocs
27.WE19 -> IDoc Test Tool
28.WE21 -> port definition
29.SE11 -> Data dictionary
30.SM21 -> log sistema xi
31.se37 -> mantain funcion modules
32.SICF -> http server configuration
33.SMGW -> trace, alzare livello di trace.
34.BD13 ->
35.BD64 -> modelli di distribuzione
36.pfcg -> Roles
37.tabella TSTC -> sap transactions codes
38.tabella TSTCT -> transaction descriptions
39.STMS -> transports management
40.SPAM -> apply ABAP support packages
41.SPAU -> manage objects after apply support packages
42.SE01 -> manage change requests
43.SLDCHECK -> Test SLD Connection
44.SLDAPICUST-> SLD API Customizing
45.SXMB_ADM -> Integration Engine - Administration
46.SXMB_MONI_BPE -> Process Engine - Monitoring

Linked and local table record size comparing using sp_MsForEchtable in MSSQL Server valid from version 2000 up to 2008

Comparación de cantidad de registros entre dos tablas una en un servidor remoto y otra local

sp_MsForeachtable 'SELECT ''?'' tabla,COUNT(*) registros, ''L'' origen FROM ? A UNION SELECT  ''?'' tabla,COUNT(*) registros, ''R'' origen  FROM SERVER.DATABASE.? B '

Extended stored procedure for SQL Server 2000 that implements a REXEC protocol client compiled with Visual C++ in Visual Studio 2003

I have done this long time ago, but may be it can be still usefull to somebody.

The first thing is to implement the REXEC protocol client as a wrapper class to be used in the extended stored procedure.

REXEC protocol client wrapper ...

#include "winsock.h"

#include "stdlib.h"

//Constants

const int CONF_BUFF = 50;

const int RECV_BUFF= 256;

const int RESP_BUFF= 512*1024;

/* 

Class: REXEC 

Descrtiption: REXEC Protocol Wrapper

Author: Bonifacio Castillo

Release Date: Aug 17, 2006

*/

class REXEC

{

//Properties

private:

char Response[RESP_BUFF]; //Command Echo Response

char aa[CONF_BUFF]; //Configuring buffer

char rr[RECV_BUFF]; //Recv buffer

int d; //WSAStartup handler

WSADATA ws; //WSAStartup structure

struct sockaddr_in a; //Socket Address handler

SOCKET s; //Socket object

int retVal; //return Value

public:

int PORT ; //Rexec listening port

//Methods

REXEC(){

PORT = 512;

        memset(Response ,0,RESP_BUFF);

retVal = 1;

}

~REXEC(){

free(Response);

free(aa);

free(rr);

}

//Response for Message

void Message(char *);

//Get Response

char* GetResponse();

//Rexec method

int  Execute(char *srvIP, char *user, char *password, char *command);

};

//Get Response

char* REXEC::GetResponse(){

//this->Response[strlen(this->Response)]='\0';

return this->Response;

}

//Response for Message

void REXEC::Message(char *p)

{

p[strlen(p)] = '\0';

if ((strlen(Response)+strlen(p))<=RESP_BUFF)

strcat(Response,p);

memset(p,0,RECV_BUFF);

}

//Rexec method

int REXEC::Execute(char *srvIP, char *user, char *password, char *command){

try{

      //Configure WSAStartup

d = WSAStartup(MAKEWORD(2,0),&ws);

sprintf(aa," WSASTARTUP = %d",d);

//Configure socket port and Socket Type

s = socket(AF_INET,SOCK_STREAM,0);

sprintf(aa," SOCKET = %d",s);

a.sin_family = AF_INET;

a.sin_port = htons(PORT);

a.sin_addr.s_addr = inet_addr(srvIP);

//Open Socket 

d = connect(s, (struct sockaddr *)&a, sizeof( a));

//Rexec Packet Sent \0user\0password\0command\0

send(s,"\0",1,0);

send(s,user,strlen(user)+1,0);

send(s,password,strlen(password)+1,0);

send(s,command,strlen(command)+1,0);

retVal = 1;

//Response receiving bucle

while (retVal > 0)

{

retVal = recv(s,rr,RECV_BUFF,0);

if (retVal!=0) 

Message(rr);   //Message Or Event Sent

}

if (retVal < 0) //Error Code Checking

return retVal; //Return Error Number

return 0; //Return With No Errors

}

catch(...){

return -1;

}

}

Now the helper functions at Helpers.h

#include <vector>

#include <string>

using namespace std;

//Split String

template< typename StrT >

int split(const char* str, const char* delim, 

     vector<StrT>& results, bool empties = true)

{

  char* pstr = const_cast<char*>(str);

  char* r = NULL;

  r = strstr(pstr, delim);

  int dlen = strlen(delim);

  while( r != NULL )

  {

    char* cp = new char[(r-pstr)+1];

    memcpy(cp, pstr, (r-pstr));

    cp[(r-pstr)] = '\0';

    if( strlen(cp) > 0 || empties )

    {

      StrT s(cp);

      results.push_back(s);

    }

    delete[] cp;

    pstr = r + dlen;

    r = strstr(pstr, delim);

  }

  if( strlen(pstr) > 0 || empties )

  {

    results.push_back(StrT(pstr));

  }

  return results.size();

}

Now here we go with the extended stored procedure body

/* 

Class Library: xp_netprotocols.dll

Descrtiption: Extended Store Procedure Library to Use Net Protocols in Microsoft SQL Server 2000

Author: Bonifacio Castillo

Release Date: Aug 17, 2006

History:

Aug 17, 2006 - REXEC protocol Added as xp_rexec Extended Procedure

*/

#include <stdafx.h>

#include "rexec.h"

#include "helpers.h"

#define XP_NOERROR              0

#define XP_ERROR                1

#define MAXCOLNAME 25

#define MAXNAME 25

#define MAXTEXT 255

#ifdef __cplusplus

extern "C" {

#endif

RETCODE __declspec(dllexport) xp_rexec(SRV_PROC *srvproc);

#ifdef __cplusplus

}

#endif

//Print Errors Function

void SendError (SRV_PROC *pSrvProc, CHAR* pErrorMsg) 

{

srv_sendmsg(pSrvProc, SRV_MSG_ERROR, XP_ERROR, SRV_INFO, 1,

        NULL, 0, (DBUSMALLINT) __LINE__,

        pErrorMsg,

        SRV_NULLTERM);

srv_senddone(pSrvProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);

}

//Send a Text Message

void Message(SRV_PROC *pSrvProc, CHAR* pErrorMsg){

srv_sendmsg(pSrvProc, SRV_MSG_ERROR, XP_ERROR, SRV_INFO, 1,

        NULL, 0, (DBUSMALLINT) __LINE__,

        pErrorMsg,

        SRV_NULLTERM);

}

//Send Procedure

RETCODE __declspec(dllexport) xp_rexec(SRV_PROC *srvproc)

{

try{

//check parameters

DBSMALLINT params = srv_rpcparams(srvproc);  

if (params<4){

SendError(srvproc,"Incorrect parameter format!\n usage: xp_rexec 'RemoteServerIP','RemoteUserName','Password','RemoteCommand'[,[0..n]Any Integer Value For QUITE Mode]  ");

return XP_ERROR;

}

//Parameter length validation

if  (

(srv_paramlen(srvproc,1)>MAXNAME)||(srv_paramlen(srvproc,1)<7) ||

(srv_paramlen(srvproc,2)>MAXNAME)||(srv_paramlen(srvproc,2)<1) ||

(srv_paramlen(srvproc,3)>MAXNAME)||(srv_paramlen(srvproc,3)<1) ||

(srv_paramlen(srvproc,4)>MAXTEXT)||(srv_paramlen(srvproc,4)<1)

)

{

SendError(srvproc,"Incorrect Parameter Size!\n [RemoteServerIP{7,25},RemoteUserName{1,25},Password{1,25}] = 25 char max len, [RemoteCommand{1,255}] = 255 char max len");

return XP_ERROR;

}

//

//declaring variables

DBCHAR srv[MAXNAME];

DBCHAR usr[MAXNAME];

DBCHAR pwd[MAXNAME];

DBCHAR cmd[MAXTEXT];

DBSMALLINT response=1;

DBSMALLINT i = 0;

DBCHAR colname[MAXCOLNAME];

DBCHAR spText[MAXTEXT];

//Obtain parameters

 

_snprintf(srv, srv_paramlen(srvproc,1), (DBCHAR*)srv_paramdata(srvproc,1));

_snprintf(usr, srv_paramlen(srvproc,2), (DBCHAR*)srv_paramdata(srvproc,2));

_snprintf(pwd, srv_paramlen(srvproc,3), (DBCHAR*)srv_paramdata(srvproc,3));

_snprintf(cmd, srv_paramlen(srvproc,4), (DBCHAR*)srv_paramdata(srvproc,4));

if (params==5){

try{

response =(int)srv_paramdata(srvproc,5) ;

}catch(...){

response = 1;

}

}

srv[srv_paramlen(srvproc,1)]   = '\0';

usr[srv_paramlen(srvproc,2)]   = '\0';

pwd[srv_paramlen(srvproc,3)]   = '\0';

cmd[srv_paramlen(srvproc,4)]   = '\0';

//Send Command to UNIX

REXEC *rexec=new REXEC();

rexec->Execute(srv,usr,pwd,cmd); 

//Retrieve Response

if (response==1){

_snprintf(colname, MAXCOLNAME, "rexec");

srv_describe(srvproc, 1, colname, SRV_NULLTERM, SRVCHAR,MAXTEXT , SRVCHAR, 0, NULL);

//split the rexec response string into substrings using \n as delimiter

vector<string> cadenas;

split(rexec->GetResponse(), "\n", cadenas);

//Send each string as a record to SQL

for( i=0; i < cadenas.size(); ++i )

{

if (strlen(cadenas[i].c_str())>0){

_snprintf(spText, MAXTEXT, cadenas[i].c_str() );

srv_setcoldata(srvproc, 1, spText);

srv_setcollen(srvproc, 1, static_cast<int>(strlen(spText)));

srv_sendrow(srvproc);

}

}

 

// Now return the number of rows processed

srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT, (DBUSMALLINT)0, (DBINT)i);

}

// Free Memory

free(srv);   

free(usr);

free(pwd);

free(cmd);

free(spText);

delete rexec;

// return with no errors

return XP_NOERROR ;

}

catch (...){

return XP_ERROR;

}

}

The code for  STDAFX.cpp

// stdafx.cpp : source file that includes just the standard includes

// xp_netprotocols.pch will be the pre-compiled header

// stdafx.obj will contain the pre-compiled type information

#include "stdafx.h"

The stdafx.h file

// stdafx.h : include file for standard system include files,

// or project specific include files that are used frequently, but

// are changed infrequently

//

#pragma once

// Insert your headers here

#define WIN32_LEAN_AND_MEAN // Exclude rarely-used stuff from Windows headers

#include <windows.h>

#include <stdio.h>

//Include ODS headers

#ifdef __cplusplus

extern "C" {

#endif 

#include <Srv.h> // Main header file that includes all other header files

#ifdef __cplusplus

}

#endif 

DLL point of entry

// xp_netprotocols.cpp : Defines the entry point for the dll application.

//

#include "stdafx.h"

BOOL APIENTRY DllMain( HANDLE hModule, 

                       DWORD  ul_reason_for_call, 

                       LPVOID lpReserved

)

{

    return TRUE;

}

Force a DIV to keep the scroll position after callbacks within an AJAX enabled UpdatePanel in ASPX

Usually when a callback occurs within an UpdatePanel and we have a div with scrolls the position of the scroll get's lost so what we need to do is implement the next JS code.

The trick is the handling of the begin and the end of the request using the PageRequestManager instance, see the next code.



<script language="javascript">
         var iX, iY; // Posiciones X y Y del scroll del div 
         var divID; // Identificador del DIV 
         var rMAN= Sys.WebForms.PageRequestManager.getInstance(); //Instancia del Page Request Manager del lado del cliente
       
         divID = "scrollableDIV";

         rMAN.add_beginRequest(beginRequestHnd);
         rMAN.add_endRequest(endRequestHnd);

         //función que se invoca al iniciar el request causado por un callback / postback asincrono
         function beginRequestHnd(sender, args) {
             iX = document.getElementById(divID).scrollLeft;
             iY = document.getElementById(divID).scrollTop;
         }
         //función que se invoca al finalizar el request causado por un callback / postback asincrono
         function endRequestHnd(sender, args) {
             document.getElementById(divID).scrollLeft = iX;
             document.getElementById(divID).scrollTop = iY;
         }
    </script>

<!--  Los siguientes tags solo como referencia de donde ubicar el código fuente de JS -->
</ContentTemplate>
</asp:UpdatePanel>

SQL Server Index automatic maintenance stored procedure, to be chained in a maintenance job, it's logic is based on index fragmentation

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*

EXEC USP_MANTTO_INDEX
@IsMaintenanceDay = 0,
@FragmentationRate = 15.0,
@ReuildOnFragmentation = 30.0,
@PageCount = 128,
@ExecutionMode = 2



*/
ALTER PROCEDURE [dbo].[USP_MANTTO_INDEX]
(

@IsMaintenanceDay BIT = 0, -- 1) Dia de mantenimiento, 0) No es dia de mantenimiento
@FragmentationRate FLOAT = 15.0, -- Tasa máxima de fragmentación permitida antes de una reorganización
@ReuildOnFragmentation FLOAT = 30.0, -- Tasa máxima de fragmentación permitida antes de una reconstrucción
@PageCount INT = 128, -- Número máximo de paginas que son usadas por el indice antes de ameritar mantenimiento
@ExecutionMode INT = 1 -- 0) Modo callado, 1) Modo detallado, 2) Solo consulta
)
AS
BEGIN

/**********************************************************************
PROCEDIMIENTO: USP_MANTTO_INDEX
PARAMETROS
@IsMaintenanceDay BIT = 0, -- 1) Dia de mantenimiento, 0) No es dia de mantenimiento
@FragmentationRate FLOAT = 15.0, -- Tasa máxima de fragmentación permitida antes de una reorganización
@ReuildOnFragmentation FLOAT = 30.0, -- Tasa máxima de fragmentación permitida antes de una reconstrucción
@PageCount INT = 128, -- Número máximo de paginas que son usadas por el indice antes de ameritar mantenimiento
@ExecutionMode INT = 1 -- 0) Modo callado, 1) Modo detallado
***********************************************************************/

DECLARE @TableName NVARCHAR(255), -- Nombre de la tabla a la que se le asocia el índice
@IndexName NVARCHAR(255), -- Nombre del índice que se esta evaluando
@Fragmentation FLOAT, -- Tasa de fragmentación del índice
@Pages INT, -- Cantidad de páginas usadas por el índice
@SQLStm NVARCHAR(1024) -- Cadena de ejecución dinámica


-- Validación los valores por defecto de los parámetros del proceso
SET @ReuildOnFragmentation = ISNULL(@ReuildOnFragmentation,30.0)
SET @PageCount = ISNULL(@PageCount,128)
SET @FragmentationRate = ISNULL(@FragmentationRate,15.0)
SET @IsMaintenanceDay = ISNULL(@IsMaintenanceDay,0)
SET @ExecutionMode = ISNULL(@ExecutionMode,1)

BEGIN TRY

IF @ExecutionMode = 2
BEGIN
SELECT
OBJECT_SCHEMA_NAME(FRAG.[object_id]) +
'.' + OBJECT_NAME(FRAG.[object_id])
TableName,
SIX.[name] IndexName,
FRAG.avg_fragmentation_in_percent Fragmentation,
FRAG.page_count Pages
FROM
sys.dm_db_index_physical_stats
(
DB_ID(),    --obtiene y utiliza la BD a la que la sesion actual pertenzca
0,          --parametro del object_id.
DEFAULT,    --parametro del index_id.
0,          --parametro del del número de la partición.
DEFAULT     --modo de scaneo. por defcto "LIMITED", el cual es el recomendado
) FRAG
JOIN
sys.indexes SIX ON FRAG.[object_id] = SIX.[object_id] AND FRAG.index_id = SIX.index_id
WHERE
--discriminación de los HEAP's.
FRAG.index_type_desc <> 'HEAP' AND
(
--Considerar solo los indices que requieren mantenimiento, de acuerdo a los parametros
(FRAG.page_count > @PageCount AND FRAG.avg_fragmentation_in_percent > @FragmentationRate)
OR
--o todos en caso de mantenimiento
@IsMaintenanceDay = 1
)
ORDER BY
FRAG.avg_fragmentation_in_percent DESC;

END
ELSE
BEGIN


DECLARE cur_mantain_index CURSOR FAST_FORWARD FOR
SELECT
OBJECT_SCHEMA_NAME(FRAG.[object_id]) +
'.' + OBJECT_NAME(FRAG.[object_id])
TableName,
SIX.[name] IndexName,
FRAG.avg_fragmentation_in_percent Fragmentation,
FRAG.page_count Pages
FROM
sys.dm_db_index_physical_stats
(
DB_ID(),    --obtiene y utiliza la BD a la que la sesion actual pertenzca
0,          --parametro del object_id.
DEFAULT,    --parametro del index_id.
0,          --parametro del del número de la partición.
DEFAULT     --modo de scaneo. por defcto "LIMITED", el cual es el recomendado
) FRAG
JOIN
sys.indexes SIX ON FRAG.[object_id] = SIX.[object_id] AND FRAG.index_id = SIX.index_id
WHERE
--discriminación de los HEAP's.
FRAG.index_type_desc <> 'HEAP' AND
(
--Considerar solo los indices que requieren mantenimiento, de acuerdo a los parametros
(FRAG.page_count > @PageCount AND FRAG.avg_fragmentation_in_percent > @FragmentationRate)
OR
--o todos en caso de mantenimiento
@IsMaintenanceDay = 1
)
ORDER BY
FRAG.avg_fragmentation_in_percent DESC;
 
 
OPEN cur_mantain_index;


FETCH FROM cur_mantain_index INTO
@TableName ,
@IndexName ,
@Fragmentation ,
@Pages

WHILE @@FETCH_STATUS = 0
BEGIN



-- Validación de la condición para la reconstrucción del índice
IF @Fragmentation > @ReuildOnFragmentation
BEGIN
IF @ExecutionMode = 1 -- Modo detallado
BEGIN
PRINT 'Reconstruyendo el indice ' + @IndexName +' porque su tasa de fragmentación es '+ltrim(rtrim(str(@ReuildOnFragmentation))) + ' y ha excedido el limite establecido en ' + ltrim(rtrim(str(@Fragmentation)))
END

-- Armado de la instrucción para la reconstrucción del indice
SET @SQLStm = 'ALTER INDEX '+ @IndexName + ' ON ' + @TableName + ' REBUILD ; '

-- Ejecución de la instrucción de reconstrucción
EXEC (@SQLStm)

END
ELSE
BEGIN

IF @ExecutionMode = 1 -- Modo detallado
BEGIN
-- Informar la causa del mantenimiento
IF @IsMaintenanceDay = 1
BEGIN
PRINT 'Reoganizando indice ' + @IndexName +' porque es dia de mantenimiento.'
END
ELSE
BEGIN
PRINT 'Reoganizando indice ' + @IndexName +' porque su tasa de fragmentación es '+ ltrim(rtrim(str(@FragmentationRate))) +' y ha excedido el limite establecido en '+ ltrim(rtrim(str(@Fragmentation)))
END
END

-- Armado de la instrucción para la reoganización del indice
SET @SQLStm = 'ALTER INDEX '+ @IndexName + ' ON '  + @TableName  + '  REORGANIZE; '

-- Ejecución de la instrucción de reorganización
EXEC (@SQLStm)

END



FETCH FROM cur_mantain_index INTO
@TableName ,
@IndexName ,
@Fragmentation ,
@Pages
END

CLOSE cur_mantain_index;
DEALLOCATE cur_mantain_index;
END

END TRY
BEGIN CATCH

IF CURSOR_STATUS('global','cur_mantain_index') >=0
BEGIN
CLOSE cur_mantain_index
DEALLOCATE cur_mantain_index
END



SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
     
 
 

END CATCH
END

Using the autocompleteextender over a popupextender or any other control with z-index property greater than zero.

This issue can be fixed easily using a little of CSS Code we need to define 3 CSS clases one for the CompletionListCssClass property of the extender, one more for the CompletionListItemCssClass and the last one for the CompletionListHighlightedCssClass attribute.

******************** Autocomplete extender control *******************************

CompletionListCssClass="autocomplete_completionListElement" CompletionListItemCssClass="autocomplete_listItem" CompletionListHighlightedItemCssClass="autocomplete_highlightedListItem"

*********************** CSS *********************************

    /* AutoComplete highlighted item */
    .autocomplete_completionListElement
    {
        margin: 0px !important;
        z-index:99999 !important;
        background-color: ivory;
        color: windowtext;
        border: buttonshadow;
        border-width: 1px;
        border-style: solid;
        cursor: 'default';
        overflow: auto;
        height: 200px;
        text-align: left;
        left: 0px;
        list-style-type: none;
    }
    /* AutoComplete highlighted item */
    .autocomplete_highlightedListItem
    {
        z-index:99999 !important;
        background-color: #ffff99;
        color: black;
        padding: 1px;
        cursor:hand;
    }
    /* AutoComplete item */
    .autocomplete_listItem
    {
        z-index:99999 !important;
        background-color: window;
        color: windowtext;
        padding: 1px;
        cursor:hand;
    }

Query to verify Excusive Locks in SQL Server from 2005 to 2012

The next code can be modified to check any other kind of lock's, this is usefull when we are gathering information of T-SQL Code or transactions being processed by the server.


SELECT
convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
OBJECT_NAME (rsc_objid) ObjectName,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 32) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
 FROM
master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u

 WHERE   master.dbo.syslockinfo.rsc_type = v.number
   and v.type = 'LR'  
   and master.dbo.syslockinfo.req_status = x.number
   and x.type = 'LS'  
   and master.dbo.syslockinfo.req_mode + 1 = u.number
   and u.type = 'L'
   and rsc_objid >0
   and  substring (u.name, 1, 8) like '%X%'

Some DBCC SQL Server Commands

Reseed an identity column of a table

DBCC CHECKIDENT (TABLE, RESEED, 1)

Shrink the log file of a database

DBCC SHRINKFILE (DB_log, Size)

BACKUP LOG DB 

WITH TRUNCATE_ONLY