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.
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.
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.
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.
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
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.
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.
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.
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 terrifically 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.
Every so often I experience problems importing data from Office files (usually XLS) into SQL. Sometimes everything works fine, others not. I've always suspected it's because I don't actually have Office installed on my Windows part of my Mac, although I do have SQL, SQL Express, and an old copy of Access.
This morning I was importing an XLS with a couple of thousand lines, into SQL 2008. And up popped this message:
The microsoft.ace.oledb.12.0 provider is not registered on the local machine
A quick Google and I found this forum which leads to this Microsoft download page for the 2007 Office System Driver: Data Connectivity Components. I downloaded and installed and now SQL likes my XLS file.
Following on from my post yesterday 'Calculate distance between web site visitor IP and list of locations' I've come across an interesting revelation.
The client was off playing with my new page that establishes the web site visitor's geolocation from their IP number. And he complained because nothing was right.
Turns out he was browsing the page via iPhone. So I set up a test with my own iPhone, and hey presto, the IP geolocator system database thinks my phone is in Sydney. Which is definitely incorrect because it's sitting on my desk in front of me here in Melbourne. I proved the point by switching the iPhone's wifi back on, and now it correctly reports its location as Melbourne.
What I presume is that mobile carriers assign IP numbers to mobile devices from a central place – eg the IP range is probably designated to their head office or datacentre. And thus, like me, your phone can think it's in Sydney when in reality it's in Melbourne.
Of course iPhone Apps can access a phone's coordinates in a different way via the cellular network – hence some applications popping up a message asking you to agree to them accessing your location. That's how cool gadgets like Four Square work.
But it you rely on IP/coordinate lookups then the answer will be wrong.
For the moment I think we'll disable the fancy stuff on the web site for people browsing with mobile devices, but now I'm wondering how we can deal with this revelation.
David Eedle 