SQL Server 2005 SSIS Can’t export XML !?!?

SQL Server 2005 is singing and dancing about it’s new XML features.

Well, I have a tiny table in SQL Server which is 81rows with 7 columns. I want it to be queried on our public web site and so was planning to use SSIS to export it to XML and FTP it. I thought this was gonna be a 5min job, but alas not…

There is no XML Destination in Data Flow Destinations, having done some googling I concluded that this is the case and I’m not missing anything, so back to the drawing board…

I found an article which said ‘just’ do the XML in SQL and export that, so we set about generating some nasty,nasty,nasty (Mark’s input there) XML in SQL Server using something like:
SELECT Col1,Col2,Col3 FROM myTable WITH XML AUTO

At this point I have something XML like, fine, now all I have to do is export it to a file. Again scuppered as as far as I can tell I only have two possible options in the Data Flow Task: “Flat File Destination” (CSV) or “Raw File Destination”. Neither work as the XML ‘column’ kicked out by the SELECT query is of type DT_IMAGE.

I’ve read a suggestion at:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65534
Which suggests DIYing it, I think that’s a last resort…

UPDATED: Actually it’s possible using powershell, checkout http://www.pluralsight.com/blogs/dan/archive/2006/10/28/41337.aspx
Failing that you can quite easily modify the powershell script included in my DiagramScripting post

SSIS leaking memory in SQL Server 2005 SP1 x64…

…workaround ? Disable the service and wait for SP2.

I’ve been hanging around on the MSDN feedback pages generally making a menace of myself due to the poor quality of SQL Server 2005.

I’ve come across this excellent advice at:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126192

I wish I could tell my clients that as a workaround stop using our product!

Exchange 2003 SMTP Connector Woes

We send [bulk] e-mail on behalf of clients aswell as our usual company e-mail via Exchange 2003.

I have two SMTP connectors, one set to reject all e-mail except from certain e-mail accounts which have client’s e-mail addresses as their primary address. E-mail delivered via this route is sent directly via DNS.
We then have another SMTP connector which by default accepts all e-mail but has a higher cost. This connector delivers e-mail via MessageLabs which in turn adds our company disclaimer on the bottom.

However things weren’t working as expected and only one connector would ever get used. I’ve been pulling my hair out over this one for months and today found this article: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q277872

It seems Exchange doesn’t bother to check the rules! – Worse still it hasn’t since Exchange 2000, lets see if Exchange 2007 is any better.

Where did my Settings button go in MS CRM 3.0 ?

We’ve been trying to work out why the settings option on the bottom left corner of MS CRM 3.0 had disappeared. I thought that this was due to my fiddling of Service Principal Names (SPN) trying to get the reporting working.

Alas it’s something much more simple (thank God!). I found this article on the Mid Atlantic Microsoft CRM blog which describes the problem.

Our CRM is sat on a machine called VENUS. I like to reference services using DNS names to allow me to move things around and so clients access it via CRM.FQDN.localdomain which is a CNAME to VENUS. I’ve now setup another CNAME for CRMOutlook.FQDN.localdomain which points at CRM.FQDN.localdomain. Now when I install the Outlook client I use the CRMOutlook alias and use the CRM alias for web clients. For currently broken machines the key can be changed at HKEY_CURRENT_USER\Software\Microsoft\MSCRMClient

How do you kill a service which is stuck ‘starting’ or ‘stopping’

I’ve once again been asked how do you kill a service in Windows NT/2000/XP/2003 which appears to have hung whilst stopping or starting. You may have realised that you can’t kill the process from your standard TaskMgr session.
This is very common whilst developing a service and the official answer is to either: Restart the machine or run the service as your own user account.
If you choose the latter you can then kill the service using your own task manager.

However, if you really need to run the service as Local System or the service was already set to that and you can’t restart the machine there is a work-around. I can’t remember where I learnt this one and so I can’t credit the author – I have been using this for at least 5years though.

Here goes:
Open a command prompt ( if you don’t know how you shouldn’t be reading this!). Do “at 14:05 /INTER taskmgr”, where 14:05 is a time immediately in the future e.g. Now()+1minute.

What is this doing? Well it’s using the “Task Scheduler” service to schedule a Task Manager process to start in 1 minutes time to run interactively.
Why is this any use? The scheduling service runs as “Local System” and so any process it spawns also runs as Local System. Local System can kill any other Local System process – hence it can kill your Hung service.
When the TaskMgr window pops up it’s not easy to tell that it’s a ‘special’ one, thus I’d recommend closing all task manager windows before you schedule the command.

Users can’t collapse and expand web parts in Sharepoint

I was disappointed yesterday when I realised that my users couldn’t restore web parts which I had minimised on our Windows Sharepoint Services (WSS) 3.0 Intranet. In WSS you need to have elevated rights to do this which is just plain silly if you ask me.
Luckily I found a custom web part by Mark Beecham that actually implements collapse and restore client-side:
http://www.msd2d.com/content/Tip_viewitem_03.aspx?ID=be318815-0b51-4b83-ba32-b3ac135c6d9f&section=SharePoint
You need to login to access the page but Google has a nice cache of it 😉