SSRS Report with SQL

Question

So I have a report that I'm trying to create for my class. I'm using the AdventureWorks database so it isn't anything to complex. I enter my SQL script in SQL Server Management Studio and HeidiSQL and both bring back the results I'm looking for, so I'm pretty sure my syntax is correct. However when I enter my query into SSDT it comes up with an error. Any ideads as to why this might happen.

My query is this:

SELECT DISTINCT
    s.Name AS 'Store Name', 
    c.FirstName AS 'Store Contact First Name', 
    c.LastName AS 'Store Contact Last Name', 
    c.Phone AS 'Store Contact Phone',
    sm.Name AS 'Shipping Company Name',
    soh.OrderDate AS 'Purchase Order Date',
    soh.PurchaseOrderNumber AS 'Purchase Order Number', 
    e.Title AS 'Employee Title', 
    c1.FirstName AS 'Sales Person First Name', 
    c1.LastName AS 'Sales Person Last Name', 
    a.AddressLine1 AS 'Bill To Address', 
    a.City AS 'Bill To City', 
    sp.Name AS 'Bill To State', 
    cr.Name AS 'Bill To Country', 
    a.PostalCode AS 'Bill To Postal Code', 
    a1.AddressLine1 AS 'Ship To Address', 
    a1.City AS 'Bill To City', 
    sp1.Name AS 'Ship To State', 
    cr1.Name AS 'Ship To Country', 
    a1.PostalCode AS 'Ship To Postal Code'
FROM 
    Sales.SalesOrderHeader AS soh 
LEFT OUTER JOIN 
    Sales.Store AS s ON soh.CustomerID = s.CustomerID 
LEFT OUTER JOIN 
    Person.Contact AS c ON soh.ContactID = c.ContactID 
LEFT OUTER JOIN 
    Purchasing.ShipMethod AS sm ON soh.ShipMethodID =sm.ShipMethodID 
LEFT OUTER JOIN 
    HumanResources.Employee AS e ON soh.SalesPersonID = e.EmployeeID 
INNER JOIN 
    Person.Contact AS c1 ON e.ContactID = c1.ContactID 
LEFT OUTER JOIN 
    Person.Address AS a ON soh.BillToAddressID = a.AddressID 
INNER JOIN 
    Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID 
INNER JOIN 
    Person.CountryRegion AS cr ON sp.CountryRegionCode = cr.CountryRegionCode 
LEFT OUTER JOIN 
    Person.Address AS a1 ON soh.ShipToAddressID = a1.AddressID 
INNER JOIN 
    Person.StateProvince AS sp1 ON a1.StateProvinceID = sp1.StateProvinceID 
INNER JOIN 
    Person.CountryRegion AS cr1 ON sp1.CountryRegionCode = cr1.CountryRegionCode

Like I said I get the results I want in HeidiSQL and in Microsoft SQL Server Management Studio but once I try to add it as a dataset in VisualBasic Datatools and error pops up saying either

could not create a list of fields for the query. Verify that you can connect to the data source (I can) and that your query syntax is correct (as far as I can tell it is). An item with the same key has already been added. To continue with out updating the fields, click ok

The other error that I get is:

could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct

Any ideas or suggestions would be greatly appreciated.

Answer 1

My best guess is you have two 'Bill To City' columns. I'm assuming the second one you meant to be 'Ship To City'. From the error message it sounds like this could be causing the problem.