Move sqlserver tables to dbo user

SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.tables
WHERE schema_id != SCHEMA_ID('dbo');

Starting JavaDB (Derby) in glassfish.

I was following sahoo's Blog for creating an enterprise application with ejb3.0.
http://weblogs.java.net/blog/ss141213/archive/2005/12/using_java_pers.html

Ran into problem since JavaDB isn't started automatically when glassfish starts.

Error was "Error connecting to server localhost on port 1527 with message Connection refused: connect."

Solution:

You will have to issue below command to start derby database:

GlassFishHome/bin/> asadmin start-database

Hope this helps someone having similar issues.

Samba File Sharing Ubuntu

SSH server is disabled by default in ubuntu. From package manager select & install ssh-server.

Samba File Sharing:

Setup up username & password:
smbpasswd -a
you'll be prompted to set the password.


Restart Samba service to take effect: [Just for good measure]
sudo /etc/init.d/samba restart

UBUNTU setting up environment variables.

Use file /etc/bash.bashrc to setup new environment variables.


Do not use: /etc/profile
Do not use: /etc/environment file might work too.

Procedure:

1. Edit: /etc/bash.bashrc
2. Add this line to the end of the file. This sets JAVA_HOME

export JAVA_HOME=/usr/lib/j2sdk1.5-sun/

Linux helpful commands

List of processes:
Solaris stats: prstat
Redhat stats: top


COMMON:
more /proc/version --- To see which version of linux
cat /proc/cpuinfo
cat /proc/meminfo

Creating symbolic link (shortcut in *unix) :
ln -s file_name shortcutname

Placing java in your path:
create link to the version of java into /usr/bin

Linux Runlevels:
who -r (gives run level of the system)
vi \etc\inittab (shows you the runlevel system is starting)

cd \etc\rc5.d (start up scripts for run level 5. K's represent donot start. S's represent start)
cd \etc\rc3.d (start up scripts for run level 5. K's represent donot start. S's represent start)
mv K36mysqld S36mysqld

ln -s ../init.d/jboss S36Jboss
mv S36Jboss K36Jboss
rm K36Jboss

WGET: [Connect to internet and download a file]

wget http://download_file_url

SQL to drop an index on table.

SQLServer:
DROP INDEX address.ix_address_loc;

Syntax for Microsoft SQLJet (and Microsoft Access):
DROP INDEX index_name ON table_name

Syntax for IBM DB2 and Oracle:
DROP INDEX index_name

Syntax for MySQL:
ALTER TABLE table_name DROP INDEX index_name

SQL for alter column

Sqlserver:

alter table customer alter column customer_name varchar(50);

Oracle Timestamp

update customer set timestamp=to_date('1998/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam') where customer_id=48303

More Info:
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html

DB2 Timestamp

SELECT STATEMENT:
select count(*) from customer where timestamp >='2010-01-01-00.00.00';

UPDATE (OR) INSERT STATEMENT:
update customer set timestamp=('2011-01-01-00.00.01.000000') where timestamp='2010-01-01-00.00.00';

Sql Server timestamp field

INSERT (OR) UPDATE STATEMENTS:

insert into customer (customer_id,timestamp) values (17982698, '2005-10-17T00:00:00.0');


SELECT STATEMENTS:

select count(*) from customer where timestamp >= convert(datetime, '2006/10/01') and issue_date <=convert(datetime, '2007/09/30');

Sql Current Timestamp.

For mysql, sqlserver, even db2 : current_timestamp

For oracle: sysdate

For mysql (one more function): now()

MySQL mysqldump tablename.MYD not found (Errcode: 24) when using LOCK TABLES

In mysql 4.1 --opt option of mysqldump was made default.

This makes mysql to LOCK TABLES.

SOLUTION:
Use --skip-lock-tables to disable.

Creating MySql Database & Setting up users.

Creating mysql Datbase:
1. At mysql command prompt issue command

create database <>;

2. Giving all users access to all the datbases from the server.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

3. Giving all users access to all the datbases from remote machines. '%' in the query signifies that.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

4. VERY VERY IMPORTANT DO NOT FORGET:
FLUSH PRIVILEGES;

LINUX MYSQL GOTCHAS

1. bind-address : If your server's ip doesn't change. Might want to make it your server's ip address. Just makes it easier to troubleshoot connectivity issues.

2. Have to have this under mysqld [this disables mysql's case sensitivity. This is only a feature on *unix operating systems. Telling you it can be quite annoying...]
lower_case_table_names = 1

Other key parameters under [mysqld] which can be quite handy.

key_buffer = 128M
max_allowed_packet = 5GB
transaction_isolation = READ-UNCOMMITTED [Especially when working with some persistence middleware provider like ejb2.1 app servers.]

3. /etc/mysql/my.cnf - Location of configuration file

4. /etc/init.d/mysql start - Starts mysql
5. /etc/init.d/mysql stop - Stops mysql
6. /etc/init.d/mysql restart - Restarts mysql.

How to import and export mysql database?

This assumes that mysql is in your classpath. Replace "dbname" in the below command with your own database name.

mysqldump -u root -p dbname > dump.sql (export)

mysql -u USER -p dbname < dump.sql (import)