Updating Foreign Keys upon Primary Key changing in Sql Server

I’ve been doing a little (lot) of reading and wanted to try something I’ve never actually used in Sql Server. That is cascade updates.

The scenario is this!

  1. I have a table that has a primary key…. wow hey!
  2. I have another table that has a foreign key that references the primary key
  3. I want to update the primary key and have the foreign keys change their values

In this case the primary key is simply going to be a gender which will be a key that is char(10)

Please ignore my use of IDENTITY…  if you don’t like it… I’m beginning to understand you I think. I also didn’t really need it for the example. I added it early on, then changed a lot of things. I’m not going to remove it now.

/* This script shows how you can automatically update Foreign keys when you change a primary key. ********************************** IMPORTANT ********************************** I would create a test database to run this script on. It creates 3 tables and then deletes them. ******************************************************************************* */ /* Create tables */ -- Contains the list of keys that the Person and Contact table will -- be constrained to have in the Gender column... No other value can be put there CREATE TABLE GenderTypes ( Gender CHAR(10) PRIMARY KEY ) -- List of People. Has a Gender CREATE TABLE Person ( PersonID int PRIMARY KEY IDENTITY, Gender CHAR(10) FOREIGN KEY REFERENCES GenderTypes(Gender) ON UPDATE CASCADE ) -- List of Contacts. Has a Gender CREATE TABLE Contact ( ContactID int PRIMARY KEY IDENTITY, Gender CHAR(10) FOREIGN KEY REFERENCES GenderTypes(Gender) ON UPDATE CASCADE ) GO /* Insert some data into the tables. */ -- These are the catagories. Male, Female and Unknown (I know they are not -- really categories, but you will get the idea!) INSERT INTO GenderTypes(Gender) VALUES('Male') INSERT INTO GenderTypes(Gender) VALUES('Female') INSERT INTO GenderTypes(Gender) VALUES('Unknown') -- Create People with one of each Gender INSERT INTO Person(Gender) VALUES('Male') INSERT INTO Person(Gender) VALUES('Female') INSERT INTO Person(Gender) VALUES('Unknown') -- Create Contacts with one of each Gender INSERT INTO Contact(Gender) VALUES('Male') INSERT INTO Contact(Gender) VALUES('Female') INSERT INTO Contact(Gender) VALUES('Unknown') GO /* Change the "Unknown" Gender in the Categories table and watch the foreign keys change! */ -- Before the change SELECT s.PersonID, s.Gender AS SHGender, gt.Gender as Gender FROM Person s JOIN GenderTypes gt ON s.Gender = gt.Gender -- Change the Unknown Gender to UNKNOWN G UPDATE GenderTypes SET Gender = 'UNKNOWN G' WHERE Gender = 'Unknown' -- See if the Gender key has changed in both of the foreign keys that point to the -- GenderTypes tabels primary key SELECT s.PersonID, s.Gender AS SHGender, gt.Gender as Gender FROM Person s JOIN GenderTypes gt ON s.Gender = gt.Gender SELECT c.ContactID, c.Gender AS SHGender, gt.Gender as Gender FROM Contact c JOIN GenderTypes gt ON c.Gender = gt.Gender GO -- This just makes a bit of a division between the 6 results sets returned (well the -- (6 important ones!) SELECT '--------------- DIVIDER ----------------' GO /* Change the Gender back again and watch the foreign keys change! */ SELECT p.PersonID, p.Gender AS SHGender, gt.Gender as Gender FROM Person p JOIN GenderTypes gt ON p.Gender = gt.Gender -- Change the UNKNOWN G Gender back to Unknown UPDATE GenderTypes SET Gender = 'Unknown' WHERE Gender = 'UNKNOWN G' -- See if the Gender key has changed in both of the foreign keys that point to the -- GenderTypes tabels primary key. Should be back to Unknown. SELECT p.PersonID, p.Gender AS SHGender, gt.Gender as Gender FROM Person p JOIN GenderTypes gt ON p.Gender = gt.Gender SELECT c.ContactID, c.Gender AS SHGender, gt.Gender as Gender FROM Contact c JOIN GenderTypes gt ON c.Gender = gt.Gender GO -- Drop the tables! DROP TABLE Contact DROP TABLE Person DROP TABLE GenderTypes GO

The above is commented and I hope that they explain what is happening.

We’ve got the GenderTypes table which contains one column called Gender with a CHAR(10) data type. This column is the primary key.

We’ve got two tables called Contact and Person which basically have the same structure. In the declarations for the table I’ve added foreign key constrains to the GenderTypes tables Gender column. and also put the ON UPDATE CASCADE.

This will result in the following:

  1. The Person.Gender and Contact.Gender columns can only contain values that are in the GenderTypes table.
  2. When one of the rows in the GenderTypes is changed (That is the primary key is changed) the Person.Gender and Contact.Gender columns will be updated for every Foreign key that matched the primary key on the GenderTypes table before the update.

PowerShell Remote Computer Shutdown Script

I wanted to be able to just tell all the servers I work with to shutdown instead of having to Remote Desktop into the server and shutdown or to hold the power button down (Gasp!)

$cred = Get-Credential -Credential "Domain\SomeoneWithPermissions"
Write-Host "Shutting Down <serverNameHere>"
$server = Get-WmiObject "Win32_OperatingSystem" -ComputerName "ServerName" -Credential $cred
$server.Shutdown()

The first line specifies that you want to get the credentials of a given domain account and assign it to the $cred variable.

The second line just writes a message… I chose for it to write “Shutting down whateverTheServerNameis”

The third line get the WMI Object that will be used to communicate with the server specified by -ComputerName using the Credentials we stored in the $cred variable

The last line called shutdown on that objects, which calls the server/computer and tells it to shutdown nicely.

Note that when you run this you will get a dialog asking for the password of the account! you could also put that in but it would be a bit funny normally to put the password of such a powerful account into a text file… :S

Closures in JavaScript

Nice article by James Padolsey on Closures In JavaScript

Error: Missing URL parameter: Name in Microsoft.ReportViewer.WebForms

I’ve had to deal with this error often now. It goes a little something like this:

Error in 'Microsoft.ReportViewer.WebForms'.
Missing URL parameter: Name
Exception: System.ArgumentException
StackTrace:
at Microsoft.Reporting.WebForms.EmbeddedResourceOperation.PerformOperation(NameValueCollection
urlQuery, HttpResponse response)
at Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

The problem seems to be that the Uri that the ReportViewer control is sending to the server gets encoded resulting in “&Name=” being converted to “&amp;Name=” (and the same with other parameters.

The ReportServer seems to be smart enough to be able to accept or decode the Uri as the resources get sent back. I’m not sure if the Exceptions that are thrown are thrown and then the Report Server tried to decode the Uri and returns the requested resource, but either way the user seems like they are getting the resource.

I’ve found this and this post which indicates that Bluecoat proxy can be the cause of the mangled Uri.

The second post (here) had an entry that indicated some code that could be run. I suspect that he wanted to replace “&amp;” not “amp;” with “&”, but it is interesting. Here is the code that the person gave:

Sub Application_BeginRequest(ByVal sender As Object, ByVal e As EventArgs)
    If Not Request.ServerVariables.Item("HTTP_X_BLUECOAT_VIA") = Nothing Then
        HttpContext.Current.RewritePath(Request.Path & "?" & Replace(Request.QueryString.ToString, Server.UrlEncode("amp;"), "&"), True)
    End If
End Sub

I think the idea that they had has merit. And I hope to try this at a convenient time. Note that I haven’t changed the code example, but think that the test for “amp;” should possible be “&amp;” as there would be “&&” instead of just “&” in the Uri (I suspect 🙂 )

Microsoft Visual C++ Runtime Error, Explorer.exe

I’m running Vista Business and getting the following error only upon initial boot in the morning. If i restart it seems to be fine.

Microsoft Visual C++ Runtime Library

Runtime Error!
Program: C:\Windows\Explorer.EXE
This application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.

There are few possibly causes that I’ve come across for this.

  • People have said that the Google Toolbar causes this issue.. (I’ve uninstalled this to see how it goes.. Update: It didn’t work for me. Might for you though!)
  • Others have said that the Free Download Manager can cause it.
  • One person said that they ran OneCare to clean the registry. See the reply at the bottom. Only a few lines long. (Therefore another option would be to try a registry cleaner)

As far as I know I’ve not got the Free Downoad Manager installed. I do have the google toolbar though.

Will try and keep this updated as I figure it out or find more possibly solutions.

Issues While Restoring a SharePoint 2007 Backup

I’m going to list some of the issues that I’ve had while trying for a few days now to restore a SharePoint 2007 backup.

Ensure that you remove the previous backup/restore job

If you have a failed restore you will need to stop it before trying the restore again. You will actually get an error message and won’t be able to continue.

To stop it you need to open Central Administration

  1. Start Menu
  2. Administrative Tools
  3. SharePoint 3.0 Central Administration

Then:

  1. Click the “Operations” tab
  2. Select “Timer job definitions” under the “Global Configuration” section

You will then see a list of the timer jobs. If there was a previous restore (or a current backup happening possibly also) you will see and entry with the title “Backup/Restore”. This will prevent you restoring.

If it is fine to restore (This post assumes that it is safe to remove the timer) then:

  1. Click the link/title of the “Backup/Restore” item
  2. On the “Edit Timer Job” window click the “Delete” button.

You are now ready to attempt the restore again

Shared Services Getting in the Way

With my restoration i found that it was trying to restore the SharedServices1 Shared Service in the backup over the one that existed in my fresh install of SharePoint already.

For some reason I couldn’t rename it…. I just got an error. Instead I created a new one called “SharedServices2”.

  1. Start Menu
  2. Administrative Tools
  3. SharePoint 3.0 Central Administration
  4. Clicked on the “Shared Services Administration” section to the left of the page.
  5. Clicked the “New SSP” button
  6. Filled in any details (Esp the SSP Name)
  7. Specified the SSP Service Credentials
  8. Clicked “OK”
  9. Waited…
  10. Waited some more…
  11. Click “OK”

You can then set your new service as the default

  1. Click the “Change Default SSP” button
  2. Select the service you want as default (this should be “SharedServices2” as you want to delete “SharedServices1”
  3. Click “OK”

Now you can delete SharedServices1

  1. Move your mouse over “SharedServices1”
  2. Click “Delete”
  3. Choose whichever remove type and click “OK”

Naming Conflicts with Databases

To remove this issue I simply deleted all the database I had made in previous restore or made sure the database names that I defined during the restore process were not the same as the names of database in the database.

The database server is a named instance called “OfficeServers”!

JSLint

Quick link to JavaScript tool that can give you some hints about errors/possibly errors in your JavaScript code!

JSLint