Runs the SQL code located in a file you pass as a parameter against the DSN you specify as a second parameter.

What does it need?:
-----------------------------------
* isql (if you're reading this, you should have this)
* A pager (less/more/most/etc, you should have this)
* bash (you can try it in your shell, but no guarantees)
* tr (you should have this)
* sed (you should have this)

The code:
-----------------------------------

#!/bin/bash
# {{{ sql $1 $2 - Run a SQL script
function sql
{
      tr '\n' ' ' < "$1" | tr '\t' ' ' | isql "$2" Username Password | sed '1,8d' | sed 's/^.*SQL> //g' | $PAGER
}
# }}}

Parameters:
-----------------------------------

$1 = The file that contains SQL
$2 = The DSN you want to run the SQL against

What does it do?:
-----------------------------------
It chops off the extraneous "thing" that is spit out whenever you start iSQL, it looks like this:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

and then it pipes everything into your specified pager ($PAGER) so you can at least scroll around in the results instead of seeing an ASCII art wrapped mess of your results.

Example use:
-----------------------------------

username@host $> sql ~/sqlscripts/getallusers.sql DSNName

Notes:
-----------------------------------
For my purposes I use the same credentials for all my databases (so I don't forget them) which is why they are hardcoded in the function and not passed into the function.

By the way, if anyone has any time on there hands, could you please pick up the slack on the development of this program. It's about the only CLI tool (that I know of) that can work with TSQL on a MSSQL 2000 server. Or, if someone knows of something better, let me know!

Thanks:
-----------------------------------
Thanks everyone, and I hope others find this somewhat useful (although I know it's a far cry from perfect),

Chrelad

This SQL statement will alter a table only if a column in the table to be altered does NOT contain a column named 'FavoriteColorId'.

From: http://haacked.com/

IF NOT EXISTS
(
    SELECT * FROM [information_schema].[columns]
    WHERE    table_name   = 'Customer'
    AND      table_schema = 'dbo'
    AND      column_name  = 'FavoriteColorId'
)
BEGIN
    ALTER TABLE [dbo].[Customer]
    ADD FavoriteColorId int

Retrieve tons of information (length, data type, name, etc.) of columns for a table. Works in (T|MS)SQL (all I've tested it in).

SELECT * FROM information_schema.columns WHERE table_name = 'Customers'

// Replace text in a TEXT type data column

SELECT     REPLACE(SUBSTRING(Note, 1, DATALENGTH(Note)), CHAR(13), '<BR>') AS Expr1
FROM         OLD_NOTES

// Simple COALESCE example

--If all arguments are NULL, COALESCE returns NULL.
COALESCE(expression1,...n) 
--is equivalent to this CASE function:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
--...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL

// Simple TSQL cursor

	declare @email nvarchar(255)
	declare CustList cursor for
		SELECT email from STE_EMAILS
	OPEN CustList
	FETCH NEXT FROM CustList 
	INTO @email
	WHILE @@FETCH_STATUS = 0
	BEGIN
	  if (SELECT count(custid) from customer where (current_customer=1) and (notes1 is not null) and (notes1 like '%' + @email + '%')) > 0
		SELECT custid from customer where (current_customer=1) and (notes1 is not null) and (notes1 like '%' + @email + '%')
		print '%' + @email + '%'
	  FETCH NEXT FROM CustList INTO @email
	END
	CLOSE CustList
	DEALLOCATE CustList

SELECT column,COUNT(*) c FROM table
GROUP BY column HAVING COUNT(*) > 1

results show the column value and the number of duplicates for that value

DECLARE @order int
SET @order = 1
SELECT * FROM users
ORDER BY 
	CASE WHEN @order = 1 THEN [name] END DESC,
	CASE WHEN @order = 2 THEN [name] END ASC,
	CASE WHEN @order = 3 THEN [email] END DESC,
	CASE WHEN @order = 4 THEN [email] END ASC

set the @order to 1-4 to get specific order by clause, setting it to anything else give results as if no order by clause supplied

remember dynamic SQL (creating SQL in a string and then executing) can not be effectively cached so this is a much better option

SELECT TOP 10 userID
FROM user
ORDER BY NEWID()

or

SELECT TOP 10
	NEWID() [egg],
	userID
FROM user
ORDER BY [egg]

or

SELECT TOP 10
	NEWID(),
	userID
FROM user
ORDER BY 1

only tried the above with TSQL