Get last date of month in sql SQL server

here is the solution

SELECT Dateadd(dd, -Day(Getdate()), Dateadd(mm, 1, Getdate())) 

you will get the result like

Replace  Getdate() function with your date will give you the last date that you want.


SELECT Dateadd(dd,-Day('2015/02/16'), Dateadd(mm, 1, '2015/02/16')) 


Result :

Thank you. :)

Get insert query from selected data in toad?

Hi,
    Now am here to tell about insert script generation from toad software. here is the steps ...


Step 1 : Write your query in editor window.

Step 2 : Click select all button in script output.

Step 3 : Right click on grid header and click Export Dataset.

Step 4 : Select export format as Insert statement. Add output path with name. Also provide table name

Step 5 : Click Ok button . You can get your insert statement from the path you specified.


Thank you. happy coding  :)

How to update table with data from another table in Oracle?

Here is a sample code ...

UPDATE customers
SET    c_details = (SELECT contract_date
                    FROM   suppliers
                    WHERE  suppliers.supplier_name = customers.customer_name)
WHERE  customer_id < 1000; 

here customers,suppliers  are my table 

here I am going to update c_details in customers table with  contract_date in 

suppliers table with the condition suppliers.supplier_name = customers.customer_name.   

 and customer_id is less then 1000 .

How to get group wise data from Datatable in C#?

Hi, If you face the problem  till now, don't worry I have a solution.,

if you have the data like this ....

id|empName |designation
-----------------------------------
1 |john    |manager 
2 |manu    |ast.manager 
3 |mathew  |accountant 
4 |sree    |ast.manager

And need to group this by designation?  The given LINQ C# code will help you to solve this

var EmData = from row in dtEmpData.AsEnumerable()
group row by new
{
des = row.Field<string>("designation")

} into g
select new
{
designation = g.Key.des,
ColumnValues = g
};

:) By using two for each statements will help you guys to traverse through these record

foreach (var k in EmData)
{
      // you can access designation by using   k. designation

     foreach (var columnValue in k.ColumnValues )
    {

      // access group by result [Employee details] through this expression   columnValue["id"]

     } 

}

:) Thank you.

How to get details of all newly restored databases from SQL server?

Here is the code to get all restored databases in SQL server.





WITH lastrestores
     AS (SELECT DatabaseName = [d].[name],
                [d].[create_date],
                [d].[compatibility_level],
                [d].[collation_name],
                r.*,
                RowNum = Row_number()
                           OVER (
                             partition BY d.NAME
                             ORDER BY r.[restore_date] DESC)
         FROM   master.sys.databases d
                LEFT OUTER JOIN msdb.dbo.[restorehistory] r
                             ON r.[destination_database_name] = d.NAME)
SELECT *
FROM   [lastrestores]
WHERE  [rownum] = 1 


Just paste this code to query window and execute,. Its results the restored database list.
  

:) 

Create Trigger in SQL server

A lazy developer should know about Trigger ;) , Now we are going to learn.. :P Not learn just know How to create trigger in SQL server.

here is the structure ,    

CREATE TRIGGER trigname ON mytable
FOR INSERT/UPDATE/DELETE
AS
  BEGIN
  /* Trigger Body*/
  END 


Here    trigname is my trigger name
            mytable is my table name
            INSERT/UPDATE/DELETE you can use either single from these.
if you want to trigger your code in both  INSERT and UPDATE , you can use  them like this  INSERT,UPDATE [Separate by comma (,)].

Please not that , Instead of  FOR , you can use   AFTER | INSTEAD OF  these guys as per your needs.

And..................

You can access the inserted guys in trigger if you want  by using this

DECLARE @MyVariable BIGINT

SELECT @MyVariable = I.myfield
FROM   inserted I; 

 :) eee

How to pass data table as a parameter in SQL server -procedure?

Hi,
 
     For doing this you must create a table type user variable (User defined table type in sql server) . Please note that, the type should be in your data table structure, I mean its type. Means data types of fields man :)



here is the code for create table type in SQL server.


CREATE type dbo.categorytabletype AS TABLE 
( categoryid INT, categoryname NVARCHAR(50) ) 

here categorytabletype  Is my table type
        categoryid and categoryname  Are fields of table type variable followed by its data type

Simply like table creation

let us start to create our procedure with this table type parameter

CREATE PROCEDURE Usp_updatecategories 
(@tvpNewCategories dbo.categorytabletype READONLY)
AS
  BEGIN
  /* CRATE YOUR PROCEDURE BODY HERE  */
  /* WE CAN ACCESS THE @tvpNewCategories LIKE A TABLE HERE*/
  END 

Don't forget about  READONLY , He is an important one, Table type parameter should be readonly.

Thank you :)

Minimize a form in C#

Hi,
   You can minimize your C# form by clicking on  a cancel button.

Here is the code

     private void btnCancel_Click(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Minimized;
        }


Here  btnCancel : is my cancel button


Thank you.

How to register dll in windows 7 64 bit

Follow my blog with Bloglovin

Paste your  dll in SysWOW64 folder [Eg path: C:\Windows\SysWOW64 ]

Run cmd [Command Prompt] as Administrator: set path to SysWOW64

Enter regsvr32 Mydll.dll

here Mydll is My dll name

Press enter , If there is a successful massage, you did bro :)

Click here for 32 bit

Thank you :)

How to register dll in windows 32 bit OS?

Paste your  dll in System32 folder [Eg path: C:\\Windows\System32  ]
             
Run cmd [Command Prompt] as Administrator: set path to system32

Enter regsvr32 Mydll.dll

here Mydll is My dll name

Thank you :)

Declare veriable in oracle procedure.

Hi, today I am talking about variable decleration inside a procedure....




CREATE OF replace PROCEDURE procedure_name 
      p_parameter type[VARCHAR(20)];
AS    
     v_variables number(10);  
BEGIN
  --PROCEDURE BODY
END;

here v_variables is my variable in number datatype. Just look at that he is lies in between AS and BEGIN keywords.

How to acess sqlplus in command prompt?

Hi,
here is the code


C:\\>SQLPLUS /HOST NAME
Enter username : Username
Enter password : Password


here HOST NAME : My database name
Username
and Password are Credentials of user have permission to acess corresponding database.


Thanks ;)

How to recover SQL Server database from suspect?

Hi friends,  May you face the situation, that your database in SQL Server shows in  suspect mode, 

 

Now I have a solution for that, Just try this  may it will help you guys to recover it.

here is the code ...

EXEC Sp_resetstatus 'AgriculturalDB';
ALTER DATABASE agriculturaldb SET emergency
DBCC checkdb('AgriculturalDB') ALTER DATABASE agriculturaldb
SET single_user WITH ROLLBACK immediate
DBCC checkdb ('AgriculturalDB', repair_allow_data_loss)
ALTER DATABASE agriculturaldb SET multi_user 


here .AgriculturalDB  : Is my suspected database name 

Simple one na, Khush karooo :)

How to get maximum cursors in oracle?

Hi,  I am going to restart my blogging, Now I am telling about Maximum cursor count in our  oracle db settings. Here is the query to pick up that buddy [:) kidding actually I mean that property]..


SELECT NAME,
       value
FROM   v$parameter
WHERE  NAME = 'open_cursors'; 



 here   v$parameter  : A view have all parameter settings
           NAME        : Parameter Name ['open_cursors']

           value       : Value set to the parameter

Note : Default cursor count is 600 in oracle.


Thank you... :)