Skip to main content

Posts

Showing posts from September, 2010

Check If Database object exists(DB,Table,View,SP,Index,Trigger,Key etc.)

Many time we need to check if object exists or not in SQL server when we create new objcet(i.e. DB,Table,View,SP,Functions etc).

There are certain situation when if particular object exist then drop/modify else Create.
Because we need to run script again and again on same DB to avoid any existance error we use check if exists object.

For such cases here i am writing Check if exist query for DB objects.....

----Check If Database Exists----

IF db_id('<DB_Name>') IS NOT NULL
    Print 'Database Exists'
ELSE
    Print 'Database Does Not Exists'

----Check If Table Exists----

IF OBJECT_ID ('<Table_Name>','U') IS NOT NULL
    Print 'Table Exists'
ELSE
    Print 'Table Does Not Exists'


----Check If SP Exists----

IF EXISTS (SELECT * FROM sysobjects WHERE id =  object_id (N'<SP_Name>') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    Print 'SP Exists'
ELSE
    Print 'SP Does Not Exists'


----Check If View Exis…

Download File and delete it from server after download is finished

Sometime it is requirment that we should delete files once user have downloaded it.
This requirment is due to remove load on server.
Here I am writing the code that will allow user to delete the
file from server once the file is downloaded on the client’s machine.

To achieve this use following code:

private void DownloadAndDeleteFile()

{
    Response.ContentType = ContentType;
    Response.AppendHeader("Content-Disposition",  "attachment; filename=testFile.txt");

    Response.WriteFile(Server.MapPath("~/Documents/testFile.txt"));

    Response.Flush();
    System.IO.File.Delete(Server.MapPath("~/Documents/testFile.txt"));

    Response.End();
}


Cheers!!!!!!!!!!!!!!!!

SQL SERVER – Insert Multiple Records Using One Insert Statement

hmm this is interesting question
How can I insert multiple values in table using only one insert statement?
Normally when there are multiple records are to be inserted in the table we use following way in T-SQL.


USE TestDB
GO
INSERT INTO TestTable (TestColId, TestColTitle,TestColDesc)
        VALUES (1, 'TestVal1' ,'TestDesc1');
INSERT INTO TestTable (TestColId, TestColTitle,TestColDesc)
        VALUES (2, 'TestVal2' ,'TestDesc2');
INSERT INTO TestTable (TestColId, TestColTitle,TestColDesc)
        VALUES (3, 'TestVal3' ,'TestDesc3');
INSERT INTO TestTable (TestColId, TestColTitle,TestColDesc)
        VALUES (4, 'TestVal4' ,'TestDesc4');
INSERT INTO TestTable (TestColId, TestColTitle,TestColDesc)
        VALUES (5, 'TestVal5' ,'TestDesc5');
GO


The clause INSERT INTO is repeated multiple times.
Many times DBA/Programmer copy and paste it to save time.
There is another alternative to this, which is easy to use without repe…

Bom Sabado flooding on Orkut

On Orkut, you might have noticed something fishy going on over the past few hours. A large number of users are randomly flooding their friend’s scrapbooks (Orkut’s equivalent of Facebook Wall) with the following message: It doesn’t take a genius to figure out that the “Bom Sabado!” messages are automatically generated by a script. However, it is not clear if this is simply a script exploiting vulnerability in Orkut, or have the accounts sending the automated scraps been compromised. If you are amongst those affected, it’s highly recommended that you follow the steps highlighted below: Switch to the “older version” of Orkut.Log out of Orkut.Clean your browser’s cache
and cookies. Log in and change your password and securityquestion.If you haven’t been affected yet, it is strongly advised that you avoid Orkut until the issue has been resolved. I managed to trigger the same exploit while researching this article. Recently other high profile websites like Twitter and YouTube also fell victim…

RowFilter with IN operator over a DataView column of type Guid

Case

Apply a RowFilter on a DataView which filters a column of type System.Guid and can contain a variable number of values.

Example:

DataView dv = new DataView();
dv.RowFilter = "tableId in ('<guid>', '<guid>', ...)";

where tableId column is of a System.Guid type. As the number of values is variable it makes perfect sense to use the in operator.


When you are looking at this expression you may think this should be working as expected, i.e. the result should only contain the data where tableId is in the specified values. In fact, if you run it you will get the following exception:

Cannot perform '=' operation on System.Guid and System.String.

Solution :

To convert the String to Guid you should use the Convert(expression, type) method like this:

dv.RowFilter = "tableId in (Convert('<guid>', 'System.Guid'), Convert('<guid>', 'System.Guid'))";