SQL Server 2005 Notification Services Deployment Issues with Subscription Management Interface

• Posted by justin on February 26, 2008
• 0 comments

When we went to deploy our notification services (NS )instance, we ran into an issue with the subscription management web page failing with the login failed message below. 

Notification Services failed to get the metadata for the specified instance. Instance Name: [YourAppName] SqlServerError: Source: .Net SqlClient Data Provider Number: 18456 State: 1 Class: 14 Server: (local) Message: Login failed for user 'ASPNET'. Procedure: Line Number: 65536

The same code worked just fine previously in SQL Server 2000 NS and it worked with no problem in our dev environments since we all have sql admin rights and are running one a single machine whereas in production we do not have admin rights and the web/database servers are seperate machines. 

So it turns out that with 2005 NS when you call say new NSInstance and pass the NS Database Name, it now automatically connects to the instance versus in SQL 2000 NS it waited until you set the reference to NSApplication and passed the NSInstance into is. So now our code looks like the following:

_instance = new NSInstance(AlertsDatabaseName, AlertsDatabaseUser, AlertsDatabasePassword);

            _application = new NSApplication(_instance, _applicationName);

ActiveRecord, ObjectDataSource, and GridView Custom Paging

• Posted by justin on February 26, 2008
• 1 comments

Updated 4/25/2008: Add sample source code

Problem: How to efficiently implement paging in GridView using an ObjectDataSource

For small amounts of data the built-in paging for the GridView worked fairly well and takes practically no work to do it. However, once you get to a decent amount of data, it becomes incredible slow and provide for a horrible user experience. The problem is that basically the GridView and ObjectDataSource are returning all of the records each time from the database and only showing the ones for the current page. This is a very inefficient use of bandwidth.

Solution:

To get around this problem, you can implement paging with the ObjectDataSource. When you implement paging with the ObjectDataSource, it automatically passes in the values for the startIndex and maximumRows to your select statement and needs to have a SelectCount method defined so that it can get the total number.

Sounds really easy right? Once you figure out how to get the RowCount for the query it is. For simple queries you can use the CountQuery class in ActiveRecord. However for more complicated queries that use a DetachedCriteria, there is currently no overload for CountQuery that take a DetachedCriteria. Hopefully this will be in a future release since there was a patch submitted on March 5^th for this feature.

Luckily both NHibernate and ActiveRecord are open source so I was able to look thru their code and tests. In ActiveRecord it is well structure with a directory in their ActiveRecord Framework project called Queries.

In my research it became clear that a Projection query was what I needed but I still was not sure

So I finally found a ScalarProjectionQuery class that took a DetachCriteria. Below is an example call to the method. query is a DetachedCriteria

ScalarProjectionQuery<WorkOrder, int> proj = new ScalarProjectionQuery<WorkOrder, int>(Projections.RowCount(), query);

Int count = proj.Execute();

To implement the actual paging for the ObjectDataSource , I used the ActiveRecord SlicedFindAll method for my model along with the Sql 2005 Dialect.

Gotcha:

With turning on paging for the ObjectDataSource is that it automatically passes the startIndex and maximumRows parameters into your defined Select method as the last 2 parameters and if you use the designer to configure the ObjectDataSource, it adds those parameters to the parameter collection defined in the html code. This in turn causes the startIndex and maximumRows to be passed twice into the SelectMethod .

Sample Code:

ObjectDataSource Definition:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="FindUserBlogs"
        TypeName="MyClassThatMyFindMethodIsIn" OldValuesParameterFormatString="original_{0}"
        OnSelecting="ObjectDataSource1_Selecting" EnableViewState="False" EnablePaging="True"
        SelectCountMethod=" FindUserBlogsCount">
        <SelectParameters>
            <asp:Parameter Name="userID" Type="Int32" />
        </SelectParameters>
    </asp:ObjectDataSource>

ActiveRecord Method Definition:

public static Blog[]  FindUserBlogs (int userID, int startRowIndex, int maximumRows) {
     // use SlicedFindAll to start at a specific row and limit the results }

public static int FindUserBlogsCount (int userID) {
    //query is a DetachCriteria    
     ScalarProjectionQuery<Blog, int> proj = 

SSIS Error Trying to View Packages Stored in MSDB with no default sql instance

• Posted by justin on February 12, 2008
• 0 comments

I had a weird SSIS error this morning and couldn't find anything in books online. I actually had to google for the fix. http://msdn2.microsoft.com/en-us/library/ms137789.aspx

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2005 Books Online.

Login Timeout Expired

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Named Pipes Provider: Could not open a connection to ssNoVersion [2]. (MsDtsSvr).