#!/usr/bin/python """ Repair MS SQL (c)2008 RAB Linux Indonesia Proses dump pada tabel yang memiliki field bertipe NTEXT akan menemui kendala, seperti yang dijelaskan di http://pymssql.sourceforge.net/troubleshooting.html#unicode Script ini bermanfaat untuk "memperbaiki" tipe data tabel tersebut dengan cara membuat tabel baru berawalan __ (underscore dua kali) diikuti nama tabel yang bermasalah, contoh: __pegawai. Strukturnya sama dengan tabel aslinya, namun field yang bertipe NTEXT diganti menjadi TEXT. Kemudian gunakan mssql-dump.py untuk proses dump. Script ini akan mengambil data dari tabel __pegawai untuk tabel pegawai. """ import pymssql import sys dbname = sys.argv[1] without_width = ['smalldatetime','smallint','int','tinyint','text'] nullables = { 0: ' NOT NULL', 1: '' } def gettype(fieldtype, fieldlength): if fieldtype == 'ntext': fieldtype = 'text' if fieldtype in without_width: return fieldtype return '%s(%s)' % (fieldtype, fieldlength) def create_table( tablename ): s = '' fieldnames = [] for fieldname, fieldtype, fieldlength, nullable in tables[tablename]: try: fieldnames.index(fieldname) continue except: fieldnames.append(fieldname) s += '\n %s %s%s,' % (fieldname.lower(), gettype(fieldtype, fieldlength), nullables[nullable]) return """ CREATE TABLE %s.%s ( %s );""" % (dbname, tablename, s.strip('\n')) def repair( tablename ): for fieldname, fieldtype, fieldlength, nullable in tables[tablename]: if fieldtype == 'ntext': sql = create_table( tablename ) sql = sql.replace( 'CREATE TABLE %s.%s' % (dbname, tablename), 'CREATE TABLE %s.__%s' % (dbname, tablename)) return sql def query( sql ): print sql cur.execute( sql ) con = pymssql.connect(host='127.0.0.1:1433',user='sa',password='',database=dbname) cur = con.cursor() sql = "SELECT id, name FROM sysobjects WHERE xtype='U'" cur.execute( sql ) cur1 = con.cursor() tables = {} while True: recs = cur.fetchall() for id, tablename in recs: tablename = tablename.lower() sql = """SELECT syscolumns.name, systypes.name, syscolumns.length, syscolumns.isnullable FROM syscolumns, systypes WHERE syscolumns.xtype = systypes.xtype AND syscolumns.id = %s ORDER BY syscolumns.colid""" % id cur1.execute( sql ) fields = [] while True: recs1 = cur1.fetchall() for rec1 in recs1: fields += [rec1] if not cur1.nextset(): break tables[tablename] = fields if not cur.nextset(): break for tablename in tables.keys(): sql = repair(tablename) if not sql: continue try: query( sql ) except pymssql.DatabaseError, msg: print msg sql = "DELETE FROM %s.__%s" % (dbname, tablename) query( sql ) sql = "INSERT INTO %s.__%s SELECT * FROM %s.%s" % ( dbname, tablename, dbname, tablename) query( sql ) con.commit() con.close()