Create Stored Procedure IF NOT EXISTS (SQL Server)

This is especially useful if you're writing update scripts like I am.

IF NOT EXISTS(Select * from sysobjects where name = 'usp_UserSproc')
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE usp_UserSproc
(
@ID int = NULL --nullable input
)
AS
BEGIN
SET NOCOUNT ON;
IF @ID IS NOT NULL
SELECT * FROM My_Table WHERE ID=@ID;
ELSE
SELECT * FROM My_Table WHERE ID=1;
END';
END

SQL Server Analysis Services Deployment Error

I was following the MSDN Tutorial on SQL Server Analysis until I got stuck in the Deployment lesson. The error is mostly about being unable to connect to the database or having insufficient permissions.

I've tried everything and finally decided to start from scratch. Then the next attempt succeeded. I searched through whatever difference my first project had with the second and here is what I found out:

• Right click your Project and click Properties.
• Server: localhost
• Database: (e.g., Sample SSAS (attempt 3) should be Sample SSAS attemp 3)

This is where my first Project crashed. I assumed that the server is supposedly the Server in my SQL Server Management Studio (which is MSSQL) and then the Database is naturally the database that I'm designing my Analysis Services Project for, which is AdventureWorksDW. Apparently, I am very much mistaken.

I shall find out more regarding this.

Missing Business Intelligence Types and Analysis Services Template

While following the tutorial on Analysis Services on MSDN:
http://msdn.microsoft.com/en-us/library/ms170208.aspx

I got stuck in item 3 in Creating Analysis Services Project
http://msdn.microsoft.com/en-us/library/ms166989.aspx

I have SQL Server 2008, Developers' Edition
I have installed Analysis Services (and Reporting Services for future use) so that's not the problem.
- If you're not sure if you have that. Go to Start -> Programs -> Microsoft SQL Server-> SQL Server Business Intelligence Development Studio

If you have that they you have Analysis Services and you might be encountering the same problem I did. When trying to create my first Analysis Services Project, the Business Intelligence Type was missing and so was the Analysis Services Templates.

Solution:

In MS Visual Studio (BIDS) click on Tools -> Import and Export Settings

Import Selected Environment Settings
Yes, Save my current settings
highlight "Business Intelligence Settings" and click on Finish.

This will add the Business Intelligence projects to BIDS.
Source: http://www.windows-tech.info/15/4e09444d2e59562b.php

FREE Starbucks Java Chip ^__^

Oyen is currently enjoying her FREE tall Java Chip from Starbucks.

Last week when we went to see Tropic Thunder, I bought a chocolate glazed doughnut from Starbucks. Then the cashier guy handed me my receipt and told me that I can claim a free tall drink from any Starbucks branch if I answer their online survey. Just follow the steps I took:



1. Go to http://www.mystarbucksvisit-ph.com/
2. Input the Customer Code that you can find in your receipt.
3. Answer the quick survey (took me 10 minutes)
4. On the provided space in your receipt, write the code that appears at the end of the survey.

That receipt will let you claim a free tall drink and is valid for a month, starting from the day you entered your code in the site.

British Comedy Insults Filipinos

British Comedy Insults Filipinos
Filipinos are insulted by BBC Comedy, 'Harry and Paul', where a sketch portrayed Enfield ordering his neighbor's Filipina maid to mate with his Northerner neighbor
View more »

How to create backlinks for Blogger

I am currently trying to create backlinks for my blog - no, not this one, I'm referring to http://thingswelovetohate.blogspot.com where more intere
sting things happen. So I'm posting this as an "adventure" and hope that somebody learns a thing or two.

The following can be done for free:

1. Submit your site to Yahoo.

2. Answer forum questions through links to your site: so make sure you post the answer FIRST before giving the link, and provide the link to the actual blog entry so that it can still be accessible to other people who are browsing for answers to the same question.

The following are good sites where you can go and answer other people's questions:

1. Yahoo! Answers

2. MyLot

3. When you create an account in myLot, you may add your blog to their site. myLot is a high traffic site. To add your blog, go to: http://www.mylot.com/o/bloggers.aspx?a=a

Tip: Your default Blogger RSS Feed URL is http://blogname.blogspot.com/atom.xml

You can check it out by entering that your URL in your browser and you'd see a summary of your blog posts. Mine is http://thingswelovetohate.blogspot.com/atom.xml

4. Join Qassia. [Here's my user page: http://oyen.qassia.com/ Take a look to get a feel with Qassia.]

Qassia is a site that helps you earn with Google adsense, they display your blog and whatever ad revenues your page gets from the Google adsense is attributed to you. However, this is just a BONUS. What we really want to get out of Qassia is the backlink. When you apply your blog/s to Qassia then you get more page views and they provide you quality blog link. So if you don't have Google adsense, then don't even worry about it. If you DO need help, drop a comment and I'll get back to you ;)

*Don't forget to apply for the Sticker Promo. They will crawl into your site (randomly) and that's another way to be featured.

*Add Intel to get more quality back links. What you do is write about something that you know, then they would credit your site/blog for the knowledge that you share. That's another quality back link for you.I also signed up for Link Vault but later found out that I had to upload both a .php file and a .txt file for it to work and that isn't allowed in Blogger.

There's also Receive Links that I'm still trying to figure out. Once I get that running, I'll probably write about that as well.


5. Make sure you put your blog link in your email signature, forum signature. And update your social networking accounts and put your blog link on the MyWebsite area.

6. Post a bulletin on Friendster, MySpace, Facebook, Plurk and other social networking sites that you belong to and invite them to take a look at your blog. You may also do this for blog entries that may interest such audience.

7. Ping your site every time you post a new entry. This may be tiresome but it's worth it - while you're at it, make sure your blog posts have tags because Ping sites such as Technorati utilize blog entry tags.

Sphynx Clothing by Seekers for Php480

This can also be seen in Multiply.com for larger resolution of images

SRP Php 480
If you also want to consign, give email me or comment here.

Sizes available (as for now):
- Medium
- Small (bustline: 32-34, length: 24)

Applique colors (embroidered satin):
- Black (thread) on silver (satin),
- yellow on gold,
- yellow and silver on green,

Racerback color:
- black
- dark brown

Email: oyen.kai@gmail.com
Or comment here.

This can also be seen in Multiply.com for larger resolution of images

How to fight pimples/acne with Eskinol

Mix Eskinol with Dalacin C.

Dalacin C is an over the counter capsule.

Take a 500mg Dalacin C capsule and pour the contents into a large Eskinol bottle - I think that's 120mL or something. (Just go get the "large" bottle - there's a small, a medium, then there's a large. There is NO extra large. If you have that in your place, then take the smaller container.)

It's as simple as that. It has been tried and tested. My sister uses it every time she gets her pimple attack on her back. They work perfectly fine.

Be sure to take a bath before applying it. It also works best before bed because the body goes into a healing/recovering mode when we're asleep. And while you're at it, sleep more! It's another advisably habit to get rid of pimples/acne, that alongside drinking lots of water, avoiding cholesterol-rich foods, and exercising regularly.

To your health! Cheers!

Full Text Search Step by Step Tutorial

Download and Install AdventureWorks for SQL Server 2008

Download AdventureWorks database: AdventureWorksDB.msi in Codeplex

Then double click your .msi file to Install your database.

By default it will install your database in
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Your database will not appear automatically in your Object Explorer.

Go to Databases, right click then choose Attach...


If your database does not appear yet, refresh your Databases folder in the Object Explorer of your SQL Server Management Studio. To view your Object Explorer go to your menu bar then go to Views/Object Explorer or press F8.

Then you will see among your databases AdventureWorksDW.

Enable Full Text Search in Database

Create a new Full Text Catalog



A window will pop up to prompt for more details:
Full Text Catalog Name: FTCatalog

Create a new Full Text Index

Install Full Text Search
If your option is blocked (if it is not clickable) then re-run your SQL Server 2008 Installer. Under Installation (below Planning, on the left panel), choose New SQL Server stand-alone isntallation or add features to an existing installation.

Install Support Files.

Under Installation Type, choose Add features to an existing instance of SQL Server 2008.

Under Feature Selection check Full-Text Search.

Click Next then complete the installation. This will take around 5 minutes.

A Wizard window will appear after choosing Define Full-Text Index...

Follow the directions of the Wizard: choose the Primary Key for Indexing, then select all tables to enable full-text queries on all of them. Then choose the FTCatalog that we created previously when prompted. Skip Define population schedules for this tutorial. Then hit Finish.

Populate Index


Query Scripts - Test Full Text Search

Let's test our Full Text Search through the following SQL scripts:


On the new Query window, copy and paste the following scripts:

USE AdventureWorks
GO

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE FREETEXT(*, 'Marketing Assistant');

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE CONTAINS(Title, 'Marketing OR Assistant');

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE CONTAINS(Title, 'Marketing AND Assistant');
GO

Then Execute or F5 to run the query.


Notice that the results of the first SELECT query and the second SELECT query are the same.

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE FREETEXT(*, 'Marketing Assistant');

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE CONTAINS(Title, 'Marketing OR Assistant');

The FREETEXT( ) query searches all job titles with either the string "Marketing" or "Assistant". The results are arranged according to their foreign keys.

By using the "AND" constraint on the third SELECT, only job titles with exact matches as the query string showed.

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE CONTAINS(Title, 'Marketing AND Assistant');

The arrangement of the results still depend on the foreign key (EmployeeID).

FREETEXT( ) Is predicate used to search columns containing character-based data types. It will not match the exact word, but the meaning of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches.

• Separates the string into individual words based on word boundaries (word-breaking).
• Generates inflectional forms of the words (stemming).
• Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

CONTAINS( ) is similar to the Freetext but with the difference that it takes one keyword to match with the records, and if we want to combine other words as well in the search then we need to provide the “and” or “or” in search else it will throw an error.

Source: http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

Fund Raising proposal thru online earnings

I am proposing an unconventional way of fund raising. This is primarily to earn more money for the continuing construction of the San Rafael Parish Chapel in San Rafael, Tarlac City, Philippines.

Target: Every active parishioner of San Rafael Parish Chapel with an Internet connection at home.

How: By joining in http://www.myLot.com

How does myLot work?
You participate in discussions by either starting your own discussions or by commenting on other discussions.

Overview of earnings
You will earn around 0.40cents for 50 quality posts a day. The earnings are updated daily. "Quality posts" means at least 3 lines of comments to a discussion or a new discussion that many users will comment on.

By "3 lines", that means 3 lines on the text area where you write your discussion, not how it appears on the screen after you posted it. By "many" that's relative, expect a good discussion to reach up to 40 comments and will span to 5 pages.

How do myLot members get paid?
The payment mode is through PayPal. That means that the Parish will have to acquire a PayPal account (free) and a bank account to withdraw the money from PayPal.

But not all parishioners have PayPal!
Right now that doesn't matter. This is primarily a parishioner's labor of love. This is an additional means to contribute to the parish. If the parishioner wants to get a PayPal account to cashout his personal earnings, it's free for him to get one. However, to become a myLot member and to contribute to the San Rafael Parish Chapel's fund raising, this is not required. You can easily update your myLot account when you have created your PayPal account. That means that even though you still don't have a PayPal account, you are already earning through your myLot participation.

So how does the fund raising work? How will that help the parish?
Do I have to surrender my earnings to the San Rafael Parish?
NO. You get to keep your own earnings for yourself. The Parish will earn from its referral link. That means that you HAVE to use the Parish's referral link. That will NOT deduct any of your personal earnings. Tha Parish will earn 25% of what you will earn. Note that you will also increase your earnings by increasing your own referrals. HOWEVER, please don't steal referrals from the Parish because even though the Parish will STILL earn from those who took your referral link (since it earns 25% of your earnings), it will earn much less than if that person took the Parish's referral link directly.

Let's do the math.
Let's say that a hundred parishioners join myLot, all under the Parish's referral link. If each of those parishioners was to participate with 150 quality posts every week (which is either 50 posts a day for 3 days in a week or 10 posts everyday after you come home from school or work), then that would mean 1.20$ earning per parishioner and 30cents earning of the Parish per parishioner per week. That would sum up to $30 per week or Php1,350. In a month, that would accumulate to $120 or Php5400. That's not enough to finish the Parish Chapel construction but it will help and that's only the start. We can earn more if we have more people participating.

Will the parish now how much I'm contributing?
YES. I'm a Computer Science graduate of the Ateneo de Manila University. I'm currently working on a program that will let the Administrator know if you've been doing your homework or not.

Will my soul burn in hell if I don't accomplish the *strongly suggested* number of posts?
Of course not! But then it's not like we meet people from hell to confirm that.

Will I go to heaven if I do this?
Come on, people! We've all learned about how selling indulgences in the olden times led to the restructing of the Catholic Church and the separation of Protestantism. We're past that era now.

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.

Test your color IQ

My score: 4

http://www.xrite.com/custom_page.aspx?PageID=77

A call for underrepresented individuals

My friend is currently working on her thesis and she's having a hard time locating her subjects.

It's a photographic essay on twenty underrepresented individuals who have proved to play significant roles in society. These individuals’ greatness are not recognized because they do not fit the canonical standard typical of choosing who is worthy of recognition.

This group includes (but is not limited to) the poor or working class, the old people, the women, the homosexuals, the political deviants, the ethnic minorities and the handicapped. This could be a burnt child who saved his family from a burning house, a mother who single-handedly raised her six children while balancing three jobs at the same time, an old woman who despite her age serves as her family's breadwinner or an Ifugao elder from Cordillera who despite the changing trends in their culture brought about by modernization, continues to protect the dying art of tattooing.

A side note:
...I was wondering if Database admins are underrepresented individuals. Database admins always serve as the scape goat in companies - when something doesn't work right, they point their finger at the database admin. It's not like SQL Server Transactions are the only problem that exists.

How to copy SQL table content into INSERT INTO statements in SQL Server using SQL CLR

Most of the time during the database development stage of a specific system, programmers concerned with the different aspects of the system work all at the same time. While the Database Admin fine tunes the data structure including its constraints, metadata, relationships, views, stored procedures, the Data Access Layer Man has already started working on his end of the fence. He determines the anchor or the point of access, which table would most efficiently access all other related information. He makes sure that when data is inserted, deleted, updated then all other related information, all other related tables to that specific data are updated appropriately.

While the Data Access Layer is working on those delicate and logic intensive information, he has to have access to the database. However, the Database Admin is still working on the database itself. Since the Data Access Layer especially when done in an Entity Data Model (.NET Entity Framework) is sensitive to the stability of the information in the database, it is not wise if they were to connect to the same database.

In most business practices, the Data Access Layer team/man is given only a copy of the original database being developed. When a significant change was made to the database, he rebuilds his Entity Data Model accordingly. This is a tough job but it’s the only way to work at the same time with the DBA and therefore deliver faster. Sometimes the DAL would request the DBA to insert sample data within the database so that he may test his scripts against the database. But since they are physically connected to different databases, the DBA fixes SQL Script of Insert statements for him.

On the occasion when the DAL actually inserts his own table rows and the Web Dev man sees that there is already enough structure for him to connect his application and to test some of its units, he would need a copy of that database. It easy to script the database structure including its triggers and content to create it in a different local machine, however, when the database is already running in a different local machine and all the Web Dev needs is the rows to check if he can successfully display the table rows, let’s not waste time dropping and recreating databases.

Click to download code.

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Text; using System.Collections.Generic; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void CopyContentOfTable(String tablename) { using (SqlConnection conn = new SqlConnection("context connection=true")) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; StringBuilder sb = new StringBuilder(); sb.Append("SELECT column_name FROM information_schema.columns ") .Append("WHERE table_name = '").Append(tablename).Append("'"); cmd.CommandText = sb.ToString(); //List down all the colum names in the table List<String> columnames = new List<String>(); SqlDataReader reader; using (reader = cmd.ExecuteReader()) { if( reader.HasRows ) while (reader.Read()) { columnames.Add(reader["column_name"].ToString()); } } //get all data from table sb.Length = 0; sb.Append("SELECT * from ").Append(tablename); cmd.CommandText = sb.ToString(); //define needed logical objects for creating the insert statement String columnvalue = String.Empty; bool notfirstcolumn = false; bool notfirstrow = false; using (reader = cmd.ExecuteReader()) { sb.Length = 0; //Enable inserting identity, //we want to make sure that the databases are as identical as possible sb.Append("SET IDENTITY_INSERT ").Append(tablename).AppendLine(" ON;"); //you may turn off any related triggers if you wish to here... //Build our insert statements sb.Append("INSERT into ").Append(tablename).AppendLine(" ("); //list down all column names foreach( String column in columnames) { if(notfirstcolumn) sb.Append(","); else notfirstcolumn = true; sb.Append(column); } //add each row, result from the previous SELECT transaction notfirstcolumn = false; sb.AppendLine(") VALUES "); if (reader.HasRows) while (reader.Read()) { if (notfirstrow) sb.Append(","); else notfirstrow = true; sb.Append("("); foreach (String column in columnames) { if (notfirstcolumn) sb.Append(","); else notfirstcolumn = true; columnvalue = reader[column].ToString(); if (columnvalue.Equals(String.Empty)) sb.Append("NULL"); else sb.Append(columnvalue); } sb.AppendLine(")"); notfirstcolumn = false; } //re-disable indentity inserts sb.Append("SET IDENTITY_INSERT ").Append(tablename).Append(" OFF;"); } //Your insert statement will appear in the Message tab of the query window. //Copy and paste it onto the query window and run SqlContext.Pipe.Send(sb.ToString()); } } };