David Eedle

Using a SQL table function in a join in a query

I think this has to qualify as one of the most useful things I've learnt in SQL Server this month. How to to use a table function in a join in a query.

It's called the APPLY operator, and it only works on SQL 2005 and above. Here's an example I found:

select a.PersonId, b.Passportnumber,
p.col1, p.col2, p.col3 from Person a OUTER APPLY dbo.fn_Passport(a.PersonId) p

You use CROSS APPLY when the function must return 1 or more rows to retain the Person record. Use OUTER APPLY to keep the Person record even if the function results in no rows.
   
CROSS APPLY similar to INNER JOIN
OUTER APPLY similar to OUTER JOIN

There's some real power in this, for example, you can write a query that returns to the first X rows from each category of people, as illustrated by this article 'Using CROSS APPLY in SQL Server 2005'.

 

Drag and Drop stops working on Mac Lion

Strangest thing. I rebooted my Macbook Pro this morning. And discovered dragging and dropping files was broken. I could select a file and drag, but when I released the mouse button the file would not drop. The mouse icon remained displaying the file name.

Clicking escape seemed to release the file. I rebooted again. Still not working. I turned my Magic Mouse on and off, disconnected from bluetooth and re-paired. Still not working. Checked the track pad on the Mac itself, same problem. So it's not a Magic Mouse issue. Nor, one presumes with the mouse drivers given it's an issue with both the external mouse and the inbuilt track pad.

A Google search shows I'm not alone, this is a quirk of Mac OSX, occurs at random. Various solutions offered including deleting your mouse preferences or Finder preferences files.

And the fix is equally strange and random. Put your Mac to sleep and wake it. Worked for me.

SQL Server mqSQL "Commands out of sync; you can't run this command now"

Scenario is:

1. You have a SQL Server db

2. You have a mySQL db

3. The SQL server db has a Linked Server connection to mySQL and you are using the mySQL Connector/OBDC

4. You try an insert or update statement in SQL server against the mySQL db using OPENQUERY

5. You receive the error "Commands out of sync; you can't run this command now"

To solve, uncheck the Forward Only Cursor option in the OBDC options.

It's no wonder Australian retailers are losing business to overseas web sites

The lack of competitiveness in many areas of Australian retail were thrown into stark relief for me today. Our eldest daughter is a math whizz, a trait I hasten to assure you inherited from her mother rather than me. She's in Grade 9, but currently chewing her way through the Grade 10 maths syllabus.

As part of her Grade 10 course, she arrived home yesterday with an order form for a fancy 'bath the baby' graphing caculator that is a requirement. Cost? $188, coming from a company in Melbourne.

But even the teacher had worded our daughter up to check eBay for a second-hand version.

Out of idle interest I checked Amazon. And there it was, for $US149. Add $US11 delivery on, convert to AUD and grand total of $A155 delivered. A $33 saving. AND I didn't have to fill out a form, and make sure our daughter took the form to school. My credit card is already saved on Amazon, sign in, one click, agree to everything, done in a minute without moving from my desk.

Amazon exemplifies the challenges facing Australian retailers. The caculator is a globally available Texas Instruments product - exactly the same in the USA as it is in Australia.

Amazon makes the purchase process simplicity itself; and at an extremely competitive price. My experience is that Australian retailers, on the whole, do neither.

How much do you love IE CSS problems!

Oh how I love working with IE browsers. Sure, IE6 is pretty much dead and buried - wasn't that a fun period in browser evolution - but now we grapple with IE7, IE8 and IE9. And the good folks at Microsoft at least have now been kind enough to add th F12 Developer Tools window where we can emulate back versions.

All this was thrown into stark highlight last week when I came across an interesting quirk, with someone running IE7, viewing an SSL secured page, containing a set of input fields, and styled using CSS that included a file called 'ie-css3.htc' from http://fetchak.com/ie-css3/.

 

Problem was he couldn't even move cursor focus onto thos fields, let alone type anything into the boxes. Everything was completely fine in every other browser and version and platform.

Turns out the author was prescient:

You will probably run into issues with z-index, especially if embedding one IE-CSS3 enabled element inside of another. There are two simple workarounds:

    Set the z-index of the IE-CSS3 element to a number larger than its surrounding elements.

We did just that, and changed the z-index of the input field class to 9999 instead of 0 and all was good.

It's the little quirks that make me laugh.

 

 

Reseed a SQL Server identity column

We all use them. But confess rarely need to change the seed on an identity colum in SQL server. Or if I do, it's probably going to happen via dropping and recreating the table. But once in a while it's handy to be able to change the seed.

How do I... Reseed a SQL Server identity column?

DBCC CHECKIDENT

(

  tablename

  [, [NORESEED | RESEED [, newreseedvalue]]]

)

Is something I'm tucking away in my goody bag for another day.

 

 

 

 

 

 

Beware Service Packs for Windows Vista under Parallels

A word to the wise, beware installing Service Packs for Windows Vista running as a virtual machine under Parallels on your Mac.

I wanted to upgrade my Internet Explorer from 7 to 9 the other day, but the installer said I needed to install Vista Service Pack 1 and 2. So I dutifully downloaded them, and installed SP1. Vista rebooted...except it didn't. It hung with the DOS screen saying something like '!! 0xc0190036 !! 5612/68495'

I Googled. Bunch of links (I'm not the first clearly) like:

Vista won't boot after Service Pack 1 installation - Vista Help

SP1 Fails: Message: !! 0xc0190036 !! - Vista Forums

Vista won't boot windows after SP1 update - PC Help Forum

Vista won't start after attempt to install Service Pack 1 - Microsoft Answers

Black screen on reboot during install, !! 0xc0190036 !! 4068/72408 (cdosys.dll.mui) flashing onscreen

All sorts of advice, which mostly wind up falling back to the old 'clean install' recommendation. Gotta love an operating system that the most common piece of advice to fix it is 'turn it off and turn it on', and the second most common is 'just install Windows again'.

I took the absolute chicken way out because I didn't feel like wasting a day of my life frigging around with some esoteric driver/DLL/system program thingy from my Vista install. So I went and bought Windows 7. I've spent 3 years living with the mistake of installing Vista on my Parallels when I could have easily enough dropped in a copy of XP. Then again, I could have got up off my behind and upgraded to Win7 ages ago, so I pay the price for laziness, or lack of a spare day in my life to install Windows, plus all my Visual Studio/SQL etc tools.

Anyhow, Windows 7 installed without a hitch into a Parallels virtual machine. Interestingly the VM takes up just over half the space of Vista - Vista was a little under 50Gb, as of today Win7 is chewing up 25Gb. I don't store any data on the VM other than cloud-based synced files - for example Team Foundation Server source control files. Everything else is stored outside the Parallels VM so it will backup via Time Machine. And I have exactly the same software installed other probably a few utilities, certainly not enough to explain a nearly 25Gb difference.

Must say, Win7 is chugging along way faster than Vista ever did. So the pain was probably worth it after all.

 

 

 

 

Using SSL on your localhost for web site development

SSL is a fact of life for virtually every web developer, and crucial if like me you work on ecommerce projects on a daily basis. Of course we all have an SSL cert on our development and staging servers, along with production, but what about your localhost machine? Wouldn't it be good if that was running SSL as well? And so you can:

Tip/Trick: Enabling SSL on IIS 7.0 Using Self-Signed Certificates

This came up for me because a) I was dealing with a problem that was only evidenced under SSL; and b) because I recently had to do a clean Windows install I'd lost my previous self-signed certificate in IIS7.

 

 

Mac Spotlight Search stops indexing local hard drive

Spotlight is one of the ultra-great things about Macs, I run Windows7 as well, and Windows search is completely useless in comparison. Takes forever, never seems to find what you want and generally seems redundant. Spotlight by comparison, with its predictive typeaheads and lightening speed almost invariably locates what I'm after.

However Mac Spotlight search was starting to piss me off yesterday, I was searching in my Mac Mail for a keyword and no results were returned. I tried a few combinations, then realised it was nothing to do with my keywords, I simply wasn't seeing any search results at all.

The text search in Mac Mail is powered by Spotlight, so I tried the same keywords in my Spotlight search box - no results. So I experimented some more and deliberately gave Spotlight keywords of files on my Desktop. Again no results.

Yet for some keywords I WAS seeing results come back - and I finally twigged, they were all files from my external hard drive where I archive things I'm not currently working on.

It became apparent that Spotlight was not returning any search results at all from my local hard drive.

I hunted online. All of the available advice was that the Spotlight index file for the drive needed to be deleted, then Spotlight would be forced to re-index. Also, as a precaution, many people suggest running Disk Utility and doing a permissions repair - which I did, although that by itself did not kick Spotlight into action.

So, if you are happy using Terminal, here's what I found worked for me. Please bear in mind I'm using OSX Lion 10.7.2, I don't know if the location and file name for the Spotlight index is the same on other releases.

In terminal make yourself super user:

david-eedles-macbook-pro-4:~ davideedle$ sudo -s

You should be at the bash prompt.

Change to the directory where the index is located (don't forget to escape the spaces):

cd /volumes/Macintosh\ HD/.Spotlight-V100/

Note your HDD might have a different name. Mine is 'Macintosh HD'.

Now kill the file:

rm -rf Store-V1

I rebooted, and Spotlight started to index the drive. This morning my searches are absolutely fine. I have no idea why Spotlight decided to forget there was a drive inside my MacBook Pro and only index the external drive.

If you want to locate the file in Finder you'll need to turn on showing hidden files. I use the handy TinkerTool for tasks like this.

 

 

 

SQL Server 2008 Database Mail Failed

Had one of those 'how weird' moments today. We seemed to not be sending emails from a web site I look after. We're using SQL Server 2008, and the terrificly handy Database Mail service.

I originally set this up, using instructions I found in a well explained blog post 'Installing and Configuring Windows Server 2008 SMTP'.

When I checked the Database Mail logs I found a bunch of errors saying:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2011-03-25T18:13:13). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it 127.0.0.1:25).

Which seemed odd. So I tried a simple Telnet localhost 25, and received connection refused. Which made me wonder where the SMTP service had gone.

I checked the Features list in Server Manager, and the SMTP service was showing - so I definitely wasn't hallucinating that I had originally installed the service (it's not installed by default).

I tried sending a Test Mail (right click on Database Mail in SQL Server Management Studio, then used the following to check the send log and the error log:

SELECT * FROM msdb.dbo.sysmail_allitems WHERE mailitem_id = [test mail id]
  
SELECT * FROM msdb.dbo.sysmail_event_log WHERE mailitem_id = [test mail id]

And it showed as failed, with the same error message I'd been seeing in the logs. Then I opened IIS6.0 - the SMTP service was not showing.

Checking the logs showed the emails starting to error on a specific day. Which makes me wonder whether someone else on the team has been messing around the on the server.

I decided to try and reinstall, so I used Remove Feature in Server Manager to remove the SMTP service, then Add Feature to reinstall, and then again followed the instructions in the above blog post - which is really just about ensuring you've set Relaying for the localhost, so the SMTP service will accept emails from the SQL Database Mail service.

A test email went through fine. And trying functions on the web application showed emails sending fine as well.

So I have a little bit of a mystery. SQL Database Mail was all fully installed and operational. But the SMTP service seemed to have disappeared, or somehow been changed. Think I had better ask around to see who has been doing what.