3 November 2015

SQL SERVER – Find Currently Running Query – T-SQL

This is the script which I always had in my archive. Following script find out 
which are the queries running currently on your server.
SELECT 
sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,req.total_elapsed_time
FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
While running above query if you find any query which is running for long time 
it can be killed using following command.
KILL [session_id]

3 June 2015

6 March 2015

Find Column Names From Table in SQL Server

Select Column_Name
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME='CustomerOrderDetails'


Id
ExportLicenseNumber
CustomerOrderId
RepairSerialNumber
IsInitialQuote
Revision
CODate
COReceiptDate
PriorityCodeOfCustomer
CustomerRefNumber
CustomerRefDefinition
OrderFromCustomerId
DeliverToCustomerId
ResponsibleLocationId
DeliveryPoint
DeliveryTerms
PaymentTerms
ShipmentViaSupplierId
OtherCommentsAndSpecialInstructions
CurrencyId
LeadTime
LeadTimeUOM
QuoteTypeId
QuotePriceType
ExpectedDeliveryDate
EstimatedDeliveryDate
PartialShipmentAllowed
ShippingAndHandlingPrice
ShippingAndHandlingPriceUSD
OtherChargesType
OtherCharges
OtherChargesUSD
OtherCharges1
OtherCharges1USD
OtherCharges1Type
IncountryTransportation
IncountryTransportationUSD
Insurance
TaxPercentage
DiscountUSD
Discount
ItemReferenceNumber
ReferenceNumber
PartId
PartConditionId
SupplierPartNumber
Quantity
Price
PriceUSD
IsItemClosed
CustomerOrderNotes
OrderFromCustomerAddressId
OrderFromcustomerPOCId
CustomerAddressId
CustomerPOCId
DeliverToAddressId
DeliverToPOCId
SalesExportLicenceRequired
RepairExportLicenceRequired
ExportLicenseTypeId
ItemDecremented
DSP
ExportLicenseCategoriesId
ExportToCountryId
EndUserCountryId
EndUserCustomerId
CFR1234a
CFR1234b
DSP61
WarrantyRequirement
CustomerRefOrderNumber
IsArchived
COCreationDate
CustomerOrderItemNote
DeclaredCustomsValueoftheGoodbyCustomer

5 March 2015

12 December 2014

5 Ways to send data between ASP.NET Pages

How many ways do you know to send the data between ASP.NET Pages? In this post I’m going to list 5 different ways. First 2-3 ways are very well-known and last 2-3 ways are not much popular. You will find here mostly inline methods to receive the data on different pages. You can download the complete code.

So, let’s start with the very well-known one.

1. Using Session State or Application Variable

Using this technique I will store the data in session variable on the client machine and on the next page will grab it. Using Application Variable instead of Session Variable is recommend by experts.

ASPX Page:

<asp:TextBox ID="txtData" runat="server"></asp:TextBox>
<br /><br />
<asp:Button ID="btnSessionState" runat="server" Text="Session State" OnClick="btnSessionState_Click"/>

Code-Behind:

protected void btnSessionState_Click(object sender, EventArgs e)
{
    Session["Data"] = txtData.Text;
    Response.Redirect("SessionState.aspx");
}

Receiver ASPX Page:

<div>
<h1>Session State</h1>
    Data is: <%=Session["Data"] %>
And you all set, run it test it.

2. Using Query String

Using this technique I will add my data with URL and on the next page will grab it.

ASPX Page:

<asp:TextBox ID="txtData" runat="server"></asp:TextBox>
<br /><br />
<asp:Button ID="btnQueryString" runat="server" Text="Query String" OnClick="btnQueryString_Click" />

Code-Behind:

protected void btnQueryString_Click(object sender, EventArgs e)
{
    Response.Redirect("QueryString.aspx?Data=" + Server.UrlEncode(txtData.Text));
}

Receiver ASPX Page:

<h1>Query String</h1>
        Data is: <%=Server.UrlDecode(Request.QueryString["Data"]) %>
And you all set, run it test it.

3. Using HttpPost

Using this technique I will call a post back url and the on next page using Request.From I will grab it.

ASPX Page:

<asp:TextBox ID="txtData" runat="server"></asp:TextBox>
<br /><br />
<asp:Button ID="btnHttpPost" runat="server" Text="HTTPPost" PostBackUrl="~/HttpPost.aspx" />

Note: There is no any code-behind method call instead of a postbackurl in button attribute.

Receiver ASPX Page:

<h1>HttpPost</h1>
        Data is: <%=Request.Form["txtData"] %>

And you all set, run it test it.

4. Using Public Properties

Using this technique I will send the using a public method and on the next page will grab it using PreviousPage.MethodName.

ASPX Page:

<asp:TextBox ID="txtData" runat="server"></asp:TextBox>
<br /><br />
<asp:Button ID="btnPublicProperties" runat="server" Text="Public Properties"OnClick="btnPublicProperties_Click" />

Code-Behind:

protected void btnPublicProperties_Click(object sender, EventArgs e)
{
    Server.Transfer("PublicProperties.aspx");
}
public string PublicData
{
    get
    {
        return txtData.Text;
    }
}

Receiver ASPX Page:

<h1>Public Properties</h1>
        Data is: <%=PreviousPage.PublicData %>

And you all set, run it test it.

5. Using Controls

Using this technique I will just redirect the user on next page and on the next page will use PreviousPage.FindControl to grab the data.

ASPX Page:

<asp:TextBox ID="txtData" runat="server"></asp:TextBox>
<br /><br />
<asp:Button ID="btnControl" runat="server" Text="Control" OnClick="btnControl_Click" />

Code-Behind:

protected void btnControl_Click(object sender, EventArgs e)
{
    Server.Transfer("Control.aspx");
}

Receiver ASPX Page:

<h1>Control</h1>
        Data is: <asp:Label ID="Label1" runat="server" Text="Label" />

Receiver Code-Behind Page:

protected void Page_Load(object sender, EventArgs e)
{
    var textbox = PreviousPage.FindControl("txtData") as TextBox;
    if (textbox != null)
    {
        Label1.Text = textbox.Text;
    }
}

And you all set, run it test it.

You can use any technique given above depending upon your business requirement.

25 September 2014

SQL Server - Cursor

Cursor is a variable in SQL Server Database which is used for row-by row operations. The cursor is so named because it indicates the current position in the resultset.
  • Let us look this example.
Here I am just taking 2 columns  from a table and passing through the cursor and printing them.

  • CREATE PROCEDURE Usp_cursor_test
    AS
      BEGIN
          –Declaring the  variables needed for cursor to store data
          DECLARE @Name VARCHAR(50)
          DECLARE @EmptypeID INT
          –Declaring the Cursor cur_print For name and Emptypeid in the Employeedetails table 
          DECLARE cur_print CURSOR FOR
            SELECT name,
                   emptypeid
            FROM   employee.employeedetails
          –After declaring we have to open the cursor 
          OPEN cur_print
          –retreives the First row from cursor and storing it into the variables. 
          FETCH NEXT FROM cur_print INTO @Name, @EmptypeID
          – @@FETCH_STATUS returns the status of the last cursor FETCH statement issued against  
          – any cursor currently opened by the connection. 
          – @@FETCH_STATUS = 0 means The FETCH statement was successful. 
          – @FETCH_STATUS = -1 The FETCH statement failed or the row was beyond the result set. 
          – @@FETCH_STATUS = -2 The row fetched is missing. 
          WHILE @@FETCH_STATUS = 0
            BEGIN
                –Operations need to be done,Here just printing the variables 
                PRINT @Name
                PRINT @EmptypeID
                –retreives the NExt row from cursor and storing it into the variables. 
                FETCH NEXT FROM cur_print INTO @Name, @EmptypeID
            END
          –Closing the cursor 
          CLOSE cur_print
          – removes the cursor reference and relase cursor from memory 
          – very Important 
          DEALLOCATE cur_print
      END 
      
    Note: 
  • Once cursor is opened we have to close the cursor
  • After the usage cursor should be deallocated from the memory.
  • As a DBA , I will not recommend the usage of cursors in all scenarios because it affects performance, since for each result it will have a  network round trip which will cause a major performance issue in large data sets. You can make use of case statement instead of cursors for some scenarios.