Daves Blog

There is a question that has come up a few times with some of our customers.  This is the scenario:  A ticket is run for a customer, who pays with a debit card (not a credit card), and the ticket is completed.  Then, for some reason, it is desired to void the ticket.  If the ticket was paid with a credit card, or any other way than via a debit card for that matter, this is possible.  However, if you attempt to void a completed ticket that was paid with a debit card, you get a message that this cannot be done.

The reason for this, is that debit cards and credit cards are processed differently.  With a credit card, at the time of the transaction, the charge is authorized.  When this happens, the credit card processor verifies that the card is valid, and has sufficient credit available.  Additionally, the card issuing company is made aware of an upcoming charge, and the amount.  Basically, when the charge is authorized, there is an agreement that the funds will be transferred at some point in the future.  However, the actual transfer is not initiated until the credit cards transactions are settled.

So, with a credit card transaction, if the ticket is voided after the fact, and before the transactions are settled, what happens is that transaction drops out of the records to settle.  With no settlement for that transaction, the actual transfer of money is not started.  That is why sometimes a customer will see a “pending” transaction for several days, until the authorization expires.

With debit cards, it is a little different.  The processing of a debit card does not generate an authorization, but actually initiates the money transfer request.  This does not mean that debit transactions will be received by the merchant any quicker, as the actual transfer takes place at a later time.  However, it does mean that at the point the transaction is done, that the transfer will take place the next time that the issuing company processes its transfers.  

In order to void a completed debit transaction, then, a reversing transaction would need to be generated.  It is not a matter of just not settling the transaction.

So, how do you deal with this issue?  Do a return ticket, for the identical items that were sold.  Then, use the same debit card for the payment (credit for the return) that was used for the original ticket.  This will create the reversing transaction, to balance everything out.  Be aware, however, it may take a day or two for both transactions to process all the way through.

In today’s world of spam and phishing, it can be very useful to be able to find out where an email actually came from.  Just because it says that it is from "someone@domain.com", does not mean that is who sent it.  Nor, does it even mean that it came from anyone at "domain.com".

What can not be faked, however, is the delivery trace.  All emails contain headers that log the date, time, and IP address, for every computer that had a hand in delivering that email.  From the originator, to their mail server, to the next server, and so on until it arrives at your mailbox.

The trick is to get to and interpret those headers.  Each email client differs in the manner that you get to these headers.  In Outlook (depending on the version), you right-click on the message and select "Options".  In Eudora, you click the "BLAH-BLAH" button,  Sometimes getting to the headers is the hardest part.

Assuming you have been able to see the mail headers, what are you looking for?  The "received" headers.  The following is an example of a "received" header (with some data obscured for security reasons):

    Received: from mailserver.domain.com (resolved.name.of.server [192.168.1.1])
            by mailserver.somedomain.com (8.14.8/8.14.7) with ESMTP id s4TEU1RO007434
            for ; Thu, 29 May 2014 07:30:02 -0700

This received line is one in the middle.  The relevant information is

    The message was received from "mailserver.domain.com.  More importantly, the IP address of that machine is 192.168.1.1
    The machine that received the message at this step, was mailserver.somedomain.com

So with that, you have the sending and receiving machine at each hop that the mail took.  Usually, these received headers are in reverse order, with the latest at the top, and earlier ones below.  So you start with the last received header, and that usually identifies the machine that actually sent the email.  Then, the received line above that is the next hop and so on.  If the email originated inside a private network, you may have to check a few received lines, until you get one that has a public IP address.  

Once you have a public IP address, then it is a matter of identifying where that address actually is.  There are several tools for this available on the internet.

I have written previously about the basics of triggers in MS-SQL.  Today, I am going to look at using cursors as the proper way to access data in a trigger.  (See my previous blog about using cursors.)

It is considered proper to use cursors in triggers, as there may be more than one record inserted, updated, or deleted, at a time.  SQL may batch multiple inserts, updates, or deletes, together when it executes.  So, even though you have, separate insert, update, or delete, statements, it is possible that SQL will batch several of those inserts, updates, or deletes, together when it actually performs its processing.  Therefore the use of a cursor is called for in order to step through all of the records that were inserted, updated, or deleted.

By using a cursor, on the appropriate table, you make sure that each record is processed.  Which table to use?  For insert triggers, the table name is INSERTED.  For delete triggers it is DELETED.  The interesting one is an update triggers, as it uses both the INSERTED and DELETED tables.  During updates, the INSERTED table contains the new data, and the DELETED table contains the old data.

The INSERTED, or DELETED, table has an identical schema to the table that the trigger is on.  So, if you define an insert trigger for your INVOICES table, the INSERTED table will have the exact same column names and definitions as the INVOICES table.  So, if you wanted to process for each TICKET_NO that was inserted, the cursor declaration would be:

    DECLARE DATA_CURSOR CURSOR for SELECT TICKET_NO FROM INSERTED

Also, since the INSERTED and DELETED tables are just that, tables, you can perform joins, order by, where, and just about any other filtering that you would do on any table.

So that is the short-and-sweet explanation for using cursors to process the data in triggers.

A common requirement in MS-SQL is the need to step through a list of records one at a time.  This is done via the use of cursors.

The idea is that the list of records is generated, which is the cursor definition.  Then, each record is processed one at a time, so that the appropriate actions can be performed.

There are a few steps involved with doing this.  First, a set of variables must be defined to hold the records for the cursor as they are processed.  Next, the cursor must be defined, and opened.  Then the first record needs to be read, followed by the processing loop.  Within the processing loop, whatever actions are required are performed, and then the next record is read.  After the processing loop exits, the cursor needs to be closed, and deallocated to remove it.

To illustrate, let us assume that we have an invoice table (INVOICES), which contains the field ticket number (TICKET_NO), and that we want to step through each ticket.

First we need to declare a variable to hold the ticket numbers as they are read, and then the cursor itself, and then open the cursor:

    DECLARE @TICKET_NO varchar(20)
    DECLARE DATA_CURSOR CURSOR LOCAL FOR SELECT TICKET_NO from INVOICES
    OPEN DATA_CURSOR

Now that we have the cursor defined, and opened, we need to get the first record:

    FETCH NEXT FROM DATA_CURSOR INTO @TICKET_NO

Then, we loop based on the status of the "fetch" command.  Within this loop, do your processing, and then perform the "fetch" command again:

    WHILE @@FETCH_STATUS=0
    BEGIN
        (do whatever processing you want here)
        FETCH NEXT FROM DATA_CURSOR INTO @TICKET_NO
    END

The loop will exit, when the "fetch" command no longer returns any values to process.  At that point, all that is left is to close the cursor, and clean up:

    CLOSE DATA_CURSOR
    DEALLOCATE DATA_CURSOR

That is all that there is to being able to step through data row-by-row, if you need to.  

Cursors are not exactly efficient when they are executed, however.  If possible, it is better to use an "update-join", or similar approach if possible.  There are times that it can not be avoided, though, and you have to use a cursor to do what you want.

Dave.

Have You Been Hacked?

Sometimes your system can be compromised, and there is no obvious indications that anything has occurred.  Recently, however, we have seen cases of systems being hacked, when there was obvious indications that something had occurred.

When a user first logs in for the day, and their desktop has been changed, or new programs have been installed, that should be a red flag.  While it is true that many larger companies will do updates after hours, if no one has said anything about it ahead of time, the user needs to alert their supervisor immediately.

What I have seen includes the desktop icons have been re-arranged.  New icons on the desktop, especially ones for remote control such as VNC (Virtual Network Connection) and some of its variants.  It could be just about anything that is just different.

If that occurs, the machine should be shut down immediately, and reported to a supervisor, or the IT department.  If it is a case of legitimate changes having been made, there is no harm, and the machine can be brought back into service.  If the changes are not legitimate, then by shutting down the potential for additional damages, such as stealing information, or participating in a botnet, are prevented while a course of action is determined.

At CCS we are very familiar with issues like this.  Contact our support department if you suspect your have been hacked and your IT department is not familiar with this issue.

Dave.
 

Creating Secure Passwords

The need for having good, complex, passwords has been discussed here before.  Today I want to delve a little deeper in that topic.

You have probably noticed many sites now require complex passwords that contain both upper and lower case letters, numbers, and special symbols.  By requiring all of these in your password, it makes it more difficult for your password to be cracked.

Now, let us take it a step further.  Take the case of requiring a password to have at least on upper case letter, at least one lower case letter, and at least one number.  Also, let us use the word “password” for the base.  (Not a good choice in the real case, but this is just for discussion purposes.)

The majority of people will capitalize the first letter, and add a number at the end.  Thus, “Password1”, would probably end up as the result in most cases.  Let us improve on that.

First, capitalize a different letter, and not the “w” in “word”, as that is another obvious tact.  Now we have something like “paSsword1”.  A bit harder to crack.  The next piece that I see, is that trailing “1”.  While “1”, or “123” easy to remember, it is also easy to guess.  Better to use a number that has some type of meaning for you that would not be common knowledge.  The number of your favorite sports player, or the day of the month your child was born.  Something along those lines give you a password that is easy for you to remember, while being much harder to crack.  

Another thing that I would do for this example, is move that number from the end, to the middle.  Therefore the result would be more like “paSs43word”, which is a pretty good basis for a secure password.  Adding a special symbol such as “#”, “@”, etc., would make it even better.

Dave..
 

Wise Disk Cleaner

There is a disk cleaning tool that I have found to be very good.  This is the Wise Disk Cleaner from WiseCleaner.com.

This free tool will scan your disk for files that are no longer needed.  These are files that are left over from your browsing the internet, updating your Windows system, and just general usage of your computer.  In my experience, it is very good at identifying those files that can safely be removed.

The Common Cleaner tab is the place to start.  This area scans for the most common and quickest to find files that can be removed.  It does this by looking in the most common locations on your Windows system for these files to reside.  The browser cache directories, and the Windows system cache, for example.

The Advanced Cleaner tab will do a more thorough job, by doing a complete scan of your hard drive, and analyzing all of the files that it finds.  Obviously, this is a much more time consuming operation.  It can yield very good results, though, and is well worth the time if you want to truly clean up your disk.

There is also a Slimming System tab, that allows you to remove additional files.  These are the files that are left over from your Windows Update, for example.  If you are confident that you do not need to uninstall any of the Windows Updates, then this is the place that you go to clean up those files.

The final tab, is the Disk Defrag option.  Personally, I have not used this one, as I have another disk defragmentation program that I use and have been very happy with.

Overall, I find that this utility has been simple to use, and have been very pleased with the results.  Obviously, this is only a small part of performance tuning your system, but it is a user friendly and powerful tool that gets you started.

Dave.
 

Tracking Records in SQL

There are times that you want to track the exact order that records are added to a table.  The best way to do this, is with a line number field, that automatically increments as each record is added.  This is accomplished using the IDENTITY() clause for an integer field when creating the table.

The IDENTITY() clause takes two arguments:  The starting number, and the increment number.  If you want to start at one, and increment by one for each record, then you would use IDENTITY(1,1).  To start at 1000 and increment by 10, it would be IDENTITY(1000,10).

What you will get, is that when you insert a record into the table, if you do not specify the value of the field with the identity on it, it will get automatically assigned.  The number that it will get, is the highest value that has previously been used, plus the increment amount.  Note, that if you have deleted the last record added, the identity field will still go up to the next increment amount.  So, if you add three records, then delete the third one, and add another one, the last record will go to the next increment amount from the original third record.  In other words, if you are starting at one, and incrementing by one, you would get 1, 2, 4, as your numbers.

For a very simple example, lets define a table for short notes:

  CREATE TABLE NOTES_TABLE (NOTE_LINE INT NOT NULL IDENTITY(1,1), NOTES varchar(50))

The field NOTE_LINE will automatically increment from one, by one, as we add notes.  We then issue the commands:

  INSERT INTO NOTES_TABLE (NOTES) VALUES (‘Note 1’)
  INSERT INTO NOTES_TABLE (NOTES) VALUES (‘Note 2’)
  INSERT INTO NOTES_TABLE (NOTES) VALUES (‘Note 3’)

If the table will then contain:

  NOTE_LINE  NOTES 
  ———            —————- 
  1                     Note 1 
  2                     Note 2 
  4                     Note 3  

Using a different base and increment, such as IDENTITY(1000,10), yields the following:

  NOTE_LINE  NOTES 
  ———            ————— 
  1000              Note 1 
  1010              Note 2 
  1030              Note 3

That is all that there is to implementing a simple record counter in MS-SQL.

Dave.

Using Debugging Information

Most of the better software has varying degrees of capturing debug information.  This is valuable for initial setup, and tuning.  For the long term, though, it is best to turn these debug settings off.

Unless you are actively trying to isolate an issue, or tune your servers for better performance, the debugging information you are capturing is of no use.  In fact, it probably is not even being looked at.  What it is doing, is taking up disk space with logs, and slowing down your processing by having to write to these logs.

There are some packages that do not allow you to turn off, or turn down, the logging level.  If you have any of these, then at least make sure that you clear out the log information from time to time.  

So, if you have tuned your system to your satisfaction, and are not chasing any issues, turn off that debugging.  You may be surprised at how much your performance increases.

Dave

 

System Load Issues 

One call that I get for support rather frequently, is that users will say that their system slows down at times.  In researching their issue it turns out that many times this slow down occurs when they are posting purchase orders or inventory receiving.  Very large purchase orders.

What happens is that they create a purchase order for one of their major vendors.  Or, they get their shipment, and are posting the inventory receiving.  In either case, the purchase order, or receiving, is quite large.  Typically several hundred items are being purchased or received, and the posting takes several minutes to complete.

Let us take a look at everything that is going on.  For each line, the inventory record must be updated to reflect the changed quantities.  The purchase order must be updated with the current status.  Those are the obvious ones.  Additionally, however, there are many tables that get updated in the background.  These are such things as tables that track quantity for each site.  If you are using multi-site, the tables that track what needs to be replicated must be updated, also.

The bottom line is that there is a great deal of processing going on for each line on the purchase order or receiver.  When there are several hundred lines, the processing load is quite large.

With this in mind, there really is not anything that can be done as far as the speed of the processing.  That is assuming that the SQL server has been tuned, of course.  So, if possible, time your large postings outside of your busy hours.  Usually, these can be held off until there is little other activity.  Performing them before, or after, business hours is the best, if it can be scheduled that way.

Dave