Search This Blog

Friday, November 4, 2011

Important and useful command in SQL server 2005/2008


How to get all trigger definitions in database?

            SELECT   sysobjects.name AS [Trigger Name],
            SUBSTRING(syscomments.text, 0, 1000) AS [Trigger Definition],
            OBJECT_NAME(sysobjects.parent_obj) AS [Table Name],
            syscomments.encrypted AS [IsEncrpted] FROM sysobjects 
            INNER JOIN syscomments ON sysobjects.id = syscomments.id WHERE   
            (sysobjects.xtype = 'TR')

add user to sql server admin role 
sp_addsrvrolemember 'domain\username', 'sysadmin'

Count all system objects
select count(*) as [Table] from sys.objects where type ='U'
select count(*) as [Function] from sys.objects where type ='FN'
select count(*) as [View] from sys.objects where type ='V'
select count(*) as [SP] from sys.objects where type ='P'
select count(*) as [Triggers] from sys.objects where type ='TR'
select count(*) as [Contraints] from sys.objects where type ='PK'

list all tables referred by a stored procedure
SELECT o.name, t.TABLE_NAME, c.text
  FROM syscomments c
  JOIN sysobjects o
    ON c.id = o.id
  JOIN INFORMATION_SCHEMA.Tables t
    ON  c.text LIKE '%'+t.TABLE_NAME+'%' 

list all triggers in database
SELECT 
    [Table] = OBJECT_NAME(o.parent_obj), 
    [Trigger] = o.[name], 
    [Type] = CASE WHEN 
        ( 
        SELECT 
            cmptlevel 
        FROM 
            master.dbo.sysdatabases 
        WHERE 
            [name] = DB_NAME() 
        ) = 80 THEN 
        CASE WHEN 
            OBJECTPROPERTY(o.[id], 
            'ExecIsInsteadOfTrigger') = 1 THEN 
                'Instead Of' 
            ELSE 
                'After' 
            END 
        ELSE 
            'After' 
        END, 
    [Insert] = CASE WHEN 
        OBJECTPROPERTY(o.[id], 
        'ExecIsInsertTrigger') = 1 THEN 
            'Yes' 
        ELSE 
            'No' 
        END, 
    [Update] = CASE WHEN 
        OBJECTPROPERTY(o.[id], 
        'ExecIsUpdateTrigger') = 1 THEN 
            'Yes' 
        ELSE 
            'No' 
        END, 
    [Delete] = CASE WHEN  
        OBJECTPROPERTY(o.[id], 
        'ExecIsDeleteTrigger') = 1 THEN 
            'Yes' 
        ELSE 
            'No' 
        END, 
    [Enabled?] = CASE WHEN 
        OBJECTPROPERTY(o.[id], 
        'ExecIsTriggerDisabled') = 0 THEN 
            'Enabled' 
        ELSE 
            'Disabled' 
        END 
FROM 
    sysobjects o 
WHERE 
    OBJECTPROPERTY(o.[id], 'IsTrigger') = 1 
    -- leave out the following clause if you need to 
    -- include system triggers, e.g. those in MSDB 
    AND 
    OBJECTPROPERTY(o.[id], 'IsMSShipped') = 0 
ORDER BY 
    1,2

Sql Server: Convert table Column data into comma separated string or row


SQL SERVER – Create a Comma Delimited/Seprated string or row Using SELECT Clause From Table Column/fields




Here  I am using COALESCE function Sql Server 2005/2008 to convert a field into comma separated value of string or row
My data in the table looks like :
 

The result I want to show is like :




Select query will be:

Assuming my table name is  #test, It has 2 column field1,field2

SELECT field1,
 SUBSTRING( 
 (
  SELECT ( ',' + field2)
  FROM #test t2 
  WHERE t1.Field1 = t2.Field1
  ORDER BY t1.Field1, t2.Field1
  FOR XML PATH('')
 ), 3, 1000)
FROM #test t1
GROUP BY field1

If you wish select all the rows of field2 as comma separated list then query will be:


SELECT STUFF( -- Remove first comma
    (
        SELECT  ', ' + field2 FROM -- create comma separated values
        (
          SELECT field2 FROM #test --Your query here
        ) AS T FOR XML PATH('')
    )
    ,1,1,'') AS field2

OR

DECLARE @test NVARCHAR(max)  
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test
SELECT field2= @test 


Output:
 

Wednesday, November 2, 2011

Upload file without Postbacking AJAX in ASP.net

This article will help you to upload file asynchronously  in asp.net

download the full code from here

I am using a iframe a bit trick to upload the file asynchronously with AJAX without any postback to the server.

hope the code will help you.

Tuesday, November 1, 2011

Asp.net:Multiple insertion of record into a SQL Server database table using XML

Every time we face problem bulk insertion through store procedure to perform the following task
1)      Multiple insert in table
2)      Select multiple rows using  in-clause at where condition
3)      Delete multiple rows
      Save data from grid to database table in sql server 2005/2008

Here I am writing some very good code to pass multiple value in store procedure using xml string which is much faster and we can pass a large value having multiple rows and column some time using Gridview and other similar control.
This is the best and optimize method to insert multiple records in one go from asp.net to database table in sql server 2005/2008 using the XML formatting.

Convert  Datatable/Dataset to XML string

public string DataTableToXML(DataTable table)
{
StringWriter xml = new StringWriter();
string xmldata = string.Empty;
table.TableName = "journal";
table.WriteXml(xml,XmlWriteMode.IgnoreSchema);
xmldata = xml.ToString();
return xmldata;

}

the out put XML of the above code will be as given in the store-procedure variable.

Handling of XML string inside Store procedure/Query

Pass this xml string in your store procedure and handle this string in your SP by as following code given


Create procedure handlexml(@journalstring varchar(1000))
As
Begin


/*declare @journalstring varchar(2000)='<DocumentElement>
  <journal>
    <jrnlfk>95</jrnlfk>
    <orderfk>18</orderfk>
  </journal>
  <journal>
    <jrnlfk>165</jrnlfk>
    <orderfk>2</orderfk>
  </journal>
  <journal>
    <jrnlfk>115</jrnlfk>
    <orderfk>2</orderfk>
  </journal>
</DocumentElement>' */




Suppose your string is xml element/Node and coming as above format

------------------------------------------
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT,
@journalstring

---------------------------------------------For XML Element--------------------------
SELECT jrnlfk,orderfk
FROM OPENXML(@hdoc, 'DocumentElement/journal',3)
WITH (jrnlfk int,
orderfk int)

-----------------------------------For XML Attribute--------------------------

declare @journalstring varchar(2000)='<Journal>
<remit journalfk="165" orderno="25"/>
<remit journalfk="197" orderno="15"/>
</Journal>'
---------------------------------------------------XXX-----------------------

SELECT *
FROM OPENXML(@hdoc, 'Journal/remit', 2)
WITH (journal int '@journalfk',
orderno int '@orderno')


end -- end of procedure


Hope this help you

Happy Programming