Getting a quicker SQL row count

Credit goes to Rick Caminiti at http://rickcaminiti.com/tips/alternative-efficient-tables-rowcount-sql/

SELECT COUNT(*) statements make a full table scans to return the total table’s row count, it can take a lot of time and a lot of “energy” for a large table.

There is another way to determine the total row count in a table by using the sysindexes system table. There is a column (ROWS) in it that contains the total row count for each table in your database.

You can use the syntax below to speed up the query by several times.  You can see this by setting statistics on, as shown below.




SET STATISTICS IO ON
GO

SELECT count(*) FROM tbTest
GO

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tbTest') AND indid < 2
GO

SET STATISTICS IO OFF
GO

I tried my own test, and here are my results:


(1 row(s) affected)
Table 'myTable'. Scan count 1, logical reads 116, physical reads 1, read-ahead reads 176, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table 'sysidxstats'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Reset Windows Media Player 12 To Default Settings

Here's a cool trick that allows you to reset WMP12 back to it’s default settings. This might be handy if you are encountering any issues of your own.

Simply to go Start > Run (or hold down the Windows Key and press R) and type in the following:

msdt.exe -id WindowsMediaPlayerConfigurationDiagnostic

Press enter and follow the prompts to reset WMP.

Credit goes to Ben:  http://daily-grind.net/reset-windows-media-player-12-to-default-settings/

SQL example for updating children records from a parent record

I just ran into this again, and I know I have had to do this several times before but it gets old having to look up such an activity that I may only do twice a year.

The scenario... There are four records:
ID=123, ParentID=null, Name="Bob"


ID=1000, ParentID=123, Name="Bob"
ID=1001, ParentID=123, Name="Bob"
ID=1002, ParentID=123, Name="Bob"

The requirement is to update Name="Joe" where ID=123, and then cascade the changes to the children records.  This could be done easily w/ the same proc that updates the parent, however in my situation that proc is being shared, so my solution is to make a new query that fetches the right info w/ a join and then updates:

UPDATE children
SET
Field1 = parent.Field1,
Field2 = parent.Field2,
Field3 = parent.Field3
from ExampleTable children
inner join ExampleTable parent on parent.ID = children.ParentID
WHERE children.ParentID = @ParentID

Clear the "must change password" checkbox for new SQL users.

It's very annoying to create a new SQL user and forget to clear the "User must change password at next login" checkbox.

This will result in an error message:
Login failed for user 'login_name'.  Reason: The password of the account must be changed.

If you try to clear the message, you get:
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON. (Microsoft SQL Server, Error: 15128)

One solution is to delete the user and create again, and clear the checkbox.  Another solution I found on Experts Exchange:
ALTER LOGIN UserLogin WITH PASSWORD = 'NewPassword' UNLOCK

Then you can uncheck 'Enforce password policy' and 'Enforce password expiration' or just run the following two sql commands:

ALTER LOGIN UserLogin WITH CHECK_EXPIRATION = OFF

ALTER LOGIN UserLogin WITH CHECK_POLICY = OFF


http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23063893.html

Word 2007 document with comments is crashing

My coworker had a document that had around 30 comments and when she'd click on the comment to delete it, Word 2007 would crash.  She mentioned that the comment targets somehow were erased and the comments were orphaned but still in the document.

Word has a recovery mode that did not work.  I tried copy/pasting to a new document which did not work.  Even tried using a VBA macro to delete a comment, then altered it to select and delete these other comments.  That didn't work either.

I know Word 2007 uses the new .docx format, which is really just a zip file container.  So I unzipped the .docx file, dug around in the supporting XML files, and found two files.  There is a document.xml and a comments.xml and inside the comments.xml file is an ID in each node that the document.xml file references.  Pretty simple layout.

In the document.xml file, I searched for "w:comment" and found hits on nodes named
<w:commentRangeStart w:id="3" />
<w:commentRangeEnd w:id="3"/>
<w:commentReference w:id="3"/>

Those nodes match the comments.xml document that has
<w:comment w:id="3">

Furthermore, while searching through the document.xml file, the IDs for problematic comments just happened to have

<w:commentReference w:id="3"/>
but not

<w:commentRangeStart w:id="3" />
<w:commentRangeEnd w:id="3"/>

So... Remove the broken references in document.xml and now there are fully orphaned items in comments.xml so remove those nodes as well.  Save all docs, then rezip the folder and rename to .docx

The doc opens fine, and Word 2007 no longer crashes :)  Woohoo.

Ensure deploys are not in debug mode

http://weblogs.asp.net/scottgu/archive/2006/04/11/Don_1920_t-run-production-ASP.NET-Applications-with-debug_3D001D20_true_1D20_-enabled.aspx

The <deployment retail=”true”/> Switch in Machine.config

If you are a server administrator and want to ensure that no one accidentally deploys an ASP.NET application in production with the <compilation debug=”true”/> switch enabled within the application’s web.config file, one trick you can use with ASP.NET V2.0 is to take advantage of the <deployment> section within your machine.config file.

Specifically, by setting this within your machine.config file:

<configuration>

    <system.web>

          <deployment retail=”true”/>

    </system.web>

</configuration>

You will disable the <compilation debug=”true”/> switch, disable the ability to output trace output in a page, and turn off the ability to show detailed error messages remotely.  Note that these last two items are security best practices you really want to follow (otherwise hackers can learn a lot more about the internals of your application than you should show them).

Setting this switch to true is probably a best practice that any company with formal production servers should follow to ensure that an application always runs with the best possible performance and no security information leakages.  There isn’t a ton of documentation on this switch – but you can learn a little more about it here.

WebResource.axd returns 404 and then starts working after a while on it's own

Original question that I posted:

http://stackoverflow.com/questions/6902650/webresource-axd-wont-update

http://forums.asp.net/t/1711550.aspx/1?WebResource+axd+returns+404+and+then+starts+working+after+a+while+on+it+s+own

We have a library of common controls, and I'm attempting to steer towards using embedded resources in it for images, JS, and CSS. It seems that upon each deploy to TEST/PROD, there is a window of time where WebResource.axd is returning a 404. After that window of time expires, suddenly the page starts serving out content properly. On my local DEV machine it seems that there are times when I can compile and reload a page that is using embedded resources, and it immediately shows a difference. Other times it responds like there is a cache waiting to expire.
Things I've tried:
- browsing from different machines to rule out local caching
- iisreset
- net stop w3svc
- delete temp files from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files and C:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files
- killing the inetinfo.exe process
- killing the w3wp.exe process
- disabling IIS Output Caching
- copying the URL and adding in an extra false querystring parameter to mimic a new page request
- pressing Ctrl-F5 :)
It seems rebooting fixes the issue.
My google kung-fu has diluted results filled with everyone's answer to check the namespace. I am confident the namespace is correct. After a while the resource will start serving out properly and the correct version. And if it DOES serve at some point, it seems logical to me that the namespace must be correct.
Any ideas? What other information can I provide?
Thanks,
James
Michigan, USA

OK, WOW... FINALLY stumbled a solution.  Ready? This is going to be a long one :)

While trying to get more information, I load Fiddler and I'm looking at the headers... when I come across
this standard .NET error (edited):

<html>
    <head>
        <title>The resource cannot be found.</title>
[snip]
    </head>
    <body bgcolor="white">
            <span><H1>Server Error in '/' Application.<hr width=100% size=1 color=silver></H1>
            <h2> <i>The resource cannot be found.</i> </h2></span>
[snip]
            <b>Version Information:</b>&nbsp;Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1
            </font>
    </body>
</html>
<!--
[HttpException]: This is an invalid webresource request.
   at System.Web.Handlers.AssemblyResourceLoader.System.Web.IHttpHandler.ProcessRequest(HttpContext context)
   at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
--><!--
This error page might contain sensitive information because ASP.NET is configured to show verbose error messages
using &lt;customErrors mode="Off"/&gt;. Consider using &lt;customErrors mode="On"/&gt; or &lt;customErrors
mode="RemoteOnly"/&gt; in production environments.-->

WHOA... there's a commented stack trace that is buried in the page?!?!  After a quick Google search and I come across a link to http://stackoverflow.com/questions/1096793/system-web-httpexception-this-is-an-invalid-script-resource-request and on that page I stumble across this comment by Rob Levine (http://stackoverflow.com/users/134754/rob-levine):

Believe it or not, if your dlls have embedded resources in them, and they are dated in the future this will happen (if you ever compile dlls in the UK and then immediately deploy them to US web servers you'll be well aware of this issue!). I can't remember the event log error this shows, but would know if I saw it - and it is not immediately obvious.

That is exactly the situation... I compile in Eastern Time, then deploy to Rackspace in Central Time.  After about an hour the resources begin to load again... it makes sense... the time stamps catch up!