Stored Procedure temp table not returning any results in an ADP

[Richard-D172]

In a SQL Server database, I have the following stored procedure:

Code:


ALTER PROC Test_10
AS

CREATE TABLE ##Temp3
(
tblUsersID int
)

INSERT ##Temp3
SELECT tblUsersID
FROM tblUsers

SELECT * FROM ##Temp3



When run from the Query Analyzer, the Stored Procedure provides a result
list. When I run the Stored Procedure from the ADP, it consistently tells me
that no records were returned. I have tried using '#Temp3' for naming the
temp table.

I know this example would not require the use of a temp table, but in
theory, this example should work properly from inside an ADP. Anyone have any
idea why it is not returning any results?

Apart from this, I cannot seem to edit a Stored Procedure from inside an
ADP. When I save any changed, it tells me that "the parameter is incorrect".

Thanks for your help!!

 

[KP]

Unfortunately, you will not be able to utilize a Temp table in ADP.
Your ADP uses several different connections to access data from SQL
Server. When the ADP tries to access the data in the temp table, the
connection is terminated and a new connection is utilized to access the
temp.db. When the initial connection is terminated the temp table is
terminated as well.

 

[Richard-D172]

Hi!

Thanks for your reply. Since the temp table is only used from within the
stored procedure (note how the SP ends with the SELECT * FROM ##temp3) it
should return the records just fine. Well, it took a lot of experimenting,
but I finally discovered how to make it work. Adding the statement "SET
NOCOUNT ON" to the SP did enable the SP to return the records, just like it
does when the SP is run from within the Query Analyzer.

What I haven't solved yet, is why I am not able to edit existing SP's from
Access itself. When I edit the SP, and save it, I consistently get the
message "the parameter is incorrect". The SP edits fine from the SQL Server
tools.

Is it perhaps not possible to edit SP's from Access itself?

 

[dbahooker]

Editing sprocs works fine. lol.

but just --don't abbreviate 'create proc myproc' you need to spell out
'procedure'--
but just --don't abbreviate 'create proc myproc' you need to spell out
'procedure'--
but just --don't abbreviate 'create proc myproc' you need to spell out
'procedure'--
but just --don't abbreviate 'create proc myproc' you need to spell out
'procedure'--


I repeat-- don't use 'create proc myproc' syntax; you need to spell out
the word PROCEDURE.


For the record; I worked at Microsoft for many many years; and I
_WENT_OFF_ on those jerks for not fixing this bug.

Technically I got fired TWICE for complaining (INTERNALLY) about this
bug; and a half dozen other bugs. Technically, now I am _BLACKLISTED_
from Microsoft-- just because it's easier for them to FIRE SOMEONE WHO
SPEAKS THE TRUTH instead of FIXING A BUG THAT IS A MAJOR PAIN IN THE
BUTT.

Technically: i think that I should qualify for certain 'whistle-blower'
protection.
Anyone know a good attorney to help me to sue the shit out of Microsoft
for firing me for complaining about bugs??

I would reccomend a class action lawsuit against Microsoft to take this
seriously.

it is a PAIN IN THE BUTT and it has been for 5 years.
And I want a refund on my copy of Office because of this very bug.

-Aaron
ADP Nationalist