Open port 1433

1.      Check port is open or not by using the below command

netstat –aon

2.      Go to SQL Server Configuration Manager->SQL Server Network Configuration->Protocols, check that named pipes and tcp/ip are enabled.

3.      Restart your machine

Ping is not working in the Command Window (CMD)

Use the below command to change the directory and then use ping command

cd:c:\windows\system32

Now run this command c:\windows\system32> ping www.google.com

Copy/Extract HTML Drop Down List Options in Plain Text

You want to copy HTML dropdown list(combo box) options from a web-page. You do right
Extract dropdown list options using Notepad++.
Steps:
1. Open site.
2. Now you need to get HTML source of dropdown list:
3. Paste HTML source in Notepad++
4. Remove Select tag from top and bottom if exists
5. Put cursor at top of code and Press Ctrl + F
6. Go to Replace Tab and enter following info:
Find What:
<option[^>]*>([^<]*)</option>
Replace With:
\1\n
Select Regular Expression as Search Mode and click on Replace All. If options are
Already in new line then no need to use \n in Replace With option.
You will get all options in plain text. If there are a lot of options, It will save bunch of time.

Show Loading on Page using JAVASCRIPT

Easy way to show loading Image Using HTML and JavaScript.

Put any image in images/loading/loading.gif

<div id="divLoading" style="display: none;">
 <div class="PreLoadingEffectDiv">
 <img src="images/loading/loading.gif" />
 </div>
 <div class="PreLoadingEffect">
 </div>
 </div>
 <script language="javascript" type="text/javascript">

 if (document.getElementById('divLoading') != null) {

 document.getElementById('divLoading').style.display = "";
 }
 function HideLoading() {

 document.getElementById('divLoading’).style.display = "none";

 }
 </script>
.PreLoadingEffect
 {
 position: absolute;
 left: 20px;
 top: 0px;
 left: 0px;
 opacity: 0.7;
 filter: alpha(opacity=70);
 z-index: 1000;
 height: 1000%;
 width: 100%;
 background-color: #777777;
 }
 .PreLoadingEffectDiv
 {
 position: fixed; left: 50%; top: 50%; z-index: 2000;
 }

Create Trigger in SQL

Create Trigger in SQL : Suppose we have 2 tables. one is main table (AR_ServiceXUser ) and other table is backup of that table (AR_ServiceXUserAudit) if any data is update in main table then the old data of main table should transfer to backup table

CREATE TRIGGER tg_AR_ServiceXUser  --Name of Table
   ON  AR_ServiceXUser  --Main Table
   FOR UPDATE  
AS   
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.  
 SET NOCOUNT ON;  
   
 INSERT INTO [AR_ServiceXUserAudit]  --Audit Table to take backup on any updation of data
           ([ServiceUserID]  
           ,[ServiceID]  
           ,[UserID]  
           ,[DeliveryStatusID]  
           ,[OrderDate]  
           ,[TransactionID]  
           ,[CenterID]  
          )  
      
  
SELECT [ID]  
      ,[ServiceID]  
      ,[UserID]  
      ,[DeliveryStatusID]  
      ,[OrderDate]  
      ,[TransactionID]  
      ,[CenterID]  
  FROM DELETED  
   
   
 SET NOCOUNT OFF;  
END  

Get all trigger(s) from Database in SQL

 

Simple way to get all triggers exist into the database.
 below query show all trigger(s) into the database

SELECT a.[name] AS TriggerName, b.[name] AS TableName 
FROM sys.triggers a join sys.tables b ON a.parent_id = b.object_id

Simple query to get all trigger from the database

Multiple Rows to Single Row in SQL

 

Using SQL you can convert multiple rows to single row with any separator.

In the below example there is a table having data in 5 rows.

SELECT VALUE FROM TableName

Out Put is

VALUE
112
114
115
126
127

User wants to convert these 5 rows to a single row with all data departed by comma.

DECLARE @Names VARCHAR(8000) 

SELECT @Names = COALESCE(@Names + ', ', '') + cast(Value as varchar(100)) FROM TableName

SELECT @Names

Out Put is

112, 114, 115, 126, 127