All posts by yen

ASP.NET, Programming

Removing Character from String in C Sharp

I need to process a string in order to remove certain unwanted characters. One such character has an ASCII value of Hex 01. I happen to be using a StringBuilder, which offers a Replace method similar to String.Replace.

I can’t simply do a Replace(Convert.ToString(0x1), "") which just removes all instances of the string “1”. To get the correct character, I can use Convert.ToChar(0x1) or simply ‘\x01’. However, I can’t remove a character with the Replace(char, char) overload because there is no such thing as an “empty” character literal that’s analogous to an empty string. Each value of char type is mapped to a code point and is therefore exactly one character long.

To remove the character, simply take an additional step and convert it to a string: Replace('\x01'.ToString(), "").

SQL Server

Modifying the “Action If Name Is in Use Field” for a Merge Replication Article

The “Action if name is in use” field controls what happens to an article’s contents, at the subscriber, when a snapshot is reinitialized. The default setting (with merge replication) is to drop the existing object and create a new one. The problem is that all permissions on that object are also dropped. Fortunately, we can choose another option that will truncate(delete) the data in the object without the removing the permissions. There are a few caveats to this solution. It doesn’t work with articles that are stored procedures (keep the object unchanged is the only other supported option), you have to use transact-SQL to preform the change, and it’s a one way trip (meaning you can’t change back to the previous setting) without recreating the publication.

An alternative solution is to leave the default setting and create a script to reapply the permissions on the object after it has been dropped. In our case, we use this alternative for articles that are stored procedures. Continue reading Modifying the “Action If Name Is in Use Field” for a Merge Replication Article →

SharePoint

Amazingly Simple (and Fast) XML Formatting Trick

While pre-populating a SharePoint list instance with data, I find myself having to insert a large amount of unformatted data into an XML file in Visual Studio 2008. When formatted, the XML data consist of roughly 12000 lines. After I pasted the data into the file, Visual Studio took several agonizing minutes to format them into nicely human-readable form. Moreover, during this time, my computer’s CPU usage was also pegged at or near the maximum, rendering it practically unusable.

However, I found that if the XML data are already mostly formatted, then Visual Studio leaves them alone. So I tried to load the XML data into a text editor to see whether it could do the job faster. Unfortunately, my text editor isn’t capable of automatically formatting XML, and using regular expressions is too manual of a process.

Finally, I noticed that Internet Explorer loads and formats an XML file almost instantaneously. This suggests the simple trick of opening the XML file in IE, pasting the formatted XML content into a text editor to remove the extraneous hyphens, then pasting the results into Visual Studio. Doing so reduces the amount of time for the process from several minutes to just a few seconds.

SQL Server

Union Vs. Union All

The Union and Union All operators combine the results of two SQL queries. Union All merges every row, while Union throws out duplicate rows. It turns out that Union actually performs the equivalent of distinct sort behind the scene.

As a simple test, I ran 3 separate queries: (1) using Union All, (2) using Union, and (3) using Union All and distinct sort to generate the same results as query 2. For query 3, the order by clause is actually not necessary, as select distinct also automatically sorts in ascending order. Below is the SQL Server execution plan for each query.

Queries 2 and 3 are executed identically. As expected, both are much more expensive than query 1. So stick with Union All whenever possible.

SharePoint

Searching for Files with CAML

I was interested in searching for a file in a Document Library. Using a CAML query is much more efficient than iterating through the Document Library and examining each file. There are a couple of scenarios.

Searching at the root level of the Document Library

Searching for files is a little different from searching for other list items. In SharePoint, a file is shown with the same attributes as any other list item. When creating the CAML query, it would seem natural to search a field such as Title for a given file name. However, for a document, the Title field by default doesn’t store the file name. Rather, it contains arbitrary content that can be edited in Word (under the document’s Properties).

Furthermore, when creating the CAML query, you must reference a field using its internal name. So you won’t be able to use a field such as “Name”, as it is not a valid internal name (source: List of internal names for SharePoint fields).

Incorrect internal name error
If you don’t use the correct internal name of a field in a query, you may get the following error when attempting to access the results of the query: One or more field types are not installed properly. Go to the list settings page to delete these fields.

The correct field to use when searching for files is FileLeafRef, which is the appropriate internal name to use for the Name field. The C# snippet to create the query is as follows: Continue reading Searching for Files with CAML →

SharePoint

List of Internal Names for SharePoint Fields

In order to reference a column or field using the the SharePoint object model, you often need to know its internal name. For example, when creating a CAML query, you can specify the field on which to search by providing its internal name. As opposed to the display name, which can be changed in the UI, the internal name is immutable. While the default display name and the internal name are often identical or similar, they can also be very different. The difference isn’t always consistent. For example, spaces in the display name can be removed, such as IsCheckedoutToLocal, or replaced with the hexadecimal equivalent, such as HTML_x0020_File_x0020_Type. Furthermore, the display name can be the same for more than one fields, so the internal name is the only way to distinguish between them.

You can quickly determine the internal name of a field using the UI:

  1. Open the List Settings page
  2. Under the Columns section, select a column to view the Edit Column page
  3. The URL of this page includes the internal name in the query string. For example, the URL for the Created By field includes the following query string List=%7BF641CEF1%2DCDE2%2D49E1%2D9800%2D861A408EF890%7D&Field=Author. The value for the Field parameter, Author, is the internal name for Created By. Continue reading List of Internal Names for SharePoint Fields →

SSRS

Adding Web Reference to SSRS 2008 in Visual Studio

To add a reference to SSRS 2005 in Visual Studio, you’d specify the URL to the Web Service definition file, for example
http://server/ReportServer/ReportService2005.asmx or equivalently http://server/ReportServer/ReportService2005.asmx?wsdl. You can use a shorter alternate URL, http://server/ReportServer/ReportService.asmx. If you open these URLs in a Web browser, you’d be able to view the content of the ASMX file.

With SSRS 2008, I’d expect to use the same URLs, replacing 2005 with 2008 where applicable. Alas, it doesn’t work that way. If you browse to http://server/ReportServer/ReportService2008.asmx, you’d get a directory listing rather than the expected Web service definition. A quick check of the MSRS10.MSSQLSERVER\Reporting Services\ReportServer directory reveals that the only relevant Web service definition file is ReportService2005.asmx. Further, the ReportService.asmx file has been inexplicably removed. Continue reading Adding Web Reference to SSRS 2008 in Visual Studio →

SSRS

Scripting Deployment of Reporting Service Reports with Stored Credentials

When developing SSRS reports, it’s convenient to deploy data sources and reports to the Report Server using Visual Studio. However, when it comes to production, using Visual Studio is not practical or even possible.

A common solution is to use a tool such as RSBuild, which lets you specify deployment parameters in, appropriately, an XML file.

Alternatively, you can use the SSRS Web service API directly by writing a console application in your favorite .NET language. You can also create a Visual Basic script that can be run on the command line by the RS utility that comes with SSRS.

The problem

I had to deploy a report with stored credentials. The report is deployed correctly from Visual Studio. For production, I wrote a console application to deploy the report based on the Microsoft Reporting Service samples. However, when the report is viewed, it always prompts the user to enter data source credentials, as seen below. Continue reading Scripting Deployment of Reporting Service Reports with Stored Credentials →

SharePoint

Missing Filter Web Parts in SharePoint

While creating a web part page I discovered that the Query String Filter web part was missing. In fact, none of the filter web parts was available. A search for “filter” revealed that the filter web parts belong to a site-level feature called BizAppsSiteTemplates. However, for some reason, this feature wasn’t activated. Furthermore, it had the Hidden attribute turned on so I couldn’t see it in the site feature list. I tried to manually install the feature using STSADM, but it turned out the feature was alread installed. So I activated it with STSADM:

stsadm -o activatefeature -name bizappssitetemplates -url http://moss2007/mysite

After that the filter web parts finally showed up in the Web Part Gallery.

Once you locate the DWP file for a web part, you can also upload it directly to the Web Part Gallery. But activating the parent feature is more efficient and elegant.