normandantzig's questions

Mudei um site de um servidor Windows Server 2003 para um servidor Windows Server 2008 (64 bits). O site se conecta a um banco de dados SQL Server 2005. Importei o banco de dados e estou executando-o no SQL Server 2014 (64 bits).

Eu tenho o IIS no servidor Windows Server 2008 executando o site. O site é executado em ASP clássico. Estou tentando determinar qual tipo de objeto de conexão preciso para fazer com que o site ASP clássico se conecte ao banco de dados SQL Server 2014.

Este é o antigo DSN:

Provider=sqloledb;Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=theDatabase;User Id=myID;Password=password

Isso está relacionado a esta pergunta: Unir várias tabelas resulta em linhas duplicadas

Tenho duas tabelas que estou juntando. Eles compartilham uma chave. A tabela de pessoas tem um nome por chave primária, mas a tabela de email tem vários emails por personId. Quero mostrar apenas o primeiro e-mail por pessoa. Atualmente, recebo várias linhas por pessoa porque eles têm vários e-mails. Estou executando o SQL Server 2005.

EDIT: Este é o T-SQL. O primeiro email é literalmente a primeira linha de email por pessoa.

Edit 2: Primeiro e-mail, como vejo, seria a primeira linha de e-mail que aparece na junção à medida que o SQL funciona na consulta. Não importa qual e-mail aparece. Só que não aparece mais de um e-mail. Espero que isso deixe mais claro.

Table1: Person
Table2: Email

Select Person.PersonName, Email.Email
From person 
left join on Person.ID=Email.PersonId;

Eu tenho uma enorme string de consulta que estou tentando separar para um projeto no qual estou trabalhando. Estou procurando alguma orientação sobre como desmontá-lo (formatá-lo para legível por humanos). Além disso, quais são os sinais de adição? BTW: Eu testei e funciona.

SELECT  Name ,
        Company ,
        ID ,
        Type = CASE Person.IsClient
                 WHEN NULL THEN 'Other'
                 WHEN '' THEN 'Other'
                 WHEN 'Contact' THEN 'Other'
                 WHEN 'True' THEN 'Client'
                 ELSE ISNULL(Person.IsClient, 'Other')
               END ,
        SortOrder = CASE ISNULL(CompanyIndividual, 'nullstr')
                      WHEN 'company' THEN ISNULL(ClientName, Name)
                      ELSE LTRIM(ISNULL(LastName, '') + ISNULL(FirstName, '')
                                 + ISNULL(Company, Name) + ISNULL(Name, ''))
                    END ,
        SubType ,
        UDF1 ,
        UDF2 ,
        UDF3 ,
        UDF4 ,
        UDF5 ,
        UDF6 ,
        UDF7 ,
        UDF8 ,
        UDF9 ,
        UDF10 ,
        SuperGroup ,
        Notes ,
        WebSite ,
        UserName ,
        LastNoteDateX = CASE ISNULL(UserName, '')
                          WHEN '' THEN LastNoteDate
                          ELSE ( SELECT MAX(LastNoteDate)
                                 FROM   PersonOrganization WITH ( NOLOCK )
                                 WHERE  Person = Person.ID
                                        AND Organization = '04202000000000000066'
                               )
                        END ,
        LastNotePersonX = CASE ISNULL(UserName, '')
                            WHEN '' THEN LastNotePerson
                            ELSE ( SELECT   MAX(LastNotePerson)
                                   FROM     PersonOrganization WITH ( NOLOCK )
                                   WHERE    Person = Person.ID
                                            AND Organization = '04202000000000000066'
                                 )
                          END ,
        ItemType = ISNULL(PersonUnread.ItemType, '') ,
        Flag = ISNULL(Flag.Flag, 'Grey') ,
        PopupReminderPerson = ISNULL(PopupReminder.Person, 'F') ,
        Person.FirstName ,
        Person.LastName ,
        Person.CompanyIndividual ,
        PersonUnreadContactItemType = ISNULL(PersonUnreadContact.ItemType, '') ,
        -1 ,
        Description ,
        Person.ShortName ,
        PhoneNumber = ISNULL(( SELECT   PhoneNumber
                               FROM     Phone WITH ( NOLOCK )
                                        JOIN PersonPhone WITH ( NOLOCK ) ON PersonPhone.Phone = Phone.ID
                               WHERE    PersonPhone.Person = Person.ID
                                        AND Phone.DisplayOrder = 1
                             ), '') ,
        PersonView
FROM    Person WITH ( NOLOCK )
        LEFT JOIN PersonUnread WITH ( NOLOCK ) ON Person.ID = PersonUnread.Ref
                                                  AND PersonUnread.Person = '38808000000000001177'
                                                  AND PersonUnread.ItemType = 'ContactNote'
        LEFT JOIN Flag WITH ( NOLOCK ) ON Person.ID = Flag.Ref
                                          AND Flag.Person = '38808000000000001177'
        LEFT JOIN PopupReminder WITH ( NOLOCK ) ON Person.ID = PopupReminder.Ref
                                                   AND PopupReminder.Person = '38808000000000001177'
                                                   AND PopupReminder.Status = 'Pending'
        LEFT JOIN PersonUnread PersonUnreadContact WITH ( NOLOCK ) ON Person.ID = PersonUnreadContact.Ref
                                                              AND PersonUnreadContact.ItemType = 'Contact'
                                                              AND PersonUnreadContact.Person = '38808000000000001177'
WHERE   ( ( ISNULL(Person.PersonView, 'A') = 'A'
            OR Person.PersonView = ''
          )
          OR ( ISNULL(Person.PersonView, 'A') = 'C'
               AND ( Person.Creator = '38808000000000001177'
                     OR ISNULL(Person.Creator, '') = ''
                   )
             )
          OR ( ISNULL(Person.PersonView, 'A') = 'P'
               AND ( ( Person.Creator = '38808000000000001177'
                       OR ISNULL(Person.Creator, '') = ''
                     )
                     OR EXISTS ( SELECT Assignee
                                 FROM   PersonAssignee WITH ( NOLOCK )
                                 WHERE  PersonAssignee.Person = Person.ID
                                        AND PersonAssignee.Assignee = '38808000000000001177' )
                   )
             )
        )
        AND ( (( Person.SuperGroup = '04202000000000000066'
                 OR Person.ID IN (
                 SELECT Person
                 FROM   PersonOrganization WITH ( NOLOCK )
                 WHERE  Organization = '04202000000000000066' )
               )
              AND EXISTS ( SELECT   Person
                           FROM     PersonOrganization
                           WHERE    Person = '38808000000000001177'
                                    AND Organization = '04202000000000000066'
                                    AND ( ISNULL(RestrictContacts, 'False') <> 'True'
                                          OR EXISTS ( SELECT  Person
                                                      FROM    PersonAssignee
                                                              WITH ( NOLOCK )
                                                      WHERE   Person = Person.ID
                                                              AND Assignee = '38808000000000001177' )
                                          OR Person.ID = '38808000000000001177'
                                          OR Person.Creator = '38808000000000001177'
                                        ) ))
            )
        AND ( Person.SuperGroup = '04202000000000000066'
              OR Person.ID IN ( SELECT  Person
                                FROM    PersonOrganization WITH ( NOLOCK )
                                WHERE   Organization = '04202000000000000066' )
            )
        AND ( ( ISNULL(Person.Name, '') + ' ' + ISNULL(Person.Company, '')
                + ' ' + ISNULL(Person.FirstName, '') + ' '
                + ISNULL(Person.LastName, '') + ' ' + ISNULL(Person.MiddleName,
                                                             '') + ' '
                + ISNULL(Person.Title, '') + ' ' + ISNULL(Person.Prefix, '')
                + ' ' + ISNULL(Person.SubType, '') + ' '
                + ISNULL(Person.WebSite, '') + ' ' + ISNULL(Person.Suffix, '')
                + ' ' + ISNULL(Person.UDF1, '') + ISNULL(Person.UDF2, '')
                + ISNULL(Person.UDF3, '') + ISNULL(Person.UDF4, '')
                + ISNULL(Person.UDF5, '') + ISNULL(Person.UDF6, '')
                + ISNULL(Person.UDF7, '') + ISNULL(Person.UDF8, '')
                + ISNULL(Person.UDF9, '') + ISNULL(Person.UDF10, '') LIKE '%divorce%' )
              OR ( ISNULL(Description, '') LIKE '%divorce%' )
              OR EXISTS ( SELECT    ID
                          FROM      Note WITH ( NOLOCK )
                          WHERE     Note.Item = Person.ID
                                    AND SuperGroup = '04202000000000000066'
                                    AND ( ( Note.Title LIKE '%divorce%' )
                                          OR ( Note.Note LIKE '%divorce%' )
                                        ) )
              OR EXISTS ( SELECT    ID
                          FROM      Phone WITH ( NOLOCK )
                                    JOIN PersonPhone WITH ( NOLOCK ) ON PersonPhone.Phone = Phone.ID
                          WHERE     PersonPhone.Person = Person.ID
                                    AND ( Phone.PhoneNumber LIKE '%divorce%' ) )
              OR EXISTS ( SELECT    ID
                          FROM      Address WITH ( NOLOCK )
                                    JOIN PersonAddress WITH ( NOLOCK ) ON PersonAddress.Address = Address.ID
                          WHERE     PersonAddress.Person = Person.ID
                                    AND ( ( Description LIKE '%divorce%' )
                                          OR ( Street LIKE '%divorce%' )
                                          OR ( City LIKE '%divorce%' )
                                          OR ( State LIKE '%divorce%' )
                                          OR ( Zip LIKE '%divorce%' )
                                          OR ( Country LIKE '%divorce%' )
                                        ) )
              OR EXISTS ( SELECT    ID
                          FROM      Email WITH ( NOLOCK )
                          WHERE     Email.Person = Person.ID
                                    AND ( Email.Email LIKE '%divorce%' ) )
              OR EXISTS ( SELECT    FCGroup.ID
                          FROM      FCGroup WITH ( NOLOCK )
                                    JOIN FCGroupPerson WITH ( NOLOCK ) ON FCGroup.ID = FCGroupPerson.FCGroup
                          WHERE     FCGroupPerson.Person = Person.ID
                                    AND ( FCGroup.Name LIKE '%divorce%' ) )
              OR EXISTS ( SELECT    ID
                          FROM      Note WITH ( NOLOCK )
                          WHERE     Note.Item = Person.ID
                                    AND ( ( Note.Title LIKE '%divorce%' )
                                          OR ( Note.Note LIKE '%divorce%' )
                                        ) )
              OR EXISTS ( SELECT    P2.ID
                          FROM      Person P2 WITH ( NOLOCK )
                                    JOIN PersonAssignee WITH ( NOLOCK ) ON P2.ID = PersonAssignee.Assignee
                          WHERE     PersonAssignee.Person = Person.ID
                                    AND ( P2.Name + ' ' + P2.FirstName + ' '
                                          + P2.MiddleName + ' ' + P2.LastName
                                          + ' ' + P2.Title + ' ' + P2.Company LIKE '%divorce%' ) )
            )
ORDER BY 5