December 27, 2007

How to reset SQL Server identity column

Generally, to reset an identity column to value n on table XYZ:

So, to reset an identity column to value 17 on table XYZ:

November 21, 2007

The page cannot be found in Infoview

In Business Objects XI, you may get an error saying The page cannot be found when trying to access InfoView or the Central Management Console. One cause for this problem is that, on Windows Server 2003, IIS 6.0 blocks .NET 2.o .aspx applications by default. To enable them, go to Administrative Tools -> Internet Information Services (IIS) Manager, click the Web Service Extensions, select ASP .NET v2.0xxxx and click Allow.

Detailed info on this operation:

[repo_proxy 13] SessionFacade::openSessionLogon with user info has failed

When trying to log on to InfoView on BusinessObjects XI, you may get this error because the CMS can't be contacted. This may be happen because of a firewall or traffic filtering on your network. The first reason is reasonably easy to solve: make sure there is no firewall blocking you in the path from the client to the BO server. If you suspect it's the latter, try putting localhost on the System textbox.

November 1, 2007

Access and consume a Web Service from Integration Services

Sometimes you need to consume a Web Service that is specific enough for you not be able to use the default Web Service Task available on SSIS 2005.

In this example I'm going to show how to consume a Web Service that returns a .NET object (a serialized DataSet, for example). For this, we'll have to use a Script Component that allows us to insert some custom code that is able to do what we need. The
Script Component will be part of a Data Flow acting as a Source that provides rows to Transformations down the pipeline.

Acessing a Web Service from a Script Component is pretty much similar to how we'd do it in a standard .NET application:
  1. You need a proxy class (in the VB language) for the Web Service you want to consume. You can create it using the wsdl.exe application like this:
    wsdl /language:VB http://yourserver/yourWebService?WSDL /out:yourProxyClass.vb
  2. Access the Script Component's code and, on the code window, go to the Project menu and select "Add Existing Item". The item you have to add is the proxy class you created previously (yourProxyClass.vb, in this example). Next, go to the Project menu, select "Add Reference" and add the System.Xml and System.Web.Services assemblies.
  3. Now you have to write the code to acess the Web Service and output its results onto the outputs you defined for the Script Component.
    In this example, the Web Service defines a class called Export which has a method called getDataSet(String) that outputs is a serialized DataSet. The Script Component has one output, called Output0, with two columns (colOne and colTwo) defined as a String (DT_STR)
Public Class ScriptMain
Inherits UserComponent

Dim webServiceExport As Export
Dim dsResults As DataSet

'Prepare the required connections and initialize objects
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

webServiceExport = New Export()
dsResults = Nothing

End Sub

'Get the data from the WebService
Public Overrides Sub PreExecute()

webServiceExport.Timeout = 3600
dsResults = webServiceExport.getDataSet("test parameter string")

End Sub

'Go through the received data and add it to the output rows
Public Overrides Sub CreateNewOutputRows()

Dim dtResults As New DataTable

dtResults = dsResults.Tables(0)

Output0Buffer.colOne = dtResults.Columns(0).ToString()
Output0Buffer.colTwo = dtResults.Columns(1).ToString()


End Sub

End Class

If you need some Web Services for testing you can search at

Detailed information links:
Referencing Assemblies in a script:
Using the Script Component as a source:

October 25, 2007

Convert Access' Memo to a VARCHAR string

The problem: the Memo data type in Access is a Unicode text stream (i.e. no fixed width) and the VARCHAR type is a non Unicode text string (fixed width). So, if you're transferring data from a Memo column on an Access file to a database column of the VARCHAR(n) type, you'll have to use a Data Conversion transformation to convert the Memo to String (DT_STR, which maps to VARCHAR) and when you do, Integration Services will throw the following error
Conversion from DT_NTEXT to DT_STR is not supported
This means that you cannot directly convert a Unicode stream to a non Unicode string.
What you can do in this situation is create a Data Conversion that converts the Memo column to DT_TEXT (non Unicode stream) and after that use another Data Conversion to convert that same column from DT_TEXT to DT_STR. Keep in mind that truncation may occur if the conversion to DT_STR doesn't reserve enough width on the column to fit the whole stream contents (i.e. if the stream has at most 500 characters you'll need to set up the Length of the column to 500 in the Data Conversion transformation)
Another option could be using a Derived Column to perform both casts in a single transformation (haven't tried this solution).

October 24, 2007

Custom logging with Integration Services

Integration Services 2005 allows you to insert custom log entries from a Script Task by sending events of a specific type to your log provider. You can use something like this from within a script:
Dts.Events.FireInformation(0, "CustomInfoHere", "This is a custom log entry thrown by a script task")
You can also use this technique to raise errors, warnings, progress information, etc...

If you need help on how to set up logging for a package see this:

October 22, 2007

Freeware encryption and compression

FreeSecurity is an easy to use freeware application that allows you to use AES encryption to secure your files and safeguard your privacy, also allowing file compression. It's developed in Java and requires only that your operating system has Java 1.4 or above installed, therefore being able to run in any operating system which provides the mentioned Java support.
I developed it in 2005 and released it as a freeware application, you can get it here:

Convert MPC to MP3 and MPC to WAV

FreeMPC is a freeware, graphical and easy to use application for listening and converting MPC audio files. It can convert MPC to WAV and to MP3 (all bitrates) and you can also use it to play MPC files without having to convert them.
I developed it in 2006 and released it as a freeware application, you can get it here:

Execute SQL Task (SSIS) with parameters

On Integration Services 2005, when using a query with parameters in the Execute SQL Task, mind that in the Parameter Name field the counting starts at 0 (if, at least, the connection type is OLE DB). Therefore, if you are using multiple parameters the first Parameter Name must be 0, the second must be 1, and so on.
Otherwise, you'll get this error:
[Execute SQL Task] Error: Executing the query "DELETE FROM myTable WHERE key = ?" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Check here for more information:

October 16, 2007

Lots of 401.x errors on the IIS log

Substatus=2 and win32-status=2148074254 along with Substatus=1 and win32-status=0 means the packet exchange corresponds to normal NTLM authentication (Windows Integrated Authentication). If there are a lot of these, there is probably some configuration causing connections to close too soon.

Excel says 'user does not have permissions to run DBCC Traceon'

This is due to an incompatibility between Excel 2000 and SQL Server 2005. As a workaround, try this: press the Options button and replace the text on the "Application Name" textbox on the "SQL Server Login" panel with a single white space

Insert a newline in a Reporting Services' textbox

Use an Expression such as this one:
="Some text" & chr(10) & "more text, but on the next line"

Changing system culture programatically in C# .NET

This is useful when handling dates in US or other format in C# .NET:

Thread.CurrentThread.CurrentCulture = New CultureInfo("en-US", False) = MonthName(Row.datahorainicio.Month(), True) & " " & Row.datahorainicio.Day() & " " & Row.datahorainicio.Year()

'reset the culture to the system's default
Thread.CurrentThread.CurrentCulture = New CultureInfo("pt-PT", False)

Get the datetime representing the first day of a date

This is pretty useful when you need to get data from the past n months:

SELECT CAST(((convert(varchar(7),getdate(),121))+'-01') AS datetime)

Get the date portion of a datetime

SELECT CONVERT(varchar(11), myDate, 102)
You can then use REPLACE() to substitute the . with -

Clear Visual Studio 2005 Recent Projects list

Visual Studio's MRU list is located in the Registry under:

If you need help on editing the Registry see this article

FindControl on a MasterPage

When you need to use FindControl() on the Controls placed on a page inside a MasterPages layout, first you have to retrieve the ContentPlaceHolder that holds the page’s elements ( Master.FindControl(“myContentPlaceHolder”) ) and use FindControl on the ContentPlaceHolder.

A quick intro to MasterPages

MasterPages are a new feature of ASP .NET 2.0 and simplify the creation of a site-wide template with editable regions. In some applications I developed, the layout was previously created and styled using CSS; then, a MasterPage was created to centralize the site-wide page layout and make it easier to create a consistent and updateable look and feel across all pages, as they will appear within a ContentPlaceHolder defined on the MasterPage. Therefore, for the overall design to be applied to all other pages on the Presentation Layer it suffices to select their Master Page.

IIS Server Application Unavailable

Ever got that error when running/debugging an ASP .NET application?
Try one of these methods:
- Check if ASP .NET 2.0 is correctly installed (aspnet_regiis -lv)
- See if the aspnet user has proper (read & execute) permissions over the application's directory - if he hasn't, the error won't be logged to the Event Log.
- See if the application pool for the virtual directory is correct (this problem produces a log entry identifiable in the Event Viewer)

Fixing orphan logins on SQL Server 2005

Ever restored a backup that didn't include database logins, winding up with a lot of orphan logins? Here's a way out:

To set "targetUserNewLogin" as the new login for user "targetUser", run the following stored procedure (you can leave the first parameter unchanged):
sp_change_users_login 'update_one', 'targetUser','targetUserNewLogin'

If you need to get the list of users without logins:
SELECT * FROM sysusers
WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0)
AND suser_sname(sid) IS NULL

If you need to delete schema ownership:

SELECT s.* FROM sys.schemas AS s
INNER JOIN sys.database_principals AS dp ON dp.principal_id = s.principal_id
WHERE dp.[name] = 'someUser';

SELECT s.[name] as schemaname, o.[name] AS objectname, o.type_desc
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
INNER JOIN sys.database_principals AS dp ON dp.principal_id =s.principal_id
WHERE dp.[name] = 'someUser'
ORDER BY s.[name], o.type_desc, o.[name];


Can’t enable Design or Edit mode for a WebPart

If you can’t enable Design or Edit mode for a WebPart, check if IIS is allowing anonymous logons for the virtual directory. It shouldn't be, because the username needs to be stored in the aspnetdb database so personalization settings can se saved.

An intro to ASP .NET WebParts

WebParts are part of the Presentation Layer and empower users with the capability of customizing the contents of the Web page being visualized. A WebPart is a control whose positioning, content and design can be customized effortlessly by the user, creating a richer and easier interaction. A WebPart can be in one of five modes: Browse, for standard navigation; Design, which allows users to minimize and drag WebParts between zones; Edit, on which an EditorZone control is displayed allowing the user to add WebParts properties at runtime; Catalog, on which a CatalogZone control is displayed so that the user can use it to add new Web Parts to the page while navigating; and Connect (not explored yet). The Sentry Customer Interface has its WebParts in Design mode, allowing only their minimization and customization of their position.
WebParts use a WebPartZone as a container and can be moved within their container (to change their position) and between other WebPartZones that exist on the Web page. Almost all controls can be displayed from within a WebPart, using the GenericWebPart control as a wrapper.
The WebPart framework requires a database, local or located on a server, to enable persistence of the customization options. This database is tipically "aspnetdb" and can be created by running the "aspnet_regsql" command. The customization settings are kept in a per-user basis (with the current logged on user by default).

Backing up specific SharePoint web sites

With Microsoft Windows SharePoint Services, you can back up and restore site collections hosted on your server. You can use this backup and restore method to replace a site collection that has become corrupted, or that contains changes that need to be rolled back (for example, if you need to recover a list or a list item that was deleted accidentally). This backup and restore method is not dependent on the type of database server that you are using.
When you back up a site collection, you back up the content database for the site collection, including all pages in the site, files in document libraries or lists, security and permission settings, and feature settings. The backup process creates a single file that contains all of this data. You can then restore your site collection to either the same location, or to a new location. The backup file for a site includes any subsites of the site collection.

To backup/restore a site, you use the backup/restore operation with the Stsadm.exe command-line tool. Therefore, you can automate the backup process by using a batch file, a script, or the Scheduled Tasks item on Microsoft Windows Control Panel.

Example of backup: stsadm -o backup -urlhttp://server/site -filename backupFile.dat -overwrite
Example of restore: stsadm -o restore -urlhttp://server/site -filename backupFile.dat

Example of restore: stsadm -o restore

Grayscale an image programatically

This code allows you to dinamically apply a grayscale filter to an image contained in a div:

You have to either set the div and img height and width or set its position to absolute, or the effect won't work.
This seems not to work in ASP .NET pages, although it works perfectly on plain HTML.

There are more filters that you can use like the above, here's a list:

October 15, 2007

Secured connection unavailable while editing a Universe

When using Business Objects Designer to create a Universe you can only export it to the CMS database if it has a Secured connection. If you're trying to change the connection type and the "Secured" option isn't available, it may be a matter of user rights: you need permissions to create a Universe in order to change the connection type.

Multiple-step OLE DB operation generated errors

When running an Execute SQL Task on SSIS 2005 you get the following error:
Error: Executing the query "your SQL code here" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The last time I ran into this error it turned out that I had (yes, it was copy/paste ;D) some comments in the middle of the T-SQL code - deleting the comments got it working, hope this method helps you.