Create a single table from multiple tables with the same structure in SQL

If you're looking for a way to accomplish in SQL what an append query was to Access, you're in luck. The trick is the UNION keyword. This will combine two or more tables together into one. For example, say I have two database tables. Each table contains a list of people and includes their SSN, first name, and last name. The names of these tables are people1 and people2. Rather than having to look at each of those table individually, you can simply combine them with the UNION

SELECT * FROM people1 UNION SELECT * FROM people2

That will give you a single result set that contains all distinct records from both people1 and people2. If you had more tables you could just add more UNIONs

SELECT * FROM people1 UNION SELECT * FROM people2 UNION SELECT * FROM people3 UNION ...

One drawback with this is that UNION only returns distinct records. However, if you want to see everything from the tables you're joining whether it exists in more than one table or not, simply use UNION ALL instead. This returns all results, and does not require them to be distinct from one another.

SELECT * FROM people1 UNION ALL SELECT * FROM people2

This would return all rows from both people1 and people2, and if a person was listed in both tables they would show up twice in the result set.

If you're looking for a free SQL tutorial, or just more about using UNION, check out what W3Schools.com has to offer on their site. I've used that site for everything from SQL to HTML, and feel it has been one of the more helpful resources I've run across.

My Computer opens slowly and you get a flashlight icon

I had a user complain that her Windows XP computer starting running slow, and whenever she opened My Computer she would see a flashlight moving back and forth, but the drives either wouldn't show up or would take a long time. At first I expected it to be some type of spyware or adware, but that wasn't it. A quick Google after ruling that out brought me to the solution. Windows Image Acquisition, which is a service that can run in the background to make sure your computer is ready to recognize something plugged into it, such as a camera. Disabling supposedly solves the problem, and it worked for this particular user. For more details, here's how to do it yourself:

1. Right-click on the My Computer icon and select Manage
2. Expand Services and Applications
3. Click on Services
4. In the window to the right, scroll down until you find "Windows Image Acquisition". Right-click on it and go to Properties
5. Change the Startup Type to "Disabled" and click OK
6. Close the Computer Management window that you have open

Now go and give the My Computer icon a click and see if it still gives you a flashlight icon. In the case I dealt with, as soon as the service was disabled, clicking on My Computer brought up the system drives as quickly as it should have. Hopefully you have the same result.

Protect yourself from phishing and spoofed email

Curiosity doesn't just kill cats. It will kill your computer too if you're not careful. This post is a bit more generic than normal, but with a recent influx in spam hitting the mail server I manage I thought this might be helpful to more than just the employees at the company I work for. 

If you want to test your knowledge about phishing, take the Sonicwall quiz. It can be found at http://www.sonicwall.com/phishing/index.html. If you get 8 or more correct, then I’d say this post probably isn’t going to be useful for you so go ahead and read a different one. Otherwise you may want to keep reading.

To put the amount of spam out there in perspective, between 50-90% of all messages sent to the mail server I manage are spam. Worldwide, spam is said to make up 80-85% of all email sent, and some say that is a conservative estimate.

The first thing to be aware of is phishing. If you don't know what phishing is, the definition according to Wikipedia is "the process of attempting to acquire sensitive information ... by masquerading as a trustworthy entity in an electronic communication". This happens a lot, and if you have an email address you likely have received at least one phishing email. It’s easy to mask the actual site address with something that looks legit. Here is an example. At first glance it looks like I put two links to the Wells Fargo home page, but can you tell what’s wrong with them?

http://www.wellsfargo.com

http://www.wellsfargo.com

The top link takes you to Wells Fargo, just like you’d expect. The second link will bring you to the Google homepage, even though it looks like it should go to wellsfargo.com. Checking where a hyperlink goes before clicking on it is a good practice to adopt because it can lead to safer web browsing and instant messaging. It’s also a great way to avoid getting any type of viruses or malware. There should never be a link to files that end with .exe, .msi, or any other executable file extension unless you are expecting it from a software vendor. If you accidentally click the link, you should not agree to let anything be installed if you get prompted by the Run/Save/Cancel box. Normally to see where a link goes you can either hover over it with the mouse to see it displayed as a small information box, or hover over it and check the status bar at the bottom of the window. You can try it with the wellsfargo.com links above.

Here’s another one (not a real page, just an example):

http://www.paypal.com-account.login.mywebsite.com/login.asp

Looking at it quickly, you may think this takes you to paypal.com’s login page. However, the secret is to pay attention to the periods and forward-slashes /. This will actually direct you to somewhere on mywebsite.com. Everything else is just fluff to redirect you to a particular page, and the www.paypal.com part is used to make the link look legit. If it had been http://www.paypal.com/account.login.mywebsite.com/login.asp, this would be a page within the paypal.com site because of the / after paypal.com. Technically this URL could still be a problem, but this would only occur if the computer you were using was either severely infected, or if someone hacked Paypal.com itself, both of which are unlikely to go unnoticed.

For more information about Phishing check out any of the following links:

http://www.microsoft.com/protect/fraud/phishing/symptoms.aspx

http://en.wikipedia.org/wiki/Phishing

Spoofing is also very common, and I bet almost all of you have some experience with it. Spoofing is the practice of impersonating another email address, which then makes it look like someone else sent the message. If you ever check your Postini junk mailbox you’ll probably find emails in there with your own email address, but the content in the email is about prescription drugs or some other fake ad. This is classic spoofing. It’s very easy to do, and really hard to prevent. The reason it’s difficult to control is that the safety of your email address doesn’t just depend on you, but rather anyone you’ve ever sent an email too. If any of them happen to get infected by some type of malicious software that steals their address book or email addresses, your email is then easily spoofable. It’s so simple to do, practically any computer could be setup to spoof addresses from anyone. There are certain things that can be done on a mail server to detect and reject spoofed messages, but it’s not foolproof and still requires users to follow email best practices. The main thing to remember with spoofing is that just because an email says it’s from someone you trust, that does not make it legitimate. For more about spoofing, check out its Wikipedia entry

Here is the quick list of the 3 most important things to remember when it comes to safe email habits:

1. Before you click on anything, make sure it’s directing you to where you expect to go

2. If you don’t know what something is, don’t click it regardless of who it says it’s from. This goes for both attachments and links

3. Use your best judgment when deciding whether an email is legit or not. If it looks suspicious, it probably is. Also, don’t automatically trust the name in the From field because it could easily have been spoofed


Of course, you should also be running some type of anti-virus and possibly anti-spyware software on your computer to prevent infections as well. If you don't already have something, check out one of my older posts about some free software packages that will help keep your computer from getting infected.

Change "Out of Office" message for another user

This allows you to change the "Out Of Office" message for another user, and also specify other settings for them, without the need to know their password. This requires that you have Outlook Web Access enabled (OWA for short), and of course you have to have access to the other user's account. The specific permissions needed would be "Read permissions" and "Full mailbox access", which can be set for the user in the Exchange Advanced tab in their Active Directory account properties. The default Administrator account likely was granted these permissions by default as well, so if you have that account enabled and would rather use that to login that should work too.

Doing this is surprisingly simple, and much easier than resetting the user's password so you can access their account. First, login to your OWA account with the user account that has mailbox access to the account you want to modify. Once logged in, in the URL, add /username/Inbox to the end of it. For an example, when I login as the administrator, the URL of my Inbox is https://owa.mydomain.com/exchange. If I want to change the account for a user named User1, then I would change the URL to be https://owa.mydomain.com/exchange/User1/Inbox and then go to that page. That should take you to User1's Inbox via OWA, and from there you can go into the Options and set the Out Of Office assistant to respond however you need it to.

This works for accounts that are disabled as well, without the need to re-enable them. This can be very helpful if you want some type of auto-reply for users that are no longer at the company you work for without having to get into server-based rules. Thanks go to lazynetworkadmin.com for their post about the same thing

Fix iPhoto error "You made changes to your photo library using a newer version of iPhoto"

Firstly, if you actually have gone through the process of upgrading iLife or iPhoto and receive this error, this article likely won't help. You can try a permissions repair, but that's about the extent of what you'll find for that problem here. Now, if you haven't done anything to iPhoto but starting getting the "You made changes to your photo library using a newer version of iPhoto" error out of nowhere, then hopefully this will help you out.

I had a Mac OS X 10.4.11 user report that she was having issues getting iPhoto to open. When she opened it, she would get an error "You can not open your current photo library using this version of iPhoto. You made changes to your photo library using a newer version of iPhoto." The only thing she could think of is that she had hooked up a different camera than normal to get some pictures from it, and started getting the error after that.

I tried rebuilding her iPhoto library, but that didn't work. Repairing permissions didn't help either. Then I actually tried to verify the disk using Disk Utility, and it found some minor errors. You can't repair those errors while OS X is running though. I booted from the OS X 10.4 install disk that came with the computer, ran Disk Utility to repair the disk, then rebuilt the iPhoto library again. Viola! iPhoto launched and worked fine after that.

Here's how to do each of those things:

First, check your disk permissions. Hopefully that's all it is. To do this, go to Applications->Utilities->Disk Utility. Within Disk Utility, select your hard drive and and select Repair Permissions. Let that run. Once it's done, try iPhoto again. If it still doesn't work, continue to the next step.

Within Disk Utility, now try using the Verify Disk option. Let that run. If everything is green and no errors are found, then you have a different problem. If you get anything that shows up in red though, errors were found and you should keep reading.

To repair the disk you have to boot from your OS X install disk. To do this, put the disk in the drive and shut down the computer. Now turn the computer back on and hold down the C key on the keyboard. This will tell the computer to boot from the CD-ROM drive, rather than your hard drive. This should bring you to the installation setup screen which asks you to pick a language. Pick your language of choice and then click the -> arrow to continue.

Do not go any further with the install. At this point you just want to launch Disk Utility from the CD and use it to repair the disk. You can get to Disk Utility by going to Utilities on the top menu bar.

Once Disk Utility is open, select your hard drive again and click on Repair Disk. That should run and possibly run again after the repairs are made. If Disk Utility cannot repair your disk write down what the problem was. If it says something about an invalid node structure, check out my other post on how to fix it. If it's some other error, you'll have to look to Google and see what you can find. If you can't find a fix to the problem you may want to look into replacing your hard drive before it completely fails. Once you're done with Disk Utility, go to Disk Utility->Quit Disk Utility. Then go to Installer->Quit Installer. Your computer should reboot.

Things should load like normal, and you can try rebuilding your iPhoto database now. To do this, hold down Apple+Option when launching iPhoto, and continue holding both of them down until it prompts you to select the rebuild options. I checked all 4 boxes that were shown and clicked Ok. At this point you just have to wait for it to finish. It took ~30 minutes when I did it, and that will vary depending on the amount of photos you have in your iPhoto.

Once the iPhoto database rebuild is complete you'll find out whether this worked or not. If you're lucky and iPhoto opens correctly, you have successfully solved your problem and can go back to using iPhoto for organizing your pictures. However, if you again get the error about a newer version of iPhoto, sorry, but you're on your own now. I'd suggest making sure to backup your iPhoto library before doing anything else to make sure you don't lose any of your pictures. You can find it by going to Users->Your username->Pictures->iPhoto Library, but replacing "Your username" with whatever your particular username is on the computer.

If this works, great. If not, but you happen to find something else to solve your problem, please leave it in the comments so it can help anyone else that runs into this issue. Good luck!

QR codes and smartphone use (iPhone, Android, etc)

QR codes are an interesting thing I've come across this week. They are apparently becoming more popular with smartphone users as QR reader applications are being released. They can encode URLs, phone numbers, and other textual items into this barcode format, and then using a QR reader application an individual can retrieve that information and use it.

There are some free QR code generators out there, and I've been using http://qrcode.kaywa.com/. On my iPhone, I downloaded a free app called i-nigma. i-nigma has versions available for other phones as well, so if you're interested go to http://www.i-nigma.mobi from your mobile device to see if they have a version for your phone.

The way it works is that your phone must have a camera. The camera is used to capture the QR barcode, and you can then go to the URL encoded within the QR barcode. With the i-nigma version I have on my iPhone, I didn't even have to take a picture. The program would just tap into the camera feature and recognize the barcode in the viewfinder. It worked very well for what I tested.

This presents an opportunity to increase web traffic to particular pages, especially if you are running an e-commerce site. You could use QR codes in print ads to direct readers to a specific website related to the ad. Google is already using it to encode business locations on actual maps, to make it easy for people to jump to that location within Google Maps. There are a lot of possibilities for these QR codes, and as long as there are free code generators and applications to read them it might be something to think about using.

Of course there are limitations and cons to the idea. You have to make sure the code is large enough for a device to be able to read correctly. The smallest I was able to read was 150px X 150px, which can be kind of a large barcode. I'm using an iPhone 3G though, and I know some of the other phones on the market now have better resolution so they should be able to read smaller codes, and phone cameras continue to improve. You also have to rely on the target audience to have a smartphone, along with a QR code reader application. As long as the applications are freely available though, it should only be a matter of spreading the word about these applications and hoping more and more people turn to smartphones.

I'm no marketing expert, but I can see this as a quick way to promote products and pages to those people out there with smartphones. Since Google is already starting to use it, it's bound to gain momentum as more and more people find out about it. With the number of smartphone users out there (~34 million iPhones sold through Q4 2009, with 6.4 million in the US), it's definitely a target audience worth trying to gain a share of.

QR code for http://techierambles.blogspot.com

Convert numbers to barcode for FREE

The situation I faced was a user who was manually entering a bunch (over 1,000) of inventory numbers from a spreadsheet into our ERP system. They wanted a way to help avoid errors, while also making it quicker to enter the numbers. Converting the cell values into barcodes would allow the user to scan the barcodes with a barcode scanner, essentially allowing them to cruise through the entry of the numbers with very no chance of typos. Surprisingly, this is very easy to do with the help of a freely available barcode font called Free3of9, which creates a Code 39 bar code for you.

First, download the Free3of9 font from http://www.barcodesinc.com/free-barcode-font/. Then unzip the contents and install the fonts on your computer. If you need help, check out my previous post about installing fonts. Once you have Free3of9 installed on your computer, converting numbers to bar codes is as easy as changing the font you're using to Free3of9. However, you must make sure you surround your value with asterisks. This is because the barcode reader needs to know where to start and stop reading, and this particular font uses the asterisk * to send that signal.

A quick way to do this in Excel or OpenOffice.org Calc is to use the concatenation function. Let's say cell A1 equals 123, and you want to turn that into a barcode. In cell B1, you would enter { ="*"&A1&"*" } (everything inside the brackets {}, but not including them). This would add an asterisk before and after the contents of A1 to create a value of *123* in cell B1. Then change the font used in B1 to be Free3of9 and you'll have a fully readable bar code that will produce the value 123 when read by a barcode reader device.

Good luck, and hopefully this helps save you or someone you know from a lot of manual number entry. You can try this with text, but I haven't tried that so I can't guarantee it will work. I believe you have to use the Free3of9 Extended font, which is included in the download, in order to encode non-number text. The same asterisk rules would apply.

UPDATE 5/3/13

Wow, it's been over 3 years since I originally published this and I've come back to it many times. I have something to add that I ran into today though. If you're using this font in Microsoft Word , be careful. The reason is Word adds a carriage return character at the end of a line of text, and normally you wouldn't see it but this font actually has a value for carriage return so it will encode that into the barcode. If this happens the barcode will not work. I did a mail merge and to get around this problem, after inserting my merge field that was the UPC value, I added a space after it before the end of the line of text and then changed my font to Free 3 of 9. What this does is prevents the system from auto-selecting that carriage return and changing the font on it to Free 3 of 9. You'll know if that happened by looking to the right of the merged field. If you have <> and immediately to the right there shouldn't be any text but you see a barcode, that's because the carriage return font was changed. To fix, either add the space before, or change the font for that little section back to what it originally was. Once I did that the barcodes worked just fine out of Word

Using "Run As" from the command line

I needed to make some changes in Registry Editor on a machine, but didn't want to log out of the users account to get in as the admin. It's good that you can open a program such as Registry Editor via the Run box using the command "runas". Doing so allows you to start an application while providing administrative credentials, without the need to log out of the currently logged in user. In most cases you can right-click an application icon and choose "Run As...", but for something like Registry Editor you would either have to save a custom MMC or use this runas method.

The syntax is:

runas /user:localhost\administrator regedit


This will then prompt you for the administrator password, and assuming you know it, will give you access to the Registry Editor as an administrative user. You could also write this into a batch file and use that as a shortcut if you didn't want to type this into the Start->Run box each time you needed it.

To see the original article I found outlining this, along with more information about the batch file, check out this  www.tech-recipes.com article.

View message size in Outlook Web Access

I had a user with access only to Outlook Web Access who was trying to sort the message by size, but there didn't seem to be a column for that within the OWA interface. I did some quick searching and saw a lot of people saying that it's not possible. However, I was surprised at how simple it is to do after reading how many people said it couldn't be done. This post pertains to OWA running on an Exchange 2003 server, and you also have to be using the premium client which only runs within Internet Explorer. Other browser will use the basic client and that doesn't allow you the same flexibility as the premium client.

1. Login to your mailbox via the Outlook Web Access webpage
2. At the top of the page to the right of where it says Inbox, click and change the view to Messages

That's it. Now you should see a column labeled Size that you can use to sort the messages. If you're looking for other options, try one of the other predefined views available from the list.

Remove or upgrade a Macbook Pro hard drive

I had a user with a Macbook Pro who had a hard drive that was about to fail. The hard drive was almost full too, so I wanted to make sure to upgrade it in the process. I was surprised at how easy it was to do, and found a very helpful guide with pictures for disassembling the Macbook. Normally I'd repost the disassembly instructions here, but the guide at www.extremetech.com was done so well I'll redirect you there instead.

I removed the existing hard drive using the guide, then cloned it to the new hard drive using Disk Utility from a Mac desktop. I put one of the drives right inside the case, and used a SATA to USB converter  (~$20 for a combo IDE 2.5", IDE 3.5", and SATA to USB, and well worth it) to plug the other drive in. From there I was able to restore the existing drive on to the new drive. It looks like even though the new drive had a much larger partition (80GB existing to a 250GB new drive), Disk Utility took care of that as well and I have a full 250GB of usable space on the new drive after the restore. After I put it all back together and booted up the laptop, everything seems to be working ok and now the user has an extra 160GB to fill up and the clicking is gone.