Sunday 20 March 2011

Free FAT Calculator Online

In a couple of previous Blog’s I’ve described the FAT16 and FAT32 File Systems, and how to calculate the various elements.

I’ve created a small Windows application to help you with finding the various elements.

Feel free to download it here…

http://www.pjgcreations.co.uk/FAT_Calculator_v1.zip

It will calculate all the elements I’ve discussed in my previous blogs, as well as, when you hover your cursor over the result boxes, show you the formulas in use.

It also allows entry in either Decimal or Hex, as well as calculating both FAT16 and FAT32 addresses.

The download is simply a zipped executable. You will require the latest Dot Net Framework 4 in order to run the program.

As a note, all Hex values should be entered as 0x followed by the Hex value. Also, you are unable to mix Hex and Decimal values, please enter either one or the other.

I hope it proves useful, and keep your eyes open for an online version of the same program coming soon!

Friday 18 March 2011

Moving from FAT16 (SD) to FAT32 (SDHC) – Part 2 – Addressing Differences

In a previous blog I discussed moving from a FAT16 based SD card to a FAT32 based SDHC card.

In that blog I went through the differences in Initialisation procedures between the different SD Specification.

In this blog I’ll detail the differences in Addressing schemes between the devices and the two File Systems.

FAT32 Addressing Changes

Due to how the addressing scheme in FAT16 is organised, a top limit exists at 2GB. In order to allow the FAT File System to expand in capacity, various changes have been made to the File System, including expanding and altering registers entries.

The Boot Sector Entry:

The Boot Sector Entry or BSE, holds critical information regarding the partition’s layout. The following table shows the layout of the FAT16 BSE;

Offset Item Length Notes
00h Jump Code 3 Bytes Contains executable Machine code to allow the Host System to jump past the BSE
03h OEM ID 8 Bytes The system name which formatted the device
0Bh Bytes Per Sector 2 Bytes Normally 0x2000
(512 )
0Dh Sectors Per Cluster 1 Byte  
0Eh No of Reserved Sectors 2 Bytes  
10h No of FATs 1 Byte The Number of FAT Entries
11h Max Root Directory Entries 2 Bytes For SD Cards this is usually 0x2000
(512)
13h Total Sectors in Partition 2 Bytes Only set if the device is < 32Mb.
Will likely be unset 0x0000
15h Media Type 1 Byte 0xF8 = Hard Drive
16h Sectors Per FAT 2 Bytes Used for reaching the Root Directory
18h Sectors Per Track 2 Bytes  
1Ah No of Heads 2 Bytes  
1Ch No of Hidden Sectors 4 Bytes Number of hidden sectors between the start of the disk and the BSE
20h No of Sectors in a Partition 4 Bytes Total number of Sectors in the Partition
24h Logical Drive No 1 Byte 0x80 for Hard Drives
25h Head No 1 Byte Usually 0x00
26h Extended Boot Signature 1 Byte Always 0x29 denoting that Serial No, Label and Type fields are valid
27h Serial No 4 Bytes Serial No of the Device
2Bh Partition Name 11 Bytes  
36h FAT Type 8 Bytes  
3Eh Executable Boot Code 448 Bytes Allows the host to find the first file which boots the system
0x01FE Executable Signature 2 Bytes End of BSE

The BSE has been modified and expanded to allow the File System to expand past the 2GB limit. The modified BSE for FAT32 is shown below. The value positions which have changed or been added are shown in red;

Offset Item Length Notes
00h Jump Code 3 Bytes Contains executable Machine code to allow the Host System to jump past the BSE
03h OEM ID 8 Bytes The system name which formatted the device
0Bh Bytes Per Sector 2 Bytes Normally 0x2000
(512 )
0Dh Sectors Per Cluster 1 Byte  
0Eh No of Reserved Sectors 2 Bytes  
10h No of FATs 1 Byte The Number of FAT Entries
11h Max Root Directory Entries 2 Bytes Always 0 for FAT32
13h Total Sectors in Partition 2 Bytes Only set if the device is < 32Mb.
Will likely be unset 0x0000
15h Media Type 1 Byte 0xF8 = Hard Drive
16h Sectors Per FAT 2 Bytes Always 0 for FAT32
18h Sectors Per Track 2 Bytes  
1Ah No of Heads 2 Bytes  
1Ch No of Hidden Sectors 4 Bytes Number of hidden sectors between the start of the disk and the BSE
20h No of Sectors in a Partition 4 Bytes Total number of Sectors in the Partition
24h Sectors Per FAT 4 Bytes

Defines the total number of Sectors contained
in each FAT on the SD Card.
e.g. 0x911D0000 = 7569 Sectors per FAT

28h Flags 2 Bytes Defines flags for Active FAT Copy and Mirroring
2Ah Version 1 Byte Version of FAT Drive
2Ch Cluster of Root Dir 4 Bytes

Defines the Start Cluster of the Root Directory
on the SD Card.
e.g.0x02000000 = 2

30h FS Info Sector 2 Bytes Defines the first Sector of the File System Info.
32h Backup BSE Sector 2 Bytes Defines the First Sector of the Backup BSE
34h Reserved 12 Bytes Reserved Bytes
40h Drive Number 1 Byte Logical Drive Number of Partition
41h Reserved 1 Byte Reserved Byte
42h Boot Signature 8 Bytes

Defines the Boot Signature of the File system
in use on the SD card.
If this value is not 0x29, then the total number
of hidden sectors is halved, and all values
in the Boot Sector Entry after 0x001E should
be ignored.
e.g. 0x29 = Boot Signature = 41d

43h Volume Serial No 4 Bytes

Defines the Serial number of the File system
on the SD Card.
e.g.0x3CF21620 = Serial Number = 242223241

47h Volume Label 11 Bytes

Defines the Volume Label of the File system.
e.g. "NO NAME    "

52h File System Type 8 Bytes

Defines the type of File System on the SD card.
e.g. "FAT32   "

As can be seen above, there have been quite a few changes to the BSE structure to cope with the conversion from FAT16 to FAT32.

Calculating the Root Directory Address (FAT16):

The formula use to calculate the Root Directory Address for FAT16 is;

RDE Address = FAT1 Start Address +
              ((No of FATs(From BSE) *
              Sectors Per FAT(From BSE)) *
             
Bytes Per Sector(From BSE))

Calculating the Root Directory location (FAT32):

From the FAT32 BSE table above, we can see we have a new field at offset 2Ch; “Cluster of Root Directory”.

This field allows us to Jump from the first Data Cluster of File System directly to the Root Directory. We do of course need to calculate the first Data Cluster Address. The formula for this is;

First Data Cluster =
         (
Partition 1 Start Sector(From MBR) +
         Reserved Sectors(From BSE) +
        
(No of FATs(From BSE) *
            Sectors Per FAT(From BSE))) *
         Bytes Per Sector(From BSE)

Once we have the address of the first Data Cluster, we are then able to calculate the address of the Root Directory. This is accomplished simply by applying the formula used for locating a File or Folder;

File / Folder Address =
         Cluster 1 Start Address +
         (((File/Folder Start Cluster–2) *
         Secs/Cluster(From BSE)) * 512)

Thus we apply the formula above feeding in the Root Directory Start Cluster, to obtain the Root Directory Address.

SD Card Addressing Change

As SD cards have increased in size, it has become necessary for devices to use a different addressing scheme. FAT16 devices address on a Byte by Byte level. However, as devices have grown, this has become an unmanageable number.

This change of course affects the use of CMD17 – Block Read. Where the argument supplied is a 4 byte address to read data from. As mentioned above, for normal SD Cards this address is a byte unit address. However, the larger devices now use a Sector by Sector addressing scheme. Of course, this requires that the Host system take this into account when addressing the card, such that it must record which type of card is connected and make a decision of which addressing scheme to use based upon this. The card type is detected using the ACMD41 command sequence detailed in my previous blog.

The simplest way to handle this difference in code, is to simply check the card size before passing the address to CMD17. If the Host is connected to an SDHC device, then simply divide the Byte Address by 512 to reach the Sector Address.

Alternatively, rather than multiplying all Sector Address locations by the Bytes/Sector value, the Host may simply check the Card Type, if a Standard SD card is connected, then simply multiply the Sector Address by the Bytes/Sector value to reach the Byte Address.

This decision will no doubt depend upon whether you are writing code from scratch or upgrading already existing code.

Moving from FAT16 (SD) to FAT32 (SDHC) – Part 1 – Initialisation

Technology moves fast, only 18 months ago, the 1GB SD card was very easy and affordable to purchase. Today, they’re like hen’s teeth, where the 2GB SD card has taken over this position in the market, of course a doubling of which “Mr Moore” would be happy with.

Thus, It’s only natural to project that, in 12 – 18 months time, the 2GB SD card will be difficult to source, leaving the consumer only able to purchase the 4Gb SD Card and above with any ease.

This is all good for the consumer of course, as they are getting more bang for their buck. But, for the designer it raises it’s own set of unique technical hurdles to cross.

As technology moves on, as do the specifications which devices operate under. Moving from a 1GB legacy SD card to either a more modern 1GB Card (If you can source one!) or a larger device, requires that firmware support a different SD Card Specification be written.

In this first blog, I will deal with the differences in Initialisation of SD cards under the different Specification revisions.

The v1.01 SD Card specification was written in 2001, the v1.1 spec was introduced in 2006, with the v2 spec was introduced later that year. Following the introduction of the SDHC and SDXC formats in 2010 came v3 of the specification.

With these changes, come semantic differences in the operation of the devices.

SD Spec V1 Initialisation:

Below is the initialisation flowchart for SD Cards conforming to V1 of the SD Card Specification; (Click to enlarge)

SD Init V1

It can be seen that, from an Idle State the Host sends the CMD0 Command – “Go Idle” while holding the Chip Select pin Low. This command resets all connected SD cards to their Idle state, effectively it is a Reset command. Holding the CS pin Low forces the card into SPI mode, which is how this design is operated.

As a point of note, almost all commands sent to the SD card will be responded to with the R1 response. This response contains a set of bytes which indicate the devices status; Busy, Idle, Error, Card Locked, CRC error and a host of other status Bits.

Following CMD0, the SD card will be in it’s Idle state, from here the host may read the Operating conditions Register, or OCR, using CMD58. The SD card responds to this command with the contents of the OCR, allowing the host to ascertain such parameters as the cards operating voltage range etc. This allows the host to reject incompatible cards from the outset.

The next command can be either CMD1 or ACMD41, in my original design, for simplicity’s sake, CMD1 was chosen here.

CMD1 – “Send Op Cond”, puts the SD card into it’s Initialisation State. The SD card will respond with the standard R1 response, which contains the devices' status. The host must wait for the device to signal that it is Idle, that is the R1 response is 0x00.

At this point the device is ready to use normally.

SD Spec V2 Initialisation:

We now move onto v2 of the SD Specification, and the initialisation has changed to the following; (Click to enlarge)

SD Init V2

We can now see that the Initialisation sequence has become somewhat more involved.

With the introduction of the V2 SD Card Specification, the Host now needs to determine which specification the connected device conforms to. This is accomplished with the addition of the CMD8 Command – “Send Interface Condition”. The host must include this along with this command a parameter which defines the voltage range it allows.

Upon reception of this command, the device will send the R7 response. This response contains the devices accepted operating voltage range.

The host at this point waits for the the R7 response, meaning the device is both V2 Spec compliant and also can operate at the given voltage range. If either fail, it can then peruse alternative options for communicating with the device, or rejecting the device completely.

If the R7 response is correctly received, then the Host must now determine whether the device is a standard format SD card or the newer and larger SDHC format.

As the devices have grown, in order to maintain backward compatibility, it has become necessary for the host system to determine which “size” of device is connected.

This process is accomplished by the host, via the use of the “Send Op Cond” (ACMD41) command. By supplying the High Capacity Support (HCS) bit, the Host can inform the connected SD card as to whether the host itself can support the High Capacity Card format.

As a note, the ACMD41 command is actually a combination of two separate commands, the “Application Command” (CMD55), and the “Send Op Cond Command” (ACMD41). CMD55 instructs the SD card that the next command is an Application Specific command, thus ACMD41 must be proceeded with CMD55.

In order initialise v2 onwards compliant devices, the above process is carried out. To simplify matters, the process can be used for v2 compliant, standard SD cards as well as SDHC, where the CCS bit in the response to ACMD41 is used to determine which type of card is connected.

Further, the specification requires that the sequence CMD55 + ACMD41  must be repeated for at least 1 second at power up in order to initialise the SD card correctly.

I must note however, that I have found by trial and error that, in order to properly initialise the SD Card and determine it’s Card Capacity, it is necessary to add a CMD55 – “Read OCR” command to the ACMD41 sequence. I’m not sure at this time why this is, but once I’ll know I shall update this blog to reflect my new knowledge!

Once this process is complete the SD Card is then ready for use.

SD Spec V3 Initialisation:

The v3 SD Specification shows the following for the Initialisation Sequence; (Click to enlarge)

SD Init V3

I have included the above sequence for completeness however, it is almost the same as the V2 Initialisation sequence. It is however clear to see the progression of complexity, along with the increase in technical specs of the devices has had an impact upon how these devices are initialised.


In my next blog  I will be dealing with the differences in Data Access mode commands, and it’s effects upon the host firmware.

Monday 14 March 2011

The FAT16 File System (with SD cards)

I have been working on a  project where the client requires their device to have it’s firmware updated from a Secure Digital (SD) Card.

The standard way to store and access data on SD cards is to use the MS FAT File System.

There are several libraries available dotted around the Internet which allow developers to access SD using FAT. However, if you are on a tight program space budget, such as in the Boot-Loader of an embedded Microcontroller, then you may need to program the FAT File System access manually.

This process is reasonably difficult to accomplish in raw code as there are quite a few steps to perform before you gain access to the actual file you are after.

I’ll outline here the basic features of FAT16, which for the purposes of this example, will be the simplest to begin with.


The FAT16 Layout

The layout of the MS FAT16 File System can be seen below;

FAT16 Layout

The Master Boot Record (MBR):

Offset 0x0000
Size 512 Bytes

 

 

The Master Boot Record is the first location that the Host File System parses in order to navigate the File Structure of the SD Card. It is always located at Sector zero and not only sets out the addressing parameters of the File System but also contains operating code run by the host at startup.

Address
(Offset)
Item Length Notes
0x0000 Executable Code 446 Bytes Executed by the Host system at startup
0x01BE
(00h)
Partition 1:
State
(Partition 1 Entry Begin)
1 Byte Beginning of the Partition 1 Entry. 16 Bytes Total.

The State determines if the Partition is Active (80h) or Inactive (00h)
0x01BF
(01h)
Partition 1:
Start Head
1 Byte  
0x01C0
(02h)
Partition 1:
Start Sector / Cylinder
2 Bytes Defines the Start Sector and Cylinder of Partition 1
0x01C2
(04h)
Partition 1:
Partition Type
1 Byte Defines the Type of Partition;
01h = 12bit FAT
04h = 16bit FAT (<32Mb)
05h = Ex MSDOS
06h = 16bit FAT (>32Mb)
0Bh = 32bit FAT
(<2Gb)
0x01C3
(05h)
Partition 1:
End Head
1 Byte  
0x01C4
(06h)
Partition 1:
End Sector / Cylinder
2 Bytes Defines the End Sector and Cylinder of Partition 1
0x01C6
(08h)
Partition 1:
Start Sector
4 Bytes Contains the Start Sector Address of Partition 1.
Defined as the number of Sectors between the MBR and the first Sector of the Partition.
0x01CA
(0Ch)
Partition 1:
Partition Length
(Partition 1 Entry End)
4 Bytes End of the Partition 1 Entry. 16 Bytes Total.

Defines the number of Sectors in the Partition
0x01CE Partition 2 Entry 16 Bytes Defined as above
0x01DE Partition 3 Entry 16 Bytes Defined as above
0x01EE Partition 4 Entry 16 Bytes Defined as above
0x01FE Executable Marker 2 Bytes Set as 0x55AA
 

The Boot Sector Entry (BSE):

Offset 1st sector of Partition
Size 512 Bytes
Address Location Calculation Partition Start Sector (From MBR) * Bytes / Sector

The Boot Sector Entry contains all the pertinent information regarding the Partitions’ structure. The basic layout is shown below;

Offset Item Length Notes
00h Jump Code 3 Bytes Contains executable Machine code to allow the Host System to jump past the BSE
03h OEM ID 8 Bytes The system name which formatted the device
0Bh Bytes Per Sector 2 Bytes Normally 0x2000
(512 )
0Dh Sectors Per Cluster 1 Byte  
0Eh No of Reserved Sectors 2 Bytes  
10h No of FATs 1 Byte The Number of FAT Entries
11h Max Root Directory Entries 2 Bytes For SD Cards this is usually 0x2000
(512)
13h Total Sectors in Partition 2 Bytes Only set if the device is < 32Mb.
Will likely be unset 0x0000
15h Media Type 1 Byte 0xF8 = Hard Drive
16h Sectors Per FAT 2 Bytes Used for reaching the Root Directory
18h Sectors Per Track 2 Bytes  
1Ah No of Heads 2 Bytes  
1Ch No of Hidden Sectors 4 Bytes Number of hidden sectors between the start of the disk and the BSE
20h No of Sectors in a Partition 4 Bytes Total number of Sectors in the Partition
24h Logical Drive No 1 Byte 0x80 for Hard Drives
25h Head No 1 Byte Usually 0x00
26h Extended Boot Signature 1 Byte Always 0x29 denoting that Serial No, Label and Type fields are valid
27h Serial No 4 Bytes Serial No of the Device
2Bh Partition Name 11 Bytes  
36h FAT Type 8 Bytes  
3Eh Executable Boot Code 448 Bytes Allows the host to find the first file which boots the system
0x01FE Executable Signature 2 Bytes End of BSE


FAT Entries (FAT):

Offset FAT1 = 1st sector after Reserved Sectors
Size Sectors Per FAT (From BSE)
Address Location Calculation FAT1 = (Partition Start Sector(From MBR) + Reserved Sectors(From BSE)) * Bytes / Sector

The FAT Entries lay out how the Partitions’ identically sized clusters are laid out. There are normally two FAT Entries in order to help prevent FAT corruption problems.

The FAT is located in the first sector after the Reserved Sectors. All sectors between Partition Start Sector and the FAT, which includes the Boot Sector Entry, are contained within the Reserved Sectors Allocation.

So, to find the first FAT we navigate from the beginning of the Device, adding together the Partition Start Sector and the Number of bytes contained in the Reserved Sectors section.

Root Directory Entry (RDE):

Offset After last FAT
Size No of Root Entries(From BSE) * 32
Address Location Calculation RDE = FAT1 Start Address +
((No of FATS(From BSE) *
Sectors Per FAT(From BSE)) * Bytes Per Sector)

The Root Directory contains the Main Directory and File Structure for the entire Partition

The Root directory is almost identical to a standard File System Directory with only a few differences, namely; The first byte is the first character of the Partition Name. The Create Date and Time are set to 0x00.

The Root directory appears after the last FAT, and so can be found by adding the Start Address of the first FAT to the number of Bytes occupied by all FAT’s, which is invariably 2 for SD Cards. Thus the formula can be simplifed to;

RDE = FAT1 Start Address +
      ((2 * Sectors Per FAT) * 512)

Each entry in the Root Directory consumes a total of 32 Bytes as shown below;

Offset Item Length Notes
00h DOS Filename 8 Bytes For all other Directories the first byte has special values.
08h DOS File Extension 3 Bytes  
0Bh File Attributes 1 Byte 8 bits to denote various attributes
0Ch NT Case Info 1 Byte Used for Windows NT Casing Info
0Dh Create Time (ms) 1 Byte 10ms Units
0Eh Create Time (Hrs/Mins/Secs) 2 Bytes  
10h Create Date 2 Bytes  
12h Last Access Date 2 Bytes  
14h File / Folder
Start Cluster (High)
2 Bytes Only used in FAT32 Systems
16h Last Modified Time 2 Bytes  
18h Last Modified Date 2 Bytes  
1Ah File / Folder
Start Cluster (Low)
2 Bytes  
1Ch File Size (Bytes) 4 Bytes Folders will have a File Size of 0x0000


Sub Directory / Files:

Offset After Root Directory
Size N/a
Address Location Calculation Root Folder Address +
(Max Root Directory Entries(From BSE) * 32) +
((( File / Folder Start Cluster(From RDE) – 2) *
Sectors Per Cluster(From BSE) ) *
Bytes Per Sector(From BSE) )

Normally For SD Cards using FAT16;

Max Root Directory Entries = 512
Bytes Per Sector = 512

A Sub Directory takes on almost exactly the same format as the root Directory above.

All data stored after the Root Directory is located in the third Data Cluster, thus in order to find the location of all data we must subtract these two clusters from the Start Cluster given in the File / Folder Start Cluster Field.

The first data is found using the formula above, however for SD Cards using FAT16, the maximum Root Directory Entries and Bytes Per Sector are invariably 512. Thus the formula can be simplified to the following;

First Data = Root Folder Address + 16384 +
             (((File/Folder St Cluster – 2) *
             Sectors Per Cluster) * 512)

A Worked Example:

Card Type Sandisk 2Gb SD
MBR Length 512
Partition 1 Start Sector 129
Bytes Per Sector 512
Sectors Per Cluster 64
Reserved Sectors 2
Number of FAT’s 2
Sectors Per FAT 239
Hidden Sectors 129
BSE Length 512
Number of Root Entries 512

The table above gives all the information taken from the MBR and BSE of the Card.

Calculating the BSE Address:

BSE = Partition Start Sector *
        
Bytes Per Sector

BSE = 129 * 512

BSE = 66048 = 0x10200

Calculating the FAT1 Address:

FAT1 = (Partition Start Sector +
      
Reserved Sectors) * Bytes Per Sector)

FAT1 = (129 + 2) * 512

FAT1 = 131 * 512

FAT1 = 67072 = 0x10600

Calculating the Root Directory Address:

RDE = FAT1 Start Address +
      ((No of FATS * Sectors Per FAT) *
     
Bytes Per Sector)

RDE = 67072 +
      ((2 * 239) *
      512)

RDE = 67072 + (478 * 512)

RDE = 67072 + 244736

RDE = 311808 = 0x4C200

Calculating the First File Address:

For a file or folder which has a starting cluster value of 0x04, then the following will locate the start address;

File = Root Folder Address +
      (Max Root Entries * 32) +
      (((File/Folder St Cluster – 2) *
      Sectors Per Cluster) *
      Bytes Per sector)

File = 311808 +
       (512 * 32) +
       (((4 – 2) *
       64) *
       512)

File = 311808 +
       16384 +
       ((2 * 64) * 512)

File = 311808 + 16384 + (128 * 512)

File = 311808 + 16384 + 65536

File = 393728 = 0x60200

Thursday 3 March 2011

SQL Server - Formatting Numbers as Strings with Leading Zero’s

Sometimes it’s necessary to format numbers as Strings with leading zero’s.

One such example is when we sort a list in Excel, Excel will sort the numbers as 0,1,10,2,20,3,30,…..

So, to get round this, we simply append a leading 0 to the number.

This is achieved in T-SQL by using the RIGHT instruction with following syntax.

RIGHT('00'+ CONVERT(VARCHAR,YourNumber),2)

The first Parameter - 00, is the default format for your formatted number, and should contain the number of zero’s corresponding to the total length of your formatted string.

The second parameter -‘YourNumber’, is the name of the field you wish to convert.

The third parameter - 2, is the total length of your formatted field.

Tuesday 1 March 2011

Remaining VB.net Projects for WF Screencasts

In January I blogged about the WF screencast series by DevelopMentor instructor, freelance developer and MVP Maurice de Beijer.

All the VB.Net Projects for this series are now available on my website.

http://www.pjgcreations.co.uk/public/WFScreenCasts/

Have fun!

Retrieving data from a Stored Procedure (with parameters) in Microsoft Excel 2007 / 2010

I have a method here which allows you to add Parameters to the Stored Procedure from Excel.

There are quite a few steps, but I’ve broken it down quite alot.

The process is actually really easy once you know how... as with anything.

  1. Open Excel
  2. Select the Data Tab (If you’re using Excel 2007 onwards!)
  3. Select the “From Other Sources” drop down box
  4. Select “From SQL Server”
  5. It will open up the following box

    image
  6. Enter “.\SQLExpress” (That’s Dot Backslash SQLExpress and it’s without the quotes) in the “Server name:” box
  7. Depending on your Server Configuration, either leave the Log on credentials as “Use Windows Authentication”, or select the “Use the following User Name and Password” radio button and enter your SQL Server credentials
  8. Hit “Next >”
  9. You should then see the following screen:

    image
  10. In the “Select the database that contains the data you want:” Dropdown, select the database you wish to use.
  11. In the list at the bottom, select a table, it doesn’t matter which you choose.
  12. Press “Next >”
  13. You should then be presented with the following screen:

    image
  14. In the “Filename” Textbox, set the name you would like to save the connection as, remembering to leave the “.odc” on the end.
  15. If you want the Password to be stored with this connection, then check the “Save password in file” Checkbox, say “Yes” to the warning box.
  16. In the “Description” enter something meaningful for this connection
  17. In the “Friendly Name:” Textbox, set the name you would like to appear in the Recent Connections dialog. There’s no need for a “.odc” extension here.
  18. Hit “Finish”
  19. You should then be shown the following screen:

    image
  20. Click “Properties...”
  21. The “Connection Properties” window will be shown:

    image
  22. Click the “Definition” tab, which will show:

    image
  23. If required click the “Save password” Checkbox, and press “Yes” to the warning box
  24. Change the “Command Type” Dropdown to be “SQL”
  25. Set the “Command text to “exec ” + the name of your Stored Procedure. If you stored procedure requires parameters then simply tack them on the end seperated by commas.

    e.g. exec GetUsersByAreaandName Eastern, Smith

    If you wish to pass in a Null, then simply type “NULL” as a parameter.

    e.g. exec GetUsersByAreaandName Eastern, NULL
  26. Press “OK”, and Press “Yes” to the warning box
  27. You should be shown the Import Data screen again, Press “OK”

If everything went according to plan, you should have all your data on screen!