get Element value from xml

declare @Text xml ;

set @Text= '<?xml version="1.0" encoding="utf-8"?>

<Project>

    <System_Record_ID><![CDATA[35]]></System_Record_ID>

      <Project_ID><![CDATA[1100]]></Project_ID>

 

</Project>'

;with t as (select @Text [xmlcolumn])

--select * from t

SELECT  x.a.value('(System_Record_ID)[1]','nvarchar(max)') as [System_Record_ID]

,       x.a.value('(Project_ID)[1]','nvarchar(max)') as [Project_ID]

FROM   t

cross apply

t.xmlcolumn.nodes('//Project') x(a)

substring , reverse and charindex

declare @s varchar(200);

set @s='F9ADD13F-B055-497B-83CB-ECF6CEE29C0E;163;AAA Concrete;Payment;10.00;Cash;2015-07-23;(Payment) 10.00 Cash - AAA Concrete;24;'
declare @a varchar(200);
declare @b varchar(200);
declare @c varchar(200);
declare @d varchar(200);
declare @e varchar(200);
declare @f varchar(200);
declare @g varchar(200);
declare @h varchar(200);


set @a=       SUBSTRING(@s, charindex(';', @s)+1, len(@s) )

--select @a;

set @b=   SUBSTRING(@a,CHARINDEX(';',@a)+1 ,len(@a))

set @c=   SUBSTRING(@b,CHARINDEX(';',@b)+1 ,len(@b))

set @d=   SUBSTRING(@c,CHARINDEX(';',@c)+1 ,len(@c))

--select @d;
set @e=SUBSTRING(@d,0,CHARINDEX(';',@d) )

select @e;

set @f= SUBSTRING(REVERSE(@s),2,len(@s))



set @g= REVERSE(SUBSTRING(@f, 1, charindex(';', @f)-1))


select @g;

Windows basic tools which are very useful while troubleshooting :

Windows basic tools which are very useful while troubleshooting :

-> Event viewer:

Event viewer is the important tool where you can find all logs of windows as well as about all your applications. It will very useful to find cause of issues. like let say , application programmers generally writes all event logs in code. so whenever your application stops working or give some weird issue that time you can find log information in eventlogs/event viewer

If you are working with client/server troubleshooting than you can find logs on server as well if issues is from server side. you can open event viewer from control panel->system and security -> Administrative tools-> Event viewer

short cut to open that : open Run (you can open that by pressing Windows+R button) and type eventvwr and event viewer will open.

Below is look of that:

Alos more information you can get from msdn blog:

http://windows.microsoft.com/en-in/windows/what-information-event-logs-event-viewer#1TC=windows-7



-> Services in Windows:

 this will be very useful during your troubleshooting , whatever services is running within your system you will find here:

to open that: Open Control Panel -> System and Security -> Administrative Tools -> Services

Shortcut to open that:

type windows+R and type services.msc and services will open

How it looks like:

from here you can start service , stop service , by right click and select properties you can setup various things like how you want to run your service like automatic, manual etc.

you can also find more information from below msdn blog:

http://www.7tutorials.com/what-are-windows-services-what-they-do-how-manage-them

Registry Editor:

Registry editor is tool from where you can find registry of you system , your applications etc. Generally application programmer/developer used to write few values in registry like installation information , few config values and during troubleshooting you may need to check/update these values . Even some time you need to check system registry , microsoft applications registry etc

that time you will need to open resgistry editor:

How to open that:
Type windows +R and  type "regedit"   (without quote) and it will open.

Here is msdn article:
http://windows.microsoft.com/en-in/windows/what-is-registry-editor#1TC=windows-7

Here is its look:

Fire Wall:

Some times you need to check firewall settings , mainly during troubleshooting of client server programs , that time you will need to open port from server and client which your application is using . for that you will need to open firewall

How to open that:

type windows +R and type "firewall.cpl" (without quote) and it will open.

Below is look of firewall:

More from msdn:
http://windows.microsoft.com/en-in/windows/what-is-firewall#1TC=windows-7

Basic Tips of SQL Scripts : Need to know while troubleshooting

Good SQL knowledge is very necessary when you do technical troubleshooting. In this part you must have knowledge about basic sql like insert, update , delete ,create, drop , truncate.

->Also you must have knowledge about few keywords like "between", "top " , "order by" , "group by ", "distinct", "Replace",

->Also learn Join , mainly inner join will be very useful. Trigger, stored procedure will be also useful to troubleshoot deeply. 

->Trigger will be helpful when table's data are being updated and you need to track all data information , you can create new table and track records in that table. see below trigger example,

->Cursor knowledge is required when you will need to update multiple rows dynamically.

-> Also try to learn SQL Server Profiler , it will be very useful when you want to track sql scripts

You can follow below article for this:

http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step
See below sample scripts to get basic idea of inner join , cursor, trigger:

Inner Join Sample

select h.ID,m.ID as moduleid,dt.ID as DocTypeId,dt.Name as docTypeName,tt.Name as transactionTypeNAME,tt.ID AS tRANSACTIONTYPEID,dt.* from DocumentType dt

inner join TransactionType tt on dt.TransactionType_ID =tt.ID

inner join Module m on tt.Module_ID=m.ID

inner join HostApplication h on m.HostApplication_ID=h.ID  order by dt.Name desc

Cursor Sample:

1) 

DECLARE @i int

DECLARE @HaldID bigint

DECLARE @HaldParentID varchar(250)

DECLARE @UpdateHALD CURSOR

--BBCRM - Prospect - 442

PRINT 'BBCRM - Prospect  - 442'

SET @i=0;

SET @UpdateHALD = CURSOR FOR

SELECT ID,ParentID FROM PaperSave..HostApplicationLocalData WITH (NOLOCK)

 WHERE TransactionType_ID=442 AND ID IN (SELECT HostApplicationLocalData_ID FROM Document) and len(HostDataValues)<40

OPEN @UpdateHALD

FETCH NEXT

FROM @UpdateHALD INTO @HaldID,@HaldParentID

WHILE @@FETCH_STATUS = 0

BEGIN

WITH GetHALD AS (SELECT UPPER(CONVERT(varchar(100),PROS.ID)) AS [ParentID] ,UPPER(CONVERT(varchar(100),PROS.ID)) AS [Prospect ID] ,C.NAME AS [Constituent],c.LOOKUPID As [Lookup ID],ISNULL((select NAME From Shelby..CONSTITUENT Where ID=PROS.PROSPECTMANAGERFUNDRAISERID),'')AS [Prospect Manager],convert(date,isnull(PROS.PROSPECTMANAGERSTARTDATE,'1999-01-01')) AS [Start Date],ISNULL(AD.CITY,'') AS  [City],ISNULL(STATE.DESCRIPTION,'') As [State],ISNULL(Ad.POSTCODE,'') As [Zip] FROM Shelby..PROSPECT PROS LEFT JOIN Shelby..CONSTITUENT C on PROS.ID = C.ID  LEFT JOIN (select  CONSTITUENTID, CITY, POSTCODE,ADDRESSBLOCK,Convert(varchar(100),STATEID) as STATEID From Shelby..ADDRESS where ISPRIMARY = 1) AD ON c.ID = AD.CONSTITUENTID  LEFT JOIN Shelby..STATE ON STATE.ID = AD.STATEID WHERE UPPER(CONVERT(varchar(100),PROS.ID))=@HaldParentID)

UPDATE PaperSave..HostApplicationLocalData

SET HostData='<?xml version="1.0" encoding="utf-8"?><Prospect><Prospect_ID><![CDATA[' + (SELECT ISNULL([Prospect ID],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ']]></Prospect_ID><Constituent><![CDATA[' + (SELECT ISNULL([Constituent],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ']]></Constituent><Lookup_ID><![CDATA[' + (SELECT ISNULL([Lookup ID],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ']]></Lookup_ID><Prospect_Manager><![CDATA[' + (SELECT ISNULL([Prospect Manager],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ']]></Prospect_Manager><Start_Date><![CDATA[' + (SELECT CONVERT(varchar,[Start Date]) FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ']]></Start_Date><City><![CDATA[' + (SELECT ISNULL([City],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ']]></City><State><![CDATA[' + (SELECT ISNULL([State],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ']]></State><Zip><![CDATA[' + (SELECT ISNULL([Zip],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ']]></Zip></Prospect>'

,

HostDataValues= (SELECT ISNULL([Prospect ID],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ';' + (SELECT ISNULL([Constituent],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ';' + (SELECT ISNULL([Lookup ID],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ';' + (SELECT ISNULL([Prospect Manager],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ';' + (SELECT CONVERT(varchar,[Start Date]) FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ';' + (SELECT ISNULL([City],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ';' + (SELECT ISNULL([State],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ';' + (SELECT ISNULL([Zip],'') FROM GetHALD WHERE ParentID=HostApplicationLocalData.ParentID) + ';'

WHERE TransactionType_ID=442 AND ID=@HaldID

SET @i=@i+1;

PRINT CONVERT(varchar,@i) + ' Updated'

FETCH NEXT

FROM @UpdateHALD INTO @HaldID,@HaldParentID

END

CLOSE @UpdateHALD

DEALLOCATE @UpdateHALD

2)

use PaperSave

DECLARE @i int

DECLARE @HALD_ID bigint

DECLARE @HostDataValues varchar(250)

DECLARE @UpdateParentID CURSOR

declare @s varchar(200);

declare @a varchar(200);

declare @b varchar(200);

declare @c varchar(200);

declare @d varchar(200);

declare @e varchar(200);

declare @f varchar(200);

declare @g varchar(200);

declare @h varchar(200);

declare @Parentid varchar(200);

SET @UpdateParentID = CURSOR FOR

select ID , HostDataValues from HostApplicationLocalData where TransactionType_ID=340  and  Id=10132

SET @i=0;

OPEN @UpdateParentID

FETCH NEXT

FROM @UpdateParentID INTO @HALD_ID,@HostDataValues

WHILE @@FETCH_STATUS = 0

BEGIN

--select parentid  from HostApplicationLocalData where TransactionType_ID=340  and  Id=10132

-- F9ADD13F-B055-497B-83CB-ECF6CEE29C0E;163;AAA Concrete;Payment;10.00;Cash;2015-07-23;(Payment) 10.00 Cash - AAA Concrete;24;

set @s=@HostDataValues;

set @a=       SUBSTRING(@s, charindex(';', @s)+1, len(@s) )

--select @a;

set @b=   SUBSTRING(@a,CHARINDEX(';',@a)+1 ,len(@a))

set @c=   SUBSTRING(@b,CHARINDEX(';',@b)+1 ,len(@b))

set @d=   SUBSTRING(@c,CHARINDEX(';',@c)+1 ,len(@c))

--select @d;

set @e=SUBSTRING(@d,0,CHARINDEX(';',@d) )

select @e;

set @f= SUBSTRING(REVERSE(@s),2,len(@s))

set @g= REVERSE(SUBSTRING(@f, 1, charindex(';', @f)-1))

select @g;

set @Parentid= (select ID from BBInfinity..revenue where batchnumber =@g and amount=@e);

update HostApplicationLocalData set ParentID=@Parentid where id=@HALD_ID

-- get PO number from document id

-- store below query result in one variable

FETCH NEXT

FROM @UpdateParentID INTO  @HALD_ID,@HostDataValues

END

CLOSE @UpdateParentID

DEALLOCATE @UpdateParentID

Trigger Sample

USE [BBInfinity]

GO

/****** Object:  Table [dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_trigger]    Script Date: 7/2/2015 12:23:33 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_trigger](

[ID] [uniqueidentifier] NOT NULL,

[COMMITEDBATCHREVENUEID] [nvarchar](50) NOT NULL,

[ADDEDBYID] [uniqueidentifier] NOT NULL,

[CHANGEDBYID] [uniqueidentifier] NOT NULL,

[DATEADDED] [datetime] NOT NULL,

[DATECHANGED] [datetime] NOT NULL,

[TS] [timestamp] NOT NULL,

[TSLONG] [bigint] NULL,

[Status] [nvarchar](50) NULL,

[Date] [datetime] NULL CONSTRAINT [DF_USR_PAPERSAVE_REVENUEBATCH_MAPPING_trigger_Date]  DEFAULT (getdate())

) ON [DEFGROUP]

GO

Insert Trigger: 

/****** Object:  Trigger [dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_Insert_Logs]    Script Date: 7/2/2015 12:19:26 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author:                <Author,,Name>

-- Create date: <Create Date,,>

-- Description:        <Description,,>

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

CREATE TRIGGER [dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_Insert_Logs]

   ON [dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING]

   For Insert

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

INSERT INTO [dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_trigger]

           ([ID]

           ,[COMMITEDBATCHREVENUEID]

           ,[ADDEDBYID]

           ,[CHANGEDBYID]

           ,[DATEADDED]

           ,[DATECHANGED]

           ,[TSLONG]

           ,[Status])

   

Select  [ID]

           ,[COMMITEDBATCHREVENUEID]

           ,[ADDEDBYID]

           ,[CHANGEDBYID]

           ,[DATEADDED]

           ,[DATECHANGED]

           ,[TSLONG],'Inserted' from inserted;

END

GO

 Delete Trigger: 

USE [BBInfinity]

GO

/****** Object:  Trigger [dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_update_Logs]    Script Date: 7/2/2015 12:20:07 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author:                <Author,,Name>

-- Create date: <Create Date,,>

-- Description:        <Description,,>

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

CREATE TRIGGER [dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_update_Logs]

   ON [dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING]

   for update

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

INSERT INTO [dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_trigger]

           ([ID]

           ,[COMMITEDBATCHREVENUEID]

           ,[ADDEDBYID]

           ,[CHANGEDBYID]

           ,[DATEADDED]

           ,[DATECHANGED]

           ,[TSLONG]

           ,[Status])

   

Select  [ID]

           ,[COMMITEDBATCHREVENUEID]

           ,[ADDEDBYID]

           ,[CHANGEDBYID]

           ,[DATEADDED]

           ,[DATECHANGED]

           ,[TSLONG],'Before Update' from deleted;

END

GO

 Update Trigger: 

USE [BBInfinity]

GO

/****** Object:  Trigger [dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_After_update_Logs]    Script Date: 7/2/2015 12:20:36 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author:                <Author,,Name>

-- Create date: <Create Date,,>

-- Description:        <Description,,>

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

CREATE TRIGGER [dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_After_update_Logs]

   ON [dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING]

   after update

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

INSERT INTO [dbo].[USR_PAPERSAVE_REVENUEBATCH_MAPPING_trigger]

           ([ID]

           ,[COMMITEDBATCHREVENUEID]

           ,[ADDEDBYID]

           ,[CHANGEDBYID]

           ,[DATEADDED]

           ,[DATECHANGED]

           ,[TSLONG]

           ,[Status])

   

Select  [ID]

           ,[COMMITEDBATCHREVENUEID]

           ,[ADDEDBYID]

           ,[CHANGEDBYID]

           ,[DATEADDED]

           ,[DATECHANGED]

           ,[TSLONG],'After Update' from inserted;

END

GO

SQL Server CROSS APPLY and OUTER APPLY

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

When should I use Cross Apply over Inner Join?

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

RE: Which are more performant, CTE or temporary tables?

http://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables

Tools and tricks which can save your time while doing troubleshooting

Tools and tricks which can save your time while doing troubleshooting

When you work with support , the main thing is you need to learn is to save time. There are lots of tools which can save your time. I am going to mention all in this article which I used so far.

1) One Note 

Generally most fresher support engineers use notepad and notepad++ in their initial stage but there are lots of disadvantages of them. contents you write in notepad and ++ are not automatically saved. In notepad even don't have different pages , in ++ you have that but every time you need to save it.But tool I am going to show here is more efficient than that.

I am talking about One note , How it looks like:

Here , you can make note for every client/ for every case.

What I have been doing with one note , whenever I get new case I create new page with client name or with issue title and copy all details which I get from case notes or from tier 1 team member. I also copy all relavant kba links here , also if I get new credentials , machine details from client then will also copy here.

You will see all my work of one note in right panel. Also refer this below article to get more idea about one note.

https://support.office.com/en-za/article/Basic-tasks-in-Microsoft-OneNote-2013-da73c095-e082-4276-acf9-8728ca8b08ab


So go ahead and start , practice with this. I am sure you can save your time when you start using this instead of notepad/++

Advantage:
-> You can get all details very quickly as it displays everything in same frame
->You don't need to save when you write/ update your content this because  it saves your content automatically.

2) Difference Checker:

Many times while troubleshooting you need to compare new script/code  with old script/code that time this toll will be helpful to you.

This is online tool , below is link to open it . You will need to copy your old code/script and new code/script and you will find difference very quickly.

https://www.diffchecker.com/diff

Below is it look:

3) SQL Formatter:

This tool is important for doing formatting of your sql script. It makes your script in good format and using this you can also get quick idea where your script has an error.

Here is the link, this one is also online tool:

http://www.dpriver.com/pp/sqlformat.htm

How it looks like:

4)Make folders in your outlook: 

 When you work with support tasks , you get everyday lots of emails and you have to manage all these emails. By managing this things you can find important emails very quickly.


Outlook is providing feature called create folder , you can create different folders like for your manager emails new folder , for your supervisor different folder , for code related emails new folder etc. And when you get emails , you can move them to relevant folders. You can do this manually or also you can create some rules which can move your emails to relevant folders automatically. I generally do manually move.

How my outlook looks like:

Below link will give you idea how to move email to relevant folders automatically:

https://support.office.com/en-za/article/Manage-email-messages-by-using-rules-c24f5dea-9465-4df4-ad17-a50704d66c59


5) Snipping tool

While doing troubleshooting many times you need to capture some screen shots of errors , script etc.
That time you can use print screen option but tool I am talking about is efficient than print screen, that is snipping tool. Snipping toll provides quick capture of screen  , you can capture any portion of screen , generally in print screen you will get all portion of your screen and then you need to crop it according to your requirement but in snipping it's easy to do with one step only (don't know if print screen also provides any short cut to capture few portion of screen , I am used to with snipping tool)

Here is how it looks like:

You can place it in your machine's task bar so whenever you need , you can quickly use it and it will definitely save your time and will help you to capture all needed things quickly.

Basic tips need to know during troubleshooting:

In this article , I am going to mention all basic things which all support engineers need to take care. Generally all new support engineers have same complain that client is not giving good response when they ask anything to client and due to that they can't able to fix issue in timely manner so below steps can be helpful you you as I have also faced similar things when I was fresher so just want to share my experience.

Generally when we connect with client , we need to manage time efficiently so that customer don't get frustrated. Client frustration generally happens due to many reason.Some times they don't satisfy with our product, Some times the previous engineer wasted more time of client like in my case I am tier 2 engineer so I work on cases which can't be fixed by tier 1 team and when I connect or contact client and they say previous tier 1 engineer already connected with our system for whole day so their frustration might come on you.

So best thing is that contact tier 1 person (he/she will your internal team member so you can do this easily I believe , in my firm we have all facility to contact them if they are available) and get all details whatever he applied steps or kba etc. so that things you don't need to apply again.

Make your execution plan and then contact client. Some times client don't allow you to connect with his system , that time you can take your supervisor help or your support manager help and do conference and they will try to convince client also make aware to client about your execution plan.

Don't get started with troubleshooting without getting details. Some times if you don't have enough knowledge about the functionality for what client is getting issue that time you can do similar stuff in your test machine and also read documents for that , also discuss with colleague if they already faced similar things in past with other client , read all knowledge base regarding this and then goahead ahd ask client for access.

Remember one point , if client can provide you separate machine then ask them to give you so that client/user can continue their work , mention everything in your first email  , also make practice to copy your supervisor in all important emails as they have more experience and they know when your client will get frustrated. Also copy all relevant person in that email chain , some times person who opened case/ticket is different and person with whom you need to work is different , also some time you work on project case , that time you need to copy project manager as well so that they all get aware what are you doing. To keep updated all is always necessary and important like fixing issue.
If client issue is critical that time project manager/supervisor can provide you extra resources . some times make QA/developer available for you,. Because ultimate goal is to fix issue without any hurdle
and in  proper time,In my firm also there is survey system so once I close case , my manager get survey response of client and it include in my performance, also remember when you close case , mention all details , all steps in your case notes so that in future if you need to open case then you will have it. And also send all details to client and give them your steps to fix issue, create knowledge base your side so that you/your colleague read this in future if they get similar issue