Sorry that was a little vague Ekaterina - it was a last minute / late night post…
My basic problem is using a stored procedure in the schema as a source for a custom table. A simple worked example would be useful here as I can find virtually no documentation on how to achieve this - other than the screenshot linked in my first post. I found it very difficult to even get the schema modeler to allow me to select a stored procedure as the source for a table. I still don’t exactly know how I managed it as the drop down box listing stored procedures wasn’t available and then it was. Beyond that I still couldn’t progress much further as schema modeller didn’t appear to like using my stored procedure as the source, complaining about scalar variables being declared amongst other things. This despite the procedure working fine in SQL management studio etc… As you can see it is difficult to describe with any clarity as I have very little information from the documentation on how I should be doing it.
At that point I left it having previously tried to use the SQL directly as a source for a custom table in the schema. When doing that, DA was seeming managing to mangle the SQL so that when I looked in SQL profiler at the resulting call, there were extra square brackets inserted which fouled up the call.
As mentioned in the original post, I have now reverted to using a custom method on the server, with “AllowExecuteSQL” to enable the CTE SQL to execute correctly. On the client side I’m modifying the DataRequestCall on the remote data adapter. I do this for several other things and find it a good workaround to SQL issues.
I would still like to know how to correctly use a stored procedure as a source for a table though and if something like my original SQL should be capable of being the direct source for a custom table. I’ve listed my SQL below (which is effectively a bill of material implosion).I know is totally specific to me, but you will get an idea of the syntax I’m trying to use. Apologies for the layout, I need a tutorial on how to embed CR/LF when using preformatted text also (if that’s possible ?)
with UserCTE as ( select GIDComponent as GIDBase, NS.Model,NodeType,GIDParent,GIDComponent, DefComponent,NodeComponent, AH.TableName,AH.FieldName,Value, 0 as LevelNo from NodeStructure NS join AggregationNodeHead AH on NS.Model = AH.Model and NS.DefComponent = AH.GID and AH.GIDSum = 0 join AggregationNodeDetail AD on NS.Model = AD.Model and NS.DefComponent = AD.GIDHead and NS.NodeComponent = AD.GID and AD.GIDSum = 0 union all select Child.GIDBase, Parent.Model,Parent.NodeType,Parent.GIDParent,Parent.GIDComponent,Parent.DefComponent,Parent.NodeComponent, AH.TableName,AH.FieldName,AD.Value,Child.LevelNo +1 as LevelNo FROM UserCTE AS Child INNER JOIN NodeStructure AS Parent
on Child.Model= Parent.Model and Child.NodeType = Parent.NodeType and Child.GIDParent = Parent.GIDComponent
join AggregationNodeHead AH on Parent.Model = AH.Model and Parent.DefComponent = AH.GID and AH.GIDSum = 0
join AggregationNodeDetail AD on Parent.Model = AD.Model and Parent.DefComponent = AD.GIDHead and Parent.NodeComponent = AD.GID and AD.GIDSum = 0) SELECT * FROM UserCTE AS u where u.Model = 0 and u.NodeType = 0 and u.GIDBase = 36153 ORDER BY GIDBase, LevelNo desc
So to recap :
- Can you provide a simple screencast walkthrough showing of any stored procedure (with input parameters) being used as the source for a custom table.
- Should the above CTE query be compatible with supported DA syntax ?