Why Collation Affects T-SQL Syntax

This is something really intersting, atleast for me this is completely new and is still sort of mystery like what’s going on.  Today I deployed a SQL Server 2005 Database on one of the test servers at office and during the test my application started crashing. I was getting declaration errors form the database.

“Must declare Variable @ApplicaitonID “

It appeared that all the variables were declared but they were declared in difference a CASE and used in different CASE, well this should’nt be a problem, I have been using T-SQL without case sensitivity for long, but when I corrected the case everything seemed fine. The EXEC master.dbo.sp_server_info returned the following results (given at the end) and you can see the IDENTIFIER_CASE parameter set to SENSITIVE, Actually it should be MIXED. Anyway I tried to find how to change this parameter but it seems that there is no direct way to change it.

This parameter depends on the Collation so apperently there is a problem with the Collation, ok now I have to change the Collation to SQL_Latin1_General_CP1_CI_AS From Latin1_General_BIN. Now chaning collation on the database server is a long hard process, you have backup all your data and tables and reinstall the Database Engine with different collation and restore everything back.

The First thing I do not understand is that why collation is applied on the Variable Names (T-SQL Syntax), I always thought collation was something only for the comaprison/Case-Sensitivity of database data and something which will not be used for procedure compilation. I do not understand this and I’ll have to find some answer for this.

Secondly, SQL Server 2005 supports different collation settings for each database and the Database Engine itself, and the collation settings on the copied database was fine. I even rechecked it and even tried to set it again but still even with the correct collation I continued to get the same Errors, I opened a procedure from database with the correct collation in Management Studio and tried recompiling it, but it was giving a recompilation error because of the case insensitivity. If SQL Server 2005 supports different Collation settings for Server and each individual database why I keep getting this annoying error.

If some can explain this to me I will really appreciate.

EXEC master.dbo.sp_server_info

attribute_id attribute_name               attribute_value

———— —————————- —————————————

1            DBMS_NAME                    Microsoft SQL Server

2            DBMS_VER                     Microsoft SQL Server Yukon – 9.00.1399

10           OWNER_TERM                   owner

11           TABLE_TERM                   table

12           MAX_OWNER_NAME_LENGTH        128

13           TABLE_LENGTH                 128

14           MAX_QUAL_LENGTH              128

15           COLUMN_LENGTH                128

16           IDENTIFIER_CASE              SENSITIVE

17           TX_ISOLATION                 2

18           COLLATION_SEQ                charset=iso_1 collation=Latin1_General_BIN

19           SAVEPOINT_SUPPORT            Y

20           MULTI_RESULT_SETS            Y

22           ACCESSIBLE_TABLES            Y

100          USERID_LENGTH                128

101          QUALIFIER_TERM               database

102          NAMED_TRANSACTIONS           Y

103          SPROC_AS_LANGUAGE            Y

104          ACCESSIBLE_SPROC             Y

105          MAX_INDEX_COLS               16

106          RENAME_TABLE                 Y

107          RENAME_COLUMN                Y

108          DROP_COLUMN                  Y

109          INCREASE_COLUMN_LENGTH       Y

110          DDL_IN_TRANSACTION           Y

111          DESCENDING_INDEXES           Y

112          SP_RENAME                    Y

113          REMOTE_SPROC                 Y

500          SYS_SPROC_VERSION            9.00.1399

(29 row(s) affected)

Advertisements

CSS Background-Image Property Unsupported in XP

Was just working on interface of my application last night, and had to make few changes for background of buttons. I used background-image CSS property for setting the images for my buttons, everything looked fine untill I tried to access the application on Windows XP. Oh ! no background is displayed on XP.

Interestingly its a known Bug in Internet Explorer 6.0 which uses windows XP themes to render its controls, now thre are a couple of solutions to this, one is to use background istead of background-image and other is to include this

<meta http-equiv=”msthemecompatible” content=”no”>

to disable XP themes on the page.

http://support.microsoft.com/default.aspx?scid=kb;en-us;322240&Product=winxp

Google Strategy !

Just read something Interesting about Google Strategy.

Marc Canter has an interesting post about the strategy of google, and how Google is trying to “lock-in” the end-users/developers by using and following thier own proprietary protocols (developed based on open protocols) but not completely open something what Microsoft did through 80’s and 90’s.

Also check the Mouse Trap Post by Chris Messina on the same topic, but as Rebert Scoble pointed out it would be nice to hear what Google Says about this.

Peronsally I haven’t seen many Google Blogs !

Solar System Looses a Planet

According to awaited decision of International Astronomical Union, Pluto is no longer a planet and will be classified in a new term “dwarf planet”. The Solar System will now have total of 8 Planets.

Other celestial objects in our Solar System like Ceres and UB313 (Xena) which were highly expected to be declared planets will also remain in the “dwarf planet” category. Pluto’s moon Charon remains without a category and is not classified at the moment.

http://www.cnn.com/2006/TECH/space/08/24/pluto.ap/index.html

Space Elevator

I was just going throuh some of the space articles and found about the Concept of Space Elevator. I have seen many fictitious concepts about space but what amazes me about the concept of a Space Elevator is that it is theoratically not only possible but companies are actually working on the concept and NASA seems really interested too.

Anyway this is understandable that it is not something that is going to happen in 10 years but maybe after 50 years you may actually have a trip to space in a Elevator suspended from earth ! . The Concept is pretty simple, if you tie a thread on a side of a rotating ball with a small counterweight on the other side of the thread, due to the centrifugal force produced by the rotating ball, the thread will strat revolving with the ball and will seem as a suspended rope from the surface of the ball.

So if a 62,000-mile (100,000-kilometer) rope is suspended from the earth into space with a couterweigt attached on the other side of the rope, it will start spinning becuase of the centrifugal force of the earth, now if this rope is strong enough an Elevator Car can be attached to to it and can be used for transportation.

However, this rope cannot be directly attached to surface of the earth, but can suspended through a non-stationary plaform like an oil rig in the sea so it has some margin for movement becuase of winds and other hazards. The major hurdle in such a design was the availability of a material string enough for the rope, but now with Carbon Nanotube (discovered in 1991, 100 times stronger than steel and 1/5 in weight, a single thread of Nano Tube can easily support a full Saloon car) the material to build the Elevator is available.

Anyway It’s an extraordinary idea indeed