Month: February 2012

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.