Data shaping problem using stored procedures!

Data shaping problem using stored procedures!

Post by Leif Landé » Fri, 09 Feb 2001 22:11:03



When trying to retrive a shaped recordset i get the following problem:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near 'sp_mub_menuitem'.

On the following command:
SHAPE {sp_mub_menubuttons '261293C4-A4D7-4CE5-89F8-BFC5171F99C6' ,'10.47.17.152'}
APPEND({sp_mub_menuitem '261293C4-A4D7-4CE5-89F8-BFC5171F99C6' ,'10.47.17.152'}
As menuitem RELATE menubuttonID TO menubuttonID)

If i rewrite the code to:
SHAPE {sp_mub_menubuttons '4809836B-1DA8-47AB-9C4F-0456EC134F01' ,'10.47.17.152'}
APPEND({Select menubuttonID, menutext, querystring From mub_menuitem} As menuitem
RELATE menubuttonID TO menubuttonID)

it works.

The problem is that i must use the sp in the second select to.

Below follows the beginning and end of sp_mu_menuitem And sp_mub_menubuttons

Alter Procedure sp_mub_menuitem
        (


        )
As
<SNIPP>

querystring
From #menuitems

Alter Procedure sp_mub_menubuttons
        (


        )
As
<SNIPP>
Select menubuttonID, buttonName, buttonText
From #menubuttons

<--Bellow follows some of the suggestions i have got and the results when
testing -->

Tried it, didn't work. Some changes in the profiler tho. With double braces it
didn't go to the sql-server at all, Error message was "Syntax error or access
violation" (logged in as sa so that's NOT the problem)

This is what the profiler says on the successful (using the select)
<---Profiler output--->
Event Class     Text    Start Time

querystring + ''&amp;sessionID=AD1210AF-2220-4319-A59E-C093376A825F'' querystring




+ ''&amp;sessionID=AD1210AF-2220-4319-A59E-C093376A825F'' querystring FROM
mub_menuitem', 1

Event Class     Text    Start Time
-SQL:BatchStarting      sp_mub_menubuttons 'AD1210AF-2220-4319-A59E-
C093376A825F' ,'10.47.17.152';Select menubuttonID, menutext, querystring
+ '&amp;sessionID=AD1210AF-2220-4319-A59E-C093376A825F' querystring FROM
mub_menuitem    15:45:26.263
  sp_mub_menubuttons 'AD1210AF-2220-4319-A59E-
C093376A825F' ,'10.47.17.152';Select menubuttonID, menutext, querystring
+ '&amp;sessionID=AD1210AF-2220-4319-A59E-C093376A825F' querystring FROM
mub_menuitem
<--End Profiler output --->

It looks to me like it's sending BOTH queryies on same row, could this be a
problem when using the sp on the second command? Is there something that could
fix that?

Thanks in advance!

-----Ursprungligt meddelande-----

Skickat: den 7 februari 2001 3:19 PM
Till: aspSQLserverGurus
?mne: [aspsqlservergurus] Re: aspsqlservergurus digest: February 06,
2001

Try adding a second set of braces around the call to the sp.  Like this:

SHAPE {{sp_mub_menubuttons '261293C4-A4D7-4CE5-89F8-BFC5171F99C6'
,'10.47.17.152'}} APPEND({{sp_mub_menuitem
'261293C4-A4D7-4CE5-89F8-BFC5171F99C6' ,'10.47.17.152'}} As menuitem RELATE
menubuttonID TO menubuttonID)

<--- new test --->
Hello dave, thanks for your try!

Tested both, and varietes of both. The best i can get is a change of errormsg to

<--SHAPE SNIPPET AND ERROR MSG-->
SHAPE {sp_mub_menubuttons 'AF5A486C-6481-40C7-9F81-ECB1891F7B32' ,'10.47.17.152'}
APPEND ({Exec sp_mub_menuitem 'AF5A486C-6481-40C7-9F81-
ECB1891F7B32' ,'10.47.17.152'} As menuitem RELATE menubuttonID TO menubuttonID)
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#menuitems'.

C:\WEBBAR\INTRAN?T\_SYSTEM\../_look/content.asp, line 30
<--END SNIPPET-->

I feel that that error is more odd, since #menuitems is created in
sp_mub_menuitem and that procedure runs perfect from query analyzer.

<--PROFILER OUTPUT-->
Event Class     Text    Start Time


 Connect                09:33:05.197
 ExistingConnection             09:33:05.197
+SQL:BatchStarting      SET NO_BROWSETABLE ON   09:33:05.207


+SQL:BatchStarting      SET FMTONLY ON Exec sp_mub_menuitem 'AF5A486C-6481-40C7-
9F81-ECB1891F7B32' ,'10.47.17.152' SET FMTONLY OFF      09:33:05.207
+SQL:BatchStarting      SET FMTONLY OFF 09:33:05.247
+SQL:BatchStarting      SET NO_BROWSETABLE OFF  09:33:05.247
+RPC:Starting   sp_unprepare 1  09:33:05.247
+SQL:BatchStarting      SELECT N'Testing Connection...' 09:33:08.050
+SQL:BatchStarting      EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
        09:33:08.050
<--PROFILER OUTPUT ENDS-->

-----Ursprungligt meddelande-----

Skickat: den 7 februari 2001 3:44 PM

?mne: RE: aspsqlservergurus digest: February 06, 2001

Perhaps:

SHAPE
  {SELECT * FROM sp_mub_menubuttons
  '261293C4-A4D7-4CE5-89F8-BFC5171F99C6' ,'10.47.17.152'}
APPEND(
  {SELECT * FROM sp_mub_menuitem
  '261293C4-A4D7-4CE5-89F8-BFC5171F99C6' ,'10.47.17.152'}
  AS menuitem
  RELATE
  menubuttonID TO menubuttonID)

or:

SHAPE
  {sp_mub_menubuttons
  '261293C4-A4D7-4CE5-89F8-BFC5171F99C6' ,'10.47.17.152'}
APPEND(
  {EXEC sp_mub_menuitem
  '261293C4-A4D7-4CE5-89F8-BFC5171F99C6' ,'10.47.17.152'}
  AS menuitem
  RELATE
  menubuttonID TO menubuttonID)

 
 
 

1. Problem using shape recordsets with stored procedure that uses a temp table

Hello,

i use a stored procedure to get data. This sp uses a temporary table to get
the correct results. There is no problem with this sp retrieving the data
directly into an ado recordset, but if i want to use this sp in a shape
command the ODBC driver raises an error that he could not find the temporary
table.
In normal the temporary table is created in the sp and will be deleted after
executing them.

Is there anywhere a documentation on using temporary tables with a
hierarchical recordset, or better knows anybody the solution for this
problem.

The used shape command:
SHAPE {{ CALL sp_GetProductsByCategory (10,3,12) }} AS Products
APPEND ({{ CALL sp_GetMediaFiles() }} AS Mediafiles RELATE 'ProductID' TO
'ProductID') AS Mediafiles

The raised error:
Microsoft OLE DB Provider for ODBC Drivers: -2147467259
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'#results'

Thanks a lot for reply
Ciao

2. Exception_access_violation (dmo)

3. HELP! Deletes taking FOREVER

4. Using SHAPE Command in Stored Procedure

5. Database design (recursive tables)

6. Check out Office Connect

7. Using Shape command with stored procedures

8. Using ADO SHAPE and Stored Procedure

9. ADO SHAPE using Stored Procedure

10. Stored procedures problem using DB2 Stored Procedure Builder

11. Data Shaping with SQL stored procedures?