SQL Server Performance Forum – Threads Archive

SQL 2005 Conversion. What things to watch for???

We are going to be going to SQL2005 this year. I have already found out that if you have any code that needs to query the "Description" extened property you have to use fn_listextendedproperty rather than using sysproperties(which was an undocumented table) since they dropped it. Any thoughts on what code references will no longer work in addition to the aforementioned item? Any links of items I should review? And yes, I have run the upgrade advisor which didnt locate this issue. Thanks in Advance! Michael B
Sr. DBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

I know about the DTS issues etc.. I am just looking for something remote that will cause recoding!
like using the old outer join syntax (*=) not being supported etc. Michael B
Sr. DBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

This is quite a handy link http://msdn2.microsoft.com/en-us/library/ms144262.aspx Although it mainly covers tools and such like. you have to be careful of anything using system tables as theses are always subject to change.

Thanks<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> That is more, but you know it doesnt include the sysproperties drop<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> but I really had to dig for that one! Mike Michael B Sr. DBA "The fear of the Lord is the beginning of knowledge, but fools despise wisdom and instruction." Proverbs 1:7

http://msdn2.microsoft.com/en-us/aa224810(sql.80).aspx
http://msdn2.microsoft.com/en-us/ms179853.aspx
http://www.dotnetheaven.com/Uploadfile/LivMic/SQL_Extended_NET05222006031147AM/SQL_Extended_NET.aspx See the above links get you what youare looking… your feedback is appreciated. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

SQL Server 2005 The sysproperties table is deprecated in SQL Server 2005, so the above technique will no longer work. Thankfully, they have added a system catalog view called sys.extended_properties, which works almost the same as the sysproperties table we are already familiar with. SELECT
[Table Name] = OBJECT_NAME(c.object_id),
[Column Name] = c.name,
[Description] = ex.value
FROM
sys.columns c
LEFT OUTER JOIN
sys.extended_properties ex
ON
ex.major_id = c.object_id
AND ex.minor_id = c.column_id
AND ex.name = ‘MS_Description’
WHERE
OBJECTPROPERTY(c.object_id, ‘IsMsShipped’)=0
— AND OBJECT_NAME(c.object_id) = ‘your_table’
ORDER
BY OBJECT_NAME(c.object_id), c.column_id
http://databases.aspfaq.com/schema-…how-the-description-property-of-a-column.html MohammedU.
Moderator
SQL-Server-Performance.com

]]>