Pat Egen’s Weblog

Ramblings, chats, thoughts, ideas

Archive for June 2010

Output SQL queries to CSV

with one comment

Yesterday, I got an email from another consultant who wanted to know a way to export SQL queries to CSV files. After I formulated my response to him, I decided it would make a good and hopefully helpful blog article as well.

There is actually a couple of ways to do this. First off, and by far the easiest, is to use SQL Management Studio. Open Management Studio and create a query by clicking New Query and then type in your SELECT statement. Recently I had to do this to find some bad phone fields. I created my query, and sent the output to a CSV file to send to the client. The database had over 23000 contacts and looking for bad phone numbers couldn’t be done via the normal lookup. The fields were “funky” (technical term) because of a failed import and ACT wasn’t finding the bad fields via the normal mechanism.

Once you have created your query, do the following:

1. Right-click down in the result records section

2. Select “Save Results As…” and choose either CSV or Text

It’s that simple. However, there is a caveat. If you are using SQL Management Studio 2005, you need to do the following to ensure that quotes are added to your output (to handle commas in the middle of fields). It isn’t set that way by default. Also, depending on which version of SQL Management Studio you are running, you may not have the option to save the results to text.

*     Tools – Options – Query Results – SQL Server – Results to Grid.

*     Check the “Quote strings containing list separators when saving .csv results” option.

This is especially important if you have fields that have commas like a Company. No quotes means your imports go wonky (as my Australian friends say). In other words, the commas throw the fields over by one putting things in wrong places – i.e. nastiness.

The second way to output is great to know if you are not running Management Studio. You can do it in your SQL Select statement. Here’s an example.

*     SELECT * FROM CONTACTS

*    WHERE email = NULL

*    INTO OUTFILE ‘missingemails.csv’ FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’;

That last line is the trick. Also, the FIELDS TERMINATED BY is what adds commas between columns. The LINES TERMINATED BY ends each line and makes them separate rows of data.

As usual, it’s helping others that gives me inspiration for my blog posts – which I keep vowing to do more often. Thanks out to David for providing today’s inspiration.

Ciao for now.

Advertisements

Written by pregen

June 17, 2010 at 4:01 pm

Posted in Uncategorized

Quick SQL Select Trick to add leading zero for zip codes

with 7 comments

Working on a Salesforce.com to ACT! by Sage conversion today and realized the CSV files from Salesforce didn’t create a true zipcode column.  This particular client had a lot of zipcodes with leading zeroes – which of course were truncated during the import into SQL.

So, here’s the trick for the day.  In SQL Management Studio, I created a SQL statement (with Don’s help of course) to interrogate the zipcode field and then add a Zero if the field was less than 5 characters.  Here’s the statement – you will obviously need to plug in the right table and field names to make it work in your environment.

UPDATE dbo.[Contact]

SET [MailingPostalCode] = ‘0’ + LTRIM(RTRIM([MailingPostalCode]))

WHERE LEN(LTRIM(RTRIM([MailingPostalCode]))) <5

The first line above sets the table name.  The second line defines the field to be updated (in the SET) and assigns to that field a zero (that’s the ‘o’ part) plus the remaining part of the Zipcode field.

The SQL Trim feature  functions (LTRIM and RTRIM) are useful to ensure we are only getting clean data – i.e. these functions remove leading and trailing blanks from a string.  For example, the function LTRIM(‘ 1456′) returns:’1456’ with no leading space.  You do this to ensure you really only have clean numbers without spaces to concatenate with the leading zero you are adding.

The final line says do this only when the Zipcode is less than 5 (<5).

So, import a file into a database and find out you are missing zeroes? Well, then import into Access or SQL and run this nifty command.  Works like a charm.

Once again, thanks to my SQL king, Don.

Written by pregen

June 15, 2010 at 4:20 pm

Posted in Uncategorized

ACT for Web tips

with 8 comments

ACT by Sage for the Web is a pretty cool application.  It is a great way to access your ACT data from a browser which is convenient when you are not near your main computer.  In the process of working with the tool, we’ve found a few tricks and recommendations on improving the installation and performance.  I wrote it up in a document and thought I’d post it here as a blog article.  Note – this is going to be long but the idea is you can reference it later or print it for future review.

Overview

The installation and configuration instructions in the recent ACT! Premium for Web Administrator’s Guides (shipped with the product) are actually pretty good.  If you are Very Familiar with Windows Server administration and particularly the IIS and ASP.NET components you will do fine with just those instructions.  These notes attempt to point out things to check and be aware of that are not explicitly mentioned in the Administrator’s Guide or are glossed over in that manual.  Also, these notes reflect my experience with my own ACT! for Web installation and experience with our own customers but should by no means be considered to cover every variation possible of ACT! Premium for Web installations.  In any case, consider the Administrator’s Guide a Must Read.

Also, anytime you run into a situation that is not covered in the Administrator’s Guide or these notes, remember the first place to check should be the ACT! KnowledgeBase on http://www.act.com.

ACT! Premium for Web is supported for installation on both Windows Server operating systems as well as Windows Desktop (Client) operating systems.  Installation on a desktop operating system is not recommended for production use and I support that recommendation.  However, I run ACT! Premium for Web on Windows XP (a client operating system) for testing and demonstration purposes and it runs fine.  These notes will point out differences in the client versus server operating systems and configurations where appropriate.

My experience is primarily with ACT! Premium for Web on Windows Server Server 2003 variants.  We are actually now doing more work on Windows 2008 server 64 bit machines as well and I will post another blog entry  a bit later about what we’ve learned in that arena.  I assume that most of these notes are applicable to Server 2008.

These notes assume that this is an ACT! Premium for Web installation where the web server is the same machine as the SQL data base server.  If the installation is being split, then it is more work and there are other considerations necessary in configuring the web server and the ASP.NET impersonation user ID.  The Administrator’s Guide goes over some of those considerations.   All of our ACT! Premium for Web installations have been single server setups.

Preparation

ACT! Premium for Web depends on the Microsoft IIS (Internet Information Server) component of the Windows operating systems and also the ASP.NET component.  These components serve as the platform on which the web component operates.  ACT! Premium for Web will not function correctly if these components are not installed and enabled.

IIS is installed by default on most Windows Server 2003 (and 2000) installs.  IIS is usually NOT installed by default on most Windows XP or Windows Vista desktop installs.  The ASP.NET component also may or may not be installed by default along side IIS.  Verify or install as follows:

Windows Server
–   IIS and ASP.NET are most easily installed using the “Manage Your Server” tool.  They are both installed by adding the “Application Server” role to the server.  Add this role via “Manage Your Server” if this role is not already displayed.
–   Sometimes an IIS installation or installation of a service pack or security patch will turn off the ASP.NET extension in IIS as a security exposure.  Well, it actually is a security exposure, but is required for ACT!  Premium for Web so get over that and make sure it is turned on.  Either select “Manage this application server” from the “Manage Your Server” tool, or start the IIS Manager tool directly from Start/Administrative Tools/IIS Manager.  Once there, expand “Internet Information Services” the “local computer” then click “Web Service Extensions” in the tree view.  This will show the installed extensions and whether they are enabled or prohibited.  Any line that starts “ASP.NET” needs to show “Allowed”.  If it shows Prohibited, select that entry and click “Allow” in the right hand window.

Windows XP
–        IIS and ASP.NET are installed via “Add/Remove Windows Components” option on the “Add/Remove Programs” object on the Control Panel. On the main panel, make sure “Internet Information Services” is checked and drill down and make sure that at least “World Wide Web Service” is checked.
–        ASP.NET will be installed and enabled as part of the installation of the .NET Framework later.

I actually recommend installing the .NET Framework 2.0 now, before installing any part of ACT! Premium for Web, to get that prerequisite in place.  Install either from the ACT! Premium for Web CD1 (Dependencies folder) or direct download from http://www.microsoft.com.

Once IIS and the .NET Framework 2.0 are installed, I recommend running a Windows Update and get any and all security patches and updates for both the base Windows and the IIS and .NET Framework components.  I Do Not recommend installing Service Pack 3 for Windows XP.  I have had trouble installing various ACT! versions on Service Pack 3 so stick with Service Pack 2 and patches for that level that have been released.  There is only Service Pack 2 for Windows Server 2003 so stick with that.

Some customer IT administrators may want to see ACT! Premium for Web installed into a non-default IIS web site or configuration.  Tell them to get over it.  ACT! Premium for Web installs to the Default Web Site using port 80 and to virtual directory /APFW.  It can be moved later (see the ACT! Knowledge Base item 14868) but frankly I would not recommend it.  There is some configuration changes that can be made and I will mention them later.

Installation

The Administrator’s Guide recommends creating the ASP.NET impersonation account before installing the software.  I find it does not really matter when you do that task.  If you are doing an upgrade installation, the impersonation account already exists.  If necessary, ask the old installation of ACT! what the account logon is if you do not already know it (use the Tools/Web Site Administration panels).  Potentially you will have to reenter the login ID and password on the new installation.  If this is not an upgrade installation, you can wait and create the account after you have the ACT! software installed.  I will go over hints and tips about the impersonation account later.

Make sure you are logged on the server as an administrator.  If the server is not a Domain controller, a local administrator account is sufficient.  If the server is a Domain controller, you really need to be a Domain Administrator.  I won’t debate the issues of using a Domain controller as the ACT! Premium for Web server.  Many shops have a small number of servers and the Domain controller is either the only server or the only server accessible from the external internet, so practicality needs to override other desires sometimes. I have successfully installed and configured ACT! Premium for Web at the server console, logged on via Administrative Remote Desktop, and via other remote control software (GoToMeeting and GoToAssist being used most heavily).

If this is an upgrade, I recommend making a final backup of existing ACT! data bases using the old version before installing the new version.  This is the best way to ensure that you can restore these backups with the old version of ACT! later if you find you need to fall back.

Let the installer guide you through the installation of the appropriate SQL Server version and the actual ACT! Premium for Web code.  If this is an upgrade from one version of ACT! Premium for Web to another, follow the instructions in the Administrator’s Guide as to whether a deinstall of the previous version is required before the install of the new version.

Sometimes an upgrade of ACT! Premium for Web ST does not ask for the 2nd CD (the SQL Server Standard installation) and then gives messages that SQL Server Express will be installed.  The level of SQL Server was actually regressed in my own installation when moving from version 2005 to 2006, but the upgrade in place from 2007 to 2008 indicated that SQL Server Express would be installed but the existing SQL Server Standard was preserved and everything went well.  So I suggest trusting the installer even if the messages get a bit screwy.

At the point where the code is installed, bring up the ACT! Premium for Web GUI on the server.  Go through the registration process, then verify that you can open the new ACTxDEMO data base.  Any problems at this point get diagnosed the same way one would go about troubleshooting an installation of ACT! desktop.  We want to ensure that the program code and SQL Server instance are communicating.

If this is an upgrade, now is the time to open the old version data bases and upgrade them to the new schema versions.  This has to be done before you attempt to access the data bases from the web server, as the web server will blow up big time when it hits a schema mismatch.

Configuring the Web site

Now comes the point where ACT! Premium for Web is different from the desktop install and that is configuring the web server and configuring data bases to the web site.

Make sure you have a data base that is working with the ACT! GUI before continuing to attempt to configure the ACT! Premium for Web web site.  Make sure the data base is marked as Tools/Database Maintenance/Share Database.  Use the ACTxDEMO data base if necessary.  I usually configure the ACTxDEMO data base to the web server, at least initially, to support testing of the ACT! Premium for Web installation.  It can be removed later if having it publicly exposed is not desired.

The biggest hurdle in configuring the ACT! Premium for Web web site is the first two tabs – Web Server and User Account.  If this is an upgrade, everything previously configured should carry forward.  Just do the two tests as described in the Administrator’s Guide.

If the Web Server test fails, make sure that IIS is installed, the service is running (do Start/Run/IISRESET to see if it can be stopped and restarted), and that ASP.NET is allowed in the IIS Manager tool as described earlier.  Usually these fixes will get you past this part.  If necessary, see if the IIS server responds to anything.  Open up a web browser on the server and type http://localhost.  You should get a screen that you web site is under construction (client OS), or the base welcome page (usually a Small Business Server variant).  This says that IIS is there and listening at least.

The next step is to create and set the ACT! for Web impersonation account.  The required properties of the account are described in the Administrator’s Guide.  But, basically what works are the following.  If the machine is a client OS (Windows XP), you probably should use the user ID and password that you use to log on the system.  On a server system, it depends if the machine is a stand-alone server, a member server in a domain, or a domain controller.  If the machine is a stand-alone server or a member server, what I have found works best, if the data base server is the same machine as the web server, is to create a local Administrators account on the machine (not in the Domain/Active Directory).  This account then has the run of the machine but can’t get to resources outside the particular server.  I usually call my ids ActForWebUser and set the attributes “Password never expires” and “User cannot change password”.  If the server is a Domain Controller, then a local administrator account is not an option, so create a Domain (Active Directory) user and make it a member of the Administrators group and the Domain Users group but NOT a member of the Domain Admins group.

So enter the user ID and password into the User Account tab.  Remember that for a local administrator’s account, the domain will be the computer name.  Click “Test” and see if it works.  If the test fails, see the Administrator’s Guide.  You may need to change permissions in either the Windows Registry or the file system folders where the ACT! Premium for Web files are installed.  This is the APFW directory under the ACT for Web directory created by the installer (usually C:\Program Files\Act for Web\APFW).  However, if the user ID is correctly set up as a machine administrator this should not be necessary.

If you get this far, try adding a data base to the web site using the Add/Remove Database tab.  As I said earlier, I usually try adding the ACTxDEMO data base since it is pretty much a known entity.  Now try logging on to ACT! Premium for Web via the web browser.  Go to http://localhost/apfw .  You should get the ACT! Premium for Web log on screen.  Select the ACTxDEMO data base and you should be able to log on as Chris Huffman. Then try adding the real customer data base and try logging on to it.

One thing is, sometimes these configuration changes don’t seem to “take” immediately.  Try a Start/Run/IISRESET to restart the IIS service tasks.  That usually clears up things that should be dynamic but aren’t.  I have seen situations where the web site just won’t come up after configuring the ACT! Premium for Web web site administrator.  At that point what has cleared things up is a reboot of the server.  Try that if all else fails.

Now try accessing the web server from somewhere else in the network.  Now that you know the server works locally you are in a position to fight other network connectivity and potential firewall issues.

Fixing Performance Issues:

One way to validate that AJAX is not affecting performance is to try using Safari. This does not load new AJAX components.  Another trick would be disabling AJAX in the web.config file.  Add it in the web.config file which begins on line 61 shown as follows:

<!– This key either shows or hides the grid pager.
Options are: true, false –>
<add key=”AllowPaging” value=”false” />
<!– This key is used for setting the location of the paging labels.
Options are: top, bottom, both –>
<add key=”PagerAppearance” value=”top” />
<!– This key is used for setting the style of paging labels within the
paging area of the grid.
Options are: numeric, prevnext, quickpages, combobox –>
<add key=”PagerStyleMode” value=”numeric” />

Another trick for improving very slow web performance is to remove and replace the APFW folder on the server.  Here’s how you do that.

1. Stop IIS
2. Rename APFW folder under Program Files to OldAPFW
3. Copy contents of APFW from APFW 2009 (EX) CD1 – APFW2009PremiumEXCD1\ACTForWeb\program files\ACT\ActInstallDir\APFW same location you renamed directory to OldAPFW
4. Start IIS
5. Test
6. If it works delete the OldAPFW directory

Other tips that have improved performance revolve around the email history queue.  Go to %appdata%\act\act for windows 9\historyqueue.  It’s been found that if the  HistoryQueue folder in the ACT! for Web server was full of .xml files, ACT ran very slowly.  Delete all the XML files and try restarting IIS again.

Hope you find this helpful and look for an entry on working in the Server 2008 and 64 bit environment soon.

Written by pregen

June 14, 2010 at 7:30 pm

Posted in Uncategorized

Gmail Tip

with 2 comments

I am really behind in working on my blog since I’ve been on the road 3 straight weeks.  However, I’m going to post a quickie entry today because I stumbled on something cool.  Actually it was right in my face, but how many of us suffer from a scanning syndrome.  In other words, we scan a page without really seeing what is there.  Guilty.  That’s me.

To get mail forwarded to  my new Motorola Backflip, I have Gmail pick up mail from my various clients.  Then, I can get all mail on my phone in one place.  Cool.  Here’s the rub.  I get a ton of SPAM.  I mean a ton.  Since I have mail being forwarded from my POP account, which collects mail prior to my SPAM engine dissecting it, that means all the SPAM comes into GMail.  That’s not a bad thing because GMail does an even better job of identifying SPAM than my ASSP program does.  Ok so far.  The downside is GMail  does too good a job and puts valid emails into SPAM.  That means I have to go through thousands of SPAMs a day checking for valid emails.  Since you can only do 100 at a time it makes this a chore.

Real SPAM comes in flagged with the following text: [SPAM].  Items GMail “thinks” are SPAM don’t have this designation. I got the idea to do a search for mail with those keywords.  This worked but there was yet another wrinkle.  The search results only show 20 instead of 100 items  per page.  Grr.  That meant I was going through hundreds of pages deleting items.

Here comes the trick (and the “I need to be more observant note to self” Epiphany).  After selecting 20 on the page,  just to the right of the top line the following showed up “Select all conversations that match this search” – duh.  It’s highlighted in light blue so it’s not real easy to see, expect when you find out it’s there.  Eureka. Clicking that and then DELETE  deleted the thousands at one time. I have to confess – I didn’t find this on my own.  I found it doing Google searches for tricks to remove vast quantities of email.

I used to say, time and time again, “READ THE SCREEN” – you’d think I’d listen to my own preaching.

Ciao for now.

Written by pregen

June 6, 2010 at 3:28 pm

Posted in Uncategorized

%d bloggers like this: