Oracle to SQLServer Translation

=======================================
SQL Server Create Table As Select Syntax
=======================================
SELECT *
INTO NEW_TABLE
FROM OLD_TABLE
WHERE my_column='AAA'

=======================================
DESC

=======================================

exec sp_columns myTable

exec sp_help myTable

BUT - exec sp_columns dbo.myTable does not work...

=======================================
SQL Server NVL is ISNULL()
=======================================

SELECT product, price, SUM(ISNULL(units_on_order,0)) AS TOTAL_UNITS
FROM Products

=======================================
Concatenate Strings
=======================================

SELECT product+' '+price+' '+CONVERT(VARCHAR(10),SUM(ISNULL(units_on_order,0))) AS TOTAL_UNITS
FROM Products

=======================================
DECODE
=======================================

SELECT CASE customer_type
         WHEN 'B' THEN 'Business'
         ELSE 'Non Business'
       END
FROM CUSTOMER_TABLE 

OR

SELECT CASE 
         WHEN customer_type = 'B' 
           THEN 'Business'
           ELSE 'Non Business'
         END
FROM CUSTOMER_TABLE



=======================================
SYSDATE vs DATEADD()

=======================================

DATEADD has the following format: DATEADD(datepart,number,date)

To get last day entries, use DATEADD(day,-1,getdate())

=======================
Function Based Index
=======================
In SQL Server there is no option to directly create a Function Based Index.
The solution is a two step process:
1. Add Function Column to the table.
2. Create an Index on this new Column.

Example:
ALTER TABLE [MY_TABLE] ADD computed_column AS CONVERT(CHAR(8), [base_column], 112);

CREATE INDEX function_index ON [MY_TABLE](computed_column);

=======================

Data Dictionary in SQL Server

=======================

USER_TABLES in SQL_SERVER

use mySchema

SELECT * 

FROM INFORMATION_SCHEMA.TABLES 

WHERE UPPER(TABLE_NAME) LIKE 'MY_TABLE%';

USER_TAB_COLUMNS in SQL_SERVER

SELECT table_name, column_name
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE UPPER(column_name) LIKE '%SOME_COLUMN%'

USER_SOURCE/ALL_SOURCE in SQL_SERVER

SELECT DISTINCT object_name(id) 

  FROM syscomments with (nolock) 

 WHERE ctext LIKE '%DELETE%';

See All Functions and Procedures per schema.
SELECT * FROM INFORMATION_SCHEMA.ROUTINES;

SQLServer Date functions

getdate()
Return current date

datepart()
Get part of the date (Year, Month, Day, etc...)

Example for datepart
SELECT 
       datepart(year,getdate()) as year, 
       datepart(month,getdate()) as month, 
       datepart(day,getdate()) as day,       
       CAST(datepart(hour, getdate()) as varchar) + ':00' AS hour
       
year month day  hour
---- ----- --- -----
2015  4   2 15:00

datediff()
Get difference between dates.
select 
       DATEPART(year,getdate()) as year, 
       DATEPART(month,getdate()) as month, 
       DATEPART(day,getdate()) as day,       
       CAST(DATEPART(hour, getdate()) as varchar) + ':00' AS hour,
count(*) as emails
from dbo.email_message
where DATEDIFF(minute,convert(datetime,insert_date,100),getdate()) <60

year month day  hour emails
---- ------ --- -----  ------
2015 4 2 15:00 2

dateadd()
Add delta to a date
==========================
Select last day records
==========================
SELECT * 
FROM table_name
WHERE table_name.the_date > DATEADD(day, -1, GETDATE())

Get Future Date:
DATEADD(Month, 1, GETDATE())

convert()
Can be used to convert varchar to date or date to varchar.

SELECT * 
FROM table_name
and CONVERT(VARCHAR(7), table_name.the_date1, 111) between '2012/08' and '2012/09'

For example:
CONVERT(NVARCHAR(20), dtInsertDate, 104) => 04.03.2004   //DD.MM.YYYY
CONVERT(NVARCHAR(20), dtInsertDate, 114) => 14:43:36:000 //hh24:mi:ss:msec

===========================
Reference
===========================
List of CAST and CONVERT options:
CAST and CONVERT (Transact-SQL)

SQLServer Exception Handling

General Handling form

  BEGIN TRY
    BEGIN TRANSACTION;
 XXX
 XXX
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION;

    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
  END CATCH

Example

USE AdventureWorks2012;
GO

IF OBJECT_ID('UpdateSales', 'P') IS NOT NULL
DROP PROCEDURE UpdateSales;
GO

CREATE PROCEDURE UpdateSales
  @SalesPersonID INT,
  @SalesAmt MONEY = 0
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;
      UPDATE LastYearSales
      SET SalesLastYear = SalesLastYear + @SalesAmt
      WHERE SalesPersonID = @SalesPersonID;
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;

    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
  END CATCH
END;
GO

RAISERROR
When using RAISERROR, you should include an error message, error severity level, and error state.

The RAISERROR statement returns error information to the calling application.
As of SQL Server 2012 is was replaced by THROW.

RAISERROR syntax:
RAISERROR ( { msg_id | msg_str | @local_variable } { ,severity ,state } [ ,argument [ ,...n ] ] ) [ WITH option [ ,...n ] ]

msg_id
Is a user-defined error message number stored in the sys.messages catalog view using sp_addmessage. Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.

msg_str
Is a user-defined message with formatting similar to the printf function in the C standard library. The error message can have a maximum of 2,047 characters. If the message contains 2,048 or more characters, only the first 2,044 are displayed and an ellipsis is added to indicate that the message has been truncated.
When msg_str is specified, RAISERROR raises an error message with an error number of 50000.

@local_variable
Is a variable of any valid character data type that contains a string formatted in the same manner as msg_str. @local_variable must be char or varchar,

severity
Severity levels from 0 through 18 can be specified by any user.
Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions.
For severity levels from 19 through 25, the WITH LOG option is required.
Severity levels less than 0 are interpreted as 0.
Severity levels greater than 25 are interpreted as 25.
When severity is -1, the actual severity, is the severity value associated with the error.

state
Is an integer from 0 through 255.
You can specify -1 to return the value associated with the error.
If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors.

Arguments
Arguments are the parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters, but the total number of substitution parameters cannot exceed 20.
Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary.

Option
Can be one of the values in the following table.
LOG - Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.
NOWAIT -  Sends messages immediately to the client.
SETERROR - Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level.

Examples
RAISERROR (N'This is message %s %d.', -- Message text.
           10, -- Severity,
           1, -- State,
           N'number', -- First argument.
           5); -- Second argument.
GO
-- The message text returned is: 
This is message number 5.

SQL Server 2000

The concept of TRY-CATCH blocks was introduced in SQL Server 2005.

In SQL Server 2000, for Error Handling, need to consistently check the value of the @@ERROR system variable. 

@@ERROR is a variable updated by the SQL Server after each statement.

This variable contains the corresponding error number.

For normal execution, the value of @@ERROR is 0.

These error numbers are stored in the sysmessages table in the master database.

@@ERROR value can be saved in an integer variable immediately after the Transact-SQL statement completes.
For most error handling purposes, need to handle cases when the value of @@ERROR is non-zero, which will indicate that an error occurred.


Reference
Differences Between RAISERROR and THROW in Sql Server
Handling Errors in SQL Server 2012
RAISERROR (Transact-SQL)

Clean up objects and impdp/expdp jobs

Export Job Possible Statuses
Export job might be in several statuses:
- running
- orphaned table level export job
- stopped
- temporary stopped

The jobs used in this example:
- Export job SCOTT.EXPDP_20051121 is a schema level export that is running
- Export job SCOTT.SYS_EXPORT_TABLE_01 is an orphaned table level export job
- Export job SCOTT.SYS_EXPORT_TABLE_02 is a table level export job that was stopped
- Export job SYSTEM.SYS_EXPORT_FULL_01 is a full database export job that is temporary stopped


Step 1. Determine in SQL*Plus which Data Pump jobs exist in the database:
%sqlplus /nolog
CONNECT / as sysdba
SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state

-- locate Data Pump jobs:

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM DBA_DATAPUMP_JOBS
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;

OWNER_NAME JOB_NAME            OPERATION JOB_MODE  STATE       ATTACHED
---------- ------------------- --------- --------- ----------- --------
SCOTT      EXPDP_20051121      EXPORT    SCHEMA    EXECUTING          1
SCOTT      SYS_EXPORT_TABLE_01 EXPORT    TABLE     NOT RUNNING        0
SCOTT      SYS_EXPORT_TABLE_02 EXPORT    TABLE     NOT RUNNING        0
SYSTEM     SYS_EXPORT_FULL_01  EXPORT    FULL      NOT RUNNING        0

Step 2. Ensure that the listed jobs in DBA_DATAPUMP_JOBS are not active export/import Data Pump jobs. i.e. status should be 'NOT RUNNING'.

Step 3. Check with the job owner that the job with status 'NOT RUNNING' in dba_datapump_jobs is not an export/import Data Pump job that has been temporary stopped, but is actually a job that failed. (E.g. the full database export job by SYSTEM is not a job that failed, but was deliberately paused with STOP_JOB).

Step 4. Determine in SQL*Plus the related master tables:
-- locate Data Pump master tables:

SELECT OBJECTS.status, 
       OBJECTS.object_id, 
       OBJECTS.object_type, 
       OBJECTS.owner||'.'||OBJECTS.object_name "OWNER.OBJECT"
  FROM DBA_OBJECTS OBJECTS, 
       DBA_DATAPUMP_JOBS JOBS
 WHERE OBJECTS.owner=JOBS.owner_name 
   AND OBJECTS.object_name=JOBS.job_name
   AND JOBS.job_name NOT LIKE 'BIN$%' 
ORDER BY 4,2;

STATUS   OBJECT_ID OBJECT_TYPE  OWNER.OBJECT
------- ---------- ------------ -------------------------
VALID        85283 TABLE        SCOTT.EXPDP_20051121
VALID        85215 TABLE        SCOTT.SYS_EXPORT_TABLE_02
VALID        85162 TABLE        SYSTEM.SYS_EXPORT_FULL_01

Step 5. For jobs that were stopped in the past and won't be restarted anymore, delete the master table. E.g.:
DROP TABLE SCOTT.EXPDP_20051121;
DROP TABLE SCOTT.SYS_EXPORT_TABLE_02;
DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;

-- For systems with recycle bin additionally run:
PURGE DBA_RECYCLEBIN;

Step 6. Re-run the query on DBA_DATAPUMP_JOBS and DBA_OBJECTS (step 1 and 4).
If there are still jobs listed in DBA_DATAPUMP_JOBS, and these jobs do not have a master table anymore, cleanup the job while connected as the job owner. E.g.:

CONNECT scott/tiger
SET serveroutput on
SET lines 100

DECLARE
   h1 NUMBER;
BEGIN
   h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01','SCOTT');
   DBMS_DATAPUMP.STOP_JOB (h1);
END;
/

Note that after the call to the STOP_JOB procedure, it may take some time for the job to be removed. Query the view USER_DATAPUMP_JOBS to check whether the job has been removed:

SELECT * FROM USER_DATAPUMP_JOBS;

Step 7. Confirm that the job has been removed:
CONNECT / as sysdba
SET lines 200 
COL owner_name FORMAT a10; 
COL job_name FORMAT a20 
COL state FORMAT a12 
COL operation LIKE state 
COL job_mode LIKE state 

-- locate Data Pump jobs: 

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions 
FROM DBA_DATAPUMP_JOBS 
WHERE job_name NOT LIKE 'BIN$%' 
ORDER BY 1,2; 

OWNER_NAME JOB_NAME            OPERATION JOB_MODE  STATE       ATTACHED
---------- ------------------- --------- --------- ----------- --------
SCOTT      EXPDP_20051121      EXPORT    SCHEMA    EXECUTING          1
SYSTEM     SYS_EXPORT_FULL_01  EXPORT    FULL      NOT RUNNING        0

-- locate Data Pump master tables:
SELECT OBJECTS.status, 
       OBJECTS.object_id, 
       OBJECTS.object_type, 
       OBJECTS.owner||'.'||OBJECTS.object_name "OWNER.OBJECT"
  FROM DBA_OBJECTS OBJECTS, 
       DBA_DATAPUMP_JOBS JOBS
 WHERE OBJECTS.owner=JOBS.owner_name 
   AND OBJECTS.object_name=JOBS.job_name
   AND JOBS.job_name NOT LIKE 'BIN$%' 
ORDER BY 4,2;

STATUS   OBJECT_ID OBJECT_TYPE  OWNER.OBJECT
------- ---------- ------------ -------------------------
VALID        85283 TABLE        SCOTT.EXPDP_20051121
VALID        85162 TABLE        SYSTEM.SYS_EXPORT_FULL_01


Remarks:
1. Orphaned Data Pump jobs do not have an impact on new Data Pump jobs.
The view DBA_DATAPUMP_JOBS is a view, based on GV$DATAPUMP_JOB, OBJ$, COM$, and USER$.
The view shows the Data Pump jobs that are still running, or jobs for which the master table was kept in the database, or in case of an abnormal end of the Data Pump job (the orphaned job).
If a new Data Pump job is started, a new entry will be created, which has no relation to the old Data Pump jobs.

2. When starting the new Data Pump job and using a system generated name, we check the names of existing Data Pump jobs in the DBA_DATAPUMP_JOBS in order to obtain a unique new system generated jobname. Naturally, there needs to be enough free space for the new master table to be created in the schema that started the new Data Pump job.

3. A Data Pump job is not the same as a job that is defined with DBMS_JOBS.
Jobs created with DBMS_JOBS use there own processes.
Data Pump jobs use a master process and worker process(es).
In case a Data Pump still is temporary stopped (STOP_JOB while in interactive command mode), the Data Pump job still exists in the database (status: NOT RUNNING), while the master and worker process(es) are stopped and do not exist anymore. The client can attach to the job at a later time, and continue the job execution (START_JOB).

4. The possibility of corruption when the master table of an active Data Pump job is deleted, depends on the Data Pump job.

4.a. If the job is an export job, corruption is unlikely as the drop of the master table will only cause the Data Pump master and worker processes to abort. This situation is similar to aborting an export of the original export client.

4.b. If the job is an import job then the situation is different. When dropping the master table, the Data Pump worker and master processes will abort. This will probably lead to an incomplete import: e.g. not all table data was imported, and/or table was imported incomplete, and indexes, views, etc. are missing. This situation is similar to aborting an import of the original import client.

The drop of the master table itself, does not lead to any data dictionary corruption. If you keep the master table after the job completes (using the undocumented parameter: KEEP_MASTER=Y), then a drop of the master table afterwards, will not cause any corruption.

SQLServer code example - load data into tables and process data by delimiter

=======================
Scripts to load data
=======================

main_server_data_load.bat

bcp_load_server_data.bat
server_data_process.bat
move_files.bat

=======================
T-SQL to process data
=======================
main_populate_server_data

=======================
Scripts to load data
=======================
main_server_data_load.bat
REM call to bcp script
ECHO OFF
SET fileName=%1%
REM SET fileName=AUS_VODAF.txt
ECHO Handling file %fileName%
CALL bcp_load_server_data.bat %fileName%
CALL move_files.bat %fileName%

bcp_load_server_data.bat

REM load data with bcp utility
ECHO OFF

SET fileName=%1%

ECHO Loading data from file %fileName%

ECHO Starting to load data from file %fileName% to table SupportTool.dbo.[server_raw_data_input]

bcp SupportTool.dbo.[server_raw_data_input] in D:\serverData\input\%fileName% -S"10.135.100.106" -P"1qaz!QAZ" -c -e..\logs\Data_Load.err

ECHO Done

server_data_process.bat
REM invoke a procedure in SQL Server 2000, is done via osql utility
osql -E -S <server> -d <database> -Q "EXEC <schema>.<procedure>" > ../logs/process.log

move_files.bat

REM move processed files to history folder
ECHO OFF

SET from_fileName=%1%

SET to_fileName=%1%_loaded

ECHO Moving file %from_fileName%

ECHO move D:\serverData\input\%from_fileName% D:\serverData\history\%to_fileName%

REM move D:\serverData\input\%from_fileName% D:\serverData\history\%to_fileName%

=======================
T-SQL to process data
=======================
main_populate_server_data
clear_server_data_before_load
clear_server_data_by_server
populate_server_raw_data
populate_server_data
clear_server_data_after_load

main_populate_server_data
USE [SupportTool]
GO
/****** Object:  StoredProcedure [dbo].[main_populate_server_data]    Script Date: 12/13/2015 18:05:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Alec Kaplan>
-- Create date: <30 03 2015,,>
-- Description: <Populate server_xxx tables>
-- =============================================
--DROP PROCEDURE [dbo].[main_populate_server_data] 
ALTER PROCEDURE [dbo].[main_populate_server_data]   
AS
  DECLARE @v_server_name [nvarchar](100);
  DECLARE @v_module_name [nvarchar](100);
  DECLARE @v_step_sts    INT;
  SET @v_server_name =   '*';
  SET @v_module_name =   'main_populate_server_data';
  SET @v_step_sts    = 0;

BEGIN
  SET NOCOUNT ON;

  EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Starting','*',@v_step_sts;
  --------------------------------
  EXEC [dbo].[add_server_trace] @v_server_name, @v_module_name, 'Calling clear_server_data_before_load','*',@v_step_sts;
  EXEC [dbo].[clear_server_data_before_load];
  EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Call to clear_server_data_before_load Completed','*',@v_step_sts;
  --------------------------------
  EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Calling populate_server_raw_data','*',@v_step_sts;
  EXEC [dbo].[populate_server_raw_data]
  EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Call to populate_server_raw_data Completed','*',@v_step_sts;
  --------------------------------
  EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Calling populate_server_data','*',@v_step_sts;
  EXEC [dbo].[populate_server_data];
  EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Calling populate_server_data Completed','*',@v_step_sts;
  --------------------------------
  EXEC [dbo].[add_server_trace] @v_server_name, @v_module_name, 'Calling clear_server_data_after_load','*',@v_step_sts;
  EXEC [dbo].[clear_server_data_after_load];
  EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Call to clear_server_data_after_load Completed','*',@v_step_sts;
  --------------------------------

  EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Finished','*',@v_step_sts;

END

clear_server_data_before_load
USE [SupportTool]
GO
/****** Object:  StoredProcedure [dbo].[clear_server_data_before_load]    Script Date: 12/13/2015 18:13:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Alec Kaplan>
-- Create date: <30 03 2015,,>
-- Description: <Populate server_xxx tables>
-- =============================================
--DROP PROCEDURE [dbo].[clear_server_data_before_load] 
ALTER PROCEDURE [dbo].[clear_server_data_before_load] 
AS
  DECLARE @v_server_name [nvarchar](100);
  DECLARE @v_module_name [nvarchar](100);
  DECLARE @v_step_name   [nvarchar](100);
  DECLARE @v_step_sts    INT;

  SET @v_server_name = '*';
  SET @v_module_name = 'clear_server_data';

BEGIN

  SET NOCOUNT ON;
  EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Starting','*',0;
  --------------------------------

  BEGIN TRANSACTION
    --------------------------------    
    SET @v_step_name = 'DELETE FROM server_load_trace'
    PRINT 'In clear_server_data Before '+ @v_step_name
    DELETE FROM server_load_trace
    SET @v_step_sts = @@ERROR
    EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @v_step_name,'Completed',@v_step_sts;
    PRINT 'In clear_server_data. Step completed: '+ @v_step_name + ' status ' +  CONVERT(varchar(1), @v_step_sts)
    --------------------------------
    SET @v_step_name = 'DELETE FROM server_raw_data'
    EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @v_step_name,'Starting',0;
    PRINT 'In clear_server_data Before '+ @v_step_name
    DELETE FROM server_raw_data 
    SET @v_step_sts = @@ERROR
    EXEC [dbo].[add_server_trace] @v_server_name, @v_module_name, @v_step_name, 'Completed', @v_step_sts;
    PRINT 'In clear_server_data. Step completed: '+ @v_step_name + ' status ' + CONVERT(varchar(1), @v_step_sts)
    --------------------------------
  COMMIT TRANSACTION
  PRINT 'In clear_server_data '+ 'Finished'
  EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Finished','*',0;
  --------------------------------

END

clear_server_data_by_server
USE [SupportTool]
GO
/****** Object:  StoredProcedure [dbo].[clear_server_data_by_server]    Script Date: 12/13/2015 18:08:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Alec Kaplan>
-- Create date: <30 03 2015,,>
-- Description: <Populate server_xxx tables>
-- =============================================
--DROP PROCEDURE [dbo].[clear_server_data_by_server] 
ALTER PROCEDURE [dbo].[clear_server_data_by_server] @p_hostname nvarchar(100), @p_ip nvarchar(100)
AS
  DECLARE @v_module_name [nvarchar](100);
  DECLARE @v_step_name   [nvarchar](100);
  DECLARE @v_step_sts    INT

  SET     @v_module_name = 'clear_server_data_by_server';
BEGIN
    
SET NOCOUNT ON;
    BEGIN TRANSACTION
      ---------------------------------------------------------
      PRINT 'Starting Delete by hostname ' + @p_hostname + ' and ip: ' + @p_ip
      ---------------------------------------------------------
      SET @v_step_name = 'DELETE FROM server_details';      
      SET @v_step_sts = 0
      PRINT 'Before ' + @v_step_name
      EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Starting', @v_step_sts;
      DELETE FROM server_details WHERE hostname = @p_hostname AND ip = @p_ip;
      SET @v_step_sts = @@ERROR
      EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Finished', @v_step_sts;
      PRINT 'After ' + @v_step_name
      ---------------------------------------------------------
      SET @v_step_name = 'DELETE FROM server_services';      
      SET @v_step_sts = 0
      PRINT 'Before ' + @v_step_name
      EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Starting', @v_step_sts;
      DELETE FROM server_services WHERE hostname = @p_hostname AND ip = @p_ip;
      SET @v_step_sts = @@ERROR
      EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Finished', @v_step_sts;      
      PRINT 'After ' + @v_step_name
      ---------------------------------------------------------
      SET @v_step_name = 'DELETE FROM server_features';      
      SET @v_step_sts = 0
      PRINT 'Before ' + @v_step_name
      EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Starting', @v_step_sts;
      DELETE FROM server_features WHERE hostname = @p_hostname AND ip = @p_ip;
      SET @v_step_sts = @@ERROR
      EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Finished', @v_step_sts;      
      PRINT 'After ' + @v_step_name
      ---------------------------------------------------------
      SET @v_step_name = 'DELETE FROM server_disks';      
      SET @v_step_sts = 0
      PRINT 'Before ' + @v_step_name
      EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Starting', @v_step_sts;
      DELETE FROM server_disks WHERE hostname = @p_hostname AND ip = @p_ip;
      SET @v_step_sts = @@ERROR
      EXEC [dbo].[add_server_trace] @p_hostname, @v_module_name, @v_step_name, 'Finished', @v_step_sts;      
      PRINT 'After ' + @v_step_name
      ---------------------------------------------------------
      PRINT 'Finished Handling hostname ' + @p_hostname + ' and ip: ' + @p_ip
      ---------------------------------------------------------
COMMIT TRANSACTION;

END

populate_server_raw_data
USE [SupportTool]
GO
/****** Object:  StoredProcedure [dbo].[populate_server_raw_data]    Script Date: 12/13/2015 18:06:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Alec Kaplan>
-- Create date: <30 03 2015,,>
-- Description: <Populate server_xxx tables>
-- =============================================
--DROP PROCEDURE [dbo].[populate_server_raw_data] 
ALTER PROCEDURE [dbo].[populate_server_raw_data] 
AS

 DECLARE get_server_raw_data_cur CURSOR FOR 
  SELECT data_field
    FROM server_raw_data_input;
  
 DECLARE @v_hostname [nvarchar](100);
 DECLARE @v_ip [nvarchar](100);
 DECLARE @v_data_type [nvarchar](100);
 DECLARE @v_field_01 [nvarchar](100);
 DECLARE @v_field_02 [nvarchar](100);
 DECLARE @v_field_03 [nvarchar](100);
 DECLARE @v_field_04 [nvarchar](100);
 DECLARE @v_field_05 [nvarchar](100);
 DECLARE @v_field_06 [nvarchar](100);
 DECLARE @v_field_07 [nvarchar](100);
 DECLARE @v_field_08 [nvarchar](100);
 DECLARE @v_field_09 [nvarchar](100);
 DECLARE @v_field_10 [nvarchar](100);
 DECLARE @v_field_11 [nvarchar](100);
 DECLARE @v_field_12 [nvarchar](100);
 DECLARE @v_field_13 [nvarchar](100);
 DECLARE @v_field_14 [nvarchar](100);
 DECLARE @v_field_15 [nvarchar](100);
 DECLARE @v_field_16 [nvarchar](100);
 DECLARE @v_field_17 [nvarchar](100);
 DECLARE @v_field_18 [nvarchar](100);
 DECLARE @v_field_19 [nvarchar](100);
 DECLARE @v_field_20 [nvarchar](100);
 DECLARE @v_field_21 [nvarchar](1000);

 DECLARE @ItemList   [nvarchar](4000);
 DECLARE @DelimIndex INT
 DECLARE @StartIndex INT
 DECLARE @Delimiter  [nvarchar](1);
 DECLARE @TotalLength INT;
 DECLARE @SearchStr   [nvarchar](4000);
 DECLARE @v_step_id   INT;
 DECLARE @v_step_sts  INT;

 DECLARE @v_server_name [nvarchar](100);
 DECLARE @v_module_name [nvarchar](100);
 DECLARE @step_name     [nvarchar](100);
 DECLARE @v_more_data   INT;
 SET @v_server_name = '*';
 SET @v_module_name = 'populate_server_raw_data';

 BEGIN

SET NOCOUNT ON;
    SET @Delimiter = ',';
    SET @v_step_sts = 0
    --SET @v_step_id = 0;

    EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Starting','*',@v_step_sts;

    BEGIN TRANSACTION;
      
      OPEN get_server_raw_data_cur
   FETCH NEXT FROM get_server_raw_data_cur INTO @ItemList;
      WHILE @@FETCH_STATUS = 0
  BEGIN 

         ------------------------------------------------------------
         SET @v_field_01 = NULL
         SET @v_field_02 = NULL
         SET @v_field_03 = NULL
         SET @v_field_04 = NULL
         SET @v_field_05 = NULL
         SET @v_field_06 = NULL
         SET @v_field_07 = NULL
         SET @v_field_08 = NULL
         SET @v_field_09 = NULL
         SET @v_field_10 = NULL
         SET @v_field_11 = NULL
         SET @v_field_12 = NULL
         SET @v_field_13 = NULL
         SET @v_field_14 = NULL
         SET @v_field_15 = NULL
         SET @v_field_16 = NULL
         SET @v_field_17 = NULL
         SET @v_field_18 = NULL
         SET @v_field_19 = NULL
         SET @v_field_20 = NULL
         SET @v_field_21 = NULL
         SET @v_more_data = 1
         ------------------------------------------------------------

         SET @TotalLength = LEN(@ItemList);
         SET @SearchStr = @ItemList;
         SET @DelimIndex = 0;
         SET @StartIndex = 0;

         EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr,@v_step_sts;
         ---------------------------------------         
         PRINT 'Search String: '+ @SearchStr
         SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         IF @DelimIndex > 0 
         BEGIN
SET @v_hostname = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
SET @v_step_sts = @@ERROR
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'host name',@v_hostname, @v_step_sts;
EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr,@v_step_sts;       
             SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ---------------------------------------
         PRINT 'Search String: '+ @SearchStr
         IF @DelimIndex > 0 
         BEGIN         
           SET @v_ip = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'ip',@v_ip, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         --------------------------------------- 
         PRINT 'Search String: '+ @SearchStr        
         IF @DelimIndex > 0 
         BEGIN                  
           SET @v_data_type = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'data type',@v_data_type, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ---------------------------------------
         PRINT 'compute @v_field_01 Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         IF (@DelimIndex > 0 AND @v_more_data = 1)
           BEGIN         
             SET @v_field_01 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
             SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
             SET @v_step_sts = @@ERROR
             EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'field_01',@v_field_01, @v_step_sts;
             EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
             SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
           END
         ELSE
           BEGIN
             SET @v_field_02 = @SearchStr
             SET @v_more_data = 0
           END
         ---------------------------------------
         PRINT 'compute @v_field_02 Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         IF @DelimIndex > 0 
         BEGIN                 
           SET @v_field_02 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'field_02',@v_field_02, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ---------------------------------------
         SET @step_name = 'field_03'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_03 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_03 = @SearchStr
             SET @v_more_data = 0
           END
         END
         IF @v_field_03 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_03;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_03, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         ---------------------------------------
         SET @step_name = 'field_04'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_04 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_04 = @SearchStr
             SET @v_more_data = 0
           END
         END
         IF @v_field_04 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_04;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_04, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         ---------------------------------------
         SET @step_name = 'field_05'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_05 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_05 = @SearchStr
             SET @v_more_data = 0
           END
         END
         IF @v_field_05 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_05;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_05, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END

         ---------------------------------------
         SET @step_name = 'field_06'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_06 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_06 = @SearchStr
             SET @v_more_data = 0
           END
         END
         IF @v_field_06 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_06;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_06, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END

         ---------------------------------------
         SET @step_name = 'field_07'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_07 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_07 = @SearchStr
             SET @v_more_data = 0
           END
         END
         IF @v_field_07 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_07;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_07, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         ---------------------------------------
         SET @step_name = 'field_08'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_08 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_08 = @SearchStr
             SET @v_more_data = 0
           END
         END
         IF @v_field_08 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_08;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_08, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         ---------------------------------------
         SET @step_name = 'field_09'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_09 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_09 = @SearchStr
             SET @v_more_data = 0
           END
         END
         IF @v_field_09 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_09;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_09, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END         ---------------------------------------
         SET @step_name = 'field_10'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_10 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_10 = @SearchStr
             SET @v_more_data = 0
           END
         END
         IF @v_field_10 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_10;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_10, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         ---------------------------------------
         SET @step_name = 'field_11'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_11 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_11 = @SearchStr
             SET @v_more_data = 0
           END
         END
         IF @v_field_11 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_11;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_11, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         ---------------------------------------
         SET @step_name = 'field_12'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_12 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_12 = @SearchStr
             SET @v_more_data = 0
           END
         END
         IF @v_field_12 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_12;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_12, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         ---------------------------------------
         SET @step_name = 'field_13'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_13 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_13 = @SearchStr
             SET @v_more_data = 0
           END
         END
         IF @v_field_13 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_13;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_13, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         ---------------------------------------
         SET @step_name = 'field_14'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_14 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_14 = @SearchStr
             SET @v_more_data = 0
           END
         END

         IF @v_field_14 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_14;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_14, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         
         ---------------------------------------
         SET @step_name = 'field_15'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_15 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_15 = @SearchStr
             SET @v_more_data = 0
           END
         END

         IF @v_field_15 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_15;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_15, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         ---------------------------------------
         SET @step_name = 'field_16'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_16 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_16 = @SearchStr
             SET @v_more_data = 0
           END
         END

         IF @v_field_16 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_16;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_16, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         ---------------------------------------
         SET @step_name = 'field_17'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_17 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_17 = @SearchStr
             SET @v_more_data = 0
           END
         END

         IF @v_field_17 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_17;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_17, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         ---------------------------------------
         SET @step_name = 'field_18'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_18 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_18 = @SearchStr
             SET @v_more_data = 0
           END
         END

         IF @v_field_18 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_18;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_18, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         ---------------------------------------
         SET @step_name = 'field_19'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_19 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_19 = @SearchStr
             SET @v_more_data = 0
           END
         END

         IF @v_field_19 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_19;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_19, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         ---------------------------------------
         SET @step_name = 'field_20'
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @v_more_data ' + CONVERT([nvarchar], @v_more_data)
         PRINT 'compute '+ @step_name+' Search String: '+ @SearchStr + ' @DelimIndex ' + CONVERT([nvarchar], @DelimIndex)
         IF (@DelimIndex > 0) BEGIN
           PRINT 'compute '+ @step_name+' Inside IF'
           SET @v_field_20 = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
           SET @SearchStr = SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1)
           SET @v_step_sts = @@ERROR
           SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         END
         ELSE BEGIN
           IF (@v_more_data = 1) BEGIN
             PRINT 'compute '+ @step_name+' Inside ELSE'
             SET @v_field_20 = @SearchStr
             SET @v_more_data = 0
           END
         END

         IF @v_field_20 IS NULL BEGIN
           PRINT @step_name + ' is empty'
         END
         ELSE BEGIN
           PRINT @step_name +' was set to ' + @v_field_20;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @step_name, @v_field_20, @v_step_sts;
           EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'handle data',@SearchStr, @v_step_sts;
         END
         ---------------------------------------
         PRINT 'MY Row: ' + @v_hostname+ ' ' + @v_ip+        ' ' + @v_data_type
         PRINT 'MY Row: ' + ISNULL(@v_field_01,' ')+ ' ' + ISNULL(@v_field_02,' ') + ' ' + ISNULL(@v_field_03,' ') + ' ' + ISNULL(@v_field_04,' ') + ' ' + ISNULL(@v_field_05,' ') + ' ' + ISNULL(@v_field_06,' ') + ' ' + ISNULL(@v_field_07,' ') + ' '+ ISNULL(@v_field_08,' ') + ' ' + ISNULL(@v_field_09,' ') + ' '+ ISNULL(@v_field_10,' ')
         PRINT 'MY Row: ' + ISNULL(@v_field_11,' ')+ ' ' + ISNULL(@v_field_12,' ') + ' ' + ISNULL(@v_field_13,' ') + ' ' + ISNULL(@v_field_14,' ') + ' ' + ISNULL(@v_field_15,' ') + ' ' + ISNULL(@v_field_16,' ') + ' ' + ISNULL(@v_field_17,' ') + ' '+ ISNULL(@v_field_18,' ') + ' ' + ISNULL(@v_field_19,' ') + ' '+ ISNULL(@v_field_20,' ')
                    
         ---------------------------------------
         
    INSERT INTO server_raw_data (hostname, ip, data_type ,
                    field_01, field_02, field_03, field_04, field_05, field_06, field_07, field_08, field_09, field_10,
                    field_11, field_12, field_13, field_14, field_15, field_16, field_17, field_18, field_19, field_20)
            VALUES (@v_hostname, @v_ip, @v_data_type,
                    @v_field_01, @v_field_02, @v_field_03, @v_field_04, @v_field_05, @v_field_06, @v_field_07, @v_field_08, @v_field_09, @v_field_10,
                    @v_field_11, @v_field_12, @v_field_13, @v_field_14, @v_field_15, @v_field_16, @v_field_17, @v_field_18, @v_field_19, @v_field_20);

      FETCH NEXT FROM get_server_raw_data_cur INTO @ItemList;
 
END
CLOSE get_server_raw_data_cur;
DEALLOCATE get_server_raw_data_cur;

COMMIT TRANSACTION;  
    EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Finished','*', @v_step_sts;

END

populate_server_data
USE [SupportTool]
GO
/****** Object:  StoredProcedure [dbo].[populate_server_data]    Script Date: 12/13/2015 18:10:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Alec Kaplan>
-- Create date: <30 03 2015,,>
-- Description: <Populate server_xxx tables>
-- =============================================
--DROP PROCEDURE [dbo].[populate_server_data] 
ALTER PROCEDURE [dbo].[populate_server_data]  
AS

 DECLARE get_server_raw_data_pk_cur CURSOR
     FOR SELECT hostname, ip 
    FROM server_raw_data
WHERE server_raw_data.data_type = 'SERVER'
--           AND server_raw_data.hostname = 'aus-vod-2-aps-1'
      ORDER BY hostname, ip; 
-- ORDER BY DECODE (server_raw_data.data_type,'SERVER','1', 'SERVICE','2','DISK','3',data_type)
        -- ORDER BY CASE WHEN server_raw_data.data_type = 'SERVER' THEN '1'
        --               WHEN server_raw_data.data_type = 'SERVICE' THEN '2'
        --               WHEN server_raw_data.data_type = 'DISK' THEN '3'
        --               ELSE server_raw_data.data_type
        --         END
  
 DECLARE @v_hostname [nvarchar](100);
 DECLARE @v_ip [nvarchar](100);
 DECLARE @v_data_type [nvarchar](100);
 DECLARE @v_field_01 [nvarchar](100);

  
 DECLARE @v_type_server  [nvarchar](100); 
 DECLARE @v_type_service [nvarchar](100);
 DECLARE @v_type_disk    [nvarchar](100);

 DECLARE @v_step_name    [nvarchar](100);

 DECLARE @v_step_data    [nvarchar](1000);
 DECLARE @v_service_name [nvarchar](100);
 DECLARE @v_schema_name  [nvarchar](100);
 DECLARE @v_feature_list [nvarchar](1000);
 DECLARE @v_step_sts     INT;
 DECLARE @v_module_name  [nvarchar](100);
 DECLARE @feature_delimiter [nvarchar](1);
 DECLARE @DelimIndex     INT;
 DECLARE @Delimiter [nvarchar](1); 
 DECLARE @StartIndex     INT
 DECLARE @v_feature_name  [nvarchar](100);
 DECLARE @TotalLength    INT;
 DECLARE @SearchStr       [nvarchar](1000);

 SET  @v_type_server  = 'SERVER';
 SET  @v_type_service = 'SERVICE';
 SET  @v_type_disk    = 'DISK';

 SET  @v_module_name = 'populate_server_data';
 SET  @feature_delimiter = '|';
 BEGIN

SET NOCOUNT ON;
    
    PRINT 'Staring '+ @v_module_name
    PRINT 'Opening Cursor get_server_raw_data_pk_cur in '+ @v_module_name

    ------------------------------------------------
    -- Start Delete Loop
    ------------------------------------------------
    OPEN get_server_raw_data_pk_cur
FETCH NEXT FROM get_server_raw_data_pk_cur INTO @v_hostname, @v_ip;
    WHILE @@FETCH_STATUS = 0
  BEGIN 
         ---------------------------------------------------
         BEGIN TRANSACTION;
         ---------------------------------------------------

           PRINT 'Calling to clear_server_data_by_server for '+  @v_hostname +' '+ @v_ip
           EXEC [dbo].[clear_server_data_by_server] @v_hostname, @v_ip; 
           PRINT 'After clear_server_data_by_server'

         COMMIT TRANSACTION;   
         FETCH NEXT FROM get_server_raw_data_pk_cur INTO @v_hostname, @v_ip
  
 END
CLOSE get_server_raw_data_pk_cur


    ------------------------------------------------
    -- Start Populate Loop
    ------------------------------------------------

    OPEN get_server_raw_data_pk_cur
FETCH NEXT FROM get_server_raw_data_pk_cur INTO @v_hostname, @v_ip;
    WHILE @@FETCH_STATUS = 0
  BEGIN 

         ---------------------------------------------------
         BEGIN TRANSACTION;
         ---------------------------------------------------
        INSERT INTO server_details (hostname, ip,server_type, total_memory, free_memory, cpu_cores, cpu_speed, cpu_model, redhat_version)
                         SELECT hostname, ip, 
                           field_01, field_02, field_03, field_04, field_05, field_06, field_07                        
             FROM  server_raw_data
             WHERE server_raw_data.hostname = @v_hostname
      AND server_raw_data.ip = @v_ip
      AND server_raw_data.data_type = @v_type_server;
         
         SET @v_step_sts = @@ERROR
         SET @v_step_name = 'INSERT INTO server_details';         
         SELECT @v_step_data =  ' hostname = ' + @v_hostname + ' ; IP = ' + @v_ip + ' ; TYPE = '+@v_type_server
         EXEC [dbo].[add_server_trace] @v_hostname, @v_module_name, @v_step_name, @v_step_data, @v_step_sts;
         PRINT 'After '+@v_step_name + ' for '+  @v_hostname
         ---------------------------------------------------
         SET @v_step_name = 'INSERT INTO server_services';
         SELECT @v_step_data =  'Before INSERT INTO server_services hostname = ' + @v_hostname + ' ; IP = ' + @v_ip + ' ; TYPE = '+ @v_type_service
         EXEC [dbo].[add_server_trace] @v_hostname, @v_module_name, @v_step_name, @v_step_data, @v_step_sts;
         PRINT 'Before '+@v_step_name + ' for '+  @v_hostname + ' ; IP = ' + @v_ip;

    INSERT INTO server_services (hostname, ip, 
                                      service_name, schema_name, db_server_ip, db_version, ig_version,
                                      ig_common_version, ig_infra_version, crm_version, glr_version,ulticom_version,
 tcap_list, cnf_list, omni_version, is_redundant,prov_base,
                                      prov_version, rpt_version)
              SELECT hostname, ip, 
                     field_01, field_02, field_03, field_04, field_05,
                     field_06, field_07, field_08, field_09, field_10,
                     field_11, field_12, field_13, field_14, field_15,
                     field_16,field_17
                FROM  server_raw_data
                WHERE server_raw_data.hostname = @v_hostname
         AND server_raw_data.ip = @v_ip
         AND server_raw_data.data_type = @v_type_service;

 -- aus-vod-2-aps-1,10.98.20.22,SERVICE,aus-hut-lid,AUS_HUTCH_IRMQQ,10.98.20.22,4.0.017,
 -- ig_version,ig_common_version,ig_infra_version,crm_version,glr_version,
 -- ulticom_version,tcap_list,cnf_list,omni_version,is_redundant,prov_base,prov_version,
 -- aus-vod-2-aps-1,10.98.20.22,SERVICE,aus-hut-lid,AUS_HUTCH_IRMQQ,10.98.20.22 ,4.0.017   ,,,,,,,,,,,,,,<feature_list>
 -- hostname       ,ip         ,SERVICE,field_01   ,field_02       ,field_03    ,field_04  ,,,,,,,,,,,,,,<feature_list>

        
         SET @v_step_sts = @@ERROR
         SET @v_step_name = 'INSERT INTO server_services';
         SELECT @v_step_data =  ' hostname = ' + @v_hostname + ' ; IP = ' + @v_ip + ' ; TYPE = '+ @v_type_server
         EXEC [dbo].[add_server_trace] @v_hostname, @v_module_name, @v_step_name,@v_step_data, @v_step_sts;
         PRINT 'After '+@v_step_name + ' for '+  @v_hostname+ ' ; IP = ' + @v_ip
         ----------------------------------------------------
         SET @v_step_name = 'INSERT INTO server_features';
         PRINT 'Before '+@v_step_name + ' for '+  @v_hostname + ' ; IP = ' + @v_ip
         PRINT 'feature list: '+@v_feature_list
         SELECT @v_field_01 = field_01, @v_feature_list =  field_18 
           FROM  server_raw_data
          WHERE server_raw_data.hostname = @v_hostname
  AND server_raw_data.ip = @v_ip
  AND server_raw_data.data_type = @v_type_service
         PRINT 'Getting feature list'+ ' for '+  @v_hostname + ' ; IP = ' + @v_ip + ' data_type=' + @v_type_service
         PRINT 'feature list: '+@v_feature_list
         --Roaming Information| System| Utilities| Leakage Identifier| 
         SET @Delimiter = @feature_delimiter;
         SET @TotalLength = LEN(@v_feature_list);         
         SET @SearchStr = @v_feature_list;
         SET @StartIndex = 0;
         
         SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
         WHILE @DelimIndex > 0
           BEGIN             
             SET @v_feature_name = SUBSTRING(@SearchStr, @StartIndex, @DelimIndex)
             PRINT 'Adding server_feature : ' + @v_hostname +' '+ @v_ip+' '+ @v_field_01+' '+ @v_feature_name
             INSERT INTO server_features (hostname, ip, service_name, feature_name)
                 VALUES (@v_hostname, @v_ip, @v_field_01, @v_feature_name);
             PRINT 'Adding server_feature Done';
              SET @SearchStr = LTRIM(SUBSTRING(@SearchStr, @DelimIndex+1, @TotalLength-@DelimIndex+1))
              SET @DelimIndex = CHARINDEX(@Delimiter, @SearchStr, @StartIndex)
              SET @TotalLength = LEN(@SearchStr);   
         END;

         SET @v_step_sts = @@ERROR
         SELECT @v_step_data =  ' hostname = ' + @v_hostname + ' ; IP = ' + @v_ip + ' ; TYPE = '+ @v_type_server
         EXEC [dbo].[add_server_trace] @v_hostname, @v_module_name, @v_step_name, @v_step_data, @v_step_sts;
         PRINT 'After '+@v_step_name + ' for '+  @v_hostname + ' ; IP = ' + @v_ip

         ---------------------------------------------------
         SET @v_step_name = 'INSERT INTO server_disks';
         SELECT @v_step_data =  'Before INSERT INTO server_disks hostname = ' + @v_hostname + ' ; IP = ' + @v_ip + ' ; TYPE = '+ @v_type_disk
         EXEC [dbo].[add_server_trace] @v_hostname, @v_module_name, @v_step_name, @v_step_data, @v_step_sts;
         PRINT 'Before '+@v_step_name + ' for '+  @v_hostname + ' ; IP = ' + @v_ip

INSERT INTO server_disks (hostname, ip, mount, total, used, free, used_pct) 
              SELECT hostname, ip, field_05, field_01, field_02, field_03, field_04
           FROM  server_raw_data
           WHERE server_raw_data.hostname = @v_hostname
    AND server_raw_data.ip = @v_ip
    AND server_raw_data.data_type = @v_type_disk;
         ---------------------------------------------------
         SET @v_step_sts = @@ERROR
         
         SELECT @v_step_data =  'After INSERT INTO server_disks hostname = ' + @v_hostname + ' IP = ' + @v_ip + ' TYPE = '+@v_type_disk
         --EXEC [dbo].[add_server_trace] @v_hostname, @v_module_name, @v_step_name, @v_step_data, @v_step_sts;
         PRINT 'After '+@v_step_name + ' for '+  @v_hostname + ' ; IP = ' + @v_ip + ' ; TYPE = '+@v_type_disk
         ---------------------------------------------------

         INSERT INTO server_log(hostname, ip, data_type)  VALUES (@v_hostname, @v_ip, 'SERVER');

 COMMIT TRANSACTION;   
      FETCH NEXT FROM get_server_raw_data_pk_cur INTO @v_hostname, @v_ip

END
CLOSE get_server_raw_data_pk_cur
DEALLOCATE get_server_raw_data_pk_cur


END

clear_server_data_after_load

USE [SupportTool]

GO

/****** Object:  StoredProcedure [dbo].[clear_server_data_after_load]    Script Date: 12/13/2015 18:12:29 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Alec Kaplan>

-- Create date: <30 03 2015,,>

-- Description: <Populate server_xxx tables>

-- =============================================

--DROP PROCEDURE [dbo].[clear_server_data_after_load] 

ALTER PROCEDURE [dbo].[clear_server_data_after_load] 

AS

  DECLARE @v_server_name [nvarchar](100);

  DECLARE @v_module_name [nvarchar](100);

  DECLARE @v_step_name   [nvarchar](100);

  DECLARE @v_step_sts    INT;

  SET @v_server_name = '*';

  SET @v_module_name = 'clear_server_data';

BEGIN

  SET NOCOUNT ON;

  EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Starting','*',0;

  --------------------------------

  BEGIN TRANSACTION

    --------------------------------    

    SET @v_step_name = 'Skip Delete from server_raw_data_input'

    PRINT 'In clear_server_data Before '+ @v_step_name

    EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @v_step_name,'Starting', 0;

    --DELETE FROM server_raw_data_input 

    SET @v_step_sts = @@ERROR

    EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, @v_step_name,'Completed', @v_step_sts;

    --------------------------------

  COMMIT TRANSACTION

  PRINT 'In clear_server_data '+ 'Finished'

  EXEC [dbo].[add_server_trace] @v_server_name,@v_module_name, 'Finished','*',0;

  --------------------------------

END