SQL Server Is Not Case Sensitive!! (Traumatically Humbling Note To Self)

by Jon Davis 15. June 2009 22:29

I don't know if it's a good idea for me to post this. This is so humiliating and humbling that I'm scared that anyone who might actually read my blog would consider me genuinely, well, "one who rides the short bus". 

But SQL Server is not case sensitive. I didn't know that. I've been a web & app developer with SQL Server as my essential RDBMS for .. gosh, twelve years now. Yet, I've frequently used LOWER() and LIKE to try to nudge field/text comparisons to be case insensitive, yet this whole time I didn't need to. SQL is not C#.

This guy speaks my words precisely.

http://www.bennadel.com/blog/723-SQL-Server-Text-Matching-Is-Case-INSENSITIVE.htm

Down to detail. "I am officially retarded," indeed, that's exactly how I feel. Well maybe if more people would blog this then less of us would feel so awful.

How to make it case sensitive? Here's a sample: http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/

SQL Server Express Edition and Dynamic Ports

by Jon Davis 17. May 2009 14:13

While transferring to my new web host, I ran into a roadblock that had me stalled for a day or two while I tried to figure out what was going wrong. I like to use my own laptop here at home when managing SQL stuff, so I poked a special hole in the Windows Firewall on my new VPS instance to just allow my IP on port 1433, while enabling TCP/IP for SQL Server and restarting the service. But I could not connect!

I spent several hours poking at it, rebooting it, turning off the Windows firewall completely (temporarily), trying to get connected to that darn SQL Server instance I had installed on my new VPS, but it just would not connect. Then I noticed that the client tools (namely SSMS) on the VPS itself could not connect to ITSELF on the TCP/IP stack, it could only connect on Shared Memory or Named Pipes. What is going on?!! Could it be a bad OS image on the VPS?

Eventually it got resolved. The VPS hosting company was very helpful in assisting me on the matter, and at the same time I got some replies on a SQLServerCentral.com forum post that narrowed down to the same problem: Dynamic Ports.

This SQLServerCentral.com forum reply was the critical new knowledge for this weekend.

Hello Jon,

SQL Server Express (2005 & 2008) defaults to Dynamic Ports, whereas the Default Instance of other Editions listen on Static Port 1433 (by default). 

Having a Zero in the Dynamic Port configuration will have been overriding the Static Port that you entered, and therefore causing your connection problems.

Regards,

John Marsh 
 

I'd never heard of dynamic ports before. And the reason why I've never run into this issue before was because I've always used the full version of SQL Server that comes with MSDN licenses, and that version, by default, does not use dynamic ports by default, it uses Port 1433, which I expected SQL Server to default to in my case. But for both SQL Server 2005 and SQL Server 2008, the Express edition defaults to use Dynamic Ports when TCP/IP connections are enabled. So all my setting of Port 1433 in the Configuration Manager was completely ignored because it switched right back over to Dynamic Ports. To make things even more confusing, to enable Dynamic Ports, the setting is set to '0'. That's '0', as in the programmatic standard for FALSE, just not in this case, as is thoroughly documented in SQL Server documentation. Heh heh.

So with the Dynamic Ports' setting of '0' removed, I'm up and running. And now this blog (not to mention other, more legitimate sites I run [sic?]) is finally (as of Sunday afternoon) moved off my home PC and onto my new VPS.

Annoying SQL Server 2008 Bugs That Are Kinda Ridiculous

by Jon Davis 25. February 2009 13:06

Generally, rewriting an interface is supposed to take all of the previous interface's measures of quality, or else it shouldn't be done. That rarely happens because it's no fun to go back to last year's drawing board when reviewing next year's software. But when “quality standards” are measured so acutely as in enterprise software, there’s no excuse. On this one, I have to give Microsoft one deserved thumbs down.

SQL Server 2008 is a really big software product--big in its feature set, big in its quality standards, big in market footprint and prevelance, and big in terms of just sheer size on the hard disk, in RAM, and in Windows config munging (registry bits, services, etc.).

To be fair, I haven’t come across many serious SQL Server 2008 runtime bugs; the only one I found was the Ctrl+Shift+M editor bug I blogged about earlier.

No, this bug is in the installation process. SQL Server 2005 had an annoying installer bug where the services wouldn’t be shutdown before service packs installed, resulting in a reboot warning, and the services wouldn’t be restarted before the Vista user configurator ran (which was difficult to find to retry after failing the first try due to the services being down). Even the most recent service pack had this problem.

But SQL Server 2008 has a new installer.

No, I take that back. SQL Server 2008 requires a new installer. This is complaint #1. After sitting and waiting for one or two minutes for SQL Server Express 2008 w/ Adv. Tools & services to extract, it finally pukes and says “Sorry, you have to have a new version of the Windows Installer” (equivalent text). And then it unloads. No links, no help, just leaving you out in the cold. WHY ON EARTH DOES MICROSOFT REQUIRE THE USER TO TRACK DOWN A NEW INSTALLER RUNTIME BEFORE SQL SERVER 2008 WILL EVEN INSTALL?!! One big, fat “WHAT WERE THEY THINKING?!” for Microsoft. That thing should have been bundled with each and every SQL Server 2008 installer option, including SQL Server Express, et al.

Once the new Windows Installer version is found, and the computer enjoys the tiresome reboot it demands, and I sit through the one or two minute extraction again, I have expectations for it to be a rock-solid installer and experience. But no, it looks and feels like a Windows 98 Active Desktop window; DHTML style links everywhere.

But the biggest “WHAT’S WRONG WITH YOU, REDMOND?!” moment comes when you fire up the Installer, from within the DHTML-esque launcher window, and then as you start to see the progressbar work its way across the screen, you Alt-Tab to the first window—the launcher window—and close it, to clear up screen resources and desktop space. It closes fine. But a few seconds later, suddenly the real installer (the one with the progress bar) pukes in your face, as it complains that a bunch of files are missing. Ohnoes! Is it a corrupt CD/DVD image or download?

Nope, Microsoft just let their QA staff take a vacation when the brand spanking new installer that Microsoft DEMANDED that you install was actually put together.

What essentially happens is the launcher app is what the initial extractor is waiting to finish before all the extracted installer files are cleaned up. Once the launcher app with the DHTML-esque interface is shoved in your face, and you click on a link, the launcher window is neither minimized nor hidden until the launched application finishes. Failing that, the launched app would need to be in a modal window so you can’t close it. But none of this is the case. So you have to learn by being burned that closing the launcher app will destroy the installer in progress, including potentially its rollback files. Wee.

But, once installed, I’ll give it to Microsoft that I’m still excited about SQL Server 2008. It’s yesteryear’s news, but I have a lot to learn about it still—post-installed.

UPDATE: In addition to restoring this post (not sure why I deleted it) I wanted to mention also that some people, including myself, have struggled with another problem with SQL Server just taking forever—as in, like, 24 hours-ish—to install. I found the cause of this in the SQL 2005 days, and the same cause probably still exists in 2008. Basically, the ACL (Windows Access Control List) of each and every DLL and registry addition is manually verified by the installer against Windows Security / Active Directory. On a standalone machine, this is instantaneous, but in an Active Directory enterprise environment, what happens is the verification is sent to the AD controller, and the AD controller then echos the verification request to every tree in the forest!!

The official workaround to this for AD admins is to configure AD not to replicate the verification requests (it’s some weird setting buried deep in the AD settings, I don’t remember the MS Support link) when the forest is deemed too large to handle it. This will allow the AD controller to be self-sufficient in replying to these verifications all on its own.

But my own workaround was simpler: disconnect the network cable before installing, and reconnect it after it is installed.

The official workaround should have been that Microsoft just go give itself a spanking and come back when it is truly red-faced sorry for verifying the ACL on every single file and registry edit that it imposes.

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Tags:

SQL Server

EntitySpaces 2009 Q1 WCF Demo

by Jon Davis 25. January 2009 16:45

I created a new WCF demo for EntitySpaces, one of the most popular ORM solutions available for .NET which now comes with its own code generator (no longer relies on CodeSmith or myGeneration). The demo is bundled in the Release Candidate for v2009 Q1. (The developer version is released, trial version will be released tomorrow.) This one includes both console and Windows Forms clients, and a console-based service, for showing the barebones basics of what it takes to get EntitySpaces working with WCF. Both full proxies (EntitySpaces runtime libraries referenced on the client) and lightweight proxies/stubs (*no* EntitySpaces runtime libraries referenced on the client) are demonstrated, but the lightweight demo is currently limited to a console app.

Next on my plate will be a WPF demo for the lightweight proxies/stubs. No guarantees...

Anyway, here’s the documentation that went with the demo. It got posted on the EntitySpaces blog.

http://www.entityspaces.net/blog/2009/01/25/EntitySpaces+2009+And+WCF.aspx

Currently rated 5.0 by 1 people

  • Currently 5/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Tags:

C# | Cool Tools | Pet Projects | Software Development | SQL Server | WCF


 

Powered by BlogEngine.NET 1.4.5.0
Theme by Mads Kristensen

About the author

Jon Davis (aka "stimpy77") has been a programmer, developer, and consultant for web and Windows software solutions professionally since 1997, with experience ranging from OS and hardware support to DHTML programming to IIS/ASP web apps to Java network programming to Visual Basic applications to C# desktop apps.
 
Software in all forms is also his sole hobby, whether playing PC games or tinkering with programming them. "I was playing Defender on the Commodore 64," he reminisces, "when I decided at the age of 12 or so that I want to be a computer programmer when I grow up."

Jon was previously employed as a senior .NET developer at a very well-known Internet services company whom you're more likely than not to have directly done business with. However, this blog and all of jondavis.net have no affiliation with, and are not representative of, his former employer in any way.

Contact Me 


Tag cloud

Calendar

<<  August 2020  >>
MoTuWeThFrSaSu
272829303112
3456789
10111213141516
17181920212223
24252627282930
31123456

View posts in large calendar

RecentPosts