len vs datalength - sql

DECLARE @Test_len VARCHAR(20)
DECLARE @Test_Dlen NVARCHAR(20)

SET @Test_len = 'Find Length'
SET @Test_Dlen = 'Find Length'

SELECT
LEN(@Test_len) AS DATA1_LEN,
DATALENGTH(@Test_len) AS DATA1_DLEN,
LEN(@Test_Dlen) AS DATA2_LEN,
DATALENGTH(@Test_Dlen) AS DATA2_DLEN


Above example, two variable have declared in two different datatypes.
=> Where as Len will count only the length of strings in the column and
=> Datalength will count the length of string as per datatype.

In short, LEN is used to return number of character In string and
In DataLength is used for number of bytes.

Remove all Triggers - sql server

Run the below code within the database where the triggers must be removed

DECLARE @SQLCmd nvarchar(1000)
DECLARE @Triggers  varchar(500)
DECLARE @sch varchar(500)

DECLARE TGCursor CURSOR FOR

SELECT ISNULL(tbl.name, vue.name) AS [schemaName]
     , trg.name AS triggerName
FROM sys.triggers trg
LEFT OUTER JOIN (SELECT tparent.object_id, ts.name
                 FROM sys.tables tparent
                 INNER JOIN sys.schemas ts ON TS.schema_id = tparent.SCHEMA_ID)
                 AS tbl ON tbl.OBJECT_ID = trg.parent_id
LEFT OUTER JOIN (SELECT vparent.object_id, vs.name
                 FROM sys.views vparent
                 INNER JOIN sys.schemas vs ON vs.schema_id = vparent.SCHEMA_ID)
                 AS vue ON vue.OBJECT_ID = trg.parent_id

OPEN TGCursor
FETCH NEXT FROM TGCursor INTO @sch,@Triggers
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLCmd = N'DROP TRIGGER [' + @sch + '].[' + @Triggers  + ']'
EXEC sp_executesql @SQLCmd
PRINT @SQLCmd

FETCH next FROM TGCursor INTO @sch,@Triggers
END

CLOSE TGCursor
DEALLOCATE TGCursor

Close existing connection before deleting or restore database - sql server

Below query will close all running connection.

++++++++++++++++++++++++++++++++++++++++++++++++++++

USE master
GO

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'Database Name'
IF db_id(@DBName) < 4
BEGIN
  PRINT 'Connections to system databases cannot be killed'
  RETURN
END

SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
  EXEC(@spidstr)
  SELECT @ConnKilled = COUNT(1)
  FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END

Run batch file from asp.net c#

 string batpath = " Batch file path ";
 System.Diagnostics.Process proc = new System.Diagnostics.Process();
 proc.StartInfo.FileName = "batch file name";
 proc.StartInfo.WorkingDirectory = batpath;
 proc.StartInfo.UseShellExecute = true;
 proc.Start();
 proc.Close();

Run EXE with parameters from asp.net c#

 ProcessStartInfo startInfo = new ProcessStartInfo("exe path");
 startInfo.WindowStyle = ProcessWindowStyle.Minimized;
 startInfo.UseShellExecute = true;

 //set user and password if needed
 //startInfo.UserName = "chetan";
 //startInfo.Password =  "Password";
 //startInfo.Domain = "MyDomain";

// pass parameter to exe
 startInfo.Arguments = strInputPath + "@" + strOutputPath;
 Process.Start(startInfo);

Manage IIS Using Asp.net

You can do operation with IIS using asp.net C#. below is the step for it.
1st you need ServerManager dll which you can copy from :
"C:\Windows\System32\inetsrv" (or as per your system)

=> Working with the ServerManager
ServerManager server = new ServerManager();


=> Getting Sites Associated with the Server
SiteCollection sites = server.Sites;
foreach (Site site in sites)
{
    ApplicationDefaults defaults = site.ApplicationDefaults;

    //get the name of the ApplicationPool under which the Site runs
    string appPoolName = defaults.ApplicationPoolName;

    ConfigurationAttributeCollection attributes =  defaults.Attributes;
    foreach (ConfigurationAttribute configAttribute in attributes)
    {
        //put code here to work with each ConfigurationAttribute
    }

    ConfigurationAttributeCollection attributesCollection = site.Attributes;
    foreach (ConfigurationAttribute attribute in attributesCollection)
    {
        //put code here to work with each ConfigurationAttribute
    }

    //Get the Binding objects for this Site
    BindingCollection bindings = site.Bindings;
    foreach (Microsoft.Web.Administration.Binding binding in bindings)
    {
        //put code here to work with each Binding
    }

    //retrieve the State of the Site
    ObjectState siteState = site.State;

    //Get the list of all Applications for this Site
    ApplicationCollection applications = site.Applications;
    foreach (Microsoft.Web.Administration.Application application in applications)
    {
        //put code here to work with each Application
    }
}

=> Getting Applications Associated with each Site
ApplicationCollection applications = site.Applications;
foreach (Microsoft.Web.Administration.Application application in applications)
{
    //get the name of the ApplicationPool
    string applicationPoolName = application.ApplicationPoolName;

    VirtualDirectoryCollection directories = application.VirtualDirectories;
    foreach (VirtualDirectory directory in directories)
    {
        //put code here to work with each VirtualDirectory
    }
}

=> Getting Virtual Directories Associated with each Application
VirtualDirectoryCollection directories = application.VirtualDirectories;
foreach (VirtualDirectory directory in directories)
{
    ConfigurationAttributeCollection attribues = directory.Attributes;
    foreach (ConfigurationAttribute attribute in attributes)
    {
        //put code here to work with each attribute
    }

    ConfigurationChildElementCollection childElements = directory.ChildElements;
    foreach (ConfigurationElement element in childElements)
    {
        //put code here to work with each ConfigurationElement
    }

    //get the directory.Path
    string path = directory.Path;

    //get the physical path
    string physicalPath = directory.PhysicalPath;
}

=> Getting Application Pools Associated with a Server

ServerManager server = new ServerManager();

ApplicationPoolCollection applicationPools = server.ApplicationPools;
foreach (ApplicationPool pool in applicationPools)
{
    //get the AutoStart boolean value
    bool autoStart = pool.AutoStart;

    //get the name of the ManagedRuntimeVersion
    string runtime = pool.ManagedRuntimeVersion;

    //get the name of the ApplicationPool
    string appPoolName = pool.Name;

    //get the identity type
    ProcessModelIdentityType identityType = pool.ProcessModel.IdentityType;

    //get the username for the identity under which the pool runs
    string userName = pool.ProcessModel.UserName;

    //get the password for the identity under which the pool runs
    string password = pool.ProcessModel.Password;
}

=> Creating a New Application Pool
ServerManager server = new ServerManager();

ApplicationPool myApplicationPool = null;

//we will create a new ApplicationPool named 'MyApplicationPool'
//we will first check to make sure that this pool does not already exist
//since the ApplicationPools property is a collection, we can use the Linq FirstOrDefault method
//to check for its existence by name
if (server.ApplicationPools != null && server.ApplicationPools.Count > 0)
{
    if (server.ApplicationPools.FirstOrDefault(p => p.Name == "MyApplicationPool") == null)
    {
        //if we find the pool already there, we will get a referecne to it for update
        myApplicationPool = server.ApplicationPools.FirstOrDefault(p => p.Name == "MyApplicationPool");
    }
    else
    {
        //if the pool is not already there we will create it
        myApplicationPool = server.ApplicationPools.Add("MyApplicationPool");
    }
}
else
{
    //if the pool is not already there we will create it
    myApplicationPool = server.ApplicationPools.Add("MyApplicationPool");
}

if (myApplicationPool != null)
{
    //for this sample, we will set the pool to run under the NetworkService identity
    myApplicationPool.ProcessModel.IdentityType = ProcessModelIdentityType.NetworkService;

    //we set the runtime version
    myApplicationPool.ManagedRuntimeVersion = "v4.0";

    //we save our new ApplicationPool!
    server.CommitChanges();
}

if (myApplicationPool != null)
{
    //for this sample, we will set the pool to run under the identity of a specific user
    myApplicationPool.ProcessModel.IdentityType = ProcessModelIdentityType.SpecificUser;
    myApplicationPool.ProcessModel.UserName = UserName;
    myApplicationPool.ProcessModel.Password = Password;

    //we set the runtime version
    myApplicationPool.ManagedRuntimeVersion = "v4.0";

    //we save our new ApplicationPool!
    server.CommitChanges();
}

=>Creating a New Site

ServerManager server = new ServerManager();

if (server.Sites != null && server.Sites.Count > 0)
{
    //we will first check to make sure that the site isn't already there
    if (server.Sites.FirstOrDefault(s => s.Name == "MySite") == null)
    {
        //we will just pick an arbitrary location for the site
        string path = @"c:\MySiteFolder\";

        //we must specify the Binding information
        string ip = "*";
        string port = "80";
        string hostName = "*";

        string bindingInfo = string.Format(@"{0}:{1}:{2}", ip, port, hostName);

        //add the new Site to the Sites collection
        Site site = server.Sites.Add("MySite", "http", bindingInfo, path);

        //set the ApplicationPool for the new Site
        site.ApplicationDefaults.ApplicationPoolName = myApplicationPool.Name;

        //save the new Site!
        server.CommitChanges();
    }
}

Export data into excel and storing location excel outside network - sql

Below for the query for the export excel but make sure your network path will be in sharing
"\\192.168.1.11\test\dir\"

Insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\192.168.1.11\test\dir\test.xls;',
'SELECT * FROM [SheetName$]') select * from SQLTable