___ __ __ __
| |_ / |__||\ |/ \
| |__\__| || \|\__/
- beauchamp.me -With SQL Server Standard Edition and above, it is easy to copy a database. You simply have to right-click on its name, select Tasks and select Copy Database… However that menu item is not there in SQL Server Express. Googling for a solution, I found a script that does just that, but it was written […]
I needed to be able to send emails from a stored procedure using something like EXECUTE msdb.dbo.sp_send_dbmail @recipients=’address@domainname.com’, @subject = ‘Test email’, @body = ‘Test email body’ The solution was to add the user I was using to run the above query to the msdb database and to assign the role DatabaseMailUserRole to the user: […]
After restoring a database on a different server than the one it is coming from, it will contain orphaned users. Here is how to find these orphaned users: exec sp_change_users_login ‘report’ In order to fix this, the logins for the reported users will have to be created on the new server if they do not […]
Yesterday, I wanted to use SQL Server Profiler to check what was happening on the database side of an application I am developping. However, SQL Srever Profiler was giving me the following error message: In order to run a trace against SQL Server you must be a member of sysadmin fixed server role or have […]
I am working on a database containing city names in French, and I needed to use these names to create URLs. However, the city names contain accents and special characters like apostrophe that I did not want in my URLs. So I had to write a User Defined Function to make the city names Unix […]
Once, I was in a situation where I had to change the collation of a database. This is not an easy task, since collations are defined at three levels: Database, Table and Columns. So even, if you change the database collation, all the table and fields will still retain the original collation. Changing collation in […]
Recently, I was in a situation where I had to move data from an old version of a database to a newer version of the same database. The structure of the database had changed slightly. I was trying to figure out what data to move first. I wanted to move data that was referenced before […]
Sometimes you want to find out where a table or a column is referenced in a database. I found this script that does just that.
I came accross a situation where I needed to temporarily insert multiple rows in a table with a Null value in a column with a UNIQUE constraint on it. In other words, I wanted the values in that column to be unique, except when they are Null. According to ANSI SQL specifications, this should be […]
For the past few days, I have been working on the new version of a database. The database was in development and had to go in production. Of course, the right structure and lookup tables data was in the development version of the database and the good data was in the old production database. Both […]