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.

Extracting Date from Datetime Expression or Datetime Field in SQL Server

Use Convert() Function. From MSDN, this is the syntax:

Syntax for CONVERT: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

To get it from a datetime expression,

Select Convert(varchar(10),GetDate(),111)

or from a field,

Select Convert(varchar(10),SomeField(),111)

So that when you perform a query with a criteria, you may write it like this:

Select convert(varchar(10),somefield(),111) between '2012/01/01' and '2012/01/10'

Scanning Individual Records In SQL Server and VFP

At work, it's been a common thing for me to execute my own queries in our SQL Server for reasons like fixing invalid entries or just for reporting purposes. Our POS and Data Management system are working fine, but the need for database administrator's intervention like myself is far from over. There are still data that needs to be pulled out from the database which the system cannot produce quickly.

Once, I was asked to update a few records from the database of which values in fields are in dire need of updates. The task was a bit tricky as each row must be evaluated individually. I originally planned to write a short program in VFP using an

How To Shrink SQL Server Database Transaction Log

I find this technique to be fairly simple enough but powerful. This must be run in SSMS.

backup log [dbname] with truncate_only;

go

DBCC SHRINKDATABASE ([dbname], 10, TRUNCATEONLY);

go

Don't bother doing this if you have no important reason to do so. I just needed to get a copy of our server's data into my  SQL Server Express which has a 4 Gig per database license restriction.

Have fun! :)

String Manipulation In SQL Server And Visual Foxpro SQL

One of my friend asked me about writing a query in SQL Server which can alter a portion of word or characters in a field in a table.

Since he is a VFP guy, I showed him how I could do this both in VFP and in SQL Server so he could understand it easily.

In Visual Foxpro, we can either write xbase-type codes or make use of VFP-SQL and use UPDATE in a query to change a portion of words in a field.

An example in VFP would be:

Automatically Backup An SQL Server 2005 Database

There are certain ways of creating a backup routine for SQL Server 2005 Management Studio (SSMS) and one of them is by making a Maintenance Plan.

The beauty of building a task for backup purposes under MP is that it can automate the job for you via the SQL Server Agent Service.

Using the SSMS, you don't need even a single code to achieve that. Things can be as easy as drag and drop literally. To begin with, let's open the SQL Server Management Studio and log in to an instance.