Category: SQL

Migrating from Mango Blog to WordPress

I finally decided to move from Mango Blog to WordPress purely down to hosting and its costs.

I was on the hunt for something that would take all my posts, categories and comments and import them into the WordPress. I finally came a cross a blog entry by Marcos Placona where he wrote a script in CFML to import from Mango Blog to WordPress.

The scrip can be downloaded from here Mango2WordPress.

The script is pretty straight forward add the two data source names (Mango Blog and WordPress) and then run the scrip on the ColdFusion/Railo server.

I came across two issues

  1. No variable for set up for the table prefix in the Mango Blog database
  2. Everything but the post contents got migrated. Since I did not have many blog posts it was easy for me to copy and paste them over.

[Macromedia][SQLServer JDBC Driver]Object has been closed.

I came across this very odd error today while trying to write a query in Coldfusion 9. I ensured the query worked in SQL Management Studio but each time I called the CFM page I was presented with the following error:

[Macromedia][SQLServer JDBC Driver]Object has been closed.

Having double checked everything, from my code to the set up of the server and DSN – everything looked perfectly as it should.

Finally, I stumbeled upon the solution: either resart the Coldfusion server or make a change to the DSN. I hope this helps saves others time as it took me forever to work this out.

Change Schema name on Tables and Stored Procedures in SQL Server

It maybe necessary to change schema of a data for a number of reasons. I generally find myself doing this when I am re-using a database. At the same time I would add a new login and a new user. To change the schema for all the tables in the database you simply run the following query:

SELECT 'ALTER SCHEMA NEW_SCEHEMA TRANSFER ' + s.Name + '.' + p.Name 
FROM sys.Tables p 
INNER JOIN sys.Schemas s ON p.schema_id = s.schema_id

This is produce a list of code (one line per table) which you would need to copy and paste into a Query Analyzer window and run. This will in turn change the schema of every table in the database.

The syntax to change the schema is:

ALTER SCHEMA NEW_SCEHEMA TRANSFER OLD_SCHEMA.TABLE_NAME

In order to change the schema of Stored Procedures you use the same code as above but change sys.Table to sys.Procedures.

It is also useful to know how to delete the old schema or even the user.

Delete Scheme

DROP SCHEMA SCHEMA_NAME

Delete User

DROP USER USER_NAME

 

Restoring a MS SQL database from one server to another

I find myself restoring databses from one server to another from time to time. I generally do this when we want to take a snap shot of a live database and use it on a development site.

The easiest way to restore the database from a backup file (.bak) is to use the Microsoft SQL Server Management Studio GUI.

  1. Once logged into the server you want to restore the database to. Expand the database folder in Object Explorer panel so you see all your databases listed.
  2. Right click on the database you want to restore then select
    Tasks -> Restore -> Database…
    restoreDB1
  3. This will bring up a window titled ‘Restore Database – yourdbname‘. Click the radio button ‘From Database’ followed the ‘…’ button.
    restoreDB2This bring up a window titled ‘Specify Backup’ click the ‘Add’ button this will allow you to browse your computer and locate the backup file (.bak), click ok twice and this will return you back to the ‘Resotre Database – yourdbname‘
  4. On the ‘Restore Database – yourdbname‘ window ensure the restore check box is checked under ‘Select the backup sets to restore:’
    restoreDB3
  5. Now we have selected where to restore our back up from we need to set a few paramerters before we can restore. Click on Options from the ‘Select a page’ panel.
  6. On the Options screen we need to check ‘Overwrite the existing database (WITH REPLACE)’ under restore section. Also we need ensure we are restoring the database to the correct files. Ensure the paths to ‘Raw Data’ and Log are pointing to the correct files. This is particularly important if you are running production and development databases of one server.
    restoreDB4
  7. Now we have everything set click on OK and it will start to restore the database.

This is pretty much all you need to do to restore a database. However, you may find you will have an orphan user that you need to link the database user to a SQL server login.

If you have created the original login name on the SQL server before the datebase restore, then run the following command against the database from the query analyser.

sp_change_users_login 'auto_fix', 'Login'

You must specify the login in the above command.

If however, you have multiple users in the databse which have their own logins, you will have to the orphan them individually as follows:

sp_change_users_login 'update_one', 'user', 'Login'

You must specify the user and login. The login must exist.