Wednesday, November 24, 2010 7:54 AM
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?)
Friday, November 26, 2010 11:11 AMModerator
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
Sunday, November 28, 2010 1:00 AM
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!
Monday, November 29, 2010 5:42 AMModerator
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
Tuesday, November 30, 2010 3:29 AM
Thanks. I will give it a go and let you know how it works out.
Wednesday, December 01, 2010 11:26 PM
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!
Thursday, December 02, 2010 7:24 AMModerator
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
Friday, December 03, 2010 12:19 AM
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.
Saturday, February 18, 2012 3:10 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.