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

Pass Eval as Argument Of Javascript Function

<input type="button" id="but1" onclick="myFunction('<%# Eval("id") %>','<%# Eval("name")%>' )" />

Get or set shortdate format from regional setting in .net

Public regKey As Microsoft.Win32.RegistryKey
regKey = My.Computer.Registry.CurrentUser.OpenSubKey("Control Panel\International", True)
 If regKey.GetValue("sShortDate") = "dd/MM/yyyy" Then 
Else
   SetRegionalSetting("dd/MM/yyyy") 
End If

Enable disable all Trigger In One Query

sp_MSforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL"
GO
sp_MSforeachtable "ALTER TABLE ? ENABLE TRIGGER ALL"

Change Collation Of Database

USE [master]

ALTER DATABASE [DbName]  SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

GO
ALTER DATABASE [DbName] COLLATE SQL_Latin1_General_CP1_CI_AS
GO
ALTER DATABASE [DbName] SET RECOVERY FULL WITH NO_WAIT
GO

Go
ALTER DATABASE [DbName]  SET MULTI_USER 
GO

Sql X Types

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
FN = Scalar function
IF = Inlined table-function
K = PRIMARY KEY or UNIQUE constraint
L = Log
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
V = View
X = Extended stored procedure

Comparing Tables For Difference Version

Create table #nik
(
    table_Name Varchar(300),
    Column_Name Varchar(300)
)
Create Table #Temp 
(
    Table_Name Varchar(300)
) 

Insert Into #nik Select TABLE_NAME ,Column_Name From dbName.Information_Schema.columns 
Where Column_Name Not IN
(Select Column_Name From dbName2.Information_Schema.columns) 
Select * From #nik 

Select * From #nik  Where table_Name Not in (Select table_Name from #Temp)
Insert Into #Temp Values('Table')
Select * From #Temp 

Rename Computer Name When Sql Server Already Installed

To rename a computer that hosts a stand-alone instance of SQL Server
•    For a renamed computer that hosts a default instance of SQL Server, run the following procedures:

sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
Restart the instance of SQL Server.
•    For a renamed computer that hosts a named instance of SQL Server, run the following procedures:

sp_dropserver <'old_name\instancename'>
GO
sp_addserver <'new_name\instancename'>, local
GO
Restart the instance of SQL Server.
 After the Renaming Operation
________________________________________
After a computer has been renamed, any connections that used the old computer name must connect by using the new name.
To verify that the renaming operation has completed successfully
•    Select information from either @@SERVERNAME or sys.servers. The @@SERVERNAME function will return the new name, and the sys.servers table will show the new name. The following example shows the use of @@SERVERNAME.

SELECT @@SERVERNAME AS 'Server Name'

Kill Sleeping Connection in Sql

Create PROCEDURE [dbo].[SleepedConnKill] 
AS
 SET NOCOUNT ON;  
    Declare @StrKill Varchar(30)         
    Declare @SPID_Cur Numeric(18,0)
   
   Declare SysProcess_Cursor Cursor LOCAL  For
     Select SPID From sys.sysprocesses  Where SPID>50 And  Status='sleeping' And  DateDiff(MINUTE ,Last_Batch,GetDate())>5 
   Open SysProcess_Cursor
   Fetch Next From SysProcess_Cursor Into @SPID_Cur
   While @@Fetch_Status = 0                    
   Begin   
    
     Set @StrKill = 'KILL '+ convert(varchar(10),@SPID_Cur)            
     EXEC(@StrKill)    
    
   Fetch Next From SysProcess_Cursor Into @SPID_Cur
   End    
   Close SysProcess_Cursor
   Deallocate SysProcess_Cursor
    Return

UPDATE STATISTICS Fot all tables in Database

Here is the sp which update STATISTICS for all table.

EXEC sp_updatestats

Update STATISTICS is necessary to avoid deadlock in database.
 

Using ROW_NUMBER() Function Fetch Exact number of Row.

Fetch Exact 10th row from table where there is no primary key. 

select * from
(Select Emp_First_Name ,ROW_NUMBER() OVER(ORDER BY Emp_First_Name DESC) AS 'Emp' From Table)Qry Where Qry.emp = 10

Get how many Number Of Rows and Coulmns and datasize in all tables in particular Database.

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

Update Tables Of 2 muliple databases in bulk

 UPDATE db1.dbo.tblA
SET Coulmnname = db2.DBO.tblA.Coulmnname
FROM db2.DBO.tblA INNER JOIN db1.dbo.tblA ON
db2.DBO.tblA.PKCOULMN = db1.dbo.tblA.PKCOULMN

Web Garden Setting In Iis 6.0

http://msdn.microsoft.com/en-us/library/Aa720391

Specified argument was out of the range of valid values.Parameter name: utcDate

Please visit below link that's solve my problem.

http://dev-for-fun.blogspot.com/2007/12/net-ajax-axd-give-error-regarding.html?showComment=1299050714092#c106412783630990912

Run Sql Bunch From .net

   Public Sub PrepareSqlProcedure(ByVal ConString As String)
        Dim Dt_Check As New DataTable
        Try
            Dim ObjDataHandler As Object
            Dim strExePath As String
            Dim strpath As String
            Dim intPer As Short
            Dim fnum, intCount As Short
            Dim arrStr() As String
            Dim strFileText As String
            Dim strLocalConnect As String
            ' On Error GoTo errhandler
            'ObjSysCon = New ClsConnection
            'ObjSysCon.OpenConnection(ConString)
            Dt_Check = ObjSysCon.Getdatatable_New("Select * From Sysobjects Where XType='p' and Name='Shift_Summary'")
            If Dt_Check.Rows.Count > 0 Then
                'Nothing
            Else
                Dim sSql As String
                strpath = My.Application.Info.DirectoryPath & "\Script\Script.sql"
                fnum = FreeFile()
                FileOpen(fnum, strpath, OpenMode.Input)
                strFileText = InputString(fnum, LOF(fnum))
                FileClose(fnum)

                'Dim Dt As New DataSet
                'strFileText = ""
                'ObjSysCon.ExcuteQuery(Dt, "Select * from ViewOrange_SP")
                'strFileText = EnCrypt(Dt.Tables(0).Rows(0)("SP").ToString)
                arrStr = Split(strFileText, "GO")

                Dim dblVal As Double
                For intCount = 0 To UBound(arrStr) - 1
                    ' intPer = System.Math.Round((CDbl(intCount) * 100) / CDbl(UBound(arrStr)), 0)
                    System.Windows.Forms.Application.DoEvents()
                    If arrStr(intCount).ToString.Length <> 0 Then
                        If ObjSysCon.ExcuteCommand(arrStr(intCount)) = False Then
                            'GoTo errhandler
                            'Throw ex
                        End If
                    Else
                       Msg.Box(intCount.ToString)
                    End If
                Next
            End If
        Catch ex As Exception
        Finally
        End Try
    End Sub