The Bit Bucket

Hi Folks,

Just wanted to focus some attention on Arnie Rowland's project to help unemployed developers to get some software in return for helping a non-profit. Seems like a great idea Arnie!

http://sqlblog.com/blogs/arnie_rowland/archive/2010/07/02/there-will-be-no-free-lunch-just-great-feelings-of-accomplishment.aspx

I got the news this morning that another of the free SQL Server 2008 R2 eClinics that we've been building for Microsoft Learning has been published.

The eClinic for StreamInsight has been rev'd to RTM and is now available here:

http://www.microsoft.com/learning/elearning/course/10335be.mspx

Enjoy!

Hi Folks,

We've been updating the free eClinics for SQL Server 2008 R2 for the released version of the product. Three of these clinics are now published:

Master Data Services

http://www.microsoft.com/learning/elearning/course/10331be.mspx

Application and Multi-server Management (Data-tier applications, SQL Server Utility)

http://www.microsoft.com/learning/elearning/course/10336be.mspx

Managed Self-Service BI (PowerPivot for Excel, Reporting Services in R2, Report Builder 3.0 and PowerPivot for SharePoint)

http://www.microsoft.com/learning/elearning/course/10334be.mspx

Enjoy!

While building some maps today in SQL Server 2008 R2 Reporting Services, I kept coming up with an error that said:

A generic error occurred in GDI+

I was struggling to think what I'd done wrong. After much nashing of teeth and removal of hair, I finally worked out what the error was. When I got to the "Choose Color Scheme and Visualization" page of the map wizard for building a color analytical map, I hadn't picked the correct value for the "Field to Visualize" drop-down. I'm guessing that because it had defaulted to my GEOMETRY column, instead of the field that I actually wanted to use for analysis, it must get itself into some sort of stack overflow or nesting problem.

Regardless, the error message isn't helpful. Picking the correct column to visualize solved the problem.

Hope it helps someone else (or me when I forget and do it again in future :-))

Our buddy Buck Woody made an interesting post about a common question: "How do I back up a single table in SQL Server?"

That got me thinking about what a backup of a table really is. BCP is often used to get the data but you want the schema as well.

For reasonable-sized tables, the easiest way to do this now is to create a script using SQL Server Management Studio. To do this, you:

1. Right-click the database (note not the table)

2. Choose Tasks > Generate Scripts

3. In the Choose Objects pane, select the table you want to script

4. In the Set Scripting Options pane, click Advanced.

5. In the Types of Data to Script option, choose Schema and Data. (If you also want indexes, etc. make sure they are also chosen)

Click your way through the remaining screens and you're done.

A question came up on the SQL Down Under mailing list today about how to add an included column to an index using SMO. A quick search of the documentation didn't seem to reveal any clues but a little investigation turned up what's needed: the IndexedColumn class has an IsIncluded property.

Index i = new Index();

IndexedColumn ic = new IndexedColumn(i, "somecolumn");

ic.IsIncluded = true;

 

Great to see that our friend Ola Hallengren has updated his maintenance utility scripts to deal with SQL Server 2008 R2.

These scripts are highly regarded, particularly given the price: free !

You'll find them here:

http://ola.hallengren.com/Versions.html

 

Ola noted that the main change from 2008 is that backup compression is now supported in Standard Edition of SQL Server. That in itself is good news.

In CTP3, I found setting up the StreamInsight Event Flow Debugger fairly easy. For RTM, a number of security changes were made.

First config: To be able to connect to the management interface, your user must be added to the Performance Log Users group. After you make this change, you must log off and log back on as the token is only added to your login token when you log on. I forgot this and spent ages trying to work out why I couldn't connect.

Second config: You need to reserve the URL that the management service will be exposed on. For current operating systems, this involves a command like:

netsh http add urlacl url=http://*:8090/StreamInsight/MSSQLSERVER user=GREGWIN7\Greg

This must be executed from a command prompt with elevated privileges (ie: right-click command prompt and Run As Administrator). This says that my user is allowed to use the URL reserved above. The user specified would need to be the user that is the identity your StreamInsight server would run as.

Third config: You need to open a WCF endpoint. I did this by declaring the following members:

private WSHttpBinding _binding = null;

private ServiceHost _host = null;

and then configuring and opening the host as:

_host = new ServiceHost(_server.CreateManagementService());

if (_binding == null)

{

  _binding = new WSHttpBinding(SecurityMode.Message);

  _binding.HostNameComparisonMode =

                  HostNameComparisonMode.WeakWildcard;

}

_host.AddServiceEndpoint

               (typeof(IManagementService),

                _binding,

                @"http://localhost:8090/StreamInsight/MSSQLSERVER");

_host.Open();

Once this was working I could connect to the endpoint using Event Flow Debugger, using the address in the AddServiceEndpoint call.

Another thing that tripped me up at first was that my app was too UI-bound and the management service wasn't responding in a timely manner. I either saw timeouts on connection or I saw a message telling me that my server was too busy. Turns out that was an accurate message. I just hadn't believed it while I couldn't get it working!

 

 

For those that have worked with the earlier versions of the simulator that Bill Chesnut and I constructed for the Metro content (the Highway Simulator), changes are also required to how AdvanceTimeSettings are specified.

The AdapterAdvanceTimeSettings value is now generated by binding an AdvanceTimeGenerationSettings (that is based on your adapter configuration) with an AdvanceTimePolicy setting.

public class TollPointInputFactory :

          ITypedInputAdapterFactory<TollPointInputConfig>,

          ITypedDeclareAdvanceTimeProperties<TollPointInputConfig>

{

  public InputAdapterBase Create<TollPointEvent>

                          (TollPointInputConfig configInfo,

                           EventShape eventShape)

  {

    return new TollPointInput<TollPointEvent>(configInfo);

  }

 

  public void Dispose()

  {

  }

 

  public AdapterAdvanceTimeSettings DeclareAdvanceTimeProperties<TPayload>

                                    (TollPointInputConfig configInfo,

                                     EventShape eventShape)

  {

    var atgs = new AdvanceTimeGenerationSettings

                   (configInfo.CtiFrequency,

                    TimeSpan.FromSeconds(0),

                    true);

    var ats = new AdapterAdvanceTimeSettings

                  (atgs,

                   AdvanceTimePolicy.Drop);

    return ats;

  }

}

In this case, I've specified the Drop policy. An alternate policy is Adjust. The documention suggested that Adjust causes the event to be moved back into the window that you're using. For the events we were using, we found that wasn't what happened. It turns out that that was because we were using Point events. What Adjust actually does is clip an event to lie wholly within the CTI period as long as the event overlaps the CTI. This would only then work with Interval or Edge events, not with Point events.

 

I'd briefly played around with user-defined aggregates in StreamInsight with CTP3 but when I started working with the new Count Windows, I found I had to have one working. I learned a few things along the way that I hope will help someone.

The first thing you have to do is define a class:

public class IntegerAverage: CepAggregate<int, int>

{

    public override int GenerateOutput(IEnumerable<int> eventData)

    {

        if (eventData.Count() == 0)

        {

            return 0;

        }

        else

        {

          return eventData.Sum() / eventData.Count();

        }

    }

}

In this case, I've defined an IntegerAverage class that inherits from CepAggregate. CepAggregate is then declared as a generic type taking an int input parameter and providing an int output. The GenerateOutput method is overriden and called when it's time to process the input. In this case, I've just checked if there are any values, returned zero if not and returned an integer average if there are values.

This is fine but to make it work with Intellisense and make the compiler play nicely, etc. you need to do more. The following code helps:

public static class UDAExtensionMethods

{

    [CepUserDefinedAggregate(typeof(IntegerAverage))]

    public static int IntAvg<T>(this CepWindow<T> window,

                                Expression<Func<T, int>> map)

    {

        throw CepUtility.DoNotCall();

    }

}

We define the name that we will use in code (in this case IntAvg). As this should never be called directly, we throw an exception if it is ever called.

The part that tricked me at first was this:

Expression<Func<T, int>> map

I had seen it used in the doco in Books Online but BOL didn't ever mention where it came from. To use this, you need to add a using statement in your code as:

using System.Linq.Expressions;

This is the line that maps the function signature. Without it, you'll get an error telling you that your aggregate has no overload taking one parameter. This allows me to then call it in code via:

select new { AverageMilliseconds = w.IntAvg(e =>

                         e.MillisecondsToPassSpeedCheckPoint) };

Passing The Event Instead

An alternative to this would be to build an aggregate that's specific to the the event type. You could then call it via:

select new { AverageMilliseconds = w.SomeAvg() };

but then your user-defined aggregate would have to be defined via:

public class SomeAverage: CepAggregate<TollPointEvent, int> 

In this case, TollPointEvent was the name of the event type that the window is being created over. You'd need to then access members of the event class inside your aggregate code, like:

public override int GenerateOutput(IEnumerable<TollPointEvent> events)

{

  if (events.Count() == 0)

  {

    return 0;

  }

  else

  {

    return events.Sum(e => e.MillisecondsToPassSpeedCheckPoint)

         / events.Count();

  }

}

Finally, you'd need to modify your declaration similar to:

public static class UDAExtensionMethods

{

  [CepUserDefinedAggregate(typeof(IntegerAverage))]

  public static int SomeAvg<T>(this CepWindow<T> window)

  {

      throw CepUtility.DoNotCall();

  }

}

Note that the Expression section would not be needed as the whole event was being passed.

While you can do this, I'd only look to do this where your aggregate is very specific to the event class and where it involved multiple fields from within the event.