Maximum number of columns exception

Folks, I'm having trouble with one of my DB's (on SQL server 2005).
I can't seem to be able to add a new column due to the following:
System.Data.SqlClient.SqlException: CREATE TABLE failed because column
'_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
columns.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
The offending call looks like:
ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
But this also doesn't work just using the Management Studio UI.
The mystery to me is that the table doesn't have anywhere close to 1024
columns. It has 22.
Any ideas?
Eloy
Can you post your .NET code + table's structure to be tested?
"Eloy Perez" <EloyPerez@.discussions.microsoft.com> wrote in message
news:CA48A7AA-6FE0-45F0-A3FD-AF366E852428@.microsoft.com...
> Folks, I'm having trouble with one of my DB's (on SQL server 2005).
> I can't seem to be able to add a new column due to the following:
> System.Data.SqlClient.SqlException: CREATE TABLE failed because column
> '_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
> columns.
> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
> exception, Boolean breakConnection)
> The offending call looks like:
> ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
> But this also doesn't work just using the Management Studio UI.
> The mystery to me is that the table doesn't have anywhere close to 1024
> columns. It has 22.
> Any ideas?

Maximum connections for SQL 2000 Developer Edition

I can't seem to find this info online anywhere. What's
the maximum connections for SQL 2000 Developer Edition?
We have a server running several instances of Developer
edition so does they have the share these connections or
is it a max connections per instance?
Thanks,
Van
Hi
Technically 32 737 per instance, but you will run out of TCP ports long
before a multi-instance installation can max out.
Developer Edition is Enterprise Edition with licensing limitations.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:129201c55266$1fbd2b00$a601280a@.phx.gbl...
>I can't seem to find this info online anywhere. What's
> the maximum connections for SQL 2000 Developer Edition?
> We have a server running several instances of Developer
> edition so does they have the share these connections or
> is it a max connections per instance?
> Thanks,
> Van

MAX, subquery, TOP or something else?

Ive tried many different ways to get this to work right and cant seem to get it. Should I use a Subquery, MAX, TOP or something else?!

I want the last note only for this field:("pc.Description AS Notes").

Any help is greatly appreciated!!!

CODE

SET NOCOUNT ON

SELECT
CONVERT(VARCHAR,pv.visit,101) AS DOS,
pv.TicketNumber,
ISNULL(pp.first,'')+ ' '+ ISNULL(pp.last,'')AS [Patient Name],
ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.Address2,'')+ ' '+ ISNULL(pp.City,'')+ ' '+ ISNULL(pp.State,'')+ ' '+ ISNULL(pp.Zip,'') AS [Patient Address],
IsNull(CONVERT(varchar,pp.Birthdate,101), '') AS Birthdate,
pp.PatientId,
ISNULL(ic.ListName,'') AS [Current Carrier],
ISNULL(pi.InsuredId,'') AS [Insured ID],
df.ListName AS Doctor,
df1.ListName AS Facility,
df2.ListName AS Company,
pva.PatBalance AS [Visit Pat Balance],
pva.InsBalance AS [Visit Ins Balance],
ppa.PatBalance AS [Total PatBalance],
ppa.InsBalance AS [Total InsBalance],
ISNULL(CONVERT(VARCHAR,g.LastStatement,101),'')AS [Last Statement]
-- pc.Description AS Notes,
-- MAX(pc.Created)

FROM PatientVisit pv
LEFT JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
LEFT OUTER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
LEFT JOIN PatientInsurance pi ON pv.CurrentPICarrierId = pi.PatientInsuranceId
LEFT JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId AND pv.CurrentInsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId
INNER JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
LEFT OUTER JOIN DoctorFacility df1 ON pv.FacilityId = df1.DoctorFacilityId
LEFT JOIN [DoctorFacility] AS df2 ON pv.[CompanyId]= df2.[DoctorFacilityId]
LEFT OUTER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
LEFT JOIN PatientCorrespondence pc ON pp.PatientProfileId = pc.PatientProfileId AND pv.PatientVisitId = pc.PatientVisitId

WHERE pv.PatientVisitId = ('6')

-- GROUP BY pv.visit, pv.ticketnumber, pp.first, pp.last, pp.address1, pp.address2, pp.city, pp.state, pp.zip,
-- pp.birthdate, pp.patientid, ic.listname, PI.insuredid, df.listname, df1.listname, df2.listname, pva.patbalance,
-- pva.insbalance, ppa.patbalance, ppa.insbalance, g.laststatement, pc.description,pc.created

As you suggested there are a few ways you could do it, a subquery should be as good as any, and simpler than most others

SELECT ..., (SELECT TOP 1 description
FROM PatientCorrespondence
WHERE PatientProfileID = pp.PatientProfileID AND PatientVisitID = pv.PatientVisitID
ORDER BY Created DESC) AS Notes
FROM ...

|||SNMSDN - you are awesome! THANKS!