Cannot resolve the collation conflict on temporary table?(2) Problem solved.

(Please backup all your database if you are going to try this)

From my last post, I proceed to install my MSSQL 2005 again, I found a strange behaviour this time. The length of the instance name is only limited to certain amount of value.

From BOL/MSDN:

Instance names are limited to 16 characters.

I feel something is wrong last time, during the command I issued:

start /wait setup.exe /qb INSTANCENAME={my SS2005 instance name} REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD={my SS2005 instance sa password} SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

I mistreated the server name as SS2005 instance name. Actually the server name is:

"server name" = "PC name"\"instance name".

So this time, I re-used the same setting to install the SS2005. For the first time, I copied out the content from the image I clone from installer, using PowerISO

I issued the command, with the correct instance name, and I get this. Wow, this is not available last time. It proved I was wrong last time, on the term of "Instance Name".

But after this I get this error message:

"An installation package for the product Microsoft SQL Server 2005 cannot be found. Try the installation again using a valid copy of the installation package 'SqlRun_SLQ.msi".

I think the image is corrupted, during I mounted it virtually and copying it. Thus I inserted the original CD. And I issued the same command.

Yes, it ran successfully and I checked my SQL Instance collation. Although it's AI(Accent Insensitive), but I can do the same thing again to change to AS(Accent Sensitive).

And I checked all the database. All the database is dropped.

So I need to attached them back.

But at least I have learnt something today.

Cannot resolve the collation conflict on temporary table?

"Cannot resolve the collation conflict between "Collation_A" and "Collation_B" in the equal to operation."

When I am using MSSQL 2000, it's normal when I hit this kind of collation problem on physically-existed table. Either the source database is conflict with new database, due to an upgrade from older version of customized database, or from other production site from different country.

But recently, I hit this issue at Temporary Tables (because I changed a new job and performed a new installation on my company laptop).

Let's say you have a following scripts written at stored procedure(SP):

-- Use AdventureWorks
create table #tempTable (ID int IDENTITY(1,1) NOT NULL ,
column1 varchar(200) NOT NULL)
INSERT INTO #tempTable
Values('test')
create table tempTable (ID int IDENTITY(1,1) NOT NULL ,
column2 varchar(200) NOT NULL)
INSERT INTO tempTable
Values('test')
Select * from #tempTable t
inner join tempTable p
on t.ID = p.ID
where t.column1 = p.column2

--drop table #tempTable
--drop table tempTable

and when you execute it, you will get the following error message:

Msg 468, Level 16, State 9, Line 10
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

At the first look, I was confused what went wrong. ( Of course you can suggest assign the associate collation will do, but what if there's tons of SP you need to modify? And even worst, they are decrypted!)

Of course the best practice would be something like this:

CREATE TABLE #TestTempTab
(PrimaryKey int PRIMARY KEY,
Col1 nchar COLLATE database_default
)
as in Setting and Changing the Column Collation, but I doubt most people do that.

So first I traced down from the definition of the Temporary Tables. From MSDN:

You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions. Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
...

The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix.

I queried from tempdb at System Databases with this query: (Note: you must use "Like" statement for the name)

use tempdb
Select collation, * from sysobjects so
inner join syscolumns sc
on so.id = sc.id
where so.name like '#tempTable%'

Why? Why it's collated in "Latin1_General_CI_AS"? Then I checked the database collation for this "tempdb" database and server instance:

Why all in "Latin1_General_CI_AS" collation? Then I went through the screenshot I took when I installed the MSSQL 2005 Developer Edition long long time ago (I have a habit to store the installation settings for future purpose, as in this case), I found this:

I switched the collation to Collation designator, which default to "Latin1_General_CI_AS" collation, as in my OS culture! I just realized I misused the in-appropriated collation with the current database (in this case "AdventureWorks", which uses "SQL_Latin1_General_CP1_CI_AS" collation.

So now, what is the solution?

I thought the faster way should be alter the "tempdb" database collation like this :

or from script:

USE [master]
GO
ALTER DATABASE [tempdb] COLLATE SQL_Latin1_General_CP1_CI_AS
GO

But I get this error message:

I searched the MSDN/BOL, and I found this: Setting and Changing the Server Collation

So I inserted my installer in CD-ROM and tried to use this command:

start /wait setup.exe /qb INSTANCENAME={my SS2005 instance name} REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD={my SS2005 instance sa password} SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

but the wizard came and went but nothing happened:

So I tried to look at the summary.txt produced, as suggested in SQL Server 2005 Setup Log File. Nothing too!

Then "SQLSetup0011_(pcName)_Core.log":

..
Error: Action "LaunchPatchedBootstrapAction" threw an exception during execution. Error information reported during run:"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe" finished and returned: 0Aborting queue processing as nested installer has completed.

I also try to repair it. But there's no such options.

Eventually I uninstalled it. Haha.

Confuse with pointer? Learn it in Zen-way!

Are you confused with pointer in C/C++? Now, you can learn it in Zen-way!

In this article, you can know the actual meaning of pointer, through 5 pillars:

1) The First Pillar: What is an Address?

2) The Second Pillar: What is a Variable Name?

3) The Third Pillar: Finding the Address of a Variable Name

4) The Fourth Pillar: Declaring Pointer Variables

5) The Fifth Pillar: Declaring References

Amitabha! Does it enlighten you??

Windows 7 Milestone 1 Release 2 ???

After posting a video on the latest OS windows 7, no longer people called it an hoax. Now it is one of the hottest place to see the latest deveopment of the windows 7: 刀枪Blue

And he also claimed this is 2nd release of Windows 7 Milestone 1. (this image is an external link.)

If you are an fan on the windows OS latest developement, sure you must save this site in your favourite folder!

Just curious if he's a beta tester, he should not allow to expose any of the content, right?

Think you're smart? Then crack it!

You think you are smart in programming? In job place, everyone seems seek you for the answer whenever there's a problem? Then go and try : crackthecode, by MSDN Canada.

And the prize is (who knows, maybe Microsoft Canada is hiring potential developer?):

And now for the bounty: Two $500 Future Shop gift cards can be won on April 29, 2008 and May 15, 2008. The $4000 Grand Prize gift card draw will be on June 13, 2008. The winners will receive a call, so keep an eye on your phone.

While I was trying yesterday, I only managed to answer first and third questions correctly.

Here are some hints:
Question 1: The answer got something to do with what you see when you entered the page! And I solved it.

Question 2: (I do not know. Most of my time was doing windows programming! Maybe you can download Microsoft Script Debugger and see, or even Visual Studio 2005/2008???)

Question 3 : This is a mathematics question, all those C-like languages programmer should be able to answer it easily. What's a waste to put this as question 3. :( And I lost my key because of slow Internet connection, WTF!

Question 4 : (I do not have the key!)

Maybe I need to apply another hotmail account! And it's your turn now.

Update:
After going thru' the rules and regulations:

1) THE CONTEST AND THE CONTEST PERIOD : The Microsoft "Crack the Code" contest commences at 12:01 a.m. Eastern Standard Time on April 1, 2008 and concludes at 11:59 p.m. Eastern Standard Time on May 31, 2008.

2) ELIGIBITY : The Contest is open to residents of Canada, excluding Quebec, who have reached the age of majority in his or her province of residence, but excluding employees, directors and officers of, Vortxt Interactive Inc. and any affiliate (as defined in the Canada Business Corporations Act), employees, directors and officers of Microsoft Canada Co. (the "Sponsor"), employees, directors and officers of their respective advertising and promotional agencies, as well as family members of any of the foregoing.

It's SQL injection, Not IIS vulnerability

Half A Million Microsoft-powered Sites Hit With SQL Injection.

Few days ago, people thought it was an IIS vulnerability but eventually it is a SQL injection. And Aaron Bertrand have few pieces of advices for web developers:
1) Do not expose your SQL Server to the Internet directly.

From TechNet: A common step in troubleshooting connectivity problems is to use the Ping tool to ping the address of the computer to which you are trying to connect. When you ping, you send an ICMP Echo message and get an ICMP Echo Reply message in response. By default, Windows Firewall does not allow incoming ICMP Echo messages and therefore the computer cannot send an ICMP Echo Reply in response. To configure Windows Firewall to allow the incoming ICMP Echo message, you must enable the Allow incoming echo request setting.
Beware if you turn this on!

2) Make your passwords strong.
3) Follow the principle of least privilege Always use stored procedures, or at least parameterized statements .
4) Use TRY/CATCH to return more generic error messages.
5) Do not store passwords in your Users table.

And some tips from Open Web Application Security Project (OWASP), there're few ways for testing the SQL Server:
1: Testing for SQL Injection in a GET request.
2: Testing for SQL Injection in a GET request (2).
3: Testing in a POST request
4: Yet another (useful) GET example
5: custom xp_cmdshell (Especially those who are still using MSSQL 2000!)
6: Referer / User-Agent
7: SQL Server as a port scanner
8: Upload of executables
9: bruteforce of sysadmin password.

Have you tried it on your server?

How To Get Hired

As a software developer for three years, I have changed several jobs. If you were same as me, go thru' it before you go for inteview, to know how much chance you stand!

1. Employers look for people who are Smart and Get Things Done
2. Employers look for people who Have Already Done Things .
3. You can't get a job doing something you haven't done before.
4. Employers Google your name to see what you've said and done .