Using SHAPE command with Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using SHAPE command with Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-27 : 09:18:45

Hardik writes "I am trying to use SHAPE command to retrieve hierarchical Data. Instead of using long SQL statements, I am generating those SQL statements in stored procedure and using those stored procedure within the SHAPE command. Here is an example:

SHAPE{{CALL dbo.sp_test (param1, param2, param3)}}
APPEND({{CALL dbo.sp_child (param1, param2, param3)}}
RELATE 'Column1' TO 'Column2') AS Column3

This setup gives me "Provider command for child rowset does not produce a rowset. " Error. But, when I run both of the stored procedure separately directly without using SHAPE, they work fine. Has anyone encountered similar problem? If so, what was the solution/work-around?"

uberbloke
Yak Posting Veteran

67 Posts

Posted - 2002-06-27 : 09:42:08

I have had the same problem and the answer is....

you can only use an SP for the first part of the shape command, the child recordset (the append stuff) has to be done using raw sql.

I am sure there is a very good reason for this, but I have forgotten it.

also make sure that your connection string to sql server is set up to allow shape....

application("dbSHAPE") = "PROVIDER=MSDataShape;DATA PROVIDER=SQLOLEDB;Data Source=[your stuff here];Initial Catalog==[your stuff here];UID==[your stuff here];PWD==[your stuff here];"

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-06-27 : 10:14:31

I've gotten it to work OK with stored procedures. This code works


Dim objConn
	Set objConn = Server.CreateObject("ADODB.Connection")
	objConn.Provider = "MSDataShape"
	objConn.Open "DSN=xxxx;UID=yyyy;Password=zzzz;"
	
	Dim objOrderRS, objOrder_NoteRS
	Set objOrderRS = Server.CreateObject ("ADODB.Recordset")
		
	Dim strShapeSQL
	strShapeSQL = "SHAPE {exec wl_peripheral @ptype_parm = " & ptype_parm & "} " &_
" APPEND({exec pull_order_notes_for_wls} as ORDNOTES " &_
" RELATE ord_id to ord_id) "

objOrderRS.Open strShapeSQL, objConn