Category: SQL

Non-matching records in T-SQL One-to-Many Relations

I was recently tasked with finding all the people in our CRM software that lack email addresses, and with around 3000 contacts, there was no way I was going to do it by hand. SQL to the rescue!
There was just one issue, email addresses are stored in a different table to allow each person to have multiple addresses on file. After a bit of digging around, I came up with this:

SELECT
    T.FirstName,
    T.LastName,
    T.CompanyName
FROM (
    SELECT
        CRM.dbo.Companies.CompanyName,
        CRM.dbo.People.FirstName,
        CRM.dbo.People.LastName,
        CRM.dbo.Emails.EmailAddress
    FROM CRM.dbo.People
    LEFT JOIN CRM.dbo.Emails
    ON CRM.dbo.Emails.PersonId = CRM.dbo.People.PersonId
    LEFT JOIN CRM.dbo.Companies
    ON CRM.dbo.Companies.CompanyId = CRM.dbo.People.CompanyId
) AS T
WHERE T.EmailAddress IS NULL

Let’s go through this line-by-line so we can see exactly what it does.

Continue reading Non-matching records in T-SQL One-to-Many Relations

SCCM & SQL

Today I decided to install SCCM to see if it would help with the WDS work I have been doing.
After a quick download from the Microsoft website, I kicked off the installer, only to find a nice helpful error message:

Setup is unable to connect to SQL server with the connection information provided. Verify the following:
- The SQL Server and instance names are entered correctly
- The specified SQL Server instance is not configured to use dynamic ports
- If a firewall is enabled on the SQL Server, inbound rules exist to allow connections to the correct ports
- The account used to run Setup has permissions to connect to the specified SQL Server instance.

Continue reading SCCM & SQL