Skip to content

CategoryDatabases

Kooboo CMS First Impressions

Kooboo is a CMS based on ASP.NET MVC. Recently, I got a chance to take it for a spin, and here are some of my thoughts. Keep in mind that the drawbacks here may come just from my ignorance of the tool use. :) I’ll update this as the more I learn about the inner workings.

Benefits

  • Admin Panel’s UI is intuitive for designers/programmers
    • Easy to add pages
    • Easy to add your own themes/styles
    • Easy to create your own type of content
    • Easy to add content
  • Lots of Features, more than Orchard
  • Mature, has been around for a while (2008)
  • Views are coded in Razor
  • Can connect to MongoDB and other datasource types
  • Versioning of any piece of content and view differences
  • Manage website resources easily – images/documents/etc.

Drawbacks

May not be a drawback once I figure out the “how” and get better understanding.

  • Once a site is created, when I migrated from XML to MongoDB, I lost all the website data from the XML files.
  • Admin Panel’s UI may not be intuitive to non-designers/programmers.
  • Site directory structure Kooboo generates is not the same as the traditional ASP.NET MVC.

Let’s take a look. For a site I created using Kooboo, named “batman”:

  • When a content type is created, it does not create a C# class file. (I didn’t see one at least, in the directory structure.) It does, however, create a MongoDB collection for the content, there’s just no C# class mapped to it.
  • There’s no clear way to bind a View to a model class as in traditional ASP.NET MVC since, Kooboo doesn’t create a C# class file. It doesn’t follow the traditional file/folder naming convention: for each View, you map that to a model.
  • Community not as large as other CMS communities (Orchard, Umbraco, DNN).

I’ll keep exploring, but this is what I’ve found so far.

Checklist to Consider Before Migrating from SQL Server to NoSQL

Here’s a checklist of items to consider before migrating to a NoSQL db from SQL Server. If you come from SQL Server land, you may be used to these features and facilities. So before shifting over to a different world, consider if the db you’re jumping into (MongoDB, CouchDB, etc.) meets your needs by analyzing these items. Keep in mind that some features may not exist in NoSQL, not because of immaturity, but because it may not apply to the nature of a NoSQL database.

Handle Load

Compare against SQL Server:

Reading

Writing

Resource Monitoring

CPU Usage

Memory Use

Disk use

Transactions

Are there software design patterns to rollback

Locking

Reading

Programming

Syntax

SELECT / UPDATE / DELETE / INSERT / JOINS

Reusable Code Modules

Compiled Stored procs

Functions

Custom Data Types

Dynamic Management Views

Indexing

Clustered

Non-Clustered

Toolset / Resources

IDE

Administration

Profiler

Excution Planner

Community

Books

Administration

Complexity

Backing up

Full

Differentials

Transaction Log

Recovery

Replication / Clustering

Snapshots

Security

C# Integration

Facilities

Full Text Search

Snapshots

SSMS Template Explorer (Snippets)

I sometimes see people miss this. A very resourceful feature is the Template Explorer in SQL Server Management Studio. Here’s how to take advantage of it:

1. First you show the panel in SQL Server:

2. Double click on the snippet/template. We’ll double click on the Create Multi-statement table-value Function:

3. After you do so, it’ll open up a new query window with the source of the snippet.

4. Do CTRL+SHIFT+M to fill in the parameters:

Getting Started with the MongoDB C# Driver

Here’s my attempt on writing a super-barebones tutorial for using the MongoDB C# Driver. It’s a very hands-on tutorial-by-example, showing you the basics of the driver by creating console apps.

I’m assuming you’ve installed the official MongoDB C# driver and read the intro. Heads up on how you set up the driver. The .msi sets it up in the GAC. The zip file has the assemblies that can just be referenced in your project.

After you’ve done those two things, you can proceed. :)

Dependencies

In projects, you have the option of utilizing the following namespaces:

using MongoDB.Bson;
using MongoDB.Driver;
using MongoDB.Bson.IO;
using MongoDB.Bson.Serialization;
using MongoDB.Bson.Serialization.Attributes;
using MongoDB.Bson.Serialization.Conventions;
using MongoDB.Bson.Serialization.IdGenerators;
using MongoDB.Bson.Serialization.Options;
using MongoDB.Bson.Serialization.Serializers;
using MongoDB.Driver.Builders;
using MongoDB.Driver.GridFS;
using MongoDB.Driver.Wrappers;

The most commonly used are Bson, Driver, Serialization, and Serialization.Attributes. This tutorial will use these four in all code samples, so make sure you include them in your app, even if I don’t have it in the code samples. You can learn more about the API/namespaces by checking out the docs.

Connecting to a MongoDB Database

Connecting is pretty simple. Once you include the C# driver assemblies, you connect as follows:

namespace ConsoleApplication1
{
  class Program
  {
    static void Main(string[] args)
    {
      MongoServer  server = MongoServer.Create("mongodb://work2");
      MongoDatabase Cars = server.GetDatabase("Cars");
    }
  }
}

That’s pretty much all you need to connect. Of course, I recommend putting the connection string in the Web.config, or some initializer.

Inserting a document on MongoDB

There are tons of ways to do this, but one of the simplest ways is to create a class, create an instances of that class, and pass it into a MongoDB object that serializes that object into BSON and submits it to your database.

So for this example, before inserting a document into the database from C#, one has to do two things (aside from the establishing a connection and choosing which db to write to):

  1. Create a Generic Collection object. In this case, it’s a MongoDB collection.
  2. Create a class that’s to be used by the Generic Collection.

Those two go together.

Also, note that while working with MongoDB in C#, the word “collection” may be thrown around loosely. In MongoDB you have a “Collection” that’s comparable to a Table in a traditional ER database. A MongoDB “Collection” holds “documents,” which is comparable to a “record” in a traditional ER database. On the opposite side, in C#, a collection is a data structure that unlike an Array, can be resized dynamically and is more flexible as far as functionality.

The MongoDB Generic Collection is a C# collection that can hold any type of object, but it has to know what type of object it’s holding. You let it know what kind of type by creating a class, and then when you create a collection, you specify the name of the class. So for example, this is the syntax for creating a Generic Collection that has Car objects (where person is defined in a class):

After that line of code, you populate the vehicles object (remember it’s a MongoDB Generic collection that holds Cars objects), by calling the method “GetCollection” from the Cars object we creating in the last section “Connecting to a MongoDB database”:

In the above statement, if MongoDB does not find a collection on the database called “vehicles,” it will create it for you.

What does that Cars class look like? Pretty simple:

  public class Cars
  {
    public string _id { get; set; }
    public string brand { get; set; }
    public string lname { get; set; }
    public Int32 miles { get; set; }
    public int age { get; set; }
  }

Then of course, before sending it to the MongoDB database, you create an object and set the values of the object:

      Cars newCar = new Cars();
      newCar._id = System.Guid.NewGuid().ToString();
      newCar.brand = "Honda";
      newCar.miles = 186000;
      newCar.age = 1;

And then insert it in MongoDB:

vehicles.Insert(newCar);

To put it in perspective, here’s the entire program:

namespace ConsoleApplication1
{
  public class Cars
  {
    public string _id { get; set; }
    public string brand { get; set; }
    public string lname { get; set; }
    public Int32 miles { get; set; }
    public int age { get; set; }
  }

  class Program
  {
    static void Main(string[] args)
    {       

      MongoServer  server = MongoServer.Create("mongodb://work2");
      MongoDatabase Cars = server.GetDatabase("Cars");

      MongoCollection vehicles;

      vehicles = Cars.GetCollection("vehicles");

      Cars newCar = new Cars();
      newCar._id = System.Guid.NewGuid().ToString();
      newCar.brand = "Honda";
      newCar.miles = 186000;
      newCar.age = 1;      

      vehicles.Insert(newCar);
    }
  }
}

The insert method should serialize the Cars object (newCar) and create the MongoDB document, mapping the properties of the Cars class to MongoDB document key-pair values.

Let’s see how this got created in our database:

Run:

db.vehicles.find()

(e.g. ignore the lname property. This was removed in the source code)

Inserting Complex Objects in MongoDB

Let’s try inserting an object with a more complex data structure. Let’s assume we have the following class:

  public class Cars
  {
    public string _id { get; set; }
    public string brand { get; set; }
    public string lname { get; set; }
    public Int32 miles { get; set; }
    public int age { get; set; }

    public Hashtable history { get; set; }
    public Hashtable complex { get; set; }
    public int[] magicNumbers { get; set; }
  }

Now let’s declare the class and its properties:

      newCar._id = System.Guid.NewGuid().ToString();
      newCar.brand = "Honda";
      newCar.miles = 186000;
      newCar.age = 1;
      
      newCar.history = new Hashtable();   
      newCar.history.Add("2010-08-14", "Puss 'N Boots");
      newCar.history.Add("2011-12-01", "Puff the Magic Dragon");
      newCar.history.Add("2009-03-07", "Fraggle Rock");      

      newCar.magicNumbers = new int[3];
      newCar.magicNumbers[0] = 60;
      newCar.magicNumbers[1] = 186000;
      newCar.magicNumbers[2] = 1440;

      newCar.complex = new Hashtable();
      newCar.complex.Add( newCar.magicNumbers[1], newCar.history );
      newCar.complex.Add( System.Guid.NewGuid().ToString(), newCar.history); 

It outputs the following (formatted for better reading):

{
  "_id": "a07513c8-9cb5-46cd-a9cb-425cabc0dd76",
  "brand": "Honda",
  "lname": null,
  "miles": 186000,
  "age": 1,
  "history": 
  {
    "2010-08-14": "Puss 'N Boots",
    "2009-03-07": "Fraggle Rock",
    "2011-12-01": "Puff the Magic Dragon"
  },
  "complex": 
  [
    ["eb7b3d4d-87b1-43c1-9fe7-5a5e4b5b79de",
    {
        "2010-08-14": "Puss 'N Boots",
        "2009-03-07": "Fraggle Rock",
        "2011-12-01": "Puff the Magic Dragon"
    }],
    [
    186000,
    {
        "2010-08-14": "Puss 'N Boots",
        "2009-03-07": "Fraggle Rock",
        "2011-12-01": "Puff the Magic Dragon"
    }]
  ],
  "magicNumbers": [60, 186000, 1440]
}

Now let’s look at the entire code for all this:

namespace ConsoleApplication1
{
  public class Cars
  {     
    public string _id { get; set; }   
    public string brand { get; set; }  
    public string lname { get; set; }  
    public Int32 miles { get; set; }  
    public int age { get; set; }
    
    public Hashtable history { get; set; }
    public Hashtable complex { get; set; }
    public int[] magicNumbers { get; set; }
  }

  class Program
  {   
    static void Main(string[] args)
    {
      
      MongoServer  server = MongoServer.Create("mongodb://work2");          
      MongoDatabase Cars = server.GetDatabase("Cars");

      MongoCollection vehicles;
      
      vehicles = Cars.GetCollection("vehicles");
      
      Cars newCar = new Cars();

      newCar._id = System.Guid.NewGuid().ToString();
      newCar.brand = "Honda";
      newCar.miles = 186000;
      newCar.age = 1;
      
      newCar.history = new Hashtable();   
      newCar.history.Add("2010-08-14", "Puss 'N Boots");
      newCar.history.Add("2011-12-01", "Puff the Magic Dragon");
      newCar.history.Add("2009-03-07", "Fraggle Rock");      

      newCar.magicNumbers = new int[3];
      newCar.magicNumbers[0] = 60;
      newCar.magicNumbers[1] = 186000;
      newCar.magicNumbers[2] = 1440;

      newCar.complex = new Hashtable();
      newCar.complex.Add( newCar.magicNumbers[1], newCar.history );
      newCar.complex.Add( System.Guid.NewGuid().ToString(), newCar.history); 
      
      vehicles.Insert(newCar);         
      
      Console.ReadLine();                
    }     
  }
}

Reading the 1st MongoDB document from the database

Before you query the database, you must make sure the class and member types match the values/objects that the MongoDB connection will return.

Let’s try to fetch the 1st document. To see what we’re expecting, let’s fetch it via the console first:

db.vehicles.find().limit(1)

which returns:

{
  "_id" : ObjectId("4d95f30058430000007a5943"),
  "ObjectId" : ObjectId("000000000000000000000000"),
  "brand" : "Ford",
  "lname" : null,
  "miles" : 2500,
  "age" : 1
}

Now that we know what it returns, let’s make sure we have the class in place that will handle the kind of key-value pairs in the MongoDB collection:

  public class Cars
  {
    public MongoDB.Bson.ObjectId _id { get; set; }
    public MongoDB.Bson.ObjectId ObjectId { get; set; }
    public string brand { get; set; }
    public string lname { get; set; }
    public Int32 miles { get; set; }
    public int age { get; set; }

    // Complex Properties
    public Hashtable history { get; set; }
    public Hashtable complex { get; set; }
    public int[] magicNumbers { get; set; }
  }

Notice that the properties history, complex, and magicNumbers are not part of the MongoDB document we queried. That’s OK. The MongoDB C# drivers are smart enough to map to to a null value when deserializing from MongoDB to a C# object.

Now how do you map values from a MongoDB connection to a C# object? Like so:

    Cars car = vehicles.FindOne();

The FindOne() method will return the first document. Now let’s add some context to this line of code, by adding MongoDB connection code (that we established before), writing it to the screen, and checking for nulls in the complex properties:

namespace ConsoleApplication1
{
  public class Cars
  {
    public MongoDB.Bson.ObjectId _id { get; set; }
    public MongoDB.Bson.ObjectId ObjectId { get; set; }
    public string brand { get; set; }
    public string lname { get; set; }
    public Int32 miles { get; set; }
    public int age { get; set; }

    // Complex Properties
    public Hashtable history { get; set; }
    public Hashtable complex { get; set; }
    public int[] magicNumbers { get; set; }
  }

  class Program
  {
    static void Main(string[] args)
    {
      MongoServer  server = MongoServer.Create("mongodb://work2");
      MongoDatabase Cars = server.GetDatabase("Cars");

      MongoCollection vehicles;
      vehicles = Cars.GetCollection("vehicles");

      Cars car = vehicles.FindOne();

      Console.WriteLine(car._id);
      Console.WriteLine(car.ObjectId);
      Console.WriteLine(car.brand);
      Console.WriteLine(car.lname);
      Console.WriteLine(car.miles);
      Console.WriteLine(car.age);

      if (car.history == null)
        Console.WriteLine("history was null");
      else
        Console.WriteLine(car.history);

      if(car.complex == null)
        Console.WriteLine("complex was null");
      else
        Console.WriteLine(car.complex);

      if (car.magicNumbers == null)
        Console.WriteLine("magicNumbers was null");
      else
        Console.WriteLine(car.magicNumbers);                  

      Console.WriteLine("ha made it this far!");
      Console.ReadLine();
    }
  }
}

This will print the following on to the screen:

Read All Documents from a MongoDB Collection

Let’s try to output all the documents from a collection. We’re going to create a new MongoDB collection under the Cars database. Through the MongoDB client, we’ve created four documents. Let’s read them from the console first:

db.bankaccount.find()

The four documents look like:

{ "_id" : ObjectId("4d9650301b6e000000004116"), "name" : "Batman" }
{ "_id" : ObjectId("4d9650391b6e000000004117"), "name" : "Spider-Man" }
{ "_id" : ObjectId("4d96503f1b6e000000004118"), "name" : "Superman" }
{ "_id" : ObjectId("4d9650431b6e000000004119"), "name" : "Spawn" }

So the first thing is to create a class that represents each MongoDB document:

  
public class BankAccount
  {
    public MongoDB.Bson.ObjectId _id;
    public string name;
  }

The class is used to create an object that will contain the data that is deserialized (from MongoDB to a C# object).

Here’s the code to query all documents, iterating through them and showing them on the screen:

      MongoCursor cursor = BankAccountCollection.FindAll();

      foreach (BankAccount ba in cursor)
      {
        Console.WriteLine(ba._id);
        Console.WriteLine(ba.name);
        Console.WriteLine("=============================");
      }

Looks straightforward enough. What is a cursor? We can think of a MongoDB Cursor as a DataSet in .NET. Later, we’ll see MongoDB Queries, which resemble a SqlCommand object in .NET. Queries are configurable objects to search for document data. The Query object is then passed into the a find method (there are several) of the MongoCollection. The find method then typically returns a Cursor, which is then iterated through. (More about this later.) Also note that a Cursor is not “populated” with documents until the there’s an attempt to retrieve the first result from the server.

Let’s see the entire program in action.

namespace ConsoleApplication1
{
  public class BankAccount
  {
    public MongoDB.Bson.ObjectId _id;
    public string name;
  }

  class Program
  {
    static void Main(string[] args)
    {
      MongoServer  server = MongoServer.Create("mongodb://work2");
      MongoDatabase BankAccountDatabase = server.GetDatabase("Cars");

      MongoCollection BankAccountCollection;
      BankAccountCollection = BankAccountDatabase.GetCollection("bankaccount");

      MongoCursor cursor = BankAccountCollection.FindAll();

      foreach (BankAccount ba in cursor)
      {
        Console.WriteLine(ba._id);
        Console.WriteLine(ba.name);
        Console.WriteLine("=============================");
      }      

      Console.WriteLine("\n\nha made it this far!");
      Console.ReadLine();
    }
  }
}

The above program outputs the following:

If we wanted to retrieve a specific query, rather than doing this (from the above code):

     
 MongoCursor cursor = BankAccountCollection.FindAll();

We would do:

      var query = Query.EQ("name", "Batman");
      var cursor = BankAccountCollection.Find(query);

One last thing to mention about the Find() method, as stated from docs:
The Find method (and its variations) don’t immediately return the actual results of a query. Instead they return a cursor that can be enumerated to retrieve the results of the query. The query isn’t actually sent to the server until we attempt to retrieve the first result (technically, when MoveNext is called for the first time on the enumerator returned by GetEnumerator). This means that we can control the results of the query in interesting ways by modifying the cursor before fetching the results.

Get only key-value pairs from a collection

In this example, we’re going to try to do something like this in SQL:

SELECT Column1, Column2
FROM  Table

We’re also going to be using the collection “bbu”. Let’s try to translate this query:

db.bbu.find({},{whichAPI:1}).limit(10)

Which would return the following:

{ "_id" : ObjectId("4d90fbd85843000000004359"), "whichAPI" : "SL" }
{ "_id" : ObjectId("4d90fbd8584300000000435a"), "whichAPI" : "SL" }
{ "_id" : ObjectId("4d90fbd8584300000000435b"), "whichAPI" : "SIT" }
{ "_id" : ObjectId("4d90fbd8584300000000435c"), "whichAPI" : "SL" }
{ "_id" : ObjectId("4d90fbd8584300000000435d"), "whichAPI" : "SL" }

First, we’re going to define the class. We’re going to establish all the properties that are needed for a class, but then only query and populate two colums from the class.

Define the class and all of its properties.

public class BBU
  {
    public MongoDB.Bson.ObjectId _id;
    public int counter;
    public string whichAPI;
  }

Now the actual program:

  class Program
  {
    static void Main(string[] args)
    {    

      MongoServer  server = MongoServer.Create("mongodb://work2");
      MongoDatabase BBUDatabase = server.GetDatabase("bbu");

      MongoCollection BBUCollection;
      BBUCollection = BBUDatabase.GetCollection("bbu");

      MongoCursor cursor = BBUCollection.FindAll().SetFields("id","whichAPI").SetLimit(5);

      foreach (BBU ba in cursor)
      {
        Console.WriteLine(ba._id + "----" + ba.whichAPI);
        Console.WriteLine("=============================");
      }      

      Console.WriteLine("\n\nha made it this far!");
      Console.ReadLine();                        

    }
  }

In the example above, SetLimit(5) will only return the top 5 documents.

Conclusion

I hope you got a good a sense of the basics of using the MongoDB C# driver. I’ve only scratched the surface of the API. I’ll be discussing more advanced topics in future posts.

For the meantime, I point you to the MongoDB Google Group: http://groups.google.com/group/mongodb-user?pli=1 where you’ll get informative responses, usually very quickly.

I also recommend you following the following folks on Twitter and these blogs/sites:

Lastly, I recommend you check out the MongoDB video presentations using the C# Driver and working in a Microsoft environment.

msdb.dbo.sp_sqlagent_get_perf_counters high CPU

That stored proc generates server stats, and depending on the stat results, it will fire off the alerts in SQL Server. If that instance of that stored proc hangs or has abnormally high CPU, just kill it. The agent will fire it again when needed.

Also, by default, SQL Server comes with a set of alerts that the server can use (called, “Demo”). I would keep the alerts active if possible and see what alerts are being triggered and why – check logs.

There was also a bug on SQL Server 2008 that was fixed that stemmed from this proc causing high CPU, even if all Alerts are disabled. I’m not sure if this fix applied to SQL Server 2000 with latest SP.

More details: http://connect.microsoft.com/SQLServer/feedback/details/536354/execute-msdb-dbo-sp-sqlagent-get-perf-counters-hogs-cpu-although-no-alerts-enabled#details

Order of Indexing Matters

In SQL Server, the order that you declare Indexes matters! So take the following example:

And we run this query:

SELECT  cast(ca.AreaID AS varchar(4)),
        RemovalDate
FROM    ContentAreas ca WITH ( NOLOCK )
WHERE   ContentID = 232232
        AND removerID = 34343

If you are to run the following query, since you are using ContentID as part of the WHERE clause, you want to put the ContentID’s index at the top of the indexes/key in SSMS. It speeds up retrieval.  Also put RemoverID below that – test with viewing the execution plan first to see if it makes a difference. Remember, the more indexes you add, the more it takes to update the index.

Add a Covered Index to tables to get rid of Bookmark Lookups

Add a Covered Index to tables to get rid of “bookmark lookups.” The idea of bookmarks: SQL Server uses Bookmarks when you use columns in your query that are not non-clustered/clustered indexes. So if we have something like this:

SELECT  AreaID,
        ContentID,
        ApprovalCodeID,
        isSubmissionPoint

FROM    ContentAreas

WHERE   AreaID = 31
        AND ContentID > 30000
        AND ModifierID > 65000

And let’s say that AreaID is a clustered index and ContentID is a non-clustered index. If ModifierID is not an index of any kind, it would function as a “bookmark lookup” in this query. To not use a “bookmark lookup,” use a Covered Index, which clumping the Modifier column with ContentID and turning both into a Covered Index (a kind of composite index).

Script Out Table Data in SQL Server 2008

SQL Server 2008 has a new feature where you can script out data in tables via INSERT statements. Just do the following:



1. Right click on the database and select “Generate Scripts…”

2. Select the database.

3. This is the hidden part. Set “Script Data” to true.

4. Select Tables.

5. Select Tables.

6. You can script out a table script per file or as a single file. I prefer ANSI text.

7. Hit Finish.

Generate DateTime based on Time Offset

So let’s say you have a table with a column of type DateTime. Now you have to support timezones. The first thing you create is a table of time offsets (Google Time Offsets). The table would look like this:

Here’s schema for it. You can download the full script with the data as well.

CREATE TABLE [dbo].[TimeZones](
  [TimeZoneID] [int] IDENTITY(1,1) NOT NULL,
  [Offset] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [TimeLabel] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_TimeZones] PRIMARY KEY CLUSTERED 
(
  [TimeZoneID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Now for the purpose of this tutorial, let’s create an event table. This will have a set of dates/times which we’ll use to add the timeoffsets to. The table looks like this:

and here’s the script:

CREATE TABLE [dbo].[TimeEvent](
  [TimeEventID] [int] IDENTITY(1,1) NOT NULL,
  [EventName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [EventDateTime] [datetime] NULL,
  [TimeZoneID] [int] NULL,
 CONSTRAINT [PK_TimeEvent] PRIMARY KEY CLUSTERED 
(
  [TimeEventID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Now let’s INNER JOIN the tables properly adding the timeoffset to the event datetime:

SELECT  tz.TimeZoneID,
        tz.Offset,        
        te.TimeEventID,
        te.EventName,
        te.EventDateTime AS 'OriginalEventDateTime',

        -- Hour - Extract Hour from Offset
        SUBSTRING(Offset, 0, CHARINDEX(':', Offset, 0)) AS 'Hour',
        
        -- Minute - Extract Minute from Offset        
        SUBSTRING(Offset, CHARINDEX(':', Offset, 0) + 1, LEN(Offset)) AS 'Minute',
        
        -- New Date with UTC Addition
        DATEADD(HOUR,
                CAST(SUBSTRING(Offset, 0, CHARINDEX(':', Offset, 0)) AS INT),
                DATEADD(MINUTE,
                        CAST(SUBSTRING(Offset, CHARINDEX(':', Offset, 0) + 1,
                                       LEN(Offset)) AS INT), EventDateTime -- DateTime change via Offset
                         )) AS 'EventDateTimeWithTimeZone'
FROM    TimeZones tz
        INNER JOIN TimeEvent te ON tz.TimeZoneID = te.TimeZoneID

Here’s the result:

Logical vs Physical ER Diagrams

Logical diagrams are to convey requirements only. Physical diagrams represent the actual data structure to support the requirements and take into account technical scalability and speed.

Edit: I hate the way I had to format this document for this blog post. If you want this tutorial better formatted, check out the Word document.

One-to-Many Relationship


Logical

On ER/Studio, two tables are created. The Store table has a primary key StoreID. The Manager table has a primary key, ManagerID and a foreign key, StoredID (which is mapped to StoreID from the Store table).

Physical

On SQL Server, two tables are created. The Store table has a primary key StoreID. The Manager table has a primary key, ManagerID and a foreign key, StoredID (which is mapped to StoreID from the Store table).

Manager Table:

If you allow NULLs for StoreID in the Manager table, then you’ll be able to have a Manager without a store. If you don’t allow NULLs (leave it unchecked), then you’ll have to have at least one Store assigned to a Manager.

Querying

The above states that one store can have many managers. Here’s some sample data what’s in the tables:

<div align="center"><br />
  <img src="http://www.shinylight.com/wp-content/uploads/2010/01/image006.jpg" width="240" height="171" />
  </p>
</div>
<p>
<div align="center"><br />
  <img src="http://www.shinylight.com/wp-content/uploads/2010/01/image007.jpg" width="414" height="77" />
  </p>
</div>
<p>Get all manager information with for all managers that  belong to a store:</p>
<table width="100%" border="0" cellspacing="5" cellpadding="3">
  <tr>
    <td>SELECT  Manager.ManagerID,  Manager.FirstName,  Manager.LastName,  Manager.StoreID,
        Store.[Name],  Store.Address,  Store.State,  Store.City,  Store.Zip
FROM    Manager
        INNER JOIN Store ON Manager.StoreID = Store.StoreID


Get all manager information with for all managers (even if they don’t have a store):

SELECT  Manager.ManagerID,  Manager.FirstName,  Manager.LastName,  Manager.StoreID,
        Store.[Name],  Store.Address,  Store.State,  Store.City,  Store.Zip
FROM    Manager
        LEFT OUTER JOIN Store ON Manager.StoreID = Store.StoreID


Notice the NULL for Steamboat Willie. He doesn’t have a store, so all Store related fields show as NULL.


Many-to-Many Relationship



In order to implement this physically, you need a join table. In this case, we use StoreManager. Logically, you only need only two entities (Store and Manager).

Logical

Physical


On SQL Server, three tables are created. The Store table has a primary key StoreID. The Manager table has a primary key, ManagerID. The table StoreManager has two foreign keys:  StoredID (which is mapped to StoreID from the Store table) and ManagerID (which is mapped to the ManagerID from the Manager table).

StoreManager Table:

If you allow NULLs for StoreID and ManagerID in the StoreManager table, then you’ll be able to have a Manager without a store. If you don’t allow NULLs (leave it unchecked for both), then you’ll have to have at least one Store assigned to a Manager.

Here’s some sample data what’s in the tables:

<p align="center"><img src="http://www.shinylight.com/wp-content/uploads/2010/01/image016.jpg" width="157" height="140" /></p>
<!--start_raw--><br /><!--end_raw-->

<p align="center"><img src="http://www.shinylight.com/wp-content/uploads/2010/01/image017.jpg" width="331" height="65" /></p>
<!--start_raw--><br /><!--end_raw-->

<p align="center"><img src="http://www.shinylight.com/wp-content/uploads/2010/01/image018.jpg" width="73" height="204" /><br />
</p>
<!--start_raw--><br /><!--end_raw-->
<p>Get all manager information associated with his store:</p>
SELECT  Manager.ManagerID,  Manager.FirstName,  Manager.LastName,
        StoreManager.StoreID, StoreManager.ManagerID, Store.StoreID,
        Store.[Name],Store.Address, Store.State, Store.City, Store.Zip
FROM    Store
        INNER JOIN StoreManager ON Store.StoreID = StoreManager.StoreID
        INNER JOIN Manager ON StoreManager.ManagerID = Manager.ManagerID

SQLCompare via Command Console

If you haven’t heard of SQLCompare by RedGate, you’re missing out. It’s an amazing product. In summary, you can do the following (which I use it for 99% of the time):

  • Compare Schema / TSQL code from Different Databases
  • Sync Schema / TSQL code across two databases
  • Generate TSQL code from your comparison so you can use it for deployments
  • Generate reports on SQL Changes

Most of those things can be done via the GUI version of the tool. The product also comes with a command line version, which you can use it in your build script.

To script out your entire database via the command console, you can do the following:

SQLCompare /force /database1:YOURDATABASENAME /username1:sa /password1:password /server1:YOURDATABASESERVERNAME /makescripts:c:\x

To compare two databases and generate a report via the command console, you can do the following. The reason there’s so many switches is because you need to enter the database name and credentials for the two databases.

SQLCompare /force /database1:DB1NAME /username1:sa /password1:password /server1:SERVER1NAME /database2:DB2NAME /username2:sa /password2:password /server2:SERVER2NAME /report:c:\report.html /reporttype:Interactive

Java Pairs Well with Which Database?

In the same way there’s a tight bond between MySQL and PHP, SQL Server and ASP.NET, SQL Server and ColdFusion – what goes well with Java? Oracle? Being curious, I started searching in employment web sites. I searched for “Java” and one of these databases: Oracle, MySQL, SQL Server and PostgreSQL. (I put in “SQL Server” using quotes.) The sites used were: craigslist, Monster.com, Dice.com, and Yahoo! Hotjobs.

The numbers signify how many job entries were returned.

So it does seem Oracle goes with Java. Also I noticed how many people call “SQL Server” just “SQL.” Sort of confusing and hard to tell if they’re referring to the platform or language.

SQL Server 2008 Installation Errors

Recently I’ve had to install SQL Server 2008 on a few machines. Some of these errors made absolutely no sense, but I found solutions that worked for me. Maybe they’ll help you.

Windows 2000 Server (32-bit)


“The procedure entry point GetConsoleProcessList could not be located in the dynamic link library KERNEL32.dll.”

That sounds pretty self-explanatory, right? Actually, this was the easiest error to make sense of. This is the error you get because SQL Server 2008 is not compatible with Windows 2000 Server. It comes from the fact that SQL Server 2008 needs .NET 3.5+, and that version of .NET is not Windows 2000-compatible.

Windows XP and Windows 2003 Server (32-bit)


Surprisingly, I received no errors while trying to install it on Windows XP. Everything went fine with Windows 2003 Server.

Windows 7 (64-bit)


OK, this was hell on earth. First I ran the setup, and mid-way through the installation, I received this gem:

“SQL Server Setup has encountered the following error:

Invoke or BeginInvoke cannot be called on a control until the window handle has been created..”

I noticed that this arises if you have the focus on another Window while the installer is running. I was browsing the web via Firefox and I received this. To make sure you don’t get this, don’t have any other Window running and only have the focus on the installer.

That wasn’t the only error I received. When I initially ran the setup, I saw a console window flash and then nothing would happen. (I waited for a few minutes and nothing. Setup.exe wasn’t even in the task manager.) When you run setup.exe, it fires landingpage.exe, which is the actual wizard installer. Unfortunately, it wasn’t running it. I even made sure I had the requirements, and nothing. I rebooted several times, and finally decided to run landingpage.exe directly. When I ran it, it finally saw the wizard. I was joyful, until it got to this part:

What the heck?! Where are the features?

Why would the wizard make it this far and then then not have the features to let me install? This was useless. If you’re curious, it should’ve looked like this:

Then for the heck of it, I decided to Install SQL Server 2008 SP1, without even installing SQL Server 2008 first. Don’t know why, but it let me install it anyways. Then when I ran the SQL Server 2008 installer again, but this time, I would see these features:

Ugh, still no luck. Finally, the only thing that I could conclude was that I may have a bad installation. So I restored my machine to an earlier restore point (to undo this installation mess) and re-downloaded the installation files.

Voila! No more problems. So that’s what it was. I was finally able to see all the features to install. So maybe the files got corrupted upon download or it was an incomplete download.

Temporary Stored Procedure

They can be handy sometimes if you want to modularize your code for the life-time of a routine. To create a temp stored procedure, all you do is use the # sign before the name of the sproc. Same concept as a temp table. Only the owner can execute it and its life is that of the session. Here’s an example that uses the Northwind db.

CREATE PROCEDURE #GetCustomers 
(
  @companyName VARCHAR(50)
)
AS
BEGIN
  SELECT * FROM Customers
  WHERE  CompanyName LIKE '%' + @companyName + '%'
END

To use it:

EXEC #GetCustomers 'The'

You can find the temp sproc in the tempdb:

If you want more information on the temp sproc, you can query the system view under the tempdb:

SELECT * FROM tempdb.sys.objects
WHERE [object_id] = OBJECT_ID('tempdb.dbo.#GetCustomers')

Your next question maybe if you can create temp user defined functions? The answer is a big juicy delicious, “NO!” (at least as of SQL Server 2008 Enterprise).

Backup MySQL DB From Remote To Your Local Machine

Why do this?

In case you don’t have shell access to your server from your hosting provider. Also if the provider doesn’t let you writing files locally from a script.

If you want to backup from a remote machine to your local machine:

(Don’t use the symbols when you type it, except the last > that redirects to a file.)

mysqldump --opt -Q -h  -u  --password=  > 

If you want to backup from a remote machine to that same remote machine.

mysqldump --opt -Q -h  -u  -p  > 

You can also pipe that to gzip to compress, and schedule it as needed.

Search for Code in Stored Procedures

One handy trick that I used to do was use the function OBJECT_DEFINITION() to view the code of a sproc. It’s a handy little function that doesn’t get much love as OBJECT_ID or OBJECT_NAME(). Let’s say we want to get the code of the sproc dbo.uspGetBillOfMaterials from DB AdventureWorks. We do like so:

SELECT OBJECT_DEFINITION( OBJECT_ID('dbo.uspGetBillOfMaterials') )

You get a result that may not be too pretty. In this case, use PRINT rather than SELECT, since SSMS strips out line breaks to squeeze it in the cell.

Now let’s search all available user sprocs in the database to search for the string “%GetBillOf% :

SELECT  OBJECT_DEFINITION( p.[object_id] ) AS code 
FROM    sys.procedures p
WHERE   OBJECT_DEFINITION( p.[object_id] ) LIKE '%GetBillOf%'

Now, I do longer search this way since every day I have a job that writes out all DB objects (tables, sprocs, etc.) to a individual files and they get indexed via Copernic Desktop Search. I script them out using SQL Compare.

Unfortunately, I don’t know why, OBJECT_DEFINITION() doesn’t work on tables. It’s been a wanted feature by the community. If you want to learn more about this function, check out: