Agency Software | Staff Management Software | Temporary Staff Software | Nurse Bank Software | Nursing Agency Software

Old tools for sql 2008: backward compatibility.

There was a question on a news group that I belong to which was asking where old tools could be found for (specifically) DTS in SQL 2008. Now it's almost a year since the pack was published, but 2008 is going to be around for a while longer. I thought it would be a good idea to have a searchable article on our site with a link. Just in case I forget!

The tools concerned are in a download:  Microsoft SQL Server 2008 Feature Pack, April 2009

I thought it would be helpful to list all the goodies in the pack:

Microsoft ADOMD.NET
Microsoft Analysis Management Objects
Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider
Microsoft SQL Server 2005 Backward Compatibility Components
Microsoft SQL Server 2008 Command Line Utilities
Microsoft Connector 1.0 for SAP BI
Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007
Microsoft SQL Server 2008 Datamining Viewer Controls
Microsoft SQL Server Driver for PHP 1.0
Microsoft Core XML Services (MSXML) 6.0
Microsoft SQL Server 2005 JDBC Driver 1.2
Microsoft SQL Server 2008 Management Objects
Microsoft OLEDB Provider for DB2
SQL Server Remote Blob Store
Microsoft SQL Server 2008 Native Client
Microsoft SQL Server 2008 Policies
Microsoft Windows PowerShell Extensions for SQL Server
Microsoft SQL Server 2008 Replication Management Objects
Microsoft SQL Server 2008 Report Builder 2.0
Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint
Microsoft SQL Service Broker External Activator
Microsoft SQL Server System CLR Types
Microsoft SQLXML 4.0 SP1
Microsoft Sync Framework
Microsoft SQL Server 2008 Upgrade Advisor

Download page:http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=b33d2c78-1059-4ce2-b80d-2343c099bcb4

Another Ava agency software 2.0 web site going live.
Over the past few years we have provided simple easy to use agency staffing web pages. These give both immediate views of the booking status of any current requests and vacancies listed to advertise to employees.  The information on these pages is easily filterable to show specific date ranges and to select only the information for departments, sites or user defined groupings of departments (e.g. directorates) or sites.
We are now providing another NHS Trust with the more recent version of our Intranet or Internet facing software. This provides direct secure access to allow a department manager to request shifts and (optionally) a higher manager to authorise these requests. Each individual user can be separately authorised only to carry out the various specific tasks for those departments which they have been designated.
Other pages allow booking of available employees, time sheets authorised and invoices viewed. The ability to carry out each of these functions can be specified to department level for each individual user.
The authenticated user can also be an employee, in which case they can specify their own availability, work preferences and present time sheets.
This provides a complete on line booking process to compliment the back office system.
 Heart of England NHS Foundation Trust

Ava always tries to give a human side to support calls and requests. Sometimes this is difficult for genuine reasons.  The reason which is most genuine and also repeated (fortunately not too often) is that the customer's server or network is down and that prevents any software working. Even so we can often help in determining where the faults is or its exact nature. This often means getting the service quickly restored by contacting the customer's IT support with a technical description of the problem.

It is all the more frustrating when we are knocked back by larger company's support. I had arranged broadband for someone with Pipex home broadband. The service no longer became appropriate and the phone line was ceased.  Still broadband bills arrived. So I tried calling Pipex: even on their premium rate sales or support numbers I just got a long announcement and a suggestion to call back in 24 hours. Their web site just crashed every time I tried to submit a contact request (five or six times).  Emails (not using their web site) just got an automated response with a request to state my problem between anti-spam markers - which then got an automatic reply with a request to state my problem between anti-spam markers - etc. I received an email from Talk Talk asking me to follow a link -within 24 hours. I followed it within 24 minutes. I was told it has expired.

The one good thing to come out of this was a google link to saynoto0870.com which gave me a non premium rate number which was actually answered! This superb site allows you to search for non premium rate numbers for companies and it is in my opinion excellent.

To coninue with the Pipex saga. The call centre was in the Philippines islands and no, they could not help either. It was just good to hear a human voice after two hours of total frustration.  At Ava agency software we do not use premium rate number for support and our customers have my mobile number too.

In the Agency software business in the UK, there is a seasonal pattern. As the Christmas period approaches, NHS nurses do as much overtime as possible to build up their pay packets. As a consequence,  Agency nurses and Agencies (can) get a quiet period.  This usually means that it's that time of year when server moves are scheduled. With Ava often helping out in these moves or migrations, it's a busy time for us. With SQL 2008 being a year old, the more nervous and cautious are migration from SQL 2000, SQL 2005 to SQL 2008.
Here are some links to articles I've found on the web to help this process:
An overview of migrating SQL 2000 to 2008 from dell - in clear English
http://www.dell.com/downloads/global/solutions/public/white_papers/SQL_2000_SQL_2008_Migration_Guide.pdf
And the Microsoft offerings:
How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133
How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008
http://support.microsoft.com/kb/918992

As some of you know after many years soaring the updrafts in various parts of the world, I gave up gliding and to gain a bit more fitness and fill some time, I took up Ceroc dancing.

At the end of last year, just before I was snowed in, I went to the Christmas event at Windsor. I'll be candid; I'm not a great fan of Windsor: it's on the same night as Henley and it's like dancing in an aircraft hanger except that you get a smell of chlorine from the swimming area.  Despite the snow and Ice, I arrived shortly after opening: I wasn't the first, there must have been 30 or so there before me. The music was definitely not dance music: everyone was sitting down. Actually this state of affairs continued for nearly an hour, by which time there were about 80 people who had come to dance, to a person, sitting around a dance floor.  I must admit that it takes some skill as a DJ to play music for an hour that Dancers refuse to get up and dance to. They or he managed it.

For the New year I'll be going to Henley, Reading, Newbury, occasionally further afield but definitely not Windsor.

 

Ah well, back to the agency software

 

Happy dancing

Ian

The latest version of AVA Agency software supports NHS AIMs exports and reports. AIMS is a system which allows the NHS to import and data warehouse purchasing information. It relies either on time consuming line by line data entry for invoices or a single file import format for each Trust and invoice run. The information for the file upload required is quite specific and requires repeated entry of job descriptions, pay rate descriptions and specific codes.  There are place holders within Ava for each of these codes and a single file export mechanism. Setting up these placeholders is described by this support article on the Ava Pa. It may be found quickly by searching for AIMS using the site search.

Actually the title is somewhat incorrect. It is what the universe looks like now, except that some of the light has been traveling for 13 Billion years to get to the Hubble deep space telescope.  

These are the latest pictures of the universe's early day that have been released. The farthest galaxies are the red ones  (due to red shift) and we see them as they were only 600 million years after the big bang. With the current estimate of the age of the universe being 13.7 Billion years, this means that we have gone (are viewing objects) back 96% of the way to the beginning of the universe. 

 

Scientific American article

http://www.scientificamerican.com/gallery_directory.cfm?photo_id=6FB7880C-9F1E-88E3-B8EABBEAF59E4D6E 

Hubble article: 

 http://hubblesite.org/newscenter/archive/releases/2009/31/

 Hubble biggest downloadable image:

http://hubblesite.org/newscenter/archive/releases/2009/31/image/a/format/xlarge_web/

 

Enjoy the view. 

Security is in all our interests - except for the bad guys out there. Bad guys use weak security where they find it to install viruses or bots which are programs that hi-jack computers so that rather than doing what you want (and paid for), your computer spends time attacking other computers. As long as each infection finds (on average) more than one other infect-able computer before it is cleaned by up-to-date antivirus software, the infection will never die out.  Often these infections or bots are used to test passwords to see if they can get into an otherwise protected system. The following article is to a reasonably non technical Microsoft document on what not to use as a password.
http://blogs.technet.com/mmpc/archive/2009/11/27/do-and-don-ts-for-p-w0rd.aspx
and
http://www.microsoft.com/protect/fraud/passwords/checker.aspx

As a matter of course, Ava (Agency software) will advise all our customers if we find inappropriate password settings.

Astronomically good game!

Ever since school I've been interested in astronomy. Even before when my old physics master Dicky Dyson took a group of us one evening to gaze at the moon through a very significant telescope. It seemed as detailed as those images we now see quite frequently on HD television. It was entrancing. Well there is a new game and its one all of us can play. At http://mergers.galaxyzoo.org/ there are a sequence of simulations of galaxies colliding and you choose the best match with an actual collision.  How the universe has grown since my childhood. Back then there was the Andromeda galaxy and maybe (it seemed) a handful of star clusters.  There was, perhaps, an inkling that there were quite a few galaxies, but hundreds of thousands? More to the point millions of them colliding? Enough so you could make a passable representation of the alphabet? Believe it or not this game is actually helpful to our understanding of the universe.

Enjoy the game

For many years we have be able to read the SQL version in use by our agency software. (www.ava.co.uk) This goes back all the way to SQL7.0. From time to time we have scoured the web for information on SQL versions. It all really started with the Tripod entry http://vyaskn.tripod.com/sqlsps.htm and from time to time other resources surfaced: http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx http://www.sqlteam.com/article/sql-server-versions of course perhaps the least helpful was http://support.microsoft.com/kb/321185 but so what is new? The following script is for the current stored procedure we use to report version numbers for our Help About Window.

 

Please feel free to use, it is provided without any warrantee of any kind. If you do use it pleas place a link to:  www.ava.co.uk  with the words 'Agency software' adjacent to the link on a web site you administer. Thank you

 

 

Code: 

 

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

if exists ( select  * from dbo.sysobjects where id = object_id(N'[dbo].[ap_SQLVersion]')

 and OBJECTPROPERTY(id, N'IsProcedure') = 1 )

   drop procedure [dbo].[ap_SQLVersion]

GO

 

Create

procedure [dbo].ap_SQLVersion

AS

SET NOCOUNT ON

SELECT

CONVERT(CHAR(25), @@SERVERNAME) AS 'SERVER'

, LTRIM(RTRIM(SUBSTRING(@@VERSION, 22, 5))) AS 'VERSION'

, CASE RTRIM(LTRIM(SUBSTRING(@@VERSION, 22, 5)))

WHEN '6.5' THEN SUBSTRING(@@VERSION, 35, 4)

WHEN '7.00' THEN SUBSTRING(@@VERSION, 35, 4)

WHEN '2000' THEN SUBSTRING(@@VERSION, 35, 4)

WHEN '2005' THEN RTRIM(LTRIM(REPLACE(SUBSTRING(@@VERSION, CHARINDEX('9.00.', @@VERSION) +5 , 5), '.', '')))

WHEN '2008' THEN RTRIM(LTRIM(REPLACE(SUBSTRING(@@VERSION, CHARINDEX('10.0.', @@VERSION) +5 , 5), '.', '')))

END AS 'BUILD'

, CASE RTRIM(LTRIM(SUBSTRING(@@VERSION, 22, 5)))

WHEN '6.5' THEN CASE SUBSTRING(@@VERSION, 35, 4)

WHEN '121' THEN 'NO SP'

WHEN '124' THEN 'SP1'

WHEN '139' THEN 'SP2'

WHEN '151' THEN 'SP3'

WHEN '201' THEN 'NO SP'

WHEN '213' THEN 'SP1'

WHEN '240' THEN 'SP2'

WHEN '252' THEN 'SP3 ** BAD **'

WHEN '258' THEN 'SP3'

WHEN '259' THEN 'SP3 + SBS'

WHEN '281' THEN 'SP4'

WHEN '297' THEN 'SP4 + SBS'

WHEN '339' THEN 'SP4 + Y2K'

WHEN '415' THEN 'SP5 ** BAD **'

WHEN '416' THEN 'SP5a'

WHEN '479' THEN 'SP5a(update)'

ELSE 'Unknown Hot-Fix version or script out of date'

END

WHEN '7.00' THEN CASE SUBSTRING(@@VERSION, 35, 4)

WHEN '1077' THEN 'SP4+Q316333'

WHEN '1063' THEN 'SP4'

WHEN '1004' THEN 'SP3 + Q304851'

WHEN '996' THEN 'SP3 hotfix'

WHEN '978' THEN 'SP3 + Q285870'

WHEN '977' THEN 'SP3 + Q284351'

WHEN '970' THEN 'SP3 + Q283837/282243'

WHEN '961' THEN 'SP3'

WHEN '921' THEN 'SP2 + Q283837'

WHEN '919' THEN 'SP2 + Q282243'

WHEN '918' THEN 'SP2 + Q280380'

WHEN '917' THEN 'SP2 + Q279180'

WHEN '910' THEN 'SP2 + Q275901'

WHEN '905' THEN 'SP2 + Q274266'

WHEN '889' THEN 'SP2 + Q243741'

WHEN '879' THEN 'SP2 + Q281185'

WHEN '857' THEN 'SP2 + Q260346'

WHEN '842' THEN 'SP2'

WHEN '835' THEN 'SP2 Beta'

WHEN '776' THEN 'SP1 + Q258087'

WHEN '770' THEN 'SP1 + Q252905'

WHEN '745' THEN 'SP1 + Q253738'

WHEN '722' THEN 'SP1 + Q239458'

WHEN '699' THEN 'SP1'

WHEN '689' THEN 'SP1 Beta'

WHEN '677' THEN 'MSDE O2K Dev'

WHEN '662' THEN 'Gold+Q232707'

WHEN '658' THEN 'Gold+Q244763'

WHEN '657' THEN 'Gold+Q229875'

WHEN '643' THEN 'Gold+Q220156'

WHEN '623' THEN 'Gold, no SP'

WHEN '583' THEN 'RC1'

WHEN '517' THEN 'Beta 3'

WHEN '416' THEN 'SP5a'

WHEN '415' THEN 'SP5 ** BAD **'

WHEN '339' THEN 'SP4 + y2k'

WHEN '297' THEN 'SP4 + SBS'

WHEN '281' THEN 'SP4'

WHEN '259' THEN 'SP3 + SBS'

WHEN '258' THEN 'SP3'

WHEN '252' THEN 'SP3 ** BAD ** '

WHEN '240' THEN 'SP2'

WHEN '213' THEN 'SP1'

WHEN '201' THEN 'No SP'

WHEN '198' THEN 'Beta 1'

WHEN '151' THEN 'SP3'

WHEN '139' THEN 'SP2'

WHEN '124' THEN 'SP1'

WHEN '121' THEN 'No SP'

ELSE 'Unknown - Hot-Fix version or script out of date'

END

WHEN '2000'

THEN CASE SUBSTRING(@@VERSION, 35, 4)

WHEN '2162' THEN 'SP4 + cumulative hotfix'

WHEN '2159' THEN 'KB 907250 907009'

WHEN '2151' THEN 'KB 903742'

WHEN '2148'

THEN 'KB 902150 898626 895123 899431 901200 899430 901212 900404 902955 900390 903086 900629'

WHEN '2145' THEN 'KB 826906'

WHEN '2040' THEN 'SP4 AWE hotfix'

WHEN '2039' THEN 'Service Pack 4 Downloadable'

WHEN '2026' THEN 'Service Pack 4 (Beta)'

WHEN '1029' THEN 'KB 902851 900625'

WHEN '1027' THEN 'KB 900335'

WHEN '1025' THEN 'KB 899430 899428'

WHEN '1021' THEN 'KB 897578 887700'

WHEN '1020' THEN 'KB 896985'

WHEN '1019' THEN 'KB 897572 896980'

WHEN '1017' THEN 'KB 896425'

WHEN '1014' THEN 'KB 895123 892985'

WHEN '1013' THEN 'KB 891866'

WHEN '1009' THEN 'KB 894254 894257'

WHEN '1007' THEN 'KB 892840 891640 893172 893402 893312 892940'

WHEN '1003' THEN 'KB 892935 892923'

WHEN '1001' THEN 'KB 891707 892310 892551 892205 892141 891719'

WHEN '1000' THEN 'KB 892392 891585'

WHEN '0997' THEN 'KB 891311'

WHEN '0996' THEN 'KB 891017 891201 891268'

WHEN '0994' THEN 'KB 890768 890767 890942'

WHEN '0993' THEN 'KB 888444 890925'

WHEN '0991' THEN 'KB 889314'

WHEN '0990' THEN 'KB 890755 890200'

WHEN '0988' THEN 'KB 889170 890637 889166'

WHEN '0985' THEN 'KB 888998 888429 889239 890730'

WHEN '0980' THEN 'KB 886708 887974'

WHEN '0977' THEN 'KB 889266 884850 885442 843534 884856'

WHEN '0973' THEN 'KB 884554'

WHEN '0972' THEN 'KB 885290'

WHEN '0970' THEN 'KB 884864 885158 884854 884853 884855 872842'

WHEN '0967' THEN 'KB 878501 884772 884260'

WHEN '0962' THEN 'KB 883415'

WHEN '0961' THEN 'KB 873482 875445 872843 873446'

WHEN '0959' THEN 'KB 878500'

WHEN '0957' THEN 'KB 870994'

WHEN '0955' THEN 'KB 867798'

WHEN '0954' THEN 'KB 843282 870972'

WHEN '0952' THEN 'KB 867878 867879 867880 839096'

WHEN '0949' THEN 'KB 867746 843266 843267'

WHEN '0948' THEN 'KB 843263'

WHEN '0944' THEN 'KB 839280'

WHEN '0937' THEN 'KB 841776'

WHEN '0936' THEN 'KB 841627 838409'

WHEN '0935' THEN 'KB 841401'

WHEN '0934' THEN 'KB 841175 841404'

WHEN '0933' THEN 'KB 840406'

WHEN '0929' THEN 'KB 839529'

WHEN '0928' THEN 'KB 840166 839529 839589 839884'

WHEN '0927' THEN 'KB 839688'

WHEN '0926' THEN 'KB 839523 839458'

WHEN '0923' THEN 'KB 839096 838460'

WHEN '0922' THEN 'KB 837231 837970 837969 833045 838459'

WHEN '0919' THEN 'KB 837890 837957'

WHEN '0915' THEN 'KB 837401'

WHEN '0913' THEN 'KB 836839 836651 309802'

WHEN '0911' THEN 'KB 834923 836096 836136 834720'

WHEN '0910' THEN 'KB 834798 835864'

WHEN '0908' THEN 'KB 834290 834688 835581'

WHEN '0904' THEN 'KB 834451 834453'

WHEN '0892' THEN 'KB 833710'

WHEN '0891' THEN 'KB 833406 836141'

WHEN '0879' THEN 'KB 832977'

WHEN '0878' THEN 'KB 838166 832674 832437 831950'

WHEN '0876' THEN 'KB 831997 831999'

WHEN '0873' THEN 'KB 831675 830912 830887 830773'

WHEN '0871' THEN 'KB 829386 830767 830860 831302'

WHEN '0870' THEN 'KB 830262'

WHEN '0869' THEN 'KB 830596 830588'

WHEN '0866' THEN 'KB 830366'

WHEN '0865' THEN 'KB 821537 830382 830375 830395 830298 828945 829183'

WHEN '0863' THEN 'KB 829205 829444'

WHEN '0859' THEN 'KB 821334'

WHEN '0858' THEN 'KB 828637'

WHEN '0857' THEN 'KB 827714 828308 828017'

WHEN '0856' THEN 'KB 828096'

WHEN '0854' THEN 'KB 828699 818079'

WHEN '0852' THEN 'KB 827954 830466'

WHEN '0851' THEN 'KB 827175 826754 827178'

WHEN '0850' THEN 'KB 826906 826815 826860'

WHEN '0848' THEN 'KB 826822'

WHEN '0847' THEN 'KB 826433'

WHEN '0845' THEN 'KB 825854 826364 826376'

WHEN '0844' THEN 'KB 826080'

WHEN '0842' THEN 'KB 825043'

WHEN '0841' THEN 'KB 825225'

WHEN '0840' THEN 'KB 825197 825042 825025 824430 319477 822033'

WHEN '0839' THEN 'KB 824018 824028 824027 823877'

WHEN '0837'

THEN 'KB 820788 823455 825045 821806 822757 822747 822746 822668 821535 821688 821740 820727 823514 824227 825019 821548 819829 823429 825883 825884'

WHEN '0818' THEN 'KB 826161 821277 821688 811188 818806 814919 814950'

WHEN '0816' THEN 'KB 818766 818767 818768 818769 820835 820837 332004'

WHEN '0811' THEN 'KB 819662 818897 819248 819955'

WHEN '0807' THEN 'KB 818899 818335'

WHEN '0804' THEN 'KB 818729'

WHEN '0800' THEN 'KB 817780 818414 818188 818097 818095 816937 818540 828269'

WHEN '0794' THEN 'KB 817464 816440 816883 813524 817709'

WHEN '0789' THEN 'KB 816840 817263 817262 817186 816780 817368 817359 816503 314128'

WHEN '0781' THEN 'KB 815057'

WHEN '0780' THEN 'KB 815056 319477 816084 816069 816039 815593 815592 815114 812915 815183 814997 815476'

WHEN '0775' THEN 'KB 815115'

WHEN '0769' THEN 'KB 815008 814893 815199 814889'

WHEN '0765' THEN 'KB 814509 821548 814654 331158 814894 812995 813494 810688 811611 813769 813759 810163 814460 814916'

WHEN '0763' THEN 'KB 814113'

WHEN '0762' THEN 'KB 814032'

WHEN '760' THEN 'SP3 or SP3a'

WHEN '679' THEN 'SP2 + Q316333'

WHEN '667' THEN 'SP2 + 8/14 fix'

WHEN '665' THEN 'SP2 + 8/8 fix'

WHEN '655' THEN 'SP2 + 7/24 fix (Q323875) *SQLSlammer worm vuln fixed here'

WHEN '650' THEN 'SP2 + Q322853'

WHEN '608' THEN 'SP2 + Q319507'

WHEN '604' THEN 'SP2 + 3/29 fix'

WHEN '578' THEN 'SP2 + Q317979'

WHEN '561' THEN 'SP2 + 1/29 fix'

WHEN '534' THEN 'SP2.01'

WHEN '532' THEN 'SP2'

WHEN '475' THEN 'SP1 + 1/29 fix'

WHEN '452' THEN 'SP1 + Q308547'

WHEN '444' THEN 'SP1 + Q307540/307655'

WHEN '443' THEN 'SP1 + Q307538'

WHEN '428' THEN 'SP1 + Q304850'

WHEN '384' THEN 'SP1'

WHEN '287' THEN 'No SP+Q297209'

WHEN '250' THEN 'No SP+Q291683'

WHEN '249' THEN 'No SP+Q288122'

WHEN '239' THEN 'No SP+Q285290'

WHEN '233' THEN 'No SP+Q282416'

WHEN '231' THEN 'No SP+Q282279'

WHEN '226' THEN 'No SP+Q278239'

WHEN '225' THEN 'No SP+Q281663'

WHEN '223' THEN 'No SP+Q280380'

WHEN '222' THEN 'No SP+Q281769'

WHEN '218' THEN 'No SP+Q279183'

WHEN '217' THEN 'No SP+Q279293/279296'

WHEN '211' THEN 'No SP+Q276329'

WHEN '210' THEN 'No SP+Q275900'

WHEN '205' THEN 'No SP+Q274330'

WHEN '204' THEN 'No SP+Q274329'

WHEN '194' THEN 'RTM No SP'

WHEN '190' THEN 'Gold, no SP'

WHEN '100' THEN 'Beta 2'

WHEN '078' THEN 'EAP5'

WHEN '047' THEN 'EAP4'

ELSE 'Unknown - Hot-Fix version or script out of date'

END

WHEN '2005' THEN CASE RTRIM(LTRIM(REPLACE(SUBSTRING(@@VERSION, CHARINDEX('9.00.', @@VERSION) +5 , 5), '.', '')))

WHEN '608' THEN 'Beta 1'

WHEN '645' THEN 'Internal'

WHEN '747' THEN 'Internal (IDW1)'

WHEN '767' THEN 'Internal (IDW2)'

WHEN '790' THEN 'Internal (IDW3)'

WHEN '823' THEN 'Internal (IDW4)'

WHEN '836' THEN 'Express Edition Technical Preview'

WHEN '844' THEN 'Internal'

WHEN '849' THEN 'Internal'

WHEN '852' THEN 'Beta 2 '

WHEN '917' THEN 'Internal'

WHEN '951' THEN 'October CTP'

WHEN '981' THEN 'December CTP'

WHEN '1090' THEN 'March CTP (Doc as Feb)'

WHEN '1116' THEN 'April CTP'

WHEN '1187' THEN 'June CTP'

WHEN '1314' THEN 'September'

WHEN '1399' THEN 'RTM '

WHEN '1500' THEN 'RTM +KB 910414 910416 910419'

WHEN '2047' THEN 'SP1'

WHEN '3042' THEN 'SP2 needs hot fix'

WHEN '3043' THEN 'SP2 +MaintenancePlanTasks.dll'

WHEN '3044' THEN 'SP2 +GRD2'

WHEN '3050' THEN 'SP2 +GRD1'

WHEN '3052' THEN 'SP2 +933097'

WHEN '3054' THEN 'SP2 +934458'

WHEN '3059' THEN 'SP2 +934459'

WHEN '4266' THEN 'SP3 + Q974648 (Cumulative HF6)'

WHEN '4230' THEN 'SP3 + Q972511 (Cumulative HF5)'

WHEN '4226' THEN 'SP3 + Q970279 (Cumulative HF4)'

WHEN '4224' THEN 'SP3 + Q971409'

WHEN '4220' THEN 'SP3 + Q967909 (Cumulative HF3)'

WHEN '4216' THEN 'SP3 + Q967101'

WHEN '4211' THEN 'SP3 + Q961930 (Cumulative HF2)'

WHEN '4207' THEN 'SP3 + Q959195 (Cumulative HF1)'

WHEN '4035' THEN 'SP3 + Q955706'

WHEN '4053' THEN 'SP2 Express advanced services'

WHEN '3355' THEN 'SP2 + Q216793 (Cumulative HF16)'

WHEN '3330' THEN 'SP2 + Q972510 (Cumulative HF15)'

WHEN '3328' THEN 'SP2 + Q970278 (Cumulative HF14)'

WHEN '3327' THEN 'SP2 + Q948567 / 961648'

WHEN '3325' THEN 'SP2 + Q967908 (Cumulative HF 13)'

WHEN '3320' THEN 'SP2 + Q969142'

WHEN '3318' THEN 'SP2 + Q967199'

WHEN '3315' THEN 'SP2 + Q962970 (Cumulative HF12, available via request.)'

WHEN '3310' THEN 'SP2 + Q960090'

WHEN '3303' THEN 'SP2 + Q962209'

WHEN '3302' THEN 'SP2 + Q961479 / 961648'

WHEN '3301' THEN 'SP2 + Q958735 (Cumulative HF11, avail. via request.)'

WHEN '3295' THEN 'SP2 + Q959132'

WHEN '3294' THEN 'SP2 + Q956854 (Cumulative HF10, avail. via request.)'

WHEN '3291' THEN 'SP2 + Q956889'

WHEN '3289' THEN 'SP2 + Q937137'

WHEN '3282' THEN 'SP2 + Q953752 / 953607 (Cumulative HF9, avail. via request.)'

WHEN '3261' THEN 'SP2 + Q955754'

WHEN '3260' THEN 'SP2 + Q954950'

WHEN '3259' THEN 'SP2 + Q954669 / 954831'

WHEN '3257' THEN 'SP2 + Q951217 (Cumulative HF8, avail. via request.)'

WHEN '3253' THEN 'SP2 + Q954054'

WHEN '3244' THEN 'SP2 + Q952330'

WHEN '3242' THEN 'SP2 + Q951190'

WHEN '3240' THEN 'SP2 + Q951204'

WHEN '3239' THEN 'SP2 + Q949095 (Cumulative HF7, avail. via PSS only)' 

WHEN '3235' THEN 'SP2 + Q950189'

WHEN '3233' THEN '(QFE) SP2 + Q941203 / 948108'

WHEN '3232' THEN 'SP2 + Q949959'

WHEN '3231' THEN 'SP2 + Q949687/949595'

WHEN '3230' THEN 'SP2 + Q949199'

WHEN '3228' THEN 'SP2 + Q946608 (Cumulative HF6, avail. via PSS only)'

WHEN '3224' THEN 'SP2 + Q947463'

WHEN '3222' THEN 'SP2 + Q945640 / 945641 / 947196 / 947197'

WHEN '3221' THEN 'SP2 + Q942908 / 945442 / 945443 / 945916 / 944358 '

WHEN '3215' THEN 'SP2 + Q941450 (Cumulative HF5, avail. via PSS only)'

WHEN '3209' THEN 'SP2 (KB N/A, SQLHF Bug #50002118)'

WHEN '3208' THEN 'SP2 + Q944902'

WHEN '3206' THEN 'SP2 + Q944677'

WHEN '3205' THEN 'SP2 (KB N/A, SQLHF Bug #50001708/50001999)'

WHEN '3203' THEN 'SP2 (KB N/A, SQLHF Bug #50001951/50001993/50001997/50001998/50002000)'

WHEN '3200' THEN 'SP2 + Q941450 (Cumulative HF4, avail. via PSS only)'

WHEN '3195' THEN 'SP2 (KB N/A, SQLHF Bug #50001812)'

WHEN '3194' THEN 'SP2 + Q940933'

WHEN '3186' THEN 'SP2 + Q939562 (Cumulative HF3, avail. via PSS only)'

WHEN '3182' THEN 'SP2 + Q940128'

WHEN '3180' THEN 'SP2 + Q939942'

WHEN '3179' THEN 'SP2 + Q938243'

WHEN '3178' THEN 'SP2 (KB N/A, SQLHF Bug #50001193/5001352)'

WHEN '3177' THEN 'SP2 + Q939563 / 939285'

WHEN '3175' THEN 'SP2 + Q936305 /938825 (Cumulative HF2, avail. via PSS only)'

WHEN '3171' THEN 'SP2 + Q937745'

WHEN '3169' THEN 'SP2 + Q937041/937033'

WHEN '3166' THEN 'SP2 + Q936185 / 934734'

WHEN '3162' THEN 'SP2 + Q932610/935360/935922'

WHEN '3161' THEN 'SP2 + Q935356/933724(Cumulative HF1, avail. via PSS only)'

WHEN '3159' THEN 'SP2 + Q934459'

WHEN '3156' THEN 'SP2 + Q934226'

WHEN '3155' THEN 'SP2 + Q933549 / 933766/933808/933724/932115/933499'

WHEN '3154' THEN 'SP2 + Q934106 / 934109 / 934188'

WHEN '3153' THEN 'SP2 + Q933564'

WHEN '3152' THEN 'SP2 + Q933097 (Cumulative HF1)'

WHEN '3077' THEN 'SP2 + Q960089'

WHEN '3073' THEN 'SP2 + Q954606 (GDR)'

WHEN '3068' THEN '(GDR) SP2 + Q941203 / 948109'

WHEN '3054' THEN 'SP2 + Q934458'

WHEN '3050' THEN 'SP2 + Q933508'

WHEN '3043' THEN 'SP2 + Q933508 (use this if SP2 was applied prior to 3/8)'

WHEN '3042' THEN 'Fixed SP2 (use this if SP2 was NOT applied yet - orig. RTM removed)'

WHEN '3033' THEN 'SP2 CTP (December) - Fix List'

WHEN '3027' THEN 'SP2 CTP (November)'

WHEN '3026' THEN 'SP1 + Q929376'

END 

WHEN '2008' THEN CASE RTRIM(LT

RIM(REPLACE(SUBSTRING(@@VERSION, CHARINDEX('10.0.', @@VERSION) +5 , 5), '.', '')))

WHEN '1019' THEN 'June CTP'

WHEN '1049' THEN 'July CTP (requires Virtual Server 2005 R2)'

WHEN '1300' THEN 'February CTP'

WHEN '1600' THEN 'RTM'

WHEN '1750' THEN 'RTM + Q956718)'

WHEN '1771' THEN 'RTM + Q958611)'

WHEN '1779' THEN 'RTM + Q958186 Cumulative HF2, available by request.'

WHEN '1787' THEN 'RTM + Cumulative Update 3'

WHEN '1798' THEN 'RTM + Cumulative Update 4'

WHEN '1806' THEN 'RTM + Cumulative Update 5'

WHEN '1812' THEN 'RTM + Cumulative Update 6'

WHEN '2531' THEN 'Service Pack 1'

WHEN '2710' THEN 'SP1 + Cumulative Update 1'

WHEN '2712' THEN 'SP1 + Q970507'

WHEN '2714' THEN 'SP1 + Cumulative Update 2'

WHEN '2723' THEN 'SP1 + Cumulative Update 3'

WHEN '2734' THEN 'SP1 + Cumulative Update 4'

END

END AS 'SP'

, CASE RTRIM(LTRIM(SUBSTRING(@@VERSION, 22, 5)))

WHEN '6.5' THEN SUBSTRING(@@VERSION, 30, 4)

WHEN '7.00' THEN SUBSTRING(@@VERSION, 30, 3)

WHEN '2000' THEN RTRIM(LTRIM(REPLACE(SUBSTRING(@@VERSION, CHARINDEX('8.', @@VERSION) +2 , 3), '.', '')))

WHEN '2005' THEN RTRIM(LTRIM(REPLACE(SUBSTRING(@@VERSION, CHARINDEX('9.', @@VERSION) +2 , 3), '.', '')))

WHEN '2008' THEN RTRIM(LTRIM(REPLACE(SUBSTRING(@@VERSION, CHARINDEX('10.', @@VERSION) +3 , 2), '.', '')))

END AS 'VER'

 

SET NOCOUNT OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

--test

--exec ap_SQLVersion