Reindexing in VFP and MS SQL Server

In Visual Foxpro, there is no need to reindex an existing index once you create an index right after you open a table.

USE Temptable

INDEX on somefield TO someindex

In SQL Server, reindexing means building the index again. Rebuilding the index for a certain table in SQL Server is not cumbersome and how often you should do that would depend on your choice, if you wanted it done regularly or not at all.

You can either right click on an index (a node in indexes under a table) in SQL Server Management Studio and select REBUILD or run a query like my example below...

ALTER INDEX [pkid_index] ON [dbo].[Customers] REBUILD PARTITION = ALL WITH

   ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON,

   ALLOW_PAGE_LOCKS  = ON, IGNORE_DUP_KEY  = OFF, ONLINE = ON, SORT_IN_TEMPDB = OFF )

...or if you worry about your availability to execute a manual reindexing, you can assign the SQL Server Agent to do the thing automatically for you at a certain date and time. Mine for example is done every week at 00:00H when nobody is using my programs. You can create it in the Maintenance Plan.

Generating Unique ID's From An Auto-Increment Field

Here's an example of how we can create a reference to a 10-digit running number that we can use as a unique ID to anything we want.

We'll just need a table that contains nothing and will serve as a reference only to the last auto-generated number from its auto-increment field.

Here's the structure of the table in MSSQL to hold the 10-digit ID.

CREATE TABLE [dbo].[sometable](
 [lastuid] [int] IDENTITY(1000000000,1) NOT NULL,
 [dtstamp] [datetime] NULL
) ON [PRIMARY]

And here's the stored procedure to get the last ID from that table.

Create Procedure dbo.GetUID 
As 
Begin
 insert into sometable (dtstamp) values (getdate())
 select @@identity as lastUID
 delete from sometable
End

The procedure can be altered so that it retains a single row that contains the timestamp just in case we want to know when the last ID was generated. Just move the DELETE command to the line before the INSERT command. Either procedure will return the last generated ID.

Create Procedure dbo.GetUID 
As 
Begin
 delete from sometable
 insert into sometable (dtstamp) values (getdate())

 select @@identity as lastUID
End

And finally, we can extract a new unique ID in VFP by executing a call in an SPT connection.

=SQLExec(m.nConnection,'execute dbo.getuid','curTableID')

And from SSMS itself:

execute dbo.getuid will bring out:

Finding Matches Using LIKE With Square Bracket In MS SQL Server

In MS SQL Server, a common way to filter out a query or to get records with matching patterns is to pair a WHERE condition with the keyword LIKE.

Usually, it's much easier to pinpoint an exact match if you pair a WHERE condition with a constant value.

For example:

In a sample database provided by Microsoft, the AdventureWorks2008R2, I'll get the records from Person schema where a person's last name is Martinez. The query should be as simple as this:

use AdventureWorks2008R2
go

Select Top 2 t1.FirstName, T1.LastName from Person.Person As T1
where UPPER(T1.LastName) = 'MARTINEZ';
go

Exporting Visual Foxpro Tables to MS SQL Server

VISUAL FOXPRO 9.0 - I find out that the easiest way (for me) to upload VFP free tables into a SQL Server 2005 database is through the Upsizing Wizard provided by Microsoft Access 2010.

When Microsoft still supports VFPODBC, we can do that by setting up a data source pointing to VFP  tables and import them to a  MS Access Database.

But according to MS, they no longer support the VFPODBC and they recommend the use of Visual Foxpro OLE DB driver instead.

Too bad;  but anyway, we can still do something about this. I'll demonstrate a similar technique that does the same. Without VFPODBC, we will rely on Access' built-in dBASE importing option. The downside though is that it will only let us import one VFP table to MS Access Database at a time. If you're in for some boring software stuff, please read on. :)

Create New Table From SELECT SQL

Photo Credits: images.businessweek.com

In Visual Foxpro 9.0, it's a norm to do a SELECT statement that writes all the queried records to a new table. This saves you time from making a new table along with all the fields within and fill it with records later from a SELECT query.

A simple SELECT statement like the example below creates a new table in VFP from a query:

SQL Server Identity Column vs VFP AutoInc Field

In  order to have an efficient table structure which minimizes data redundancy, developers have employed normalization in which tables are broken down into several tables connected together by a certain field which is either a primary key or a foreign key for the purpose of RDMS.

This is being done not only to avoid data redundancy but also to keep the size of the table small as possible especially if you are working with VFP free tables which have a maximum of 2GB filesize per table.

I have come to learn the use of the primary key (PK) and foreign key (FK) from www.foxite.com when I was younger and in VFP, we heavily make use of a special field called AUTOINC (auto-increment) field.

The AUTOINC field, as the word implies by itself, is just a field which automatically increments itself after a successful INSERT INTO command. The addition of this in VFP 9 has since freed the developers from the agony of maintaining a cumulative number which is used as a PK or for in any invoicing purposes.

Running an SQL Server's Stored Procedure from Excel Sheet

Figure 1.

There are times when your boss calls for an immediate solution for a simple problem but that can only be solved by your programming skill.

You look at the problem, analyze a solution within the scope of the software the company is currently using and work it out from there.