Tuesday, December 19, 2006

ASP.Net unhandled exception logging

Here is a snippet of code that can be added to your Global.asax file that will log all errors that are not handled in your ASP.Net application. The results in this example are logged to a separate log called “FrontLine Web App” (other options would be Application or System or whatever your custom name is) and the source for the entries in that log are logged as “FrontLine Web App” as well.

void Application_Error(object sender, EventArgs e)

{

// Code that runs when an unhandled error occurs

Exception objErr = Server.GetLastError().GetBaseException();

string logName = "FrontLine Web App";

if (!EventLog.SourceExists(logName))

{

// Note: we are writing to a special FrontLine Web App log

//(specified in second param)

EventLog.CreateEventSource(logName, logName);

}

EventLog log = new EventLog();

log.Source = logName;

// write the error to the Event Log

string err = "Error Caught in Application_Error event\n" +

"Error in: " + Request.Url.ToString() +

"\nError Message:" + objErr.Message.ToString() +

"\nStack Trace:" + objErr.StackTrace.ToString();

log.WriteEntry(err, EventLogEntryType.Error);

}

Also, you will probably want to include the following line in your web config so that end users won’t see the actual unhandled exception, it will just show a custom error page.

<customErrors mode="On" defaultRedirect="FriendlyErrorPage.aspx" />

Friday, December 15, 2006

Best Comparison Tool Around

I have been using Beyond Compare 2 for a two or three of years now. It is the best $30 you will ever spend for computer software if you work with files on a daily basis. I can’t say enough good things about it. It allows you two compare and merge files and directories on local, UNC paths, and even ftp. You can specify exactly how you want to treat orphans, differences in files, exclude files, etc. You can even define rules as to how the content of files should be treated. For example, is white space important. The easiest way to compare two files is to right click the two files you want to compare, and it will open up Beyond Compare 2. You can even script everything with its very easy syntax. I use it for backing up my files to a backup share every night. It supports copying and synchronizing. You can do so from left to right, right to left, both directions. It is truly a powerful and easy to use interface for synchronizing changes at the file and directory level. If you work for a company that needs bulk licensing, unlimited, or global licensing they have extremely nice pricing for bulk customers.

Thursday, December 14, 2006

Inserting value into identity column

When moving data from one database instance to another in SQL Server 2000 or SQL Server 2005 you can create insert statements to do this. The problem comes when you try to keep relationships between tables or even just keep identity columns on one table the same as the source.

Let’s assume you have only one table to move and it is defined as follows:

CREATE TABLE [CustomerCategory](

[CustomerCategoryID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Code] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[RegionID] int NOT NULL

) ON [PRIMARY]

Assume the tabe can be scripted as follows and has two rows:

INSERT INTO CustomerCategory (CustomerCategoryID, [Name], Code, RegionID)

VALUES (45, 'Computer', 'COMPUTER', 1)

INSERT INTO CustomerCategory (CustomerCategoryID, [Name], Code, RegionID)

VALUES (46, 'Printer', 'PRINTER', 1)

Notice that the CustomerCategoryID is an identity column and therefore SQL Server won’t let you do the above.

To get around this you need to use the SQL Server specific option IDENTITY_INSERT

Here is the final solution for inserting the data into the new table and keeping the identity column values in tact.

set IDENTITY_INSERT CustomerCategory ON

INSERT INTO CustomerCategory (CustomerCategoryID, [Name], Code, RegionID)

VALUES (1, 'Computer', 'COMPUTER', 1)

INSERT INTO CustomerCategory (CustomerCategoryID, [Name], Code, RegionID)

VALUES (2, 'Printer', 'PRINTER', 1)

set IDENTITY_INSERT CustomerCategory OFF

This type of thing comes in handy when moving database tables from dev to QA or QA to Production.

Tuesday, December 12, 2006

Web.xml to configure your Java Web Service

This blog describes how to configure your Java Web Service. Specifically, how to initialize your Java Web Service. In this article I assume you have used Sun Java Studio Enterprise 8 to create your RPC based web service.

Add the following lines to the top of you web service class. Typically this class ends in Impl. Your web service class needs to implement ServiceLifecycle. The example below shows how to read the init param called MyParam that is in web.xml.


import javax.xml.rpc.server.*;
import javax.xml.rpc.*;

public class MyDemoWSImpl implements MyDemoWSSEI, ServiceLifecycle {

     // store the servletContext here since we can only get it when the applet starts up.
     private ServletContext servletContext;


     // Required by ServiceLifecycle interface
     // This is only called once when the web service is started.
     // This is NOT called everytime a web method is called
     public void init(Object context) throws ServiceException
     {
          // get the servlet context
          // This is where the General context parameters (aka init parameters) of the web.xml are stored.
          ServletEndpointContext soapContext = (ServletEndpointContext) context;
          servletContext = soapContext.getServletContext();
     }


     // Required by ServiceLifecycle interface
     public void destroy()
     {
    
     }

     public String MyParam() throws Exception
     {
          return GetInitParam("MyParam");
     }


}

 

To add and specify the value for MyParam in web.xml you need to find you web.wml file. It is typically in the Web Pages\Web-INF\web.xml path if you are using BluePrints type project in Sun Java Studio Enterprise 8. You can type the entry in web.xml as shown below or you can just double click the web.xml file and use the Sun Java Studio Enterprise 8 editor. The editor has tabs. Click on the General tab if it is not already selected. Expand the Context Parameters section, then click the Add button. Type in MyParam as the Param Name, some value as Param Value, and Description is a description of what the param is or what it is for (whatever you want to put there, it is just for documentation purposes).

Here is web.xml


<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
  <context-param>
    <description>MyParam description for documentation</description>
    <param-name>MyParam</param-name>
    <param-value>the value of MyParam</param-value>
  </context-param>
  <servlet>
    <servlet-name>WSServlet_MyDemoWS</servlet-name>
    <servlet-class>philips.MyDemoWSImpl</servlet-class>
    <load-on-startup>1</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>WSServlet_MyDemoWS</servlet-name>
    <url-pattern>/MyDemoWS</url-pattern>
  </servlet-mapping>
  <session-config>
    <session-timeout>
            30
        </session-timeout>
  </session-config>
  <welcome-file-list>
    <welcome-file>
            index.jsp
        </welcome-file>
  </welcome-file-list>
</web-app>

Tuesday, December 5, 2006

Restoring SharePoint Portal Server 2003

OK, so you have a backup plan for SharePoint that uses the spsbackup which is the Backup and Restore application in the Start Menu that comes with SharePoint Portal Server 2003 (SPS). The question now what needs to be done if the server goes down. Well, that fully depends on what size farm you have configured SharePoint to use. For simplicity we will assume you have just one server and everything is running on. Let's also assume that we have configured SharePoint as a Server Farm installation. This allows us to use SQL Server 2000 instead of MSDE. Here is a summary of the environment I am assuming in this example:
  • SQL Server 2000 SP4
  • IIS 6 with ASP.Net 1.1 installed only (no ASP.Net 2.0 as it doesn't work with SharePoint prior to SharePoint Portal Server 2003 Service Pack 2). You should be able to install ASP.Net 2.0, but your Search for SPS won't work until you patch SPS to SP2.
  • SharePoint Portal Server 2003 (SPS)
  • Windows SharePoint Services (WSS)
  • SharePoint Portal Server 2003 Service Pack 2 (SPS-SP2)
  • Windows SharePoint Services Service Pack 2 (WSS-SP2).
The short answer is you need a machine that has the same programs, service packs, versions, etc. Then restore everything via spsbackup. Here is a little more detail:
  1. Install SQL Server 2000
  2. Install SQL Server Service Pack 3 or above. Ideally, this should match whatever you are using on the production server for SharePoint, though it is not required in most cases.
  3. Install IIS and ASP.Net. I recommend using the Mnage Server app to install them. It is quick and painless. Just select the IIS and ASP.Net role.
  4. Reapply Windows Server 2003 Service Pack so that IIS will be patched also.
  5. Install SharePoint Portal Server 2003.
  6. Install Windows SharePointPortal Server.
  7. Install Service Pack 2 for SharePoint Portal Server 2003. NOTE: Service Packs may overwrite customizations.
  8. Install Service Pack 2 for Windows SharePoint Services.
  9. Install any templates, sites definitions, configuration file changes, customizations, etc.
  10. At this point the backup server and the production server should have identical environments. At this point, you can create a new portal if you haven't already and verify that SharePoint Portal Server 2003 and at least one WSS site works ok.
  11. Run spsbackup and restore SharePoint.
  12. Testing restore. You will probably note that SPS search doesn't work as expected, and that Sites directory in SPS still point to old server. This is not an issue in a real emergency restore situation where the url will not change, but if you use these steps to restore to a test server for example you need to make some changes. The simplist solution appears to be to do a search and replace in the databases for SharePoint ( 4 of them, but not all have references to url). I recommend this stored procedure that can be added to each of the databases and used to search and replace url. There is also a search only (no replace) that you can use to just see what changes will be made if you do the search and replace. One bit of warning, I read that some data is not stored in plain text and is blob, etc columns which this procedure does not operate on. I have not seen this, I just read about someone having an issue. Please let me know if you notice an issue.
  13. Upgrade SQL Server 2000 to SQL Server 2005. This step makes upgrading to SQL Server 2005 easiest since there is no backup and restore. This step is optional and should only be done if you want to use SQL Server 2005 as your database AND do not want to have a separate instance of SQL Server 2000 still running. If you want to have both SQL 2000 and SQL 2005 on the same machine, you will need to backup and restore to SQL 2005. There have been some other people reporting that spsbackup doesn't work with SQL Server 2005. This is true I think only if you do NOT have SPS-SP2. Without SPS, SQL Server 2005 is not supported.
  14. Do a backup using spsbackup. You existing (pre-SPS-SP2) backups will no longer be able to be restored using SPS-SP2 spsbackup. So, you will probably want to get a new backup.

Wednesday, November 22, 2006

Clean install or migrating SharePoint Portal Server 2003 to SQL Server 2005

Currently, we have a SharePoint Portal Server 2003 (SPS) with no service packs installed and Windows SharePoint Services 2.0 with SP2 (WSS-SP2) connecting to SQL Server 2000 with I believe the latest Service Pack. All this running on one dual processor server running Windows Server 2003 SP1.

I am moving it to a Quad Processor with dual cores, so that is 8 virtual processors. It will still be running Windows Server 2003 SP1. Windows SharePoint Services with SP2 (WSS-SP2) is already up to date.

We will be doing the following upgrades:

  • SQL 2000 -> SQL 2005
  • SPS -> SPS Service Pack 2 (SPS-SP2)

There are serveral reasons for this migration.

  • We need a faster server
  • We need to be able to have .Net 2.0 Framework installed on the server and this requires SPS-SP2.
  • We need a server to test the upgrade on without affecting production.
  • Probable speed increases due to SQL 2005 over SQL 2000.
  • Increased stability and bug fixes that come with the SPS-SP2

OK, now that we know where we are coming from and where we are going to, let's get started. The overview of what we need to do is to get new server running like old server with a new installation of SharePoint but with new SQL 2005, then restore SharePoint. This is easier said than done because SPS and WSS don't run properly if .Net 2.0 framework is installed on server and SQL 2005 requires .Net 2.0 framework. SPS-SP2 and WSS-SP2 will run with .Net 2.0 framework installed. Following the below instructions we should be able to do this just fine.

  1. Format the drive where SQL Server Data files will be kept. For optimal performance it is suggested that you use 64K blocks. Be sure to use NTFS as well.
  2. Install SQL 2005. While installing choose Mixed authentication since we have SQL users for each of our apps. Also, be sure to click Advanced on the step that says where SQL Server will be installed. Here you can specify that just the datafiles for SQL Server be stored on a particular drive. Specify the drive you formatted in step 1. If you have more than one physical disk it would be good to put tempdb on a different disk from the other database files. Also put index files on different physical drive. If you don't have that many disks, keep the installation simple, often it is just as good as a more complex one that doesn't have all the right disks. Having the data partition formatted as 64K even if same physical disk should help, but it is not known how much.
  3. Add the domain user you want to use for SharePoint to the Power Users group in Windows Server 2003.
  4. Install SPS (original installation). Choose Install without database engine. Default paths are ok. Click the Finish button in the Windows based installation wizard. The web based page will appear called "Configuration Server Farm Account Settings." Important! Fill in this page only! Click OK. STOP. Close Window. Do not fill in the next page. STOP when you reach the point at which you create a configuration database. i.e. Do NOT create a configuration db or portal site (you can do this after installed SPS-SP2). NOTE: You can use the user you added to the Power Users group. However, you may need to either add that user to any site you want to index using SharePoint indexing or add that user to the Administrator group so that it implicitly has access to all sites. We are not using built in search. We are using Google Mini to perform our searches as we have less trouble with it. NOTE: The reason this installation works is that we have not accessed SQL 2005 yet. Again, the reason we don't want to is that SPS does not support SQL 2005. If we were to create a configuration db, we would be access SQL 2005 and thus it would break.
  5. Add the user you specified in previous step to SQL Server 2005. The one that was used for the Application Pool. Add the user to the dbcreator, securityadmin, and sysadmin Server roles in SQL Server 2005. (I don't know exactly what is needed, but that should be good. You can try reducing permissions later if you are adventurous).
  6. Install WSS-SP2. You may get an error in your browser. I ignored it and have not seen any issues. Restart IIS (using iisreset from a command prompt is easiest). Close Your browser. Do NOT Set the configuration database yet! You can do this after SPS-SP2 is installed.
  7. Install SPS-SP2.
  8. Create WebSite in IIS. Open IIS and create a new WebSite or virtual directory if you prefer. Besure to set the application pool to appropriate pool.
  9. Setup Configuration Database. Open SharePoint Central Administration. You will be prompted to specify the configuration database. Finally, you can do this. Create a configuration database. Use default name for configuration database name or specify different one if you had done so in your other installation.
  10. Setup Typology. Click "Change Components" button at the bottom of the "Cofigure Server Topology" page. Specify Web only in my scenario since we are using Google. No job server for me either. You will probably need to specify them as appropriate.
  11. Specify Email Server. On "Configure Server Topology" page click the link next to "Global e-mail server" that is located under the "Database Server Settings" header.
  12. Create a portal site. The easiest way to do this is to go to the SharePoint Portal Server Central Administration. Then click the "Create a portal site" link under the "Portal site and Virtual Server Configuration" section. You should be able to see the standard SharePoint Portal Server 2003 site using the website you setup in IIS 6.
  13. Create a Windows SharePoint Services (WSS) site by clicking on the Sites link in the menu bar of the SharePoint Portal Server 2003 portal that you created. On the left side click the "Create Site" link. Follow the steps.
  14. Verify that SQL Server 2005 has the databases. Open MS SQL Server Management Studio and connect to the SQL Server 2005 server where you installed SPS-SP2 and WSS-SP2. You should 4 new databases. The example below assumes that the default configuration database name was used, and the portal we created is called Test Portal. SharePoint by default uses the first 8 characters and then "1_" to designate the database name.
    • SPS01_Config_db -- this is the configuration database and will be recreated when we restore later.
    • TestPort1_PROF
    • TestPort1_SERV
    • TestPort1_SITE

At this point you have successfully configured SPS-SP2 and WSS-SP2 and proved that it works.

  1. Install any customizations, Site Definitions, etc at this point. Test changes.

Thursday, November 2, 2006

Check parameters of methods the Microsoft way

Microsoft makes heavy use of some common internal methods in .Net 2.0 that check parameters to methods. They are internal, so they can't be used directly. However, using Reflector, we can implement our own version of it. The class is System.Web.Util.SecUtility and is static internal. Below is the source generated by Reflector for the class. namespace System.Web.Util { using System; using System.Collections; using System.Collections.Specialized; using System.Configuration.Provider; using System.Data; using System.Data.SqlClient; using System.Diagnostics; using System.Globalization; using System.Web; using System.Web.Hosting; internal static class SecUtility { internal static void CheckArrayParameter(ref string[] param, bool checkForNull, bool checkIfEmpty, bool checkForCommas, int maxSize, string paramName) { if (param == null) { throw new ArgumentNullException(paramName); } if (param.Length < 1) { throw new ArgumentException(SR.GetString("Parameter_array_empty", new object[] { paramName }), paramName); } Hashtable hashtable1 = new Hashtable(param.Length); for (int num1 = param.Length - 1; num1 >= 0; num1--) { SecUtility.CheckParameter(ref param[num1], checkForNull, checkIfEmpty, checkForCommas, maxSize, paramName + "[ " + num1.ToString(CultureInfo.InvariantCulture) + " ]"); if (hashtable1.Contains(param[num1])) { throw new ArgumentException(SR.GetString("Parameter_duplicate_array_element", new object[] { paramName }), paramName); } hashtable1.Add(param[num1], param[num1]); } } internal static void CheckParameter(ref string param, bool checkForNull, bool checkIfEmpty, bool checkForCommas, int maxSize, string paramName) { if (param == null) { if (checkForNull) { throw new ArgumentNullException(paramName); } } else { param = param.Trim(); if (checkIfEmpty && (param.Length < 1)) { throw new ArgumentException(SR.GetString("Parameter_can_not_be_empty", new object[] { paramName }), paramName); } if ((maxSize > 0) && (param.Length > maxSize)) { throw new ArgumentException(SR.GetString("Parameter_too_long", new object[] { paramName, maxSize.ToString(CultureInfo.InvariantCulture) }), paramName); } if (checkForCommas && param.Contains(",")) { throw new ArgumentException(SR.GetString("Parameter_can_not_contain_comma", new object[] { paramName }), paramName); } } } internal static void CheckPasswordParameter(ref string param, int maxSize, string paramName) { if (param == null) { throw new ArgumentNullException(paramName); } if (param.Length < 1) { throw new ArgumentException(SR.GetString("Parameter_can_not_be_empty", new object[] { paramName }), paramName); } if ((maxSize > 0) && (param.Length > maxSize)) { throw new ArgumentException(SR.GetString("Parameter_too_long", new object[] { paramName, maxSize.ToString(CultureInfo.InvariantCulture) }), paramName); } } internal static void CheckSchemaVersion(ProviderBase provider, SqlConnection connection, string[] features, string version, ref int schemaVersionCheck) { if (connection == null) { throw new ArgumentNullException("connection"); } if (features == null) { throw new ArgumentNullException("features"); } if (version == null) { throw new ArgumentNullException("version"); } if (schemaVersionCheck == -1) { throw new ProviderException(SR.GetString("Provider_Schema_Version_Not_Match", new object[] { provider.ToString(), version })); } if (schemaVersionCheck == 0) { lock (provider) { if (schemaVersionCheck == -1) { throw new ProviderException(SR.GetString("Provider_Schema_Version_Not_Match", new object[] { provider.ToString(), version })); } if (schemaVersionCheck == 0) { SqlCommand command1 = null; SqlParameter parameter1 = null; foreach (string text1 in features) { command1 = new SqlCommand("dbo.aspnet_CheckSchemaVersion", connection); command1.CommandType = CommandType.StoredProcedure; parameter1 = new SqlParameter("@Feature", text1); command1.Parameters.Add(parameter1); parameter1 = new SqlParameter("@CompatibleSchemaVersion", version); command1.Parameters.Add(parameter1); parameter1 = new SqlParameter("@ReturnValue", SqlDbType.Int); parameter1.Direction = ParameterDirection.ReturnValue; command1.Parameters.Add(parameter1); command1.ExecuteNonQuery(); if (((parameter1.Value != null) ? ((int) parameter1.Value) : -1) != 0) { schemaVersionCheck = -1; throw new ProviderException(SR.GetString("Provider_Schema_Version_Not_Match", new object[] { provider.ToString(), version })); } } schemaVersionCheck = 1; } } } } internal static bool GetBooleanValue(NameValueCollection config, string valueName, bool defaultValue) { bool flag1; string text1 = config[valueName]; if (text1 == null) { return defaultValue; } if (!bool.TryParse(text1, out flag1)) { throw new ProviderException(SR.GetString("Value_must_be_boolean", new object[] { valueName })); } return flag1; } internal static string GetDefaultAppName() { try { string text1 = HostingEnvironment.ApplicationVirtualPath; if (string.IsNullOrEmpty(text1)) { text1 = Process.GetCurrentProcess().MainModule.ModuleName; int num1 = text1.IndexOf('.'); if (num1 != -1) { text1 = text1.Remove(num1); } } if (string.IsNullOrEmpty(text1)) { return "/"; } return text1; } catch { return "/"; } } internal static int GetIntValue(NameValueCollection config, string valueName, int defaultValue, bool zeroAllowed, int maxValueAllowed) { int num1; string text1 = config[valueName]; if (text1 == null) { return defaultValue; } if (!int.TryParse(text1, out num1)) { if (zeroAllowed) { throw new ProviderException(SR.GetString("Value_must_be_non_negative_integer", new object[] { valueName })); } throw new ProviderException(SR.GetString("Value_must_be_positive_integer", new object[] { valueName })); } if (zeroAllowed && (num1 < 0)) { throw new ProviderException(SR.GetString("Value_must_be_non_negative_integer", new object[] { valueName })); } if (!zeroAllowed && (num1 <= 0)) { throw new ProviderException(SR.GetString("Value_must_be_positive_integer", new object[] { valueName })); } if ((maxValueAllowed > 0) && (num1 > maxValueAllowed)) { throw new ProviderException(SR.GetString("Value_too_big", new object[] { valueName, maxValueAllowed.ToString(CultureInfo.InvariantCulture) })); } return num1; } internal static bool ValidateParameter(ref string param, bool checkForNull, bool checkIfEmpty, bool checkForCommas, int maxSize) { if (param == null) { return !checkForNull; } param = param.Trim(); if (((!checkIfEmpty || (param.Length >= 1)) && ((maxSize <= 0) || (param.Length <= maxSize))) && (!checkForCommas || !param.Contains(","))) { return true; } return false; } internal static bool ValidatePasswordParameter(ref string param, int maxSize) { if (param == null) { return false; } if (param.Length < 1) { return false; } if ((maxSize > 0) && (param.Length > maxSize)) { return false; } return true; } } }

Thursday, October 12, 2006

Search MS SQL Server for any text

When you know little about a database or can't remember it is often nice to be able to do global type searches on that database. This scenario can be thought of in two ways.
  • Search database schema
  • Search database data

Below I describe how to do both on a MS SQL database.

Search database schema It has been my experience that when doing any kind of database analysis it is nice to know dependencies between objects. Yes, I know you can get dependencies of an object. But what if what you are looking for is cross-database or you want to search on part of name of an object. Below is a very simple, but effective stored procedure (thank you Michael Lyczywek): create proc SearchSchema(@QueryText as varchar(200)) as select distinct name from sysobjects a inner join syscomments b on a.id = b.id where text like '%' + @QueryText + '%' go For example let's say you have a person table in Database1 and you want to find all references in Database2 to the Person table. To do so you could do the following: SearchSchema 'Person' Search database data Often when I have been tasked with making a change in an application, I need to figure out where that is in the database. The following stored procedure allows you to search all columns of all tables for a given value. This has been taken directly from an article by Narayana Vyas Kondreddi.

CREATE PROC SearchAllTables
(
 @SearchStr nvarchar(100)
)
AS
BEGIN

 -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
 -- Purpose: To search all columns of all tables for a given search string
 -- Written by: Narayana Vyas Kondreddi
 -- Site: http://vyaskn.tripod.com
 -- Tested on: SQL Server 7.0 and SQL Server 2000
 -- Date modified: 28th July 2002 22:50 GMT


 CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

 SET NOCOUNT ON

 DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
 SET  @TableName = ''
 SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

 WHILE @TableName IS NOT NULL
 BEGIN
  SET @ColumnName = ''
  SET @TableName =
  (
   SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
   FROM  INFORMATION_SCHEMA.TABLES
   WHERE   TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(
      OBJECT_ID(
       QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
        ), 'IsMSShipped'
             ) = 0
  )

  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  BEGIN
   SET @ColumnName =
   (
    SELECT MIN(QUOTENAME(COLUMN_NAME))
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA = PARSENAME(@TableName, 2)
     AND TABLE_NAME = PARSENAME(@TableName, 1)
     AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
     AND QUOTENAME(COLUMN_NAME) > @ColumnName
   )

   IF @ColumnName IS NOT NULL
   BEGIN
    INSERT INTO #Results
    EXEC
    (
     'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
     FROM ' + @TableName + ' (NOLOCK) ' +
     ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    )
   END
  END
 END

 SELECT ColumnName, ColumnValue FROM #Results
END

To use this stored procedure you would do something like the following: SearchAllTables 'Brent Vermilion'

Friday, September 8, 2006

Making a SharePoint Document Library Google friendly

SharePoint comes in two flavors: Windows SharePoint Services and SharePoint Portal Server 2003. Both of them have Document Libraries, and both are unfriendly to Google indexing. In particular if youh are on a corporate intranet and have a Google Search Appliance (GSA) such as Google Mini you will quickly realize that your GSA will not find any of the documents on your SharePoint site. The reason for this is because the Document Library by default uses javascript or ActiveX control to navigate the directory structure of the Document Library. GSA will not follow links embedded in javascript. In other words if you have a page that has something like the following: <a href="javascript:myGotoFunction(">This File</a> One limited fix is to create or edit existing view for every document library you will every create and add the column called "Name (for use in forms)". This will put a column on the page that shows the url to the document as a link without javascript. Google can then index the documents. The problem with this approach is these settings are configurable at runtime by users (typically power users, but still allows end users to break indexing of a site). The more systematic approach is to create a site that lists all the files from all document libraries from all sites including the portal. This will allow documents to be indexed easily. I recommend you configure your GSA to exclude the list page we are creating unless you want people to be able to casually see how many docs exist and what they are. Security is still on the documents as it is defined at the site, not on the page. The title is will be shown here so don't put anything of private nature in title. This article assumes you want to do the systematic approach. There are at least a couple of ways to tackle this problem. The approach we will take because it requires the least amount of SharePoint specific knowledge and doesn't require access to SharePoint installation directly.

Overview of implementation

  • Create ASP.NET web site
  • Connect to Content Database for SharePoint
  • Execute query
  • Display data on page

Requirements

Microsoft Visual Studio 2005 (You could conceptually use Visual Studio 2003, or notepad also)

Implementation Open up Microsoft Visual Studio 2005 and create a new web. Create a connection to your content database. If you don't know which one that is, just look through your SharePoint database until you find one that has a "Docs" table.

The following query can be used to get the data. Add any file extensions that you want to index to the query. There are files that are in the document library that you don't even see, so I recommend explicitly specifying the file extensions you want to include in the indexing.

The table we are interested in is the Docs table. It has meta data and binary file content for all files in all Document Libraries in both SPS and WSS.

SELECT Docs.DirName + '/' + Docs.LeafName AS URL FROM Docs WHERE ((Docs.Type = 0) and Docs.LeafName not like 'template.%') AND ((Docs.LeafName LIKE '%.doc%') OR (Docs.LeafName LIKE '%.ppt%') OR (Docs.LeafName LIKE '%.xls%') OR (Docs.LeafName LIKE '%.pdf%') OR (Docs.LeafName LIKE '%.vsd%') OR (Docs.LeafName LIKE '%.txt%') )

You will probably want to specify this in code so that the following can be prepended to the url

<a href="http://hostnameHere/">http://hostnameHere/</A> Bind results to a GridView or some other control that has paging built in.

At this point you should be able to run you web application and click on links in the GridView to save or open the documents that are in SharePoint. Since there is no javascript involved, GSA should now be able to index the documents on the first page of your GridView. That is right, only your first page. Why you ask? Because if you look at the paging that is output by the GridView it uses javascript to postback and GSA can't follow javascript.

This poses another issue. How do we page our results without javascript. We can do a number of things to solve the problem.

  1. Don't use the pagers and provide our own links to all the pages in the GridView
  2. Write our own pager that doesn't use javascript (really just a simple next link works also)

If you choose option 1 and write your own pager and want each page to be equal in the GSA results to start with, I strongly recommend that the pager also have direct links to all the pages. The reason is that if you only have a next button for example, GSA will see that page 20 is two hops away from what you originally wanted it to index. GSA will still include it in the index, but gives it an extrememely low page rank (basically zero) for pages over about 10 hops away. Each page from 0 to 10 get a smaller page rank, so page 10 to 20 for example have a near zero page rank.

Wednesday, September 6, 2006

Making the relationship between OSVD objects

I wasted way too much time trying to figure this out, so I thought I would try to save others from doing the same. OVSD has a webapi available for Java. The api is quite nice. One thing that was not obvious to me at first, but kind of makes sense in the end is how to make relationships between objects. Let's assume you are creating a service call. The service call has a relationship to an assignment which as a relationship to a workgroup. Long templateID = 12345l; // note the lowercase L at the end to designate a LONG. IServicecall sc = ServiceCallHome().openNewServicecall(templateID); The first lesson is that you cannot create the assignment directly. You must call the getAssignment() method on the service call; it creates it for you. IAssignment ia = sc.getAssignment(); The next thing we do is make the relationship to the to-workgroup by calling the the setAssWorkgroup() on the assignment object. ia.setAssWorkgroup(GetServiceDesk1stLineWorkgroup()); The second lesson comes when trying to save changes. In OVSD webapi related objects don't use a save method. Instead they have a transfer() method that does some magic, but is basically the same as a save on a child/related object. ia.transfer(); Finally, call save() on the service call to save all changes to database. sc.save();

Wednesday, July 19, 2006

Microsoft ASP.NET 2.0 Free Training in Phoenix

The trainging was put on by interface technical training at the Microsoft office in Phoenix, AZ. The training was nice, but not that much more impressive than going to www.asp.net and looking at the intro videos. The content was very similar. What made the training great was the presenter (Dan Wahlin) and his tips. Below are the tips that meant something to me personally.

Instructor:

FREE SQL intellisense for Query Analyzer and I think VS Studio 2005 (through Sept) from www.redgate.com. The name of the product is SQL Prompt.

Good resource for XML can be found at www.xmlforasp.net. Check it out.

Good resource for async stuff: www.asyncpostback.com

Free Microsoft Visual Studio 2005 Standard Endition from www.learn2asp.net. You must sign up and take at least three training classes. They are actually pretty nice training videos that you can download and watch at your leisure. There is a limited time on this offer.

Detail View Control

  • ItemTemplate can be used in Detail View to add validators, DropDownList, etc instead of the default textbox.

ASP.Net Architecture

  • JIT deployment - You don't have to deploy all dll's. Can deploy just the source (.cs, .vb, etc) and will be compiled on server. This allows you to make changes on the fly in production, and even treat .cs as a scripted language. The JIT takes care of the compilation on the fly. Nothing like making changes to production. ;)
  • Web.config - Changes to web.config doesn't force application to restart.
  • Access configuration settings - To access configuration settings from .aspx or .ascx page you can use the syntax <%$ section:key %>
  • DataBinder - It now has a method called Eval(). You can also use Bind() for two way binding. You can also use XPath().
  • To access configuration you can use Configuration Manager or Web Configuration Manager.
  • App_offline.htm - If you puta file called App_offline.htm in the app directory the application will no longer accept new users and bleed off old users. This is good if you are putting up a new version and can have down time.
  • The newest SharePoint Portal Server 2003 Service Pack will allow ASP.Net 2.0 web parts to run under SharePoint Portal Server.

Visual Studio 2005 changes

  • Web Deployment Project - See www.asp.net to get "Web Deployment Project" that allows for VS Studio.Net 2003 like projects and build scenario. This will give you the bin directory and allow 1.1 like deployments. ASP.Net 2.0 does not use bin directory by default.
  • Snippets - You can add custom key for code snippets you right. For some reason VB.Net has lots of snippets when you install VS 2005, and C# does. So you have to download them separately.You can download Microsoft authored code snippets from msdn.microsoft.com and community authored snippets from www.gotcodesnippets.com.
  • VB.Net Refactor tool needed - VS 2005 for some reason does not include refactor functionality. They left it to a third party. You can get the quite nice (better than C# one in some cases) from www.shrinkster.com/fwh.

More Free Cool Tools

  • ViewState Decoder - a very cool tool that lets you see what is actually in you viewstate. Get it at www.pluralsight.com. In case you are wondering viewstate is not secure, or encrypted. It is just Base64 encoded.

Great ASP.Net blog

Connection String Encryption

  • use reg_iis with different options -pef and -pdf to encrypt the connection strings in your web.config. The encryption is specific to machine key. You can copy the machine key to decrypt connection string on another machine. You would need to do so if you have a web farm. If you don't only the machine that encrypted the string will be able to decrypt it.

Cross Page Postback

  • You can now post to another page. You can get the values from the page that contains the values you want to submit (called the Previous Page). if (Page.IsCrossPagePostback) { Page.PreviousPage.FindControl("controlName"); }

  • When accessing previous page, you will need to specify the page type. The syntax is something like <% PageType=xxx %>

Some Enhancements

  • Panel can override default button
  • Can set focus to first error on page. See property on Required Field validator to enable. Note: If the error is not in view on the page, the page is NOT scrolled.
  • You can now access Form element using Page.Form syntax.
  • You can now access Head element usingPage.Head syntax.

Detail View Nested Controls Syntax

  • Use $ to reference the custom nested controls. i.e. DetailView1$MyCustomField"
  • Another example is if you changed TextBox to a DropDownList in the DetailView.

Master Page

  • You can have more than one content placeholder for a master page.

SiteMapProvider

  • Override it to read site map from database. To override specify it in the web.config. Jeff Prosey has already done this.
  • Use SiteMapModule VS.Net Macro from Scott Allen to autogen sitemap file based on project files and directories instead of creating the file by hand.
  • Security Trimming - The ability to remove links that the current user does not have permissions to access based on authorization information in web.config.
  • Can have different files that can be referenced in main sitemap file. This would be useful if there is part of the site map that is the responsibility of departments, etc.

Profiles Database

  • The Profile database keeps track of profile information.
  • It is commonly the same as the Membership database.
  • The Profile database can be chnaged in the machine.config to other SQL Servers, including another SQL Server Instance (even on another machine).

Themes

  • Theme for entire site can be set in the web.config. <pages theme="Philips" />
  • The page directive StyleSheetTheme="" will override the setting in the web.config if you want some pages to have a different theme.

Membership

  • The source for MS Membership is now available from Microsoft.