Never been to CodeSnippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world (or not, you can keep them private!)

4 total

On This Page:

  1. 1 iSQL sucks, so make it work better
  2. 2 Fix orphaned users in MSSQL
  3. 3 Reset identity seed for a table
  4. 4 Selecting different parts of a DATETIME with MSSQL

iSQL sucks, so make it work better

to sql unix linux tsql language query transact structured odbc isql unixodbc by chrelad on Oct 28, 2008
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

Fix orphaned users in MSSQL

to users sql mssql microsoft data transact orphan orphaned user restore restoration restoring by chrelad on Dec 11, 2007
Thanks to CodeProject for this code

Run this in the Query Analyzer (make sure the right database is selected first).

sp_change_users_login 'auto_fix', 'UserName'

Reset identity seed for a table

to sql reset microsoft column transact reseed seed identity ident field ms dbcc checkident by chrelad on Nov 16, 2007
This SQL query will reset an identity seed to the specified value.

Thanks to this site for the answer:

http://www.thescripts.com/forum/thread657059.html

I know this works for MSSQL 2000 at least :)

DBCC CHECKIDENT('mytable', RESEED, 0) ;

Selecting different parts of a DATETIME with MSSQL

to time date sql timestamp mssql language query transact structured datetime parts part stamp only sub by chrelad on Nov 14, 2007
Thanks to this website for the information:

http://www.databasejournal.com/features/mssql/article.php/1442021

-- The syntax for pulling a certain part of a DATETIME is:
--
-- CONVERT(date_type[(length)],expression[,style])
--
-- The available styles are as follows:
--
-- NULL  Jun 24 2001 9:48PM
-- 1     06/24/01
-- 101   06/24/2001
-- 2     01.06.24
-- 104   24.06.2001
-- 108   21:48:00
-- 112   20010624
-- 121   2001-06-24 21:48:00.000

-- Some example usage:
SELECT CONVERT(DATETIME,GETDATE(),112) as date
-- Will output in YYYYMMDD format
SELECT CONVERT(DATETIME,client.birthday,101) as birthday
-- Will output in MM/DD/YYYY format
4 total

On This Page:

  1. 1 iSQL sucks, so make it work better
  2. 2 Fix orphaned users in MSSQL
  3. 3 Reset identity seed for a table
  4. 4 Selecting different parts of a DATETIME with MSSQL