Can Sql 2000 and Sql 2005 exist on same server?

My instructions are to install Sql 2005 on server BIGPETE2. I found that there was already an installation of Sql 2000, with databases that I was quite sure should remain as SQL 2000.

According to info I found on the internet, it should be possible to have separate instances of 2000 and 2005, so I went ahead and tried to install, giving the 2005 version a different name, BIGPETE2005. It didn't hit me with any error messages, but neither did it install a different database server. When I was finished, I was able to open Management Studio, but the only database server it could find to open was BIGPETE2, which is not what I wanted.

Enterprise Manager still works, and the mdf dates haven't updated, so I'm guessing the data is still safe, but I need both servers up and running.

Okay, I just read through the installation report, and I saw this:

Service pack requirement check:

Your upgrade is blocked because of service pack requirements. To proceed, apply the required service pack and then rerun SQL Server Setup. For more information about upgrade support, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.

1. What service pack is it asking for?

2. And doesn't that imply that it's upgrading my Sql 2000 installation instead of installing a new instance of Sql 2005?

In Management Studio, you will be able to connect to both instances. To connect to your SQL 2005 instance, you'll need to supply machine name / instance name (ex: MACHINE/BIGPETE2005).

Thanks,
Sam Lester (MSFT)

|||How do you connect to both instances in SQL 2005? Can we have a step by step with screen shots? Are any other modules needed such as the backwards compatibility module?

Can seem to get delete and exist to work right

Hi all,

I am writing a test result database in SQL 2K5 and one of the features I want to implement is a stored procedure that deletes the oldrecords while preserving a set number of records, the following is what my SP looks like

Procedure [dbo].[CleanResults]

@.RecsToKeep bigInt,

@.Output Int output

as

Declare @.Date Datetime

Declare @.Count bigint

set @.Date = getdate()

Select @.Count = Count(UniqueID) from [Main]

Print 'THE COUNT IS'

print @.Count

if ( @.Count > @.RecsToKeep) begin

set @.Count= @.Count - @.RecsToKeep

Print 'THE Number to delete is'

print @.Count

select TOP(@.Count) UniqueID from Main order By [Main].[TestDateTime] desc

Delete from [Main] where exists (select TOP(@.Count) * from Main order By [Main].[TestDateTime] desc);

set @.Output = @.Count

end

else begin

set @.Output = -1

end

whats odd is that the select staement will evaluate correctly and return the oldest record @.Count record, however the delete stament removes all the records. An advice would be appriciated.

Thanks Christopher

PS Any advice for using TOP with variables in MSDE 2k (as opposed to 2k5) would be appreciated

The WHERE EXISTS is not what you are wanting. Try this instead

Code Snippet

Delete from [Main] where UniqueID IN (select TOP(@.Count) UniqueID from Main order By [Main].[TestDateTime] desc);

This will just grab the first set of uniqueIDs and delete those records, which will be the correct number of records.|||

Thanks it worked

|||

You can try:

;with cte

as

(

select *, row_number() over(order by TestDateTime DESC) as rn

from Main

)

delete cte

where rn <= @.Count;

AMB