Looking forward to giving my first presentation at a large event.  Come here me talk about using SSIS to handle Slowly Changing Dimensions in a data warehouse.  For more info on the event: http://www.sqlsaturday.com/eventhome.aspx?eventid=32.  I hope to see you there!

Giving a presentation on using SSIS to handle Slowly Changin Dimensions tomorrow night at the Space Coast SQL User Group.  Goto http://spacecoast.sqlpass.org for details.  If you can't make it, then try and catch me at SQL Saturday on the 17th - http://www.sqlsaturday.com/eventhome.aspx?eventid=32

The run this Thursday will be the dry run so come to see me bumble through the presentation.  Come the Saturday to see the polished version.

SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held October 17, 2009 at Seminole Community College, 100 Weldon Boulevard, Sanford, FL 32773. Admittance to this event is free, but to to the challenging nature of fund raising this year there will be a $10 fee due at registration to cover the cost of lunch (a great boxed lunch from Jason's Deli). All other costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleages know about the event.

More Info: http://www.sqlsaturday.com/eventhome.aspx?eventid=32

This is impossible.  Don't try.  There are a few 'hacky' ways to do this, but why? It can be doneif you slightly modify the requirement. If you can abandon the use of a stored procedure for a User Defined Function, you can use an Inline Table-Value User-Defined Function. This is essentially a stored proc (will take parameters) that returns a table as a result set; and therefore will place nicely with an INTO statement. Here's a good quick article on it and other user defiend functions. If you still have a driving need for a Stored Procedure, you can wrap the Inline Table-Value User-Defined Function with a stored procedure. The stored proc just passes parameters when it calls select * from the Inline Table-Value User-Defined Function.

So for instance, you'd have a Inline Table-Value User-Defined Function to get a list of customers for a particular region:

          
            
              

                

                  
                    CREATE
                    FUNCTION ufCustomersByRegion
(@RegionID int)
                
                

                  RETURNS TABLE
                
                

                  AS
                
                

                  RETURN SELECT * FROM customers WHERE RegionID
= @RegionIDGO
                
              
              

            
          You can then call this function to get what your results a such:

            
              
                

                  
                    SELECT * FROM CustomersbyRegion(1)
                
              
            
          

Or to do a SELECT INTO:

            
              
                

                  
                    SELECT * INTO CustList FROM CustomersbyRegion(1)
                
              
            
          

If you still need a stored proc, then wrap the function as such:

            
              
                

                  
                    CREATE
                    PROCEDURE uspCustomersByRegion
                
                

                   @regionID int
                
                

                  AS
                
                

                  BEGIN
                
                

                  
                    SELECT * FROM CustomersbyRegion(@regionID);
                
                

                  END
                
                

                  GO
                
              
            
          

I think this is the most 'hack-less' method to obtain the desired results. It uses the existing features as they were intended to be used without additional complications. By nesting the Inline Table-Value User-Defined Function in the stored proc, you have access to the functionality in two ways. Plus! You have only one point of maintenance for the actual SQL code.

Just a quick note on a small Codeplex project I am starting.  After having done some BizTalk RFID work, I found the supplied code sample for a duplicate eliminator to be an excellent example of a custom event handler, but a poor implementation of a duplicate tag eliminator.  This codeplex project is a little better thought out dupicate tag eliminator with a few added features.

Go take a look if interested and let me know what you think:  BizTalk RFID Better Duplicate Tag Eliminator

So we've been being pestered by our DBAs (you know the ones) to move our BizTalk Server 2006's Data Tier from our aging SQL Server 2000 Cluster to our new shiny SQL Server 2005 Cluster.  The end of our fiscal year was approaching so we brought in a consultant to help us. I highly recommend Catapult Systems if you need any BizTalk help. They're smart guys and have reasonable rates (in comparison to Oracle, who doesn't?).  We met with our consultant Mark and gave him the tour of our environment and he came back a week later with a solid procedure... BAM, MsgBox, WSS, Jobs, DBs... all of it.  We executed the procedure along with enough red lines to maintain their human status.  All seemed to go well - hosts started, jobs started, Group Hub back up, but wait!  Couple of jobs are failing?  Hmmm.  Update some physical paths in the backup job and all is well.  Wait. Tracked Message Copy keeps failing: 

Executed as user: HARRIS\MLBSQLVLM1. Could not find server 'mlbxxx\sql3' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. [SQLSTATE 42000] (Error 7202).  The step failed.

Now this is very confusing.  We're all on one server.  No need for any linked servers.  Why does it think mlbxxx\sql3 is a linked server it can't connect with.  Is it a permission issue?  I checked and double checked the database configuration from the old server and nothing jumped out at me.  OK.  Time to step into the deep dark reaches of the BizTalk stored procedures... the starting point: bts_CopyTrackedMessagesToDTA at line 70:

The thing that jumped out at me was the use of the full 4 part reference to the TrackingParts1 table.  This should be extra clear.  There should be no confusion.  Let me look in sys.server... it's wrong... it has MLBYYY\sql3.  How could this be... how can I ask the server it's name:

select

SERVERPROPERTY ( 'servername' ); returns mlbxxx\sql3. correct. strange.

Is there another way?  Yes.

select

@@servername ; return mlbyyy\sql3 - wrong.  It thinks it has two names?

The detail here is how each command gets the name.  The SERVERPROPERTY comes from the Windows OS.  @@servername is the name set during installation.  I am guessing the SQL Server was built from an image and the @@servername was never adjusted when the machine was renamed.  So why didn't my query work?  It appears that when you specify the full four part name of a table, it goes to sys.servers to find out about the specified server.  If you don't specify a server (less than full 4 part naming), it bypasses sys.servers and assumes local or gets the name from the SERVERPROPERTY.

The fix:

sp_dropserver <mlbyyywhatever was returned by @@servername>;
go;
sp_addserver 'mlbxxx\sql3', local;
go

then restart the server or instance. See the KB article for the gory details.  Once this was corrected, everything worked like a charm.

Back in early 2002, my company sponsored a bunch of it's developers to get their MCSDs.  These were the days of VB6 and COM+.  After many evenings at a local college and 4 certification tests - we were MCSDs.  PrimMarily a web developer, I didn't get to put much of the COM+ to use, but the SQL Server 2000 Design & Implementation class proved extremely useful.  With my newly minted MCP & MCSD next to my name, I set out to upgrade my career.  Two of my next three jobs were made possible by interviewers who noted my credentials.  Although the credentials didn't get me the job, they got my resume the leg up it needed to get in the door.  For this, I am always grateful for having done the certification.

Over the following years, I passed the .Net solutions architechture certification but didn't do much after this.  Come 2007, I began wokring with BizTalk Server 2006.  Now anyone who has worked with this product knows it is huge.  There are numerous parts to this technology stack and each more complex than the next.  After some training and a book, we were off developing solutions.  I got the opportunity to attend Teched 2008 Developer in Orlando last year and used some of my down time to pass the Biztalk certification test.  I spent many hours in the lab doing test prep and reviewing the online test guide with some books.  I managed to pass on the first try despite the ridiculous number of social events I felt compelled to attend.  Returning to work I not only had a deeper knowledge for the core BizTalk development I was doing, but I also had a stronger understanding of the entire stack from BAM to BAS but also BRE and a few key points about orchestrations that always eluded me.

I am now sadly looking at my VB6 era MCSD and hoping to upgrade it.  I've used the online planner and have a good feel for what I need to do to reach MCPD - lots more tests.  The study room and being locked away from work at a conference center helps get things moving faster.  So regardless of wether I get to join the Bus, more certifications and visits to MS learning are in my future.  They have proven their worth to me both in job seeking and comprehensive study of the technology.

PS - Anybody reading this who doesn't know about the Get On The Bus Contest - Go here: http://blogs.technet.com/mslcommunity/archive/2009/04/09/another-chance-to-get-on-the-bus-or-fly-directly-to-teched.aspx

So, the wife's car is making noise and we need to locate the mechanic she usually goes to.  The car's been pretty reliable for the last 4 years so no need of repairs... until now.  What was the name of that shop we like (in this case, the name also changed).  Well, I don't know what its called so the usual yello page searches don't help.   But wait! I know where it is!  Google Earth time!

So there's the garage where I remember it.  What good does this do me?  Down to street view!

Problem Solved... P&J Auto in Melbourne, Florida.  Off to the yellow pages since it wasn't listed in Google's business listings.

I got this from Richard Seroter's blog - How to maintain and troubleshoot BizTalk Server databases

It looks to be very helpful from the brief skimming I gave it.

I couldn't resist sharing this.  I spent a mid-size chunk of my life playing Half Life and its related properties.  It now appears to be bleeding out into the real world:

   http://www.joystiq.com/2008/09/09/terrible-news-gordon-freeman-spotted-near-large-hadron-collider/

A very insightful bit of writing by Richar Seroter on what happens to BizTalk Web Service receive locations when the DB goes bye bye. When the SQL Server goes off line, take down the IIS service on your BizTalk servers.  This is really kind of scary.  When the SQL Server goes offline, the BizTalk web services return 202's which is just as good as a 200 to most - and the message is lost.  I need to think this one through and see how to deal with it.  We don't lose our database alot, but it has gone offline on enough occasions to make this a real concern.  Anybody have expereince handling this one?

Thanks to Joe Healy for passing on the announcement from Stan Kennedy.  On October 1st, there will be the first meeting of the Central Florida BizTalk User Group in Tampa.  A bit of a hall, but I will be attempting the trip.  Anyoneinterested in car pooling?

Since getting back from TechEd things have been a bit crazy.  The rest of the week there was a blast.  I got to meet many more community members and catch some really interesting presentations.  I am currently fascinated with the SQL Server 2008 Data Mining tools and am in search of a good problem.   I'd like to turn this into a presentation for the local User Group. 

One of the other highlights was using the Certification Testing at the conference.  After sorting out some issues with the name on my certification, I was able to finally get this logo:

That was not a pleasant test.  The tool just has so many facets to it and it's really impossible to have used all of them.  But the test prep senter at the conference and some reading got me a pass on the first try.

When: Wednesday June 11, 2008 @ 6:30PM
Where: Space Coast Credit Union Corp Headquarters

Want to know what you need to do to keep your DBA happy and make your application run really fast? Wondering if stored procedures are really faster than dynamic sql? Need some good guidelines for adding indexes? We'll spend an hour talking about performance in this very interactive presentation.

Andy Warren of End to End Training will be speaking.

Register if you plan to attend this event

Then will be pizza and magazines available for those who attend. We will be raffleing off a Technet plus subscription at the end of the meeting. 

The day started out with the key note by Bill Gates.  Very nice speech on the focus of their development efforts for the years to come.  We were also treated to the director's cut of the Bill Gates' Last Day video.

I've made some new friends.

Drank my MSDN Subscription's worht of Beer at Howl at The Moon for Geekfest 2008.  It was a nice time and I got to meet many more of the folks from the Florida .Net Communities. 

I arrived on Monday afternoon at the convention center.   I always forget how incredibly large the facility is.  The first glimpse of the floow was empty but beautiful.  The booths were very well put togeather.

The afternoon was spent at the INETA Community Summit with the Florida Delegation led by Joe Healy (Only half of us could vote).  It was a great discussion on the challenges, successes, tips and more of building a community.  I got some good ideas to bring back to the Space Coast .Net User Group.

After the summit was hotel check-in followed by Party with Palermo at the Glow Lounge.  Ran into a former co-worker who was staying at the same hotel and we got to catch-up over copious servings of beer.

I put a couple of collections on Live Maps to help everyone find the where & when of the after hours events at TechEd 2008 Dev.

• TechEd 2008 Dev Evenings - Monday
• TechEd 2008 Dev Evenings - Tuesday
• TechEd 2008 Dev Evenings - Thursday

I was comparing the list of evening events from the IT Pro week to the Developer's week and I have come to the conclusion - we're boring.  The IT Pro week is booked with evening events all over the place on every night.  It really doesn't matter, I'll be in my room in the evenings studying for certs (cough... not...).

These are odd months for the User Groups with the kids getting out of school.  You're never sure what you'll get.  We had a crowd of 15+ attendees. Some regulars and some designers who were new to the group.  Jeff's presentation was quite extensive but paced to not bore.

Next month is going tobe SQL Guru Andy Warren - more info to follow.

I ran into a BizTalk problem today that was inevitable.  We interface with PeopleSoft  a bunch.  PeopleSoft has some very specific Schemas it uses to communicate.  We have two seperate applications that make use of the same schema in reading responses posted to the PeopleSoft web service.  Well the second of two projects what put into production a few days ago.  When I came in this morning, one of them had a suspended message on the receive side of a HTTP Send/Receive adapter:

Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
Source: "XML disassembler"
Receive Port: "rcvFile"
URI: "C:\Test\In\*.xml"
Reason: Cannot locate document specification because multiple schemas matched the message type

So, as expected, BizTalk could not figure out which schema to resolve the message to as it exists in this Applicaion and another.  The default XML Receive Pipieline is not very helpful in this situation as it is just that - default.  The solution turns out to be quite simple.  Create a new Receive Pipeline and add an XML Disassembler to the Disassemble stage.  In the XML Disassembler's properties, add the desired schema from the application to the disassembler's Document Schemas collection.  This tells the disassembler to select from this list only.  If the list is empty, it will behave the same way as the default XML Receive Pipeline.

Andrew Connel & Bob Fox kick-off TechEd right with SharePoint by Day and SharePint at Night.

• SharePoint by Day, SharePint by Night @ TechEd North America 2008 Developer
  Monday, June 2, 7p ->? @ BB Kings Orlando (9101 International Drive #2230; Orlando, FL)
• SharePoint by Day, SharePint by Night @ TechEd North America 2008 IT Pro
  Monday, June 9, 7p ->? @ BB Kings Orlando (9101 International Drive #2230; Orlando, FL)

Another stop for an evening! http://www.implementsivillage.net/PermaLink,guid,1e35c8c2-ceb1-4e68-a72c-98ffe3d77ee0.aspx http://feedproxy.google.com/~r/ImplementsIvillage/~3/Dhda-ubSnag/PermaLink,guid,1e35c8c2-ceb1-4e68-a72c-98ffe3d77ee0.aspx Wed, 21 May 2008 17:13:38 GMT <p> <a href="http://www.andrewconnell.com/blog/Default.aspx">Andrew Connel</a> &amp; <a href="http://bobfox.securespsite.com/FoxBlog/">Bob Fox</a> kick-off TechEd right with <a href="http://www.andrewconnell.com/blog/archive/2008/05/19/SharePoint-by-Day-SharePint-by-Night-vTechEd-North-America-2008.aspx">SharePoint by Day and SharePint at Night</a>. </p> <ul> <li> <strong>SharePoint by Day, SharePint by Night @ TechEd North America 2008 Developer<br> </strong>Monday, June 2, 7p -&gt;? @ BB Kings Orlando (9101 International Drive #2230; Orlando, FL)<strong></strong> <li> <strong>SharePoint by Day, SharePint by Night @ TechEd North America 2008 IT Pro<br> </strong>Monday, June 9, 7p -&gt;? @ BB Kings Orlando (9101 International Drive #2230; Orlando, FL) </li> </ul> Another stop for an evening!<img width="0" height="0" src="http://www.implementsivillage.net/aggbug.ashx?id=1e35c8c2-ceb1-4e68-a72c-98ffe3d77ee0" /> http://www.implementsivillage.net/CommentView,guid,1e35c8c2-ceb1-4e68-a72c-98ffe3d77ee0.aspx TechEd http://www.implementsivillage.net/PermaLink,guid,1e35c8c2-ceb1-4e68-a72c-98ffe3d77ee0.aspx