Searching inside strings (nvarchar) in SQL using LIKE
Recently I created a simple report to allow users to search for Items in our Warehouse system that contained a word the user specified. The word the user was searching for would be contained in the Item table's Name column. Below is a simple example of how to do this using a LIKE clause inside the WHERE statement.
-Declare variable DECLARE @searchstring nvarchar(max) --Set values for search (You would normally pass this in with a report parameter) SELECT @searchstring = 'Widget' --Perform Search SELECT Items.id, Items.Name FROM Items WHERE Items.Name LIKE '%' + @searchstring + '%'
Note the wildcards added around the variable so that it will search for the user's string at any position in the original data. If @searchstring is NULL, the search will be ignored and in the above example, all rows will be returned from the Items table (this is useful in SSRS since the parameter can be made nullable and therefore optional). Warning: This is not optimized for speed, it is likely very slow on large record sets.
2011
Each year we add to our lives tends to bring memorable experiences, both positive and negative. Millions of events unfold during the days that make up a year but it is usually only remembered by a few impactful ones. This is my list of 2011 (in no order, except the first):
- The tragedy of Rebecca Dietz's passing
- Returning to Higher Education
- The Datex Conference in Florida with friends old and new
- The loss of an icon, Steve Jobs
- Realizing that Sushi is pretty damn good
- Spending September with someone I don't see often enough
- Learning more about myself and my habits
What is my 2012 resolution? Maximization of time.
Changes in SQL Server 2012 Licensing
The coming launch of SQL Server 2012 is bringing many new features and improvements to the product, but along with those improvements comes some interesting changes to the way SQL Server is licensed.
The most notable change is a move away from the traditional per socket licensing to a per core license model. For example: Standard Edition has moved from supporting a maximum of 4 CPUs to a maximum of 16 cores. With newer server processors available with 10+ cores, this move could seriously impact your licensing costs as you scale upward.
Next up is the introduction of the Business Intelligence Edition. This edition will feature many of the newer BI solutions Microsoft has been working on including PowerView, PowerPivot, Master Data Services, and the Semantic Model. While the traditional Integration Services and Reporting Services are still included in the Standard Edition, all of the newer "Self Service" BI tools will require an upsell to the BI Edition.
Lastly, Data Center Edition has be discontinued, and all of its features will be rolled into the Enterprise Edition. Along with this change, Microsoft has discontinued the Server+CAL licensing for Enterprise edition and licensing by cores will now be the only option.
For more details on the matrix of the new Edition's features, check out Microsoft's Edition page.
Step by Step: How to configure SmartUPS w/ Management Card and APCUPSD
APCUPSD is an Open Source and free alternative to the server shutdown software sold by APC. This step by step guide will help you configure the APCUPSD software for use with an APC SmartUPS (Ethernet based models) on a Microsoft Hyper-V server (or Windows Server Core).
- First you will need to configure your APC UPS Management Card if you have not done so already. This can be done through the Web admin interface with Ethernet enabled Management Cards. Refer to the instructions that came with the card to find the default IP address settings in order to connect to the Web Interface for the first time.
- Once you have the Management Card configured correctly and connected to an appropriate network that your server will have access to, make sure to add the IP address of your server you are configuring to the Clients list in the Management Card's Web Interface.
- Next will be to configure the PowerChute timers in the Web Interface. Depending on the current load factor and the capacity of your UPS the settings you chose may vary greatly. You can vary the Shutdown Delay and Return Delay to account for short power losses if they are common in your environment.
- Next, download the software at apcupsd.org and install. The installer will automatically create and start the APCUPSD service and install the tray utility (useful for non-Server core installs only) by default.
- Once APCUPSD is installed, open the config file located (by default) at C:\apcupsd\etc\apcupsd\apcupsd.conf. The config file is well commented and should help you with configuration if you have a setup that deviates from the ethernet based management card used in this guide. To configure APCUPSD for an Ethernet Management Card, you will change the following settings in apcupsd.conf:
UPSCABLE ether #Sets the connection type to ethernet UPSTYPE pcnet #Sets the connection protocol to PowerChute DEVICE upsipaddress:upsusername:upspassphrase #Tells APCUPSD how to connect to the UPS. Replace the values with what is correct for your environment LOCKFILE /var/lock #Sets the location of the lockfile UPSCLASS standalone #Sets the UPSCLASS to the appropriate type for a SmartUPS UPSMODE disable #Turns off sharing (only for ShareUPS models)
- Once you have saved the config file you can now configure Windows Firewall to allow APCUPSD to connect to the Management card. Execute the following commands on the server to configure the Firewall rules:
netsh firewall set portopening protocol=TCP port=3052 name=APCUPSD3052 netsh firewall set portopening protocol=UDP port=3052 name=APCUPSD3052 netsh advfirewall firewall add rule name="APCUPSD" protocol=TCP dir=in localport=3551 remoteport=3551 action=allow
- Now that APCUPSD and the Windows Firewall is configured, you can restart the APCUPSD service and test connectivity. To restart the service, use the following commands:
net stop APCUPSD net start APCUPSD
- To test connectivity (you may have to give the service 30seconds or so after starting before it connects), use the follow command:
apcaccess status localhost:3551
- If you see results similar to below, including the line "STATUS : ONLINE" you're up and running. Make sure you do a real power loss (pull the cord) test as soon as your environment allows!
Windows Phone 7
My 10+ year connection with Windows CE continues now with my most recent purchase, a Dell Venue Pro running Windows Phone 7.
I remember way back when Microsoft first released the Pocket PCs (aka Windows CE 3.0), I would travel to the Microsoft campus every other month for the Seattle User Group meetings to check out the coolest new devices and software. Back then it was Microsoft vs Palm and the world was much simpler.
Today the breath of choice in handheld devices (or more specifically, smart phones) is insane. With multiple, extremely robust operating systems and tons of amazing device manufacturers the space is innovating and iterating so fast it feels like the new Personal Computer.
That innovation and rapid rate of advancement is what has drawn me to the smart phone arena so much over the years. It is also what has of late had me so interested in Windows Phone 7. The Windows Phone platform had fallen behind the competition so much that it was beginning to look like a dinosaur - that is until Microsoft hit the reset button and created a truly different product.
Already after showing my phone to a few coworkers I am surprised how many have simply said "I can't believe how well I can READ this thing!". It then occurred to me how many times I've heard people complain about reading on such small screens over the years and I think it speaks a lot to the UX (user experience) design behind the phone, called Metro. A great focus on readability, typography, negative-space, and contrasting colors makes for a refreshing interface experience - and MAN is it usable!
Make no mistake however, this is a version 1.0 product. It has some sharp edges, some bugs, and it's lacking a few basic features. But compared to my last version 1.0 device (Nokia N900 with Maemo) this thing is extremely robust and the App Market is already flourishing. Microsoft has gone from department restructuring to global launch with multiple devices across multiple carriers in less then 18 months. Knowing that, I'm sitting here in great anticipation of what the next 18 months will bring.
PS: Thanks Microsoft Store @ Mission Viejo, CA for the surprisingly good customer support and the free stuff 
Storage Area Networks
Boy oh boy can these things be complicated. The plethora of options is mind boggling and once you have finally made a choice, it seems like you are reading configuration documentation for eternity. Fiber Channel vs iSCSI, RAID 5 vs RAID 10, 10K vs 15K, 2.5" vs 3.5", Vendor A vs Vendor B vs Vendor C, single vs dual controller, snapshots, replication, deduplication, multipath I/O, NFS, and on and on. I think this may be the most information I have ever crammed on, in such a short period of time.
So the true reason for writing this article, today we purchased an EMC ax4-5i iSCSI SAN array. We are planning to use it for two main functions, 1) Consolidate our disk-to-disk backup storage and, 2) Migrate our locally attached Virtual Machine storage to Shared storage so that we can use Failover and Live Migration functionality with Hyper-V.
We took a long hard look at the continuing cost of adding hard drives to our servers and also the cost of dealing with outages when our VM host server had problems. This led us to the search for an affordable but scalable entry-level SAN solution. EMC was able to provide us exactly that, for just under $5k with 5TB of physical disk space (2-3TB after RAID).
And with that, comes weeks of configuration and installation. All while we are piloting out a new Sharepoint 2010 implementation, working on an IT consolidation project for a sister company, and planning a migration to Exchange 2010. Should be an exciting quarter - that is, if business picks up...
The future of communication
One of the IT projects I was recently involved in was replacing our aging phone systems. Ultimately we decided on a cloud-hosted VoIP PBX from Vocalocity, but as with most journeys the end result was not the most interesting part.
With the exception of an old security camera system, our company is now completely IP based. Data, video, and voice all travel over a shared IP network via common switches and wiring. Moving forward this should greatly reduce wiring costs and the need for costly rip & replace jobs. Interestingly however, this has spawned some internal debate over the future of communications.
As the world moves away from land line phone services (please move faster, world), I feel the age old schema of telephone numbers and their routing paradigms are ready for death. Does it makes sense in today's age to have a business phone number, cell phone number, and home phone number? Why should my mobile phone have a fixed area code, that supposedly tells a caller where I am calling from? With the growing use of telecommuting this can be applied to business phone lines just as well.
When data travels over the Internet, it does not cost the user a single penny more to reach a site in another continent then it does his neighbor's home web server. Long distance (and even international) calling is a dying concept and I can't wait for its glorious day of defeat.
And that brings me to the most interesting part, if Long distance calling is an idea that will soon be dead, how will communications look in the future? I believe SIP addresses and Google Voice hold some important clues. A SIP address is an alternative to a telephone number for a VoIP based endpoint (think internet telephone). They can look much like an email address (ex: "sip:ross@oceancold.com"), but you can literally dial a SIP address to initiate a voice/video call. Google Voice on the other end allows a user to setup rules that allow them to screen calls. Rules can be setup to block certain callers, forward other callers directly to voicemail, or disable ringing different endpoints during different times of the day.
So with all that, here is my prediction for communications in 2025:
- Traditional desk phones will still exist but will morph into large touchscreen devices that are driven by your computer (laptop/desk pc) and are more of an accessory than a stand-alone communication device.
- Each endpoint (cell phones, desk phones, PC's) will have HD video cameras and every call will be capable of video.
- Traditional voice service on cell phones and land lines will be completely replaced by data services - after all why should a carrier maintain voice and data networks if data itself can also support voice and video?
- Phone numbers will be replaced with a globally accessible SIP/email type address. The provider issuing you an address (3rd party or the company you work for) will provide tools that allow you to screen based on the caller, domain (@oceancold.com), time of day, and your own availability/presence.
- The lines between Voice/Video/Text communication will completely blur as your one address allows Unified communication via all media forms and allows your contacts to see your presence info (out to lunch/away/etc) in real time.
- Your new unified communication address will allow for new levels of integrated communications such as desktop sharing, collaborative white boarding, and real time document sharing/editing.
These may be pipe-dream ideas but if (a very big if - given the fragmentation present today) the different players in the communication arena can come together with support behind a unified communication address, big things will be on the horizon.
It will never be complete…
That is the best way to describe a true Business Intelligence project. As business processes and applications change, the need for steady development and re-engineering of any centralized BI solution is a constant.
One such project has been a big time sink of mine over the past 18 months, growing and adapting to the changing landscape that describes our (Ocean Cold's) operations. Equally juxtaposed to the frequently changing business processes is the frequently changing inventory management system we are using, Datex Footprint. With each new version comes changes to the database schema and new entities that can be exposed through new Fact/Dimension relationships. A rapid software release cycle ensures that there is always new things on the horizon.
Ross Rydman
- LinkedIn Profile
- Serverfault Profile
- Stackoverflow CV
- Stackoverflow Profile
- Twitter Profile
- Work: Ocean Companies
Catagories
Twitter Feed
- Got mutliple monitors? Doing yourself a disservice if you don't use Ultramon and GridMove. -- 2 days ago
- #Apple is sitting on $100bln in CASH. And people thought Exxon was greedy... http://t.co/KX0qQbRD -- 2 days ago
- If I ever write something like this about Microsoft (or anyone for that sake) on a large blog, someone shoot me. http://t.co/bsU1bUiT -- 2 days ago
- World: Stop putting contact forms on your website if you NEVER REPLY TO THE EMAILS. Seriously. #youjustlostacustomer -- 2 days ago
- Insanely awesome use of CSS3. Open with Chrome - http://t.co/vO66OmfN -- 3 days ago