none
SPD Linked data wizard error - says the fields I am using don't contain matching data

    Question

  • Hi,
    I have a SharePoint (MOSS Enterprise) site where I have established two data sources using SharePoint Designer 2007. They are based on SQL views. These data sources show data quite happily when I use the context menu to show data, so I know the connection and credentials are OK.

    When  I try to link them to use as a linked data source (so I can create a subview) I get to the point of selecting the fields from each data source to join on and the SPD wizard tells me "The selected fields do not contain matching data. Click OK to continue, or click cancel to select different fields." The fields I am using are most definately matching, one is the primary key for its table, and both are set to the int data type. I have created a similar join query in SQL management studio on a number of occasions, joining the tables using the same fields. I have re-checked and the fields are exactly the same data type. I have experimented with other data sources with a common field and the wizard gives me the same error.

    I have spent the last 2 days trying to find any reference on the web about possible problems with SPD linked data sources but to no avail, and my deadline is looming (after stupidly telling the boss "sure, I can do that....")

    Is there a known issue with SPD linking data sources, or have I missed some step or made a novice mistake (why would I get as far as I do in that case?)

    cheers,

    Mark

    • Edited by Mike Walsh FIN Wednesday, November 24, 2010 8:02 AM Don't use CAPS in Titles. Removed.
    • Edited by emjem Friday, November 26, 2010 2:03 AM updated to show the full error message
    Wednesday, November 24, 2010 7:54 AM

Answers

All replies

  • Hi Mark,

    Please check the follow steps to add database into a data view:

    1 Add a database as a data source by add a new database source in data base library. Refer to this link in the Connect to database by saving the user name and password part or you can also try the other two authentication methods.

    2 Add a linked data source follow this reference. Please notice the difference between the merging and joining method. Perhaps it is the reason you haven’t get data at the beginning.

    If the steps you took are exactly correct but SqlServer data is still empty, I think you should check the database data and permission (or settings) again.


    Best regards. Emir
    Friday, November 26, 2010 11:11 AM
  • Hello Emir,

    I did follow the instructions at the links you provided. I have followed those instructions to the letter, as well as referring to my SPD Bible and many other Googled resources, but I can't find anyone with the same problem.

    I have created two SQL Views. One called Machines and the other called Peripherals. Machines is based on a table containing data on computers and Peripherals is based on a table containing assets like printers, monitors etc. I used SQL Views to minimise the amount of data gathered by the data source.

    I then created two data source connections using SQL Server user name and password (I'm not worried about the credentials being passed as plain text - it's a very limited credential) to these SQL Views, and when I select "show data" they display the expected records, so I know they are configured OK and successfully connecting. Both these data sources have a column called AssetID, which is of data-type int in both the source tables.

    My problem happens when I try to link these two data sources. I can select them in the Link Data Sources Wizard, and choose the join option, but when I tell the  wizard that the AssetID column is the common field it returns the error "The selected fields do not contain matching data. Click OK to continue, or click cancel to select different fields." This is obviously not correct. I can select two other columns which also, coincidentally, have the same data type and are common (such as Cost or AssetNo) but the wizard returns the same error. I need to join the two data sources on the AssetID column as it is primary key for the Machines view and I am trying to set up a subview to show the associated machine's peripherals.

    I have even tried using a CAST to render the AssetID field as char from both SQL Views, hoping that SPD might be having a problem with the int data type, but I still get the same error.

    I originally set this up using data sources based on connections to the actual machine and peripheral tables (i.e. not using SQL Views) but that had the same problem and is what started me off experimenting with Views etc. No matter what I try the Link Data Source WIzard just doesn't want to accept the common column, but I know it is valid! 

    Any ideas?

    regards,

    Mark


    Mark
    Sunday, November 28, 2010 1:00 AM
  • Hi Mark,

    I did a test and I got the some error when Link Data Source with two views.

    But use T-SQL command to connect the database could work successful. Please do the following steps:

    1.        Click Connect to a database under Database Connection part in Data Source Library window.

    2.        Click Configure Database Connection button.

    3.        Click Next after type the Server Name, User name and Password. Click Ok to continue connect.

    4.        Select specify custom Select, Update, Insert, and Delete commands using SQL or stored procedures > Click Finish.

    5.        Click Edit Command button, type T-sql command (to join these two views) in the select command box. Click OK to finish the connection.

    Now we no need to Link source with these two views. Insert a Data View with the new custom query data base. And it allow insert Subview too.

    Hope it helps.


    Best regards. Emir
    Monday, November 29, 2010 5:42 AM
  • Hi Emir,

    Thanks. I will give it a go and let you know how it works out.

    cheers,

    Mark
    Tuesday, November 30, 2010 3:29 AM
  • Hello again Emir,

    thanks for your previous suggestion. I am stuck trying to figure out how to get DISTINCT rows from the master Machines then a subview of each machine's Peripherals.

    I used the following custom SQL:

    SELECT LHN_MachineAssets.*, LHN_PeripheralAssets.*
    FROM LHN_MachineAssets JOIN
    LHN_PeripheralAssets ON LHN_MachineAssets.AssetID = LHN_PeripheralAssets.ParentAssetID

    I used a simple JOIN to eliminate the Machine records where AssetID is NULL.

    This returns 19331 rows, which is correct number of Peripheral records.

    But I can't figure out how to use this Data Source to set up a DFWP with multi-item view of the Machines and a subview of each Machine's peripherals in a layer. How do I show only DISTINCT Machine rows - the query returns multiple rows (obviously) for each peripheral that is related to a parent Machine, but I only want to show a single row for each Machine and then a layer will present each Machine's related Peripheral assts. I now how to insert the data view, and I know how to insert the layer, but I'm blowed if I can figure out how to get a distinct view of the Machines.

    All this trouble because SPD won't accept that the common field is valid!

    Cheers


    Mark
    Wednesday, December 01, 2010 11:26 PM
  • Hi Mark,

    I think it is not possible in DVWP, because in this way, DVWP can’t use the sort and group head here. If the group head could work, just put the parent information in the head, then I think it would the format you want.

    Have you ever use Reporting services integrate with SharePoint? Reporting Services could achieve this format easily. But you need to do some preparation. Here is a reference about it. http://blogs.msdn.com/b/sharepoint/archive/2007/02/19/microsoft-sql-server-2005-sp2-reporting-services-integration-with-wss-3-0-and-moss-2007.aspx


    Best regards. Emir
    • Marked as answer by emjem Friday, December 03, 2010 12:18 AM
    Thursday, December 02, 2010 7:24 AM
  • Hi Emir,

    OK - so we're not going to mention the SPD problem....

    Thanks for the info on Reporting Services integration. I have just printed the white paper and I'm going to delve in to it as a solution.

    Regards,


    Mark
    Friday, December 03, 2010 12:19 AM
  • old thread but I hate loose ends.

    I ended up solving this by creating SQL Views, then using the SQL View(s) in the SPD Data Source.

    Cheers all!


    Mark

    Saturday, February 18, 2012 3:10 AM