andyfrench.info

If you want to count the number of lines of SQL in stored procedures and functions try running the following SQL:

USE [Database name here]

SELECT  t.sp_name,
        SUM(t.lines_of_code) - 1 AS lines_ofcode,
        t.type_desc
FROM    ( SELECT    o.name AS sp_name,
                    ( LEN(c.text) - LEN(REPLACE(c.text, CHAR(10), '')) ) AS lines_of_code,
                    CASE WHEN o.xtype = 'P' THEN 'Stored Procedure'
                         WHEN o.xtype IN ( 'FN', 'IF', 'TF' ) THEN 'Function'
                    END AS type_desc
          FROM      sysobjects o
                    INNER JOIN syscomments c ON c.id = o.id
          WHERE     o.xtype IN ( 'P', 'FN', 'IF', 'TF' )
                    AND o.category = 0
                    AND o.name NOT IN ( 'fn_diagramobjects', 'sp_alterdiagram',
                                        'sp_creatediagram', 'sp_dropdiagram',
                                        'sp_helpdiagramdefinition',
                                        'sp_helpdiagrams', 'sp_renamediagram',
                                        'sp_upgraddiagrams', 'sysdiagrams' )
        ) t
GROUP BY t.sp_name,
        t.type_desc
ORDER BY 1

The following rules might help out in protecting a site against SQL injection:

[ISAPI_Rewrite]
RewriteRule .*(?:global.asa|default\.ida|root\.exe|\.\.).* . [F,I,O]

RewriteRule .*(?:DECLARE).* /null.htm [F,I]

RewriteRule ^.*\+update\+.*$ /null.htm [F,I]
RewriteRule ^.*SUBSTRING\(.*$ /null.htm [F,I]
RewriteRule ^.*CHARINDEX.*$ /null.htm [F,I]
RewriteRule ^.*NVARCHAR.*$ /null.htm [F,I]
RewriteRule ^.*CHAR\(.*$ /null.htm [F,I]
RewriteRule ^.*CAST\(.*$ /null.htm [F,I]
RewriteRule ^.*%20xp_.*$ /null.htm [F,I]
RewriteRule ^.*%20@.*$ /null.htm [F,I]
RewriteRule ^.*@%20.*$ /null.htm [F,I]
RewriteRule ^.*';*$ /null.htm [F,I]
RewriteRule ^.*EXEC\(@.*$ /null.htm [F,I]
RewriteRule ^.*sp_password.*$ /null.htm [F,I]

F – forbidden
I – Ignore case

Setting NOCOUNT to ON stops the message that shows the count of the number of rows affected by a T-SQL statement or stored procedure from being returned as part of the result set.

Why is this useful or important? It can result in a significant performance boost when running SQL statements:

“When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.” – from SQL Server online documentation

I keep forgetting this… Open the MMC (Start > Run… > then type “mmc”).

Then add the Active Directory Users and Computers snap-in (File > Add/Remove Snap-in…) . Take it from there.

A few notes on an issue I have been experiencing. I had the following C# code that constantly threw a timeout error on line 5:

HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://api-verify.recaptcha.net/verify");
request.Method = "POST";
request.ContentType = "application/x-www-form-urlencoded";

using (Stream stream = request.GetRequestStream())
{
    using (StreamWriter streamWriter = new StreamWriter(stream, Encoding.ASCII))
    {
        streamWriter.Write(postData);
        streamWriter.Close();
    }

    stream.Close();
}

ipconfig

Running ipconfig gives you nice information like your default gateway.

tracert

Running tracert for api-verify.recaptcha.net showed a hop to the default gateway (the first entry in the list of results) and nothing but timeouts thereafter.

Default gateway

A gateway is a routing device that knows how to pass traffic between different subnets and networks. It will know some routes to given IP addresses but not the routes to every address on the Internet. A gateway will will know the addresses of other gateways it can hand the traffic off to if the required route is unknown to it. A default gateway will be on the same subnet and is the gateway to be relied on when it doesn't know how to route traffic.

In this case, wrong default gateway?

When setting up an existing .NET application on a fresh machine you may see the following error:

"Could not load file or assembly 'Some.Assembly.Name' or one of its dependencies. Access is denied."

A potential quick solution is to go to “C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files” and modify the security settings for the folder to grant "Full control" to the 'users' group.

Not ideal but it will get the development environment up-and-running.

When opening a source file in Visual Studio, if you want the solution explorer to reflect the location of the file ensure the appropriate option is set:

Tools > Options > Projects & Solutions > General

Set the Track Active Items in Solution Explorer option.

Alternatively if you’ve got ReSharper use Ctrl-Shift-T.