ALTER TABLE {table-name} WITH NOCHECK 
ADD CONSTRAINT [{column-constraint-name}] DEFAULT (6) FOR {column-name}

select * from msdb..sysjobs

From the msdb..sysjobs, the required job_id for enable or disable to be taken.

T-SQL to dis-enable specific job in sql server

 EXEC msdb..sp_update_job @job_id = '770F175B-C8A0-4157-BACD-0AF129DF570C', @enabled = 0  

T-SQL to enable specific job in sql server

 EXEC msdb..sp_update_job @job_id = '770F175B-C8A0-4157-BACD-0AF129DF570C', @enabled = 1  

On Sql Server these alert mechanism always found to be tedious.

Though sql server on there own provided some alert sending tools, but i found to complex in configuring them.

I found an other way of alerting us through invoking an url which in-turn would send email or sms as per our requirement.

By using xp_cmdshell command in sql server, operating system shell command can be executed. So, xp_cmdshell command can be used to execute a vbscript file.

Inturn Vb Script can send email or invoke a url to alert us. xp_cmdshell statement to be included to call the vbscript

EXEC master..xp_cmdshell 'C:\winnt\system32\cscript.exe c:\sqljobalert.vbs',no_output  

VBScript source code of c:\sqljobalert.vbs

call main()
sub main()
  'Below code would send an email from vbscript
	Dim cdomsg, icon, Flds
	Set cdomsg = wscript.CreateObject ("CDO.Message")
	set icon = wscript.CreateObject ("CDO.configuration")
	Set Flds = icon.Fields 
	Flds.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "your smtp server"
	Flds.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
	flds.Update
	set cdomsg.Configuration = icon
	
	cdoMsg.To = "to mail id"
	cdoMsg.From = "from mail id"
	cdoMSg.Subject = "Subject of the mail"
	cdomsg.TextBody = "Email Message Content"
	cdomsg.send

   'Below code would invoke an url from vbscript
	Dim xmlHTTP, urlString
	Set xmlHTTP = WScript.CreateObject("Msxml2.XMLHTTP")
	urlString = "http://url to be invoked"
	xmlHTTP.Open "GET", urlString , False
	xmlHTTP.Send  ""
end sub 

This can be used to alert on sql server job failure

So we need to write a small piece of code in main webpage to detect whether the user is from mobile device.
If the user is detected to be from mobile, we can redirect to its corresponding mobile website.

The ASP.Net C# function to detect whether the request is from mobile device.

    public static Boolean isMobile()
    {
        HttpContext curcontext = HttpContext.Current;

        string user_agent = curcontext.Request.ServerVariables["HTTP_USER_AGENT"];
        user_agent = user_agent.ToLower();
        
        
        // Checks the user-agent
        if (user_agent != null )
        {
            // Checks if its a Windows browser but not a Windows Mobile browser
            if (user_agent.Contains("windows") && !user_agent.Contains("windows ce"))
            {
                return false;
            }

            // Checks if it is a mobile browser
            string pattern = "up.browser|up.link|windows ce|iphone|iemobile|mini|mmp|symbian|midp|wap|phone|pocket|mobile|pda|psp";
            MatchCollection mc = Regex.Matches(user_agent, pattern, RegexOptions.IgnoreCase);
            if (mc.Count > 0)
                return true;

           // Checks if the 4 first chars of the user-agent match any of the most popular user-agents
            string popUA = "|acs-|alav|alca|amoi|audi|aste|avan|benq|bird|blac|blaz|brew|cell|cldc|cmd-|dang|doco|eric|hipt|inno|ipaq|java|jigs|kddi|keji|leno|lg-c|lg-d|lg-g|lge-|maui|maxo|midp|mits|mmef|mobi|mot-|moto|mwbp|nec-|newt|noki|opwv|palm|pana|pant|pdxg|phil|play|pluc|port|prox|qtek|qwap|sage|sams|sany|sch-|sec-|send|seri|sgh-|shar|sie-|siem|smal|smar|sony|sph-|symb|t-mo|teli|tim-|tosh|tsm-|upg1|upsi|vk-v|voda|w3c |wap-|wapa|wapi|wapp|wapr|webc|winw|winw|xda|xda-|";
            if (popUA.Contains("|" + user_agent.Substring(0, 4) + "|"))
                return true;
        }
        
        // Checks the accept header for wap.wml or wap.xhtml support
        string accept = curcontext.Request.ServerVariables["HTTP_ACCEPT"];
        if (accept != null )
        {
            if (accept.Contains("text/vnd.wap.wml") || accept.Contains("application/vnd.wap.xhtml+xml"))
            {
                return true;
            }
        }

        // Checks if it has any mobile HTTP headers

        string x_wap_profile = curcontext.Request.ServerVariables["HTTP_X_WAP_PROFILE"];
        string profile = curcontext.Request.ServerVariables["HTTP_PROFILE"];
        string opera = curcontext.Request.Headers["HTTP_X_OPERAMINI_PHONE_UA"];

        if (x_wap_profile != null || profile != null || opera != null )
        {
            return true;
        }

        return false;
    }

RSS pubDate element is of format "Wed, 14 Sep 1983 00:00:01 GMT"

This pubData element format can be achieved in C# .Net using the following piece of code.

DateTime.Now.ToString("ddd, dd MMM yyyy HH:mm:ss K")

output would be

"Tue, 03 Nov 2009 13:45:57 +05:30"

The following sample would help in hashtable elements enumeration.

//create a new hashtable
System.Collections.Hashtable hsh = new System.Collections.Hashtable();

//add some elements in the hashtable
hsh.Add("1", "london dreams review");
hsh.Add("2", "karisma");
hsh.Add("3", "the band movie");
hsh.Add("4", "end of days");

//enumerate or looping through all the elements in the hashtable
foreach (System.Collections.DictionaryEntry entry in hsh)
{
  Console.WriteLine("Key: " + entry.Key.ToString() + " | " + "Value: " + entry.Value.ToString());
}

	using System.Xml;
	using System.Xml.Xsl;

	public static string ConvertXML(XmlDocument InputXMLDocument, string XSLTFilePath
 , XsltArgumentList XSLTArgs)
        {
            System.IO.StringWriter sw = new System.IO.StringWriter();
            XslCompiledTransform xslTrans = new XslCompiledTransform();
            xslTrans.Load(XSLTFilePath);
            xslTrans.Transform(InputXMLDocument.CreateNavigator(), XSLTArgs, sw);
            return sw.ToString();
        }

	<?xml version="1.0" encoding="utf-8" ?>
	<sexystars>
	  <category name="Bollywood">
	    <sexystar name="Antra mali" />
	    <sexystar name="Deepika Padukone" />
	    <sexystar name="Mandira Bedi" />
	  </category>
	  <category name="Hollywood">
	    <sexystar name="Jennifer Lopez" />
	    <sexystar name="Jessica Alba" />
	  </category>
	</sexystars>

	<?xml version="1.0" encoding="UTF-8" ?>
	<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	  <xsl:output omit-xml-declaration="yes"/>
	  <xsl:template match="sexystars">
	    <div>
	      <xsl:apply-templates select="category" />
	    </div>
	  </xsl:template>
	  <xsl:template match="category">
	    <h2>
	      <xsl:value-of select="@name"/>
	    </h2>
	    <ul>
	      <xsl:apply-templates select="sexystar" />
	    </ul>
	  </xsl:template>
	  <xsl:template match="sexystar">
	    <li>
	      <xsl:value-of select="@name"/>
	    </li>
	  </xsl:template>
	</xsl:stylesheet>

	XmlDocument xDoc=new XmlDocument();
        xDoc.Load(@"C:\sample.xml");
        string returnhtml = ConvertXML(xDoc, @"C:\sample.xslt", new XsltArgumentList());

	<div>
	  <h2>Bollywood</h2>
	    <ul>
	      <li>Antra mali</li>
	      <li>Deepika Padukone</li>
	      <li>Mandira Bedi</li>
	    </ul>
	  <h2>Hollywood</h2>
	    <ul>
	      <li>Jennifer Lopez</li>
	      <li>Jessica Alba</li>
	    </ul>
	</div>

Dynamic IP’s without cookie

Crawling without cookie would happen generally due to some program written to crawl. When program is written to crawl, the request to our website from this IP would be in huge number than normal.

This type of crawling can be handled by storing the incoming IP’s to our website in a HASHTABLE with count. Thereby if the count for a particular IP reaches a limit in a given time, we can take a measure to block this IP or to show a captcha image.

HASHTABLE can be cleared when count of HASHTABLE reaches a defined count, so that memory of the application due to HASHTABLE is maintained without hauling our website.

Dynamic IP’s without cookie

Crawling with cookie would happen generally when group of people manually involved in taking the content of our website. Hence when captcha image is shown, they enter captcha and keeps crawling our website. When we block the IP, they recreate a new IP by rebooting the MODEM.

This type of crawling can be handled as follows

• A distinct cookie can be set in 403.6 (IP address rejected) htm page in IIS, as 403.6 IP address rejected htm page would be normally shown when IP is blocked from our website.
• Hence when new request comes with distinct cookie, we can take a measure to block them even if it’s from newly recreated IP by rebooting MODEM.

Though this cannot be 100% safe but at least we can make the crawler think for a while.

Also, the distinct security cookie thus created can be normal html cookie. But the crawler can easily clear this cookie through browser and can restart crawling. Hence to extend our security further we can create a flash cookie which cannot be cleared through any browser.

	exec sys.sp_changeobjectowner <<object name>>, <<new owner name>>

The object name(@objname) parameter should in the format "[owner].[object]". The new owner name(@newowner) should be valid name from

	select * from sysusers

The following sql stores list of user created table with name and its schema or owner name

	select t.[name] as TableName,t.object_id,s.[name] as OwnerName 
	from sys.tables t  inner join sys.schemas s
	on s.schema_id=t.schema_id where t.is_ms_shipped=0

The following example changes the table name "CityStateMaster" of "dbo" schema to "guest" schema.

	sp_changeobjectowner 'guest.CityStateMaster',dbo

The following sql can be used to generate sqls to convert schema or owner of all user created functions and stored procedures to 'dbo' owner.

SELECT 'exec sp_changeobjectowner ''' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ''',' + 'dbo;' FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0	

System.Net.WebClient class in .Net provides DownloadData method to download file from given URI.

System.Net.WebClient is given with the required login username and password details with System.Net.NetworkCredential.

DownloadData method gives the result byte[] of the file, which can be stored to path using System.IO.FileStream class.

WebClient wc = new WebClient();
wc.Credentials = new System.Net.NetworkCredential("username", "password");
byte[] buff = wc.DownloadData("http://www.somedomain.com/filewithlogin.zip");
using (System.IO.FileStream fs = 
new FileStream(@"C:\downloads\filewithlogin.zip", FileMode.Create))
{
      fs.Write(buff, 0, buff.Length);
      fs.Close();
}