Răzvan Cosma's scratchpad

random scripts of random value

SET NOCOUNT ON
DECLARE @stringToFind NVARCHAR(100)
DECLARE @stringToReplace NVARCHAR(100)
DECLARE @schema sysname
DECLARE @table sysname
DECLARE @count INT
DECLARE @sqlCommand NVARCHAR(max)
DECLARE @where NVARCHAR(max)
DECLARE @columnName sysname
DECLARE @object_id INT
SET @stringToFind = 'Smith'
SET @stringToReplace = 'Jones'
DECLARE TAB_CURSOR CURSOR FOR
SELECT B.NAME AS SCHEMANAME,
A.NAME AS TABLENAME,
A.OBJECT_ID
FROM sys.objects A
INNER JOIN sys.schemas B
ON A.SCHEMA_ID = B.SCHEMA_ID
WHERE TYPE = 'U'
ORDER BY 1
OPEN TAB_CURSOR
FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@table,
@object_id
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE COL_CURSOR CURSOR FOR
SELECT A.NAME
FROM sys.columns A
INNER JOIN sys.types B
ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID
WHERE OBJECT_ID = @object_id
AND IS_COMPUTED = 0
AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext')
OPEN COL_CURSOR
FETCH NEXT FROM COL_CURSOR
INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' + @stringToFind + ''',''' + @stringToReplace + ''')'
SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%'''
EXEC( @sqlCommand + @where)
SET @count = @@ROWCOUNT
IF @count > 0
BEGIN
PRINT @sqlCommand + @where
PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count)
PRINT '----------------------------------------------------'
END
FETCH NEXT FROM COL_CURSOR
INTO @columnName
END
CLOSE COL_CURSOR
DEALLOCATE COL_CURSOR
FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@table,
@object_id
END
CLOSE TAB_CURSOR
DEALLOCATE TAB_CURSOR

Find all columns of type datetime:
DECLARE @DataType VARCHAR(50)
SELECT @DataType = 'datetime'
SELECT * FROM Information_Schema.Columns WHERE Data_Type = @DataType order by table_name

Find all varchar columns, change to nvarchar:

— SQL 2005
SELECT 'ALTER TABLE ' + quotename(o.name) +
' ALTER COLUMN ' + quotename(c.name) +
' nvarchar(' + ltrim(str(c.max_length)) + ') ' +
CASE c.is_nullable
WHEN 1 THEN ' NULL'
WHEN 0 THEN ' NOT NULL'
END
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE t.name = 'varchar'
ORDER BY o.name, c.name

— SQL 2000
SELECT 'ALTER TABLE ' + quotename(o.name) +
' ALTER COLUMN ' + quotename(c.name) +
' nvarchar(' + ltrim(str(c.length)) + ') ' +
CASE columnproperty(o.id, c.name, 'AllowsNull')
WHEN 1 THEN ' NULL'
WHEN 0 THEN ' NOT NULL'
END
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE t.name = 'varchar'
AND o.xtype = 'U'
ORDER BY o.name, c.name

connectionstrings.com

This is a simple attempt to build an easily replaceable and cheap storage server. Cheap means a lot of SATA drives in RAID1 or 5 with both disks and controller expected to fail miserably at any time. Linux makes booting from a RAID array unnecessary difficult (even if it’s in a mirror config, c’mon Windows got that right around NT4) so as a quick solution I used an old and slow ATA drive to boot who held the drivers and configuration for the 1TB array. Obviously, the drive died, and the system image backup was stored on the RAID disks so I:
1. booted a random install disk (knoppix will do)
2. mounted the array (if you don’t dig mdadm, Mandriva’s rescue mode will do the work for you)
3. got a cheap-ass USB key ($20 for 2GB)
3. formatted the key to have one active partition, the filesystem type doesn’t matter, but don’t use it windows-style meaning having a drive like /dev/sdf – you want a partition on it called /dev/sdf1
3. copied everything on /dev/sdf1
4. chroot on the key dir, mind you the next steps are important
5. edited grub.conf and menu.lst to point to this device
5.1 if the USB drive is listed as something like /dev/sdf when you are in the running system, but you do the config without all the SATA disks connected and assume it will be /dev/sda on boot: wrong. SATA disks will be detected first and after all of them are set up using initrd drivers, you’ll see a message “waiting for /dev/sdf to show up” or similar.
5.2 you did include ext, jbd, reiser, etc in initrd, didn’t you?
5.3 grub-install /dev/sdxx
6. fiddled with the bios until it decided to boot from the key – this highly depends on manufacturer and version of motherboard
7. am running SLES just fine in this setup, there might be some wear due to writes in /var/log but for a private storage machine it really doesn’t matter if logs are lost, you can rely on the hardware to spread them, or in Linux to buffer writes or with some time to spare read into unionfs
8. bought two other cheap-ass same manufacturer keys (Sandisk is cool, HP sucks, some other dumbasses changed the sector size from 512 to 2048 to mimic a CD drive and won’t work at all, see this thread for details)
0. remember to use UUID instead of /dev device names for RAID arrays – this will hurt really bad if forgotten.

1. Install FreeTDS. (./configure && make && make install)
2. Install PHP with freetds support (./configure –with-sybase=DIR, where DIR is wherever you placed freetds usually /usr/local)
3. Read a bit of /etc/freetds.conf (not mandatory)
4.
ini_set('mssql.secure_connection','On');
$con = mssql_connect ("1.2.3.4", "user", "pass") or die ("Could not connect to SQL server"); //.mssql_get_last_message()
mssql_select_db ("thedb", $con) or die ("Cannot open database");
$sql= "select top 10 * from test";
$rs= mssql_query ($sql, $con);
while($line = mssql_fetch_row($rs)){
print "$line[0]<br>";
}
mssql_close ($con);
exit(0);

Using the MSSQL Server extension on Win32 requires these steps:
1) Install the SQL Server Client tools on the machine running PHP
(you can get that for free from Microsoft, the one you need is probably sqlncli.msi)
2) Configure a server alias using the client network utility. The alias specifies name, host name, protocol and port numbers used for the db communication
3) Connect to the database using the alias as db name as first parameter in mssql_connect
4) If the SQL Server is configured for trusted connections only, enable this in PHP.INI by setting mssql.secure_connection = On (no need for userid and password then)

Apparently the wireless card on this model is very stubborn, quick HOWTO:
#-1 madwifi doesn’t work, be it stable version or svn commit up to 3745
#0 upgrade to the latest packages (kernel 2.6.24-19, ndiswrapper 1.50)
#1 rmmod ath_pci, ath_rate_sample, ath_hal, wlan, everything until you’re sure there there are no wireless modules loaded
#2 mcedit /etc/default/linux-restricted-modules-common
DISABLED_MODULES=”ath_hal”
#3 apt-get install ndiswrapper-common
#4 unpack this driver somewhere
#5 ndiswrapper -i net5211.inf
#6 modprobe ndiswrapper
#7 iwconfig (you should have a wlan0 interface now, either that or your system has crashed completely and you’re not reading the rest)
#8 iwconfig wlan0 mode managed essid blah key blah ; dhclient wlan0
#9 profit

Maybe 10 if you’re a slow typist. In short:
osascript -e 'tell app "ARDAgent" to do shell script "/Applications/iTerm.app/Contents/MacOS/iTerm"'

You might get a few screenfulls of errors

but the command WILL execute:

This is by far the dumbest bug I’ve encountered in the past decade, and one that makes me appreciate for the first time the separation between desktop applications and services enforced in Windows NT. A system daemon, running with root privileges, should never ever accept arbitrary input from arbitrary processes, running under arbitrary accounts, and, at the very least, it should try to NOT EXECUTE THE FUCKING INPUT.
Several mentions on the web claim that this requires local access or that the calling user is also logged on to the graphical interface, this is incorrect, AppleScript can be invoked over a ssh session by a different user (hint: Remote Apple Events)

No clue how this is done, waiting for helpful comments below.
Errata: found out how – check this video example

sudo su –
apt-get install ufw
ufw allow 22/tcp
ufw enable
Ctrl-D

This time a native install, allocating disk space for the Linux host, transferring all data and emulating the guest Windows system (for business purposes)
Step -1: backup everything on an external disk, Apple’s Time Machine is a good tool for this
Step 0: boot into OS X and make sure all your applications are running and data is intact
Step 1: install rEFIt from Sourceforge, it’s pretty straightforward
Step 2: disable journaling on the OS X partition
diskutil disableJournal /Volumes/Macintosh\ HD
(run that twice, just to be sure you see the message “already disabled”)
Step 3: delete as much useless data as possible to make room for the new partition
Step 3: (try to) defragment the OS X partition, iDefrag does a reasonable job
Step 0
Step 4: read carefully the parted manual, it applies changes immediately and will happily fuck up the drive if you mistype a single letter
Step 4: boot any recent Linux live cd, parted, select /dev/sda, resize 2 (1 is the EFI FAT partition), DO NOT try to move the partition, only shrink it!, mkpart primary ext2, reboot
Step 5: enter the rEFIt partitioning menu, apply whatever it says must be applied
Step 0
Step 6: boot the Ubuntu install media, follow the usual steps, making sure you select the bootloader to be Grub (LILO cannot handle EFI) and install it on the Linux partition (I assume it’s /dev/sda3 or hd(0,2)), NOT in the MBR – I don’t think there even is a MBR on the Macs
Step 0
Step 7: boot the shiny new Ubuntu, 8.04 has the minimal hardware support needed to show you a graphical desktop

continue reading…

Bad Behavior has blocked 248 access attempts in the last 7 days.