TSQL Latin Collation Error

Error encountered:
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I'm not sure if it's as convenient with the earlier versions but SQL Server 2008 allows you to easily change the collation of your database through the Properties window.

The first thing you would have to do is to drop all connections to the database. This can be easily done by right clicking on you database --> Tasks --> "Detach..." then tick the "Drop all connections" check box.

Then simply restore the database using your back up file (it's usually the same name as your database with a .bak file extension).

Now you're sure no one is initially connected to the database.

Right click on the database --> Properties --> Options then on the top is a drop down for the Collation options. We generally choose "SQL_Latin1_General_CP1_CI_AS".

How to drop all Database connections (TSQL)

USE master
GO

ALTER DATABASE @dbName
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE @dbName
SET ONLINE

OR simply
Right Click database -> Tasks -> Take database offline

How to check/count Back Links

I wrote about How to Create Back Links previously.

Now let's see how to check for back links. This is useful for blogs, websites, etc.

On the Google search bar simply type
link:

For example
link:thingswelovetohate.blogspot.com
or
link:http://thingswelovetohate.blogspot.com

would result to the same number of back links.

You can determine how many back links a website has on the same window. Right above the search results, and right below the Search text box is a blue (in classic mode) highlighted line that shows how many results were found.

For example
link:www.mylot.com



You will see that it had a total of 142 back links at the time of the search.

Downgrade from SQL 2008 to SQL 2000

I was given the task to downgrade a SQL 2008 to SQL 2000. I had to do it as soon as possible (= now) and I didn't have SQL 2000 in my machine.

Detaching the database from SQL 2008 and attaching to SQL 2000 doesn't work - they tried that already. Export/Import doesn't work also - so I tried my luck on using the simplest way to do the job:

Scripting.

In SQL Server 2008 Management Studio.
Databases -> MyDatabase, right click to Tasks -> Generate Scripts.



In the Script Wizard that pops up.
1. Select the database (MyDatabase/AdventureWorks)
the Check Script all objects in the selected Database.
2. Click Next.
IMPORTANT : Choose SQL 2000 in Script for Server Version

Toggle the following from False to True:
Script Database Create
Script Data
Script Indexes
Script Triggers
3. Click Next.
4. Click Finish.

Of course if you have a humongous database then the scripting the data would mean that you'd have a huge resulting .sql file. You may opt to not script the data alongside the Database itself for convenience, but if you don't have a big database then this is good enough - and pretty convenient.

How to: Inherit Database Role permissions

IF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'RestrictedRole')
exec sp_addrole 'RestrictedRole', 'dbo';

IF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'LessRestrictedRole')
exec sp_addrole 'LessRestrictedRole', 'dbo';


GRANT SELECT, INSERT, UPDATE on Table1 to RestrictedRole;
GRANT SELECT on Table2 to RestrictedRole;

EXEC sp_addrolemember 'RestrictedRole', 'LessRestrictedRole';

GRANT INSERT, UPDATE on Table2 to LessRestrictedRole;

The sp_addrolemember makes LessRestrictedRole a member of RestrictedRole, which means that LessRestrictedRole inherits all permissions given to RestrictedRole.

Granting more permissions to LessRestrictedRole would make those additional permissions exclusive to LessRestrictedRole - LessRestrictedRole may Insert and Update rows in Table2 while RestrictedRole may only Select rows.

By virtue of Inhertance, both LessRestrictedRole and RestrictedRole may Select, Insert and Update Table1.

My Google Ad isn't showing in my blog - FIX

Let's fix that critter. You'll also learn how to create a new

My Google Adsense in my other blog THINGS WE LOVE TO HATE isn't showing. It's supposed to be a banner on the bottom, my leaderboard on top and my box on the right panel are both working fine. The last thing I did (yesterday) was change the ad because it was the wrong size - from a leader board, I changed it into a banner. It wasn't showing yesterday also, but I thought it was only a system lag. So now I'm fixing it and I'll teach you how to do it on your own too (stop bothering the Google Adsense people, they're busy, too, you know).

BTW, this ad was made on the Google Adsense page - I didn't just add a Google Adsense through the ADD GADGET button on the Layout page. I created it on my Google Adsense page alongside a channel so I can monitor its performance - tell me if you need help setting this up.

Go to your Google Adsense Page: Adsense Setup tab then Preview the specific ad that's missing on your blog.

Guess what! It's STILL the wrong size. Stupid me.

Since we can't edit adsense sizes, I'll just hit this with the HIDE option (we also can't delete them), then I'll create a new one with the same name.

Set up the preferences - I created my own color scheme/panel according to my blog color motifs so I won't have to manually set it up all the time.

Make sure you have the right size, it's very first drop down box in this page and it's SO EASY to miss.

Then that's it. Choose a channel if you want to then you get the code and put it in your blog.

Mygad that sounded like a cheap shot at a tutorial.

Anyway, leave a comment if you need any help.

How to

I was about to search for a "how to sql xml" on Google Search then the following popped out. I'm not sure how Google Search decides which phrases to suggest but I'm guessing that they're the top phrases with the most queries.

I just had to see what the search would yield.