caml query returns empty columns 1

[spinnerM]

Caml query of sharePoint list (view of a list) returns values in only 5 of the 85 columns.
The parameter GUIDs are known to be correct.
The data is known to be present in the list.
The column names in the query are known to be correct
The few data columns that are returned contain correct data.
The question is why are the remaining data columns not populated?

Here is the query:

<Query>
<Method Namespace="

http://schemas.microsoft.com/sharepoint/soap/"

Name="GetListItems"/>
<ElementPath IgnoreNamespaces="true">GetListItemsResponse/GetListItemsResult/listitems/data/row{

@ows_Will_x0020_Travel_x0020_Percent
,@ows_Will_x0020_Relocate
,@ows_UniqueId
,@ows_Title0
,@ows_Title
,@ows_TE_x0020_Storage
,@ows_TE_x0020_Mainframe
,@ows_TE_x0020_Hosting
,@ows_TE_x0020_Help_x0020_Desk
,@ows_TE_x0020_End_x0020_User_x0020_Services
,@ows_TE_x0020_Applications
,@ows_Succsn_x0020_Progrsn_x0020_Reqmts_x0020_05
,@ows_Succsn_x0020_Progrsn_x0020_Reqmts_x0020_04
,@ows_Succsn_x0020_Progrsn_x0020_Reqmts_x0020_03
,@ows_Succsn_x0020_Progrsn_x0020_Reqmts_x0020_02
,@ows_Succsn_x0020_Progrsn_x0020_Reqmts_x0020_01
,@ows_Strategi
,@ows_StaleDat0
,@ows_Pre_x0020_Mgr_x0020_Tele_x0020_1
,@ows_Pre_x0020_Mgr_x0020_Name_x0020_1
,@ows_Pre_x0020_Mgr_x0020_Email_x0020_1
,@ows_Position_x0020_Code0
,@ows_Phone
,@ows_Other_x0020_Comments
,@ows_Mgr_x0020_Phone
,@ows_Mgr_x0020_Name
,@ows_Mgr_x0020_Email
,@ows_Location_x0020_Region
,@ows_Location_x0020_Major_x0020_Metro
,@ows_Location_x0020_Country_x0020_State
,@ows_LinkFilename
,@ows_Last_x0020_Modified
,@ows_Industry0
,@ows_Industry
,@ows_IE_x0020_Tansportation
,@ows_IE_x0020_Strategic
,@ows_IE_x0020_Retail_x0020_Consumers
,@ows_IE_x0020_Public_x0020_Sector
,@ows_IE_x0020_Mfg_x0020_Distribution
,@ows_IE_x0020_Health_x0020_Life_x0020_Science
,@ows_IE_x0020_Fin_x0020_Services
,@ows_IE_x0020_Energy
,@ows_IE_x0020_Comm_x0020_Media_x0020_Entertain
,@ows_ID
,@ows_Hpemployee
,@ows_Fte_x0020_Use_x0020_Exper_x0020_05
,@ows_Fte_x0020_Use_x0020_Exper_x0020_04
,@ows_Fte_x0020_Use_x0020_Exper_x0020_03
,@ows_Fte_x0020_Use_x0020_Exper_x0020_02
,@ows_Fte_x0020_Use_x0020_Exper_x0020_01
,@ows_Emp_x0020_Number
,@ows_Email
,@ows_CMPotential
,@ows_CM_x0020_Multi_x0020_Cultural
,@ows_CM_x0020_Managing_x0020_Complex_x0020_Portfolios
,@ows_CM_x0020_Exec_x0020_Bus_x0020_Partnering
,@ows_CM_x0020_Deal_x0020_Comp_x0020_Assessment
,@ows_CM_x0020_Coaching
,@ows_CM_x0020_Budget_x0020_Mgmt
,@ows_CM_x0020_Acct_x0020_Coordination
,@ows_Candidate_x0020_Weaknesses
,@ows_Candidate_x0020_Strengths
,@ows_Candidate_x0020_Name
,@ows_Available_x0020_Date_x0020_05
,@ows_Available_x0020_Date_x0020_04
,@ows_Available_x0020_Date_x0020_03
,@ows_Available_x0020_Date_x0020_02
,@ows_Available_x0020_Date_x0020_01
,@ows_Available_x0020_05
,@ows_Available_x0020_04
,@ows_Available_x0020_03
,@ows_Available_x0020_02
,@ows_Available_x0020_01
,@ows_ADMPotential
,@ows_ADM_x0020_Multi_x0020_Cultural
,@ows_ADM_x0020_ITIL
,@ows_ADM_x0020_Compliance_x0020_Mgmt
,@ows_ADM_x0020_Coaching
,@ows_ADM_x0020_Budget_x0020_Mgmt
,@ows_ADM_x0020_Acct_x0020_Relationship_x0020_Mgmt
,@ows_Acct_x0020_Ldrshp_x0020_Tier_x0020_05
,@ows_Acct_x0020_Ldrshp_x0020_Tier_x0020_04
,@ows_Acct_x0020_Ldrshp_x0020_Tier_x0020_03
,@ows_Acct_x0020_Ldrshp_x0020_Tier_x0020_02
,@ows_Acct_x0020_Ldrshp_x0020_Tier_x0020_01

}</ElementPath>
<SoapAction>

http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>

</Query>

Thank you,
Douglas

 

[xlbo]

What does this have to do with SQL Server Reporting Services?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[spinnerM]

Geoff,

> What does this have to do with SQL Server Reporting Services?

Everything.

SharePoint stores its data in a SQL database. Reporting Services can report on this data using CAML (Collaborative Application Markup Language) an XML-based query language.

A query such as is shown in my post is entered into the dataset query window under the data tab. Two parameters in the form of a GUID are required for locating the correct tables in the SQL database.

A report can then be designed in the layout window using the results.

Normally it works beautifully. However I am encountering some diffuculty. Thus the post.

Douglas

 

[xlbo]

Is the data missing in the query or in the report?

If it is in the query then this doesn't really have anything to do with RS

If it is in the report then my apologies and can you give any details about how the table / list has been set up to be associated with the query?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[spinnerM]

Geoff,

The data is missing in the query when run from the data tab within the RS dev environment.

So you must be right that this is not an RS issue.

I had been thinking that RS itself might have issues or limitaions with these kinds of queries under certain kinds of situations, but then RS is simply sending the query and parameters to SQL where the SharePoint content database resides.

Thanks!

Douglas

 

[bburnell]

Would it be possible to write a stored procedure and run that for the report? That's what I use instead of queries within the report. Just a suggestion...
Brett

--------------------------
Web/.net Programmer & DBA
Central PA