Archive for category Database

MS SQL Replace Line Break

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')

No Comments

Nested “Case” in SQL

SELECT COALESCE(
  CASE WHEN condition1 THEN calculation1 ELSE NULL END,
  CASE WHEN condition2 THEN calculation2 ELSE NULL END,
  etc...
)

No Comments

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:

  1. Open SQL Server Management Studio.
  2. On the menu choose Tools -> click Options
  3. In the Options pop-up box, expand Designers, and then click Table and Database Designers.
  4. Uncheck the option “Prevent saving changes that require the table to be re-created” check box.
  5. Click OK.
  6. Now when you go to save changes to a Table, you will be permitted to do so.

,

No Comments

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

http://msdn.microsoft.com/en-us/library/ms151198.aspx

No Comments

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.

No Comments

The Data Loading Performance Guide

http://msdn.microsoft.com/en-us/library/dd425070.aspx

No Comments

MySQL-Front, a good client for managing MySQL Server for Windows

MySQL-Front version 5.1

Data Browser Screenshot of MySQL-Front shows an easy to use GUI implementation of a MySQL Table viewing and editingMySQL-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.

Object Browser Sreenshot of MySQL-Front shows an easy to use GUI implementation of the table structure a MySQL databaseDefining 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.

No Comments

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>

1 Comment