Getting Stats from SQL Server via Query

For a long time now, I use references to the sysobjects table to get some stats on databases that are being catalogued for a database migration (or for just cataloguing in general). Anyways, I open up Query Analyzer (SQL 2000) or a new Query window (SQL 2005/2008) and use variations of the following code examples:

-- Counts all the tables in a database
SELECT [Count] = count(so.name)
FROM sysobjects so
WHERE so.xtype = 'U'

-- Counts all stored procedures in a database
SELECT [Count] = count(so.name)
FROM sysobjects so
WHERE so.xtype = 'P' 

-- Returns the count of main database objects
SELECT [Tables] = count(so.name)FROM sysobjects so WHERE so.xtype = 'U' AND left(so.type,2) <> 'dt'
SELECT [StoredProcs] = count(so.name)FROM sysobjects so WHERE so.xtype = 'P' AND left(so.type,2) <> 'dt'
SELECT [Views] = count(so.name)FROM sysobjects so WHERE so.xtype = 'V' AND left(so.type,2) <> 'dt'
SELECT [Triggers] = count(so.name)FROM sysobjects so WHERE so.xtype = 'TR' AND left(so.type,2) <> 'dt'
SELECT [ExtendedStoredProcs] = count(so.name)FROM sysobjects so WHERE so.xtype = 'X' AND left(so.type,2) <> 'dt'

This is the list of all possible values for this column (xtype):

• C = CHECK constraint
• D = Default or DEFAULT constraint
• F = FOREIGN KEY constraint
• L = Log
• P = Stored procedure
• PK = PRIMARY KEY constraint (type is K)
• RF = Replication filter stored procedure
• S = System table
• TR = Trigger
• U = User table
• UQ = UNIQUE constraint (type is K)
• V = View
• X = Extended stored procedure
  

MOSS 2007 - Anatomy of the Request Access Page

I have come across a scenario where I was thinking on adding to the "Access Denied" sets of pages, specifically the "Request Access" page. I had abandoned the idea for the project, but I thought that I would post the "If I did it scenario" (sad O.J. Simpson book reference attempt). :)

First off, if you have never seen this page in SharePoint, you will someday (below).



Anyways, I wanted to modify the page that shows up after this page if you click "Request access". The page that pulls up will have a textarea that you can fill in and then submit. This request goes to the site owner of the SharePoint site. Below, I had found the page that you can modify to change the form around to whatever you want. The only drawback here is that if you change it, it will affect all the Sharepoint sites in the server farm because they all reference this page.

Page Location: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\reqacc.aspx

I decided to add two extra form fields to it, FullName and Department.



This page also uses a resource file which is independent per web application. You can simply open up the “resx” resource file and edit the tag you want. You do not need to restart IIS after making a change to the resource file – just refresh the page.

Resource Location: C:\Inetpub\wwwroot\wss\VirtualDirectories\\App_GlobalResources

File Name: wss.en-US.resx (differs by language used)

Tags to Change (if you desire):

• Error: Access Denied
• Current User
• You are currently signed in as:
• Sign in as a different user
• Request access

I know that you’re probably like “James, that’s great, but how the heck do I control the email generation from the page?” Well, that’s another issue, but can be fixed. At the top of the reqacc.aspx page, there is the following:
<%@ Assembly Name="Microsoft.SharePoint.ApplicationPages, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"%><%@ Page Language="C#" Inherits="Microsoft.SharePoint.ApplicationPages.RequestAccess" MasterPageFile="~/_layouts/simple.master" %><%@ Import Namespace="Microsoft.SharePoint.ApplicationPages" %>
So, if you want to see the default code that is used to make the “magic” happen for email generation, fire open Reflector (download Reflector) on the SharePoint server and open up the Microsoft.SharePoint.ApplicationPages.dll.

Location: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\CONFIG\BIN\Microsoft.SharePoint.ApplicationPages.dll

Expand all the classes until you find RequestAccess. Then, Right-Click on RequestAccess and click Disassemble. Presto! There is the email generation code and how it’s getting the information to build the URL.

So, you now have access to the front end and the back end of this small part of SharePoint. You just have to create your own customized code behind (and perhaps a copy of the form for the front end so you don’t modify the OOTB page).

Enjoy!

Junction - Shortcuts for CMD and PowerShell File Paths

Some of you may know of the Junction.exe program for Windows, but some of you may not. This is a little function that has been around for a little while now, and it's really useful if you are tired of trying to remember the full path you need to type in when trying to get to stsadm.exe (or similar) and don't have a shortcut.

Well, now you can "alias" the long path via junction.exe.

Example:

Turn this: C:\windows\system32\drivers\etc
Into this: C:\hosts

Code: junction.exe hosts C:\windows\system32\drivers\etc

Another Example:

Turn this: C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12
Into this: C:\12hive

Code: junction.exe 12hive "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12"

TechNet Article and Download Link for Junction

Enjoy!

PowerShell Download for XP / 2003 / Vista (RTM) plus Extensions

If you are not using Vista (non-RTM) or Windows Server 2008, and you require PowerShell to run scripts, etc., then you will have to download PowerShell. You can do a Google search as usual and find all the parts you need, or just bookmark this post. :)

Microsoft PowerShell (ver. 1):
http://www.microsoft.com/windowsserver2003/technologies/management/powershell/download.mspx

Microsoft PowerShell (ver. 2 CTP):
http://www.microsoft.com/downloads/details.aspx?FamilyId=7C8051C2-9BFC-4C81-859D-0864979FA403&displaylang=en

PowerShell Extensions (CodePlex):
http://www.codeplex.com/PowerShellCX

Microsoft PowerShell SDK:
http://msdn.microsoft.com/en-us/library/aa830112.aspx

Microsoft PowerShell Documentation:
http://www.microsoft.com/downloads/details.aspx?FamilyId=B4720B00-9A66-430F-BD56-EC48BFCA154F&displaylang=en

Microsoft PowerShell Team Blog:
http://blogs.msdn.com/powershell/

Microsoft Scripting with Windows PowerShell Site:
http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx

MOSS 2007 - Simple Workflow Walkthrough

I decided to put together a simple Workflow in MOSS 2007 to help other get a quick introduction to them. Workflows are not some mythical creature in SharePoint, although sometimes they can seem a bit tricky. :) Below I have screenshots for the step by step of creating a simple workflow on a document library.

Here is what we want this workflow for:

• Notify for new document
  
• Collect feedback from a "Reviewer" on the new document

1) Go to your Document Library, and go to Settings > Document Library Settings

2) In the Settings page, choose Workflow Settings.



3) Click "Create a new Workflow"

4) For this walkthrough, I'm collecting feedback for all new documents.



5) For testing purposes, you can add yourself as the sole reviewer on the 2nd screen when setting up a workflow. This way, you will receive the email notifications.



6) After saving the new workflow, return to the document library, and create a new document.



7) When you first save your new document (i.e. in "Word"), you will be prompted about checking in the document. Don't worry, this is normal SharePoint behavior.



8) After you save the document and go to close it, you will be greeted with the following prompts notifiying you of "Offline Editing" and checking in your document again. Go ahead and click through these screens.





9) Once a new document is saved and checked in, you should receive an email similar to the image below (because you are a "Reviewer" in the workflow):



10) This is a screenshot of the workflow in progress. From here, you will want to "Edit" the task assigned to you. In this case, I'll add some feedback for the new document created.



11) The screenshot below shows the workflow as completed and all the tasks done.



Congratulations on making your first workflow. Hopefully this has helped get your gears turning!
Cheers.