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.