Posts

Featured Post

How do I compare all the column values based on the Partition by with ID

I have a case where there are many rows for the same ID, I just wanted to know if there is any way to find out whether the given ID includes some specific value or not.
Table 1ID Dept Salary Flag Date 1 IT 5000 N 2017-01-011 IT 5000 N 2017-01-021 IT 5000 N 2017-01-031 IT 5000 N 2017-01-041 IT 5000 N 2017-01-052 HR 4000 N 2017-01-012 HR 4000 N 2017-01-022 HR 4000 Y 2017-01-032 HR 4000 N 2017-01-042 HR 4000 N 2017-01-053 Fin 4500 N 2017-01-083 Fin 4500 N 2017-01-093 Fin 4500 N 2017-01-103 Fin 4500 N 2017-01-113 Fin 4500 N 2017-01-124 Edu 4800 N�����…

SQL Server 2008 R2: Get first not null values from column

In sqlserver, when you have multiple data with some column have null value and you need the row which not null.

So this example will help you.

CREATETABLE Family ( ID int, Name varchar(20), Gender char(1));INSERTINTO Family VALUES(1,'Ram','M'),(2,'Suraj','M'),(3,'Sunitha','F'),(4,'Deepika','F'),(5,'Minakshi','F'),(6,'Somu','M');CREATETABLE Child_parent ( Child_ID int, Parent_ID int );INSERTINTO Child_parent VALUES(1,2),(1,3),(4,5),(4,6);Expected Result:
Child_ID ChildName FatherName MotherName -----------------------------------------------
1        Ram       Suraj      Sunitha 
4        Deepika   Somu       Minakshi


Query to get Desire result with self join as below.


SELECT cp.Child_ID, f1.Name as ChildName, max(CASEWHEN f2.Gender ='M'THEN f2.Name END) FatherName, max(CASEWHEN f2.Gender ='F'THE…

❣DIY Fairy House Lamp Using Coke Plastic Bottles❣

Image
Difference between asynchronous (AJAX) and synchronous request? Now-a-days, this is a very common question that been asked in all most every basic interview – What is the main strength of AJAX? Or why and when should we use AJAX in web application?
And the most common answer is – “AJAX does not refresh or reload the whole page”. But, the more perfect answer is – “AJAX is an asynchronous technology where others request are synchronous.” So, what is the difference? In a word, a program does not wait for response after requesting an asynchronous call whereas synchronous does so. Here is a simple example – function check() {
var a=0;
a = getStatus(“getstatus.php?id=5”);
if(a==1) {
alert(“active”);
} else {
alert(“not active”);
}
} Here getStatus() function sends a AJAX request to the server with “getstatus.php?id=5” url and the php file decides (from database may be) the status and output/response as 1 or 0. But, this function will not work properly. It will alert “…

Read /Write in XML column in sqlserver

This is simple example to read and write in xml column of a table in sqlserver . You can directly execute in sqlserver too.


----------------- To insert/update in xml column
declare @t table (id int, xmlCol xml)

insert into @t values(1,'14747')

select * from @t;

update @t set xmlcol = '1474714749' where id =1

select * from @t

--even you cast as
select cast ( xmlcol as varchar(max)) , * from @t

----------------- Select example-----1st example --
Declare @responce xml; DECLARE @douvalue varchar(100);
SELECT @responce = ' 60.4655'
select @douvalue = @responce.value('(/double)[1]', 'varchar(100)');
select @responce, @douvalue AS DownloadEnvironment

--Declare @responce xml;
SELECT @responce = '
60.4655'

;WITH XMLNAMESPACES(DEFAULT 'http://www.webserviceX.NET/')
select @douvalue = @responce.value('/double[1]', 'varchar(100)');

select @douvalue

------2nd example - To retrieve value ---
declare @xml xml 
set @xml = '14747147491475014752…

Read And write xml data in xml column in sqlserver

Image
Suppose I have an entry


assetsIDs_xml  '119'

update Designs set assetsIDs_xml = ''
where ( assetsIDs_xml.exist('/root/id[text()=119]') =1 ) 


SELECT            Columnlist FROM             tableName c WHERE AND((c.userIDs_xml.exist('/root/id[text()=sql:variable("@userID")]')= 1)

IIS not work, Give error "HRESULT...." or When IIS start gives error "Already running a process on this port"

Hello All,

In my pc, IIS was working fine as I know. Today when I want to run my site from IIS. It gives me above error . I am searching lot for my this issue. Even I was did stop my firewall , still getting the same.

So I googling and get solution that skype is also share the same port of IIS : 80. So follow below to solve this issue.

The cmd NETSTAT -ano showed Skype (via the PID) was using port 80.
This was preventing IIS starting a local website with the message:
The process cannot access the file because it is being used by another process. (Exception from HRESULT: 0x80070020) This was resolved in Skype by unchecking Tools > Options... > Advanced > Connection > "Use port 80 and 443 as alternatives for incoming connections"

Refer link :Prevent Skype using port 80 and causing IIS to throw a HRESULT: 0x80070020 Exception