Data Shaping In SQL Server 2000

nidgep · June 24th, 2003, 05:39 AM

Hi

I have been looking for resources into how to write a stored procedure to work with the SHAPE command.

Ideally I want to query four related tables and build parent child recordsets of data.

I have tried to write a small procedure using the shape command but on get an error message saying:-
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
This happens whenever I try to parse the code before I even try to run it. Even the SQL Server Books Online PUBS database example gives the same error.
Whats wrong?

Jeff Mason · June 24th, 2003, 06:26 AM

Are you trying to use SHAPE commands from within a SQL Server stored procedure? As I understand it, the SHAPE language is a service provided by the OLEDB ADO client data provider and is not something that SQL Server 'understands'. That is, it is the OLEDB provider which processes the SHAPE commands, not SQL Server, so those commands cannot be part of a stored procedure as they are not legal SQL.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

nidgep · June 24th, 2003, 06:56 AM

Hi Jeff

Yes I was hoping to run a stored procedure containing the SHAPE command from within SQL server.

I had begun to wonder if this was possible, as all references I have seen have created ADODB.Connections/Recordsets and called the database using T-SQL from within an application or Server-side web page.

Would it be possible for you to recommend an alternative as I really didn't want to run SQL queries directly against the datasource tables.
I am trying to use the SHAPE command to return a hierarchical recordset of table information that will represent a folder structure within a web based CMS system.
As a result of this I didn't want to allow any access other than through a provided channel.

I do already have a stored procedure that returns the required information, but is only in a flat format and would require further coding to represent the same results as the SHAPE command could do in one go.

My initial thoughts now turn to:-

a) Get the stored procedure to return XML and bind it to the client somehow.

b) and/or use the Microsoft TreeView within the browser.
Although I have no experience of using such objects within a browser.

Basically I need a method to extract the data and present it with a tree-view-like appearance which can be updateable and resynch with the database!
I am using ASP/SQL2000 to develop this.

I would appreciate any help/tips on this.

U.N.C.L.E. · July 4th, 2003, 08:34 AM

You might want to first query for the parent data in one recordset, then query for the child data associated with the parent data in another recordset. Hope this response is not too late...

e.g.

<%
'...
' Get all the top level data
rs1.open "SELECT * FROM myTable WHERE (info='something');", db, 1, 3, 1
while not rs1.eof
  ' dynamically create TreeView nodes here
  rs2.open "SELECT * FROM myTable WHERE (info='" & rs1.fields("associatedfield") & "');", db, 1, 3, 1
  while not rs2.eof
    ' dynamically create TreeView child nodes of parent node above
    rs2.movenext
  wend
  rs1.movenext
wend
%>

Treeview sample:
<html>

<head>
<title>New Page 1</title>

<script language="VBScript">
</script>

</head>

<body>

<object classid="clsid:C74190B6-8589-11D1-B16A-00C0F0283628" id="TreeMenu" width="170" height="350">
<PARAM NAME="CheckBoxes" VALUE="0">
<PARAM NAME="LineStyle" VALUE="1">
<PARAM NAME="Style" VALUE="7">
  <param name="_ExtentX" value="4498">
  <param name="_ExtentY" value="9260">
  <param name="_Version" value="393217">
  <param name="HideSelection" value="1">
  <param name="Indentation" value="425">
  <param name="LabelEdit" value="0">
  <param name="PathSeparator" value="\">
  <param name="Sorted" value="0">
  <param name="FullRowSelect" value="0">
  <param name="HotTracking" value="0">
  <param name="Scroll" value="1">
  <param name="SingleSel" value="0">
  <param name="ImageList" value>
  <param name="BorderStyle" value="0">
  <param name="Appearance" value="0">
  <param name="MousePointer" value="0">
  <param name="Enabled" value="1">
  <param name="OLEDragMode" value="0">
  <param name="OLEDropMode" value="0">
</object>

</body>

</html>

nidgep · July 4th, 2003, 08:46 AM

Hi U.N.C.L.E

Thanks for the help/tip. It's very much appreciated.
I will try and use the example given and make it fit the requirements.

Thanks again

Ricky Vega · August 29th, 2003, 03:30 PM

A way around this particular limitation is to SQL Server 2000's User Defined functions (UDF). As long as your stored procedures do not have any temp tables (table variables are fine), then you can wrap it up into a UDF and then use the ADO SHAPE command on them.