Archive for category Database
MS SQL Replace Line Break
SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')
Nested “Case” in SQL
SELECT COALESCE( CASE WHEN condition1 THEN calculation1 ELSE NULL END, CASE WHEN condition2 THEN calculation2 ELSE NULL END, etc... )
COMPUTERS How to Disable “Saving changes is not permitted” dialog box (SQL Server 2008)
To turn off the “Saving changes not permitted” dialog box, so that you don’t have to recreate a table in order to save changes to the table, follow these SQL Server 2008 configuration instructions:
- Open SQL Server Management Studio.
- On the menu choose Tools -> click Options
- In the Options pop-up box, expand Designers, and then click Table and Database Designers.
- Uncheck the option “Prevent saving changes that require the table to be re-created” check box.
- Click OK.
- Now when you go to save changes to a Table, you will be permitted to do so.
Data Replication using MS SQL Server
Consumer Point of Sale (POS) Applications
http://msdn.microsoft.com/en-us/library/ms151330.aspx
How to: Create a Publication and Define Articles (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms151160.aspx
SQL Server 2008 R2 Replication
Truncate / Purge SQL 2005 database log files
There are 2-3 methods but the method which I used is as follows:
1) Detach the database from the SQL Server Management Studio.
2) Go to the location of transaction log and rename it (don’t delete the file yet!!)
3) Attach the database without the log file. When you do this step make sure you highlight the log file entry in the wizard and remove it. (Final attach would happen only with one file i.e. with the MDF file).
At this point there would be a new log file created by the SQL Server.
4) Delete the log file once the database is attached and you have verified it.
The Data Loading Performance Guide
Posted by allenkwc in Database, Windows Server on June 27, 2009
MySQL-Front, a good client for managing MySQL Server for Windows
MySQL-Front version 5.1
MySQL-Front is a graphical GUI for the MySQL database. Because it is a “real” application, it can offer a more refined user-interface than is possible with systems built on PHP and HTML. Response is immediate, as there is no delay of reloading HTML-pages.
As a Windows GUI application, it has total control of the graphic display, information can be presented as clearly as possible. Your data is presented in a table with resizable column-widths and sorting capability.
If your provider allows it, MySQL-Front can make direct contact with the database. Otherwise, only one small script needs to be installed on the publishing website. Login information (and passwords if wanted) are stored on your hard disk, so you no longer have to log on to different web interfaces.
MySQL-Front presents a uniform front-end for all of your current database projects. In practice, you can work with the database of your choice after only a few clicks.
Defining and managing the database-structure, importing data, editing data is now simple with this full-featured program. For designers with only a basic grasp of databases, this program is the way to go to make you independent of SQL gurus.
MySQL-Front makes working with MySQL databases a snap without hiding the language from the user. The built-in SQL Editor provides syntax highlighting and context sensitive help, so even working with SQL commands and scripts will be a matter of convenience.
backup your MySQL using a bash script and cron job
1. dump the DB into a sql file
mysqldump -u <username> -p<password> <database> > <output file name>.sql
2. compress it
gzip <Filename>
3. Ftp send to backup server
** it is not suggested because backup ftp account and the backup data is sent in plain text format **
ftp -n $ftpserver <<END_SCRIPT
quote USER $ftpuser
quote PASS $ftppwd
put $gzipOutFile
quit
END_SCRIPT
exit 0
4. OR use SSH approach
You have to setup the connection using following setup, please refer to another post:
http://www.allenkan.com/blog/?p=138
Using an SCP (secure copy command) to copy the file to the host
scp <filename> <sshuser>@<sshhost>:<filename>