This execution example shows three steps.

In step #1, OSQL is used to run the file Query.Dat against the database called "WebBoard."  The DBO (DB owner) name for this is "sa", which is the default MSDE DBO, and the password is nothing (blank).  Your sysadmin should know the DBO name and password for your data.

A typical query might be something like this:

select * from users

(If you don't know SQL, you can enter "+SQL +tutorial" in Yahoo's search engine to get a fairly long list of free tutorials.)

The -s parm to OSQL is followed by a space and a single character that will be used to separate the columns.  In this example I've chosen decimal 175 (which doesn't come out the same way in HTML as it does in plain text).  That character can't appear in the data.  You may also use a single character (other than a digit).  If you don't have a text editor that will let you type control characters, use DOS EDIT at the DOS command line.  To get that character, hold down the alt key on your keyboard and type "175" on the numeric keypad (be sure that scroll lock is off.)

The first step is all that needs to be run by a sysadmin who has server access: the output file (Raw.Dat) is relatively compressed in the sense that it doesn't have the unnecessary spaces, etc. that OSQL outputs.  It should be even smaller after being zipped.  Note that the column separator character is being fed to OSQLRf1 in the -S parm  (the "S" can also be lowercase).

In the second step, OSQLRf2 converts Raw.Dat into commas-n'-quotes format.  (That's the file CQ.Dat).  OSQLRf2 also takes the -S parm, and it has the same meaning as for OSQLRf1.

This format is ready to be loaded into many different kinds of interfaces.  The -F parm specifies a layout file, which shows the names of the fields and their lengths (beware that some fields may show up as longer than they really are).

The -T parm allows you to truncate fields automatically: in this case the fields are truncated at 256 bytes, which just happens to be the default.

The last step converts CQ.Dat (commas-n'-quotes format) into fixed-length format.  Both files are plain text ASC flat files.  Programmers might wish to use this "generic" format so that they can use any standard programming language to process the results.  The -NOQUOTES argument specifies that the fields are to be aligned next to each other, with no intervening commas-n'-quotes.

If you need more precise layouts, you can use the built-in stored procedure sp_help.

Here's an example of how to do this:

osql -U sa -P -Q "sp_help users" -d WebBoard -s # | OSQLRF1 -Q >Out.Txt

That example will dump out the users table into Out.Txt.

The format isn't very pretty, but at least you'll know the exact field types and lengths.

You can also use sp_help on any triggers, constraints, etc. which you'll see listed.

As in the previous examples, I assume that your DB owner is "sa" (follows the -U command-line argument), and that your password (follows the -P command-line argument) is the default of blank.

If you want better formatting, you can use the batch file OSQLTab.Bat

You simply execute it from the command line with the name of the database and the name of the table, e.g.:

OSQLTab WebBoard User

This example will print out the user table in the DB called WebBoard.

Output will be appended to the file OSQLTab.Txt, which will be created as new if it doesn't exist.

Two additional programs, Repl.Exe and OSQLRf3.Exe, are invoked in this batch file.

Here's what the batch file OSQLTab.Bat does in order to produce a table layout, in case you want to use this strategy in your own processes:

OSql -U %DBO% -P %PWD% -Q "sp_help %2" -d %DBNAME% -s # | OSQLRF1 -q >T1.Dat
OSQLRF3 -q < T1.Dat > T2.Dat
DBCvt T2.Dat LayOut.Txt -q -noquotes -varnoflds

After executing this example, LayOut.Txt will contain a fairly readable layout.  The DOS batch file variable %DBNAME% is the database name, the variable %2 is the table name, the variable %DBO% is the DB owner, and the variable %PWD% is the password.

The -q parm to OSQLRf1 suppresses the standard program "help" printout.

Note that this works for base tables only: not views.

OSQLTab.Txt will contain all the table structures for the database WebBoard.

If you've changed the Database Owner, or the DBO's password, you'll have to configure the batch file, by changing the variables DBO and PWD.

Use Windows Notepad (not a document editor) to change this file.  Or if you insist on using something like WORD, make sure you save it in "plain DOS text."

Output will be written to OSQLTab.Txt.

To get the data in the Users table in the database WebBoard in commas-n'-quotes format, simply type this from the DOS prompt:

OSQLDmp WebBoard Users

Output will be written to Users.Dat.

Let me again remind you that both these batch files must be configured if you've changed the DBO (database owner) from the default of "sa" or the DBO's password from the default of blank (i.e. the null string, AKA nothing).

Use Windows Notepad (not a document editor) to change these files.  Or if you insist on using something like WORD, make sure you save them in "plain DOS text."

Leading and trailing spaces aren't preserved in the data.

You can run exactly one query (SELECT) per execution of OSQLRf1 and OSQLRf2.  If you need to extract the results of more SELECTs, you'll have to clone the example code for each one.

Your syaadmin may be (understandably) reluctant to run an executable on your server, unless s/he is absolutely convinced that it doesn't contain a virus, "Trojan horse," or some other method involving unauthorized entry.  Of course, it's possible to run OSQL by itself and reformat the output on another system.  The output file will be suitably small, if zipped.  Nevertheless, OSQL writes very large files, and if you can use the C source or this web page to persuade your sysadmin to run OSQLRf1, then you'll see significant efficiency improvements.  Note that the string "open" doesn't appear in the C source (because all the I/O is done though standard input and standard output).

My hope is that most sysadmins will appreciate the fact that they don't have to start any special software: they just click once on a DOS batch file icon and send out one file . . . this makes it easier and more convenient to set up periodic extraction processes.

Because commas-n'-quotes formats vary widely when it comes to the syntax used for double quotes in the data, OSQLRf2 will convert double quotes to single quotes by default, or use the replacement character of your choice.  You can use the -D parm for this: the syntax is the same as for the -S parm (a 1-3 digit number for decimal hex values, or a single character, other than a digit).

You can discover your board ID# by putting this JavaScript on any of your board's pages:

<SCRIPT LANGUAGE='JavaScript'>
alert(document.cookie)
</SCRIPT>

Look for the WB-BoardId cookie.  This cookie is set even on "no authorization" boards or "basic authorization" boards (provided that the browser will accept cookies).

If all else fails, WB's Boards table will have the board ID#.  There is no sensitive information in this table for most applications, so you can ask your sysadmin to dump it out for you.