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

 

Images not displaying in CFDOCUMENT PDF

When creating a PDF which contains images I found that it worked in my development envirmonet but would not work in the production environment. The only difference was that the production environment had an SSL. Rightly so the SSL was causing the issue and so the PDF would display a small box with a red cross.

To get round this there are a couple of ways. You can ensure that your certificated is added to the trusted certificate store (runtime/lib/trustedStore). You can use keytool to list/view/import… certificate in the certificate store. This can get pretty involved if you are not familar with what you are doing. The other method is very simple and a quick change to the code, it uses the file protocol identifier in the image path and you will find it works a treat!

This is how we insert an image using HTML:

<img scr="https://www.mydomain.com/images/myimage.jpg" width="60px" height="60px">

When creating a PDF where the site has an SSL you simply do the following:

<img scr="file:///d:\mysite\images\myimage.jpg" width="60px" height="60px">

It is a simple as that and it’s a very quick fix and will save you time.

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.

Internet Explorer form submit by enter key

Recently I was faced with an issue whereby when the user enters data into a single text input field form and submits via the enter key. No form fields would be passed to the processing page – this worked in all browsers but not Internet Explorer.

<form action="" method="POST">
    Field 1 <input name="field_1" type="text" />
    <input name="submit" type="submit" value="submit" />
</form>

The work around to this weird and wonderful bug is to add an additional disabled input field for IE, using IE conditional comment and hiding it from view using some CSS.

<form action="" method="POST">
    <!--[if IE]>
        <input type="text" style="display: none;" mce_style="display: none;" disabled="disabled" size="1" />
    <![endif]-->
    Field 1 <input name="field_1" type="text" />
    <input name="submit" type="submit" value="submit" />
</form>

This will now enable users to submit the form via the enter key in IE.