Sunday, November 29, 2015

How to Filter Column Returned from Stored Procedure SQL

I was asked by my Team Lead a task about Stored Procedures in SQL Server, the requirement was like

We had a Stored Procedure which returns a Table and that table has about 80 columns. Now in some cases we only want some of the columns not all.

I was not allowed to alter the Stored Procedure in any way, so the only thing I could try is at the point of calling the SP.

So I had some research over this, and I found a SQL function named OPENROWSET

This Function accept three parameters

 1. Provider Name
 2. Connection String
 3. Stored Procedure to be executed

In order to make this method work we have to enable the ad hoc Query, so execute the following query.

EXEC sp_configure
  'show advanced options',

 EXEC sp_configure
  'ad hoc distributed queries',

Lets First create a table


     id   INT,
     NAME VARCHAR(10),
     age  INT,

Now we will create a Stored Procedure which will return a datatable of table Test


      SELECT *
      FROM   test

Now if we try to execute the above SP it will return all the columns contained in the Test table, in our case we only want ID and Name columns. So here's how we do it

                   'EXEC master.dbo.Getdata') AS tbl 

This way you will get only ID and Name columns

I hope that This tutorial was informative for you and I would like to thank you for reading.

Monday, November 23, 2015

Difference between Interface and Abstract Class

Following are the differences between an Interface and a Abstract Class.

InterfaceAbstract Class
1Interface support multiple InheritanceAbstract class does not support multiple inheritance
2Interface doesn't contain Data Member Abstract class contains Data Memember
3Interface doesn't contain ConstructorAbstract class contains constructor
4An interface contain only incomplete member (signature of member)An Abstract class contain both Incomplete (Abstract) and Complete Members
5An Interface cannot have access modifiers by default everything is assumed as publicAn Abstract class can contain access modifiers for the subs functions properties
6Member of Interface can not be static Only Complete Member of Abstact class can be static

I hope it was informative for you and I would like to Thank you for reading.

Wednesday, August 5, 2015

There are still remote logins or linked logins for the server

Hello Everyone,

While trying to delete linked server from SQL you might encounter the error like

There are still remote logins or linked logins for the server

Like the error says you cannot delete a linked server, until all logins associated with that linked server are removed.

So first we will have to remove those logins and than we can safely remove that linked server

To Remove logins for a specific linked server use the following query

sp_droplinkedsrvlogin 'Your Linked Server Name',null

Execute the above procedure with your linked server name and it will remove all logins associated with it.

Now try to remove the linked server by following query

sp_dropserver 'Your Linked Server Name'

That's it your have successfully deleted linked Server

I hope it was informative for you and I would like to thank you for reading.

Wednesday, June 10, 2015

How to Completely hide windows Form

If you are thinking about how to completely hide your Windows Form Application, so that it will run silently in the background without anyone knowing about it.

To do that add the following code

        protected override void OnLoad(EventArgs e)
            Visible = false;
            ShowInTaskbar = false;

This code will completely hide your form at startup.

I hope it was informative for you and I would like to Thank you for reading.

Sunday, May 10, 2015

Add Leading Zero in Excel

If you want to add leading zero's in your excel column so that your value become like following

123 > 0123
9827 > 09827
0000 > 0000

1. Select the column on which you want to apply this setting, and click Ctrl + 1

This will open Format Cell's dialog box

2. For the Category Select Custom

Now lets assume you need to enter 01234

3. So in type column just type 000#, and click on OK

That's it now if you enter 01234 in that column excel will accept it, and will not remove leading zero
I hope it was informative for you and I would like to thank you for reading.

Sunday, May 3, 2015

IF NOT EXISTS For Sql Functions

You must have used IF NOT EXISTS for DML operation, but the same method doesn't work for sql function.

In this tutorial I'm gonna show you how to use IF NOT EXISTS to create a function if it is already not created.

Download .Sql file for this project from HERE
IF EXISTS (SELECT * FROM [dbo].[sysobjects]
           WHERE ID = object_id(N'[dbo].[myfunction]')
    DROP FUNCTION [dbo].[myfunction]

create function myfunction()
 returns int
       return 5+4;

The idea here is to check if the function exists, if it exists than we will first drop it, and after that we can create the function.

I hope it was informative for you and I would like to Thank you for reading.

Wednesday, April 15, 2015

Sql Stored Procedure

  • Syntax for creating Procedure
  • We can insert row using SP
  • W can delete rows using SP
  • We can change Data Type of any column using SP
  • We can drop same table of which we are executing the SP
  • Stored Procedure can accept parameters
  • Triggers will be called from SP
  • Trigger will be called  even if there were no action took place of Delete Sql statement written in SP
  • Create procedure must be the first line in the batch this is why we have to use GO keyword
Syntax for creating procedure without Parameter

CREATE PROCEDURE Your Procedure Name
      SELECT *
      FROM   Your Table Name

Syntax for creating procedure with Parameter

    SELECT *   FROM   test1    

        WHERE  empid = @ID   AND NAME = @name 

Friday, April 3, 2015

How to Install exe as a Windows Service

In this tutorial I will show you how to install a exe as a windows services.

For this I have created a simple exe in Visual studio 2010 you can create it in any version of Visual Studio.

1. Open up your command prompt
2. Type the following line in CMD

sc create Service_Name binPath= "Exe Path"

3. Replace Service_Name with your service name and ExePath with your exe path with quotes


sc create MyService1 binPath= "c:\Taqi.exe"

I hope it was informative for you and I would like to Thank you for reading.

Thursday, March 19, 2015

How To Convert HTML into Image with Download Link of Dlls

In this tutorial I will show you how you can convert your HTML markup into a Image

For this you will need to import the following dll's in your project.


Both files are password protected, the password is 'taqi' without quotes

After downloading the above 2 dlls add the their reference in your project

using TheArtOfDev.HtmlRenderer.WinForms;


string html = "<h1>This tutorial shows how to convert HTML to Image</h1>";
int height = 700;
int width = 200;
string strfn = ("Test.png");
System.Drawing.Image objimg;
objimg = HtmlRender.RenderToImage(html, width, height);

FileStream fs = new FileStream(strfn,
FileMode.CreateNew, FileAccess.Write);
objimg.Save(fs, ImageFormat.Bmp);


I hope it was informative for you, and I would like to Thank you for your time

Monday, February 2, 2015

About SQL Views

Views :
  • A View is nothing more than a saved SQL query. A view can be considered as virtual table
  • A view can have a maximum of 1,024 columns. 
  • If we Insert or Delete from view than this will also effect on our Actual Table
  • If a trigger is set on a table and we perform DML operations on that table by views than trigger will be called

Syntax for creating view
create view myview1
select * from test1

Syntax for  Dropping View

drop view myview1

Syntax to use View

select * from myview1