(Default) instance already exists (not really!)

Ok,

I haven't had this much trouble installing MS software in many moons. My end goal is to install Team Foundation Server, but to do that I need SQL Server 2005 first, then SharePoint, then TFS. At each of these steps it is appearing to be a mine field, and it is apparent that the MS Installer is doing a terrible job at uninstalling everything.

End of whine. So my situation is this, I had installed MS SQL Server Developer 2005 and found out that the Reporting Server wasn't working. Basically, because at one time or another I had FP extentions installed, and permissions were hopelessly munged. The fix on the forums was to back everything out... SQL Server, IIS, Sharepoint, and do a scorch and burn, effectively erradicating any trace that any of these existed on the disk.

This accomplished, but... now when I try to reinstall SQL Server, it claims the default instance "already exists." So... I've tried various registry hacks, tracking down any trance of instances. Here are some of things I've tried to no avail...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=148487&SiteID=1
http://support.microsoft.com/default.aspx?scid=kb;en-us;290991&sd=tech
http://support.microsoft.com/default.aspx?scid=kb;en-us;290301

So... short of going beyond scorch and burn, and nuke the entire machine, what are my options? Erasing the OS and starting from scratch does not seem like an option because, it seems, that seems I'll never get anything installed if I have to keep doing this.

Ideas anyone? Your help is greatly appreciated.

-Eric

FYI -- When running RegSeeker I find nearly 300 bogus registry entries left over from the MS uninstall--someone's got to fix that.Ok, I found a possible solution. But... it is very very risky. So don't blame me if you torpedo your server using my advice. But it worked for me.

But I'm posting this in case someone else find themselves in the same bind as me.

Following the suggestion of one of the other forum posts I saw, I looked for all entries for SQL Server. I saw a zillion entries for version 9.0.242.0.

Using RegSeeker, I wacked those. So they were gone.

Next, I saw another bazillion entries for "SQL Server." I surmized that SQL Server was completely uninstalled, these should be gone as well. So I delete those as well.

Another search for "SQLServer" also turned up a bazillion entries. Most of these were classes. Taking a deep breath, I deleted all of these as well.

In all, there were many thousands of registry entries that probably should have been deleted by the uninstall, but weren't.

So far, I've managed to reinstall SQL Server 2005 Developer and all is A-OK. So problem solved (I'd knock on wood if I have any real wood in my cube).

-Ericsql

"Must declare the scalar variable" in table-valued function

Hi, I'm having trouble with this multi-statement table-valued function:

ALTER FUNCTION MakeArDetail
(
-- Add the parameters for the function here
@.dateStart DATETIME,
@.dateEnd DATETIME
)
RETURNS @.arDetail TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @.arTemp TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)

INSERT INTO @.arTemp
SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @.arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = @.arTemp.Insurer AND
Pims.NABP = @.arTemp.NABP AND
Pims.NumRx = @.arTemp.NumRx
);

INSERT INTO @.arDetail SELECT * FROM @.arTemp
RETURN
END
GO

I get
Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43
Must declare the scalar variable "@.arTemp".

I don't understand why SQL thinks @.arTemp is a scalar variable which has to be declared.
If I don't include the UPDATE command the thing works.

Not sure of the why, but aliasing the table variable seems to satisfy the compiler.

Code Snippet

UPDATE @.arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = t1.Insurer AND
Pims.NABP = t1.NABP AND
Pims.NumRx = 1.NumRx
)

FROM @.arTemp t1;

|||Immediately after posting that I found the workaround -- enclose @.arTemp in brackets where it is used to qualify a column.

So
ALTER FUNCTION MakeArDetail
(
-- Add the parameters for the function here
@.dateStart DATETIME,
@.dateEnd DATETIME
)
RETURNS @.arDetail TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @.arTemp TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)

INSERT INTO @.arTemp
SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @.arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = [@.arTemp].Insurer AND
Pims.NABP = [@.arTemp].NABP AND
Pims.NumRx = [@.arTemp].NumRx
);

INSERT INTO @.arDetail SELECT * FROM @.arTemp
RETURN
END
GO

works -- at least it passes the syntax check.
I think this is a SQL bug.

"Keys lock row not found" error

Jens

I also am having trouble with the reporting services not being able to the report server databases. I get the error "Keys lock row not found". I can't find any citation to help me figure out what this means nor how to fix it. Any help you can give me would be greatly appreciated.

Ron

What version of Reporting Services are you using?

How did you create your report server database? Did you make any changes to it manually using OSQL or Enterprise Manager or Management Studio?

-Lukasz

"Keys lock row not found" error

Jens

I also am having trouble with the reporting services not being able to the report server databases. I get the error "Keys lock row not found". I can't find any citation to help me figure out what this means nor how to fix it. Any help you can give me would be greatly appreciated.

Ron

What version of Reporting Services are you using?

How did you create your report server database? Did you make any changes to it manually using OSQL or Enterprise Manager or Management Studio?

-Lukasz