View Last Excuted Quries in Sql Server

View Last Excuted Quries in Sql Server

SELECT DEQS.LAST_EXECUTION_TIME AS [TIME]
,SUBSTRING(DEST.TEXT,(DEQS.STATEMENT_START_OFFSET/2)+1
,((CASE DEQS.STATEMENT_END_OFFSET WHEN -1 THEN DATALENGTH(DEST.TEXT)ELSE DEQS.STATEMENT_END_OFFSET
END - DEQS.STATEMENT_START_OFFSET)/2) + 1) AS STATEMENT_TEXT
,DEST.*
FROM SYS.DM_EXEC_QUERY_STATS AS DEQS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(DEQS.SQL_HANDLE) AS DEST
ORDER BY DEQS.LAST_EXECUTION_TIME DESC

Comma seperated records in sql without loop or cursor

SELECT STUFF
(
(

SELECT ',' + T1.FULLNAME AS [text()]

FROM DBO.TABLE_1 AS T1

INNER JOIN DBO.TABLE_2 AS T2 ON T1.ID = T2.ID

FOR XML PATH('')
)
,1,1,''
)

DECLARE @COLS NVARCHAR(2000)

SELECT @COLS = COALESCE(@COLS + ',[' + CONVERT(NVARCHAR(100),T1.FULLNAME ) + ']','[' + CONVERT(NVARCHAR(100),T1.FULLNAME) + ']')

FROM DBO.TABLE_1 AS T1

INNER JOIN DBO.TABLE_2 AS T2 ON T1.ID = T2.ID

Insert and update data Using Xml file in Sql Server

'Making Xml 
 Dim sb As New System.Text.StringBuilder

        sb.Append("")


        For i = 0 To dt.Rows.Count - 1 

            sb.Append("
            sb.Append("PQty=""" & dt.Rows(i).Item("PQty") & """")

            sb.Append("PWeight=""" & ClearWhiteSpace(dt.Rows(i).Item("PWeight")) & """")            


            sb.Append("/>")

        Next

        sb.Append("")

-- Making Stored Procedure
CREATE PROCEDURE [dbo].[TestMasterInsert]    
    @MId Int,    
    @GId Int,
    @PId Int,
    @Name varchar(50),
    @Data text,
    @TranType  Char(1)
AS
BEGIN    
    SET NOCOUNT ON;    
    
    Declare @id As Int 
        SET @id = 0
        
        DECLARE @hDoc int exec sp_xml_preparedocument @hDoc OUTPUT, @Data
        
    IF @TranType = 'I'    
        BEGIN        
            Insert Into TestMaster Values (@GId,@PId,@Name)
        
            Select @id = SCOPE_IDENTITY()  
        
            INSERT INTO TestDetail
                SELECT @id,PQty,PWeight
                FROM   OPENXML (@hDoc, '/ROOT/SI',2)          
                WITH (
                       PQty       Decimal(18,3) '@PQty',
                       PWeight    Decimal(18,3) '@PWeight'
                     )   
          
             EXEC sp_xml_removedocument @hDoc
        END
    ELSE IF @TranType = 'U'        
        BEGIN
            
            UPDATE TestDetail     
            SET     
                  TestDetail.PQty = XMLTEST.PQty,    
                  TestDetail.PWeight = XMLTEST.PWeight
            FROM OPENXML(@hDoc, '/ROOT/SI',2)
                        WITH (DId Int '@DId',PQty Decimal(18,3) '@PQty',PWeight Decimal(18,3) '@PWeight') XMLTEST
            WHERE  TestDetail.DId = XMLTEST.DId
            
            EXEC sp_xml_removedocument @hDoc        
        END    
END

Imports System.ServiceProcess
Imports System.IO
Public Class Service1
    Inherits System.ServiceProcess.ServiceBase
    Public Shared DailyTime As String = "DailyTime.txt"
    Public Shared errlogfile As String = "Error.err"


    Protected Overrides Sub OnStart(ByVal args() As String)
        Try


        Catch ex As Exception
            [Error]("OnStart:-" + ex.Message + DateTime.Now.ToString())
        End Try
    End Sub


    Protected Overrides Sub OnStop()
        ' Add code here to perform any tear-down necessary to stop your service.
    End Sub


    Protected Overrides Sub OnSessionChange(ByVal changeDescription As SessionChangeDescription)
        Try


            Select Case changeDescription.Reason
                Case SessionChangeReason.SessionLogon


                    [InsertDailyTime]("Session Logon:-" + DateTime.Now.ToString())


                    Exit Select
                Case SessionChangeReason.SessionLogoff
                    [InsertDailyTime]("Session Logoff:-" + DateTime.Now.ToString())
                    Exit Select
                Case SessionChangeReason.RemoteConnect
                    'Remote Connect 
                    Exit Select
                Case SessionChangeReason.RemoteDisconnect
                    'Remote Disconnect 
                    Exit Select
                Case SessionChangeReason.SessionLock
                    [InsertDailyTime]("Session Lock:-" + DateTime.Now.ToString())
                    Exit Select
                Case SessionChangeReason.SessionUnlock
                    [InsertDailyTime]("Session Unlock:-" + DateTime.Now.ToString())
                    Exit Select
                Case Else
                    Exit Select
            End Select


        Catch ex As Exception
            [Error]("OnSessionChange:-" + ex.Message + DateTime.Now.ToString())
        End Try
    End Sub


    Public Shared Sub [InsertDailyTime](ByVal message As String)
        Dim datewisefile As String = ""
        Dim dt As DateTime = DateTime.Now
        Dim fs As System.IO.FileStream = Nothing
        Dim info As Byte() = Nothing
        Try
            If Not System.IO.Directory.Exists(Path.GetDirectoryName("D:\Projects\TimeManager")) Then
                System.IO.Directory.CreateDirectory(Path.GetDirectoryName("D:\Projects\TimeManager"))
            End If
            'logfile = logfile.Split('.')[0] + System.DateTime.Now.ToString("yyyyMMddHHmm") + '.' + logfile.Split('.')[1]; 
            datewisefile = ((dt.Year & "") + "-" + (If(dt.Month <= 9, "0" & dt.Month & "", dt.Month & "")) & "") + "-" + (If(dt.Day <= 9, "0" & dt.Day & "", dt.Day & ""))
            DailyTime = "\Nik's_" & datewisefile & ".txt"
            fs = System.IO.File.Open(Path.GetDirectoryName("D:\Projects\TimeManager") & "\logs" + DailyTime, System.IO.FileMode.Append)
            info = New System.Text.UTF8Encoding(True).GetBytes((System.DateTime.Now.ToString() & ":") + message & vbLf)
            ' fs.Close(); 
            fs.Write(info, 0, info.Length)
            fs.Flush()
            fs.Close()
            fs = Nothing
            info = Nothing
        Catch ex As Exception
            [Error]("InsertDailyTime:-" + ex.Message + DateTime.Now.ToString())
        Finally
            'GC.SuppressFinalize(Me)
        End Try
    End Sub


    Public Shared Sub [Error](ByVal message As String)
        Dim datewisefile As String = ""
        Dim dt As DateTime = DateTime.Now
        Dim fs As System.IO.FileStream = Nothing
        Dim info As Byte() = Nothing
        Try
            If Not System.IO.Directory.Exists(Path.GetDirectoryName("D:\Projects\TimeManager") & "\logs") Then
                System.IO.Directory.CreateDirectory(Path.GetDirectoryName("D:\Projects\TimeManager") & "\logs")
            End If
            'logfile = logfile.Split('.')[0] + System.DateTime.Now.ToString("yyyyMMddHHmm") + '.' + logfile.Split('.')[1]; 
            datewisefile = ((dt.Year & "") + "-" + (If(dt.Month <= 9, "0" & dt.Month & "", dt.Month & "")) & "") + "-" + (If(dt.Day <= 9, "0" & dt.Day & "", dt.Day & ""))
            errlogfile = "\TimeManager_" & datewisefile & ".Log"
            fs = System.IO.File.Open(Path.GetDirectoryName("D:\Projects\TimeManager") & "\logs" + errlogfile, System.IO.FileMode.Append)
            info = New System.Text.UTF8Encoding(True).GetBytes((System.DateTime.Now.ToString() & ":") + message & vbLf)
            ' fs.Close(); 
            fs.Write(info, 0, info.Length)
            fs.Flush()
            fs.Close()
            fs = Nothing
            info = Nothing
        Catch ex As Exception
            'Write("Error:"+ex.Message); 
        Finally
            'GC.SuppressFinalize(Me)
        End Try
    End Sub


End Class

Open task manager on remote computer even explorer.exe is not running

sometime happens that when we take remote computer and it gets hangs and we end process the explorer.exe and may be task manager also closed.at that time you can use Ctrl+Alt+End t for opening task manager.

Never Use Sql Connection's Global Object

Never Use sqlConnection's global object in class.because it's create too much problem.it may crash the system.i really face this Issue.for Complete Issue Please see below link.

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/9793b998-3319-40e0-b4ef-5d5d8a10cfd8 

      

Split Function in Sql

CREATE FUNCTION DBO.SPLIT
(
    @LIST NVARCHAR(2000),
    @SPLITON NVARCHAR(5)
)  
RETURNS @RTNVALUE TABLE 
(
        
    ID INT IDENTITY(1,1),
    VALUE NVARCHAR(100)
) 
AS  
BEGIN
WHILE (CHARINDEX(@SPLITON,@LIST)>0)
BEGIN 
INSERT INTO @RTNVALUE (VALUE)
SELECT
    VALUE = LTRIM(RTRIM(SUBSTRING(@LIST,1,CHARINDEX(@SPLITON,@LIST)-1))) 
    SET @LIST = SUBSTRING(@LIST,CHARINDEX(@SPLITON,@LIST)+LEN(@SPLITON),LEN(@LIST))
END 
   INSERT INTO @RTNVALUE (VALUE)
    SELECT VALUE = LTRIM(RTRIM(@LIST))

    RETURN
END