Get serial number with select query

Yes,  We can select serial number / record number with our selected result from sql server , Its very easy , here is the code

SELECT ROW_NUMBER() OVER (ORDER BY EmployeeName) AS   SlNo, 
              Department ,
              EmployeeCode,
              EmployeeName           
FROM    EmployeeMaster
WHERE  Active = 1 

Here I used  ROW_NUMBER() function to get  serial number with the ascending order of employee name.  There is an option to get result order against multiple fields like this

ROW_NUMBER() OVER (ORDER BY Department ,EmployeeName)  AS  SlNo 

How to know sql server version and edition?

         







 We can easily identify the server version and edition by execute an SQL query in SQL server. Here is the code ...

SELECT Serverproperty('Edition')        AS Edition,
       Serverproperty('ProductVersion') AS ProductVersion 

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. :)

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 :)

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 :)

Connection in vb.net with dsn

Create a connection with vb.net to SQL server through dsn,

"Try
                Dim strsql As String
                strsql = "Provider=MSDASQL.1;DSN=dsnTest;UID=sa;"
                conDb.ConnectionString = strsql
                conDb.Open()
Catch exConnection As Exception
                MsgBox(exConnection.Message)
End Try"