Tuesday, April 22, 2008

Paste Unformatted Text in Word

If you copy and paste formatted text from different sources into MS Word you may have noticed that it usually will paste it in the original format. This is nice in some cases, but in most cases I find that I want the text to be unformatted (so that it uses the format of the paragraph that I am pasting it into in MS Word). To paste unformatted text all you have to do is go to the Edit Paste Special… menu item and when the dialog appears, select the “Unformatted Text” and click the OK button. While this may be an easy solution it is a complete waste of time in comparison to just typing Control-V. If you do this a lot, you want to be able to paste unformatted text with a single key stroke. Here is how to do this.

In MS Word, go to Tools menu Macro Record New Macro…

In the dialog that appears enter a name for your macro. It can’t contain spaces, and really isn’t important other than it should describe what you are doing with it. In this case, I named my macro, PasteUnformattedText. After entering the macro name, click the Keyboard button. This will open the Customize Keyboard dialog. In the Customize Keyboard window, type the key stroke you want to use to invoke the macro. I set mine to Control-Shift-V. Be sure to click the Assign button, then click the Close button. You will then see a little floating window / palette. Use it to stop recording. At this point you have a macro that is invoked by a key stroke of your choice, but it doesn’t do anything. Now let’s make the macro actually do something. Go to Tools menu Macro Macros… to bring up the Macros dialog. Select your macro, and click the Edit button. This will open the MS Visual Basic code editor. You will see a method that is called whatever you called your macro. If there is any text (besides the ones with an apostrophe before them – these are comments) in the method body, delete it. Your editor should look something like this… Sub PasteUnformattedText()

‘ PasteUnformattedText Macro ‘ Macro recorded 4/22/2008 by usb00528 End Sub

Add one line to this so that it looks like the following… Sub PasteUnformattedText()

‘ PasteUnformattedText Macro ‘ Macro recorded 4/22/2008 by usb00528 Selection.PasteAndFormat (wdFormatPlainText) End Sub

Save and close the window. No give it a test by copying some formatted text, and pasting it using your keystroke. Side Note You don’t have to follow these exact steps to get this done. You can create a new macro, and then later go to Tools Customize… and then select the Macros item from the Categories list. Then select your macro, and click the Keyboard… button to get to the Customize Keyboard dialog you used as described above. The bottom line is, you need to create a macro some how, put the one line of code in it, and assign a keystroke, or a toolbar item if you prefer.

Thursday, April 3, 2008

SharePoint Usage by List Type

SharePoint uses MS SQL Server and thus we can directly query this database to do some reporting. Below is a query that counts the number of entries for each type of list (Announcements, Contacts, Discussion Boards, Document Library, Events, Generic List, Issue List, Liks List, Image Library, InfoPath Form Library, Survey, Task List, Other). For example, if we look at the the sample results below there are 6932 documents in all the document libraries in all sites within SharePoint.

Select case tp_servertemplate when 104 then 'Announcement' when 105 then 'Contacts' when 108 then 'Discussion Boards' when 101 then 'Document Library' when 106 then 'Events' when 100 then 'Generic List' when 1100 then 'Issue List' when 103 then 'Links List' when 109 then 'Image Library' when 115 then 'InfoPath Form Library' when 102 then 'Survey' when 107 then 'Task List' else 'Other' end 'ListType', sum(tp_itemcount) as EntryCount from lists inner join webs ON lists.tp_webid = webs.Id Where tp_servertemplate IN (104,105,108,101, 106,100,1100,103,109,115,102,107,120) and tp_itemcount > 2 -- if there are only three then it is likely the sample data or a test record group by tp_servertemplate order by 2 desc

Example Results:

Type # of Entries Document Library 6932 Generic List 400 Events 356 Issue List 328 Task List 305 Announcement 292 Links List 281 Discussion Boards 276 Survey 193 Image Library 147 Contacts 128 Other 18 InfoPath Form Library 3

While that is an interesting indicator of how much the feature is being used, it doesn’t really a good picture of how many sites are using the features. In other words, all 6932 documents could be on one site and no other site is using document libraries. Below is a query that gives the number of sites that are using each type of list.

select count(distinct(webs.fullurl)) 'NumberOfSitesThatUseType', case tp_servertemplate when 104 then 'Announcement' when 105 then 'Contacts' When 108 then 'Discussion Boards' when 101 then 'Document Library' when 106 then 'Events' when 100 then 'Generic List' when 1100 then 'Issue List' when 103 then 'Links List' when 109 then 'Image Library' when 115 then 'InfoPath Form Library' when 102 then 'Survey' when 107 then 'Task List' else 'Other' end as Type from lists inner join webs ON lists.tp_webid = webs.Id Where tp_servertemplate IN (104,105,108,101, 106,100,1100,103,109,115,102,107,120) --and tp_itemcount > 2 and FullUrl like 'sites/%' group by tp_servertemplate order by 'NumberOfSitesThatUseType' desc

Example Results: Sites Type 135 Document Library 31 Links List 30 Announcement 27 Issue List 21 Task List 20 Discussion Boards 18 Events 13 Survey 10 Contacts 7 Image Library 7 Generic List 1 Other

List Sites To get a list of all top level sites I recommend the following query: select * from webs where FullUrl like 'sites/%' To get a list of all sites (includes sites created below sites) I recommend the following query. NOTE: This will also include sub sites like Meeting Work spaces, etc. select * from webs w join sites s on (w.siteid = s.id) where w.FullUrl like 'sites/%'

Find sites that are not using a type of list Sometimes you want to find all the sites that are NOT using a particular type of list. First thing you need to know is that each list type is stored as a number, not a human friendly string. Here are the mappings you will need to understand what the numbers mean. You will notice the above queries translate them using a case statement. Note: the tp_servertemplate field can have the following values:

  • 104 = Announcement
  • 105 = Contacts List
  • 108 = Discussion Boards
  • 101 = Document Library
  • 106 = Events
  • 100 = Generic List
  • 1100 = Issue List
  • 103 = Links List
  • 109 = Image Library
  • 115 = InfoPath Form Library
  • 102 = Survey List
  • 107 = Task List

You will need to know these number for the below query. Just change the number in the query to the type of list you want to use. select webs.fullurl as [Site Relative Url], webs.Title As [Site Title], lists.tp_title As Title, tp_description As Description, tp_itemcount As [Total Item] from lists inner join webs ON lists.tp_webid = webs.Id Where tp_servertemplate = 105 -- Contact List order by tp_itemcount desc

Wednesday, April 2, 2008

Starting and stopping Windows Service from the command line

In this article I will show how to restart (stop then start) a Windows Service from the command line. While it is true you can do all this from the user interface using the mouse, sometimes it is useful to be able to script restarting of services if a service is hung or down for some reason. An example of this is Apache Tomcat (assuming you installed it as a Windows Service and not to run from the command line to start with. I am going to use Apache Tomcat Windows Service as an example, but this article should apply to any Windows Service.

The below assumes you have already started a command prompt by going to Start Menu | Run… and typed cmd and then enter.

List Services

First the first thing you may want to know how to do is get a list of all started Windows services.

C:\>net start

These Windows services are started:

Apache Tomcat

Application Layer Gateway Service

Automatic Updates

Background Intelligent Transfer Service

COM+ Event System

Cryptographic Services

….

Stop a Service

Running the command below will stop the Apache Tomcat service. It is important to note the use of double-quotes here. You need them so that Apache Tomcat is treated as one parameter instead of two. The same goes for any other service with a space in its name. Also, notice the name use with the start command must match what is shown in the list above.

C:\>net stop “Apache Tomcat”

The Apache Tomcat service was stopped successfully.

Start a Service

Running the command below will start the Apache Tomcat service. The same basic rules apply as when you stopped the service.

C:\>net start “Apache Tomcat”

The Apache Tomcat service is starting.

The Apache Tomcat service was started successfully.

Restart a Service

Running the command below will restart (stop and immediately start) the Apache Tomcat service. The same basic rules apply as when you stopped the service.

C:\>net stop “Apache Tomcat”

The Apache Tomcat service was stopped successfully.

C:\>net start “Apache Tomcat”

The Apache Tomcat service is starting.

The Apache Tomcat service was started successfully.