Monthly Archives: May 2012

Using a SQL table function in a join in a query

Posted on by
Reply

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

Posted on by
11

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”

Posted on by
2

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.

2012-05-01_16-15-01