ADP: Error calling SP with "EXEC sp_helprotect" statement

P

PtrNrs

I am trying to create an SP which
(1) populates a temporary table from sp_helprotect ("INSERT . . .
EXEC sp_helprotect");
(2) cursors through the temp table to update a user table;
(3) displays the newly updated user table in Access ADP.

The routine works perfectly from Query Analyser, but fails with the
message "stored procedure executed correctly but did not return
records" when called from Access ADP.

The following code shows the problem distilled down to the absolute
basics. Called from Access ADP with @Switch = 0, - it works, otherwise
it fails.

So . . . there is some fundamental difference between sp_databases &
sp_helprotect. What is it.

I know I could use a workaround (reading sysprotects & sysobjects
directly) but I'd like to solve this problem as well.

--Test Code . . .
CREATE PROCEDURE dbo.Test
@Switch INT = 0
AS
BEGIN
SET NOCOUNT ON
SET DATEFORMAT dmy

IF(@Switch = 0) BEGIN
EXEC sp_databases
END ELSE BEGIN
EXEC sp_helprotect
END --IF
END

I will post some more detailed test code in another message - I thought
it might obscure the key issue.
 
P

PtrNrs

As promised here is the more detailed test code. This behaves
similarly to the simple test code already posted . . .

CREATE PROCEDURE dbo.Test1
@Switch INT = 0
AS
BEGIN
SET NOCOUNT ON
SET DATEFORMAT dmy

IF(@Switch = 0) BEGIN
--For simplicity assume temp table doesn't exist:
CREATE TABLE tblTemp0
(
DATABASE_NAME sysname,
DATABASE_SIZE int,
REMARKS varchar(254)
)

--Insert data from sp_databases into a temp table:
INSERT INTO dbo.tblTemp0
(
DATABASE_NAME,
DATABASE_SIZE,
REMARKS
)
EXEC sp_databases

--From ADP, this works OK:
SELECT * FROM dbo.tblTemp0

DROP TABLE dbo.tblTemp0
END ELSE BEGIN
CREATE TABLE tblTemp1
(
hpOwner VARCHAR(100),
hpObject SysName,
hpGrantee VARCHAR(100),
hpGrantor VARCHAR(100),
hpProtectType VARCHAR(100),
hpAction VARCHAR(100),
hpColumn VARCHAR (100)
)

--Insert data from sp_helprotect into a temp table:
INSERT INTO tblTemp1
(
hpOwner,
hpObject,
hpGrantee,
hpGrantor,
hpProtectType,
hpAction,
hpColumn
)
EXEC sp_helprotect

--From Access ADP, this fails with the message...
--"stored procedure executed correctly but did not return
records";
SELECT * FROM dbo.tblTemp1

DROP TABLE dbo.tblTemp1
END --IF
END
 
S

Sylvain Lafontaine

The most likely explanation for your error is because you are mixing
tblTemp1 and dbo.tblTemp1: you create the table tblTemp1 and insert into it
but after that you select from the other table dbo.tblTemp1. This second
table is empty, hence your error.

Instead of creating permanent table, you should create a temporary table
like #tblTemp1 or use a local variable table @tblTemp1. Since the table is
small, the second choice is probably better in your case.
 
P

PtrNrs

Thanks for the responses. I think you might be missing the point for
several reasons:-

- Your suggestion doesn't explain the behaviour show by the first code
fragment I posted.
- Both the posted examples work perfectly in Query Analyser but not
from ADP;
- I tried using the dbo prefix consistently - same result as before;
- I tried using temporary tables (I'm not acquainted with the local
variable @??? approach) - same result as before;

BTW - Am I not posting to the best newsgroup? I though there'd be
more response . . .
 
S

Sylvain Lafontaine

You're right, your question has more to do with programming in T-SQL than
with ADP, so m.p.sqlserver.programming is probably a better suited newsgroup
for this.

However, before posting again in this other newsgroup, I suggest that you
make the following two verifications first:

1- In QA, did you try with the same login account than with ADP?

2- In ADP, did you try with an account who has ownership of the
database?
 
P

PtrNrs

Thanks for sticking with me, Sylvain!
You're right, your question has more to do with programming in T-SQL than
with ADP, so m.p.sqlserver.programming is probably a better suited newsgroup
for this.
Click to expand...

Well not really, because the problem only appears in ADP, so T-SQL
users don't see the problem at all.
However, before posting again in this other newsgroup, I suggest that you
make the following two verifications first:
Click to expand...
1- In QA, did you try with the same login account than with ADP?
Click to expand...

Yes, both with the "sa" user.
2- In ADP, did you try with an account who has ownership of the
database?
Click to expand...

I'm embarrassed to admit that I don't really know who the database
owner is (how do I find that out?). I guess it should be the Admin
user (I created it under that user name), but I can't create an and in
account on the client PC. Nevertheless, isn't "sa" a super user
anyway?

My gut feeling is this has nothing to do with the problem - the core
issue is what is the difference between sp_databases and sp_helprotect
when you're using ADP.

Heres an even simpler example - create the following SP in ADP and swap
the "--" from "EXEC sp_databases" to "EXEC sp_helprotect" and see what
happens.

CREATE PROCEDURE z01
AS
EXEC sp_databases
--EXEC sp_helprotect

Good luck!
 
S

Sylvain Lafontaine

By owner of the database, I simply mean an account such as sa that will
simply result with "dbo." as the owner of the object; to make sure that
there is no problem at this level. Also, I don't see

I've tried your first piece of code yesterday both with ADP and QA and both
with sp_databases and sp_helprotect and I didn't see any problem with it;
all versions were working perfectly well on my system when using Integrated
Security and dbo.

What's the connection string and the piece of VBA code that you are using to
make your calls to the database from ADP?
 
P

PtrNrs

By owner of the database, I simply mean an account such as sa that will
simply result with "dbo." as the owner of the object; to make sure that
there is no problem at this level. Also, I don't see
Click to expand...

Well, I'm sorry I don't understand what you're getting at - as I said
before I don't know how to determine the owner of the database and the
same applies to objects in the database. However, the user is "sa" in
both cases - I hope that satisfies your requirements.
I've tried your first piece of code yesterday both with ADP and QA and both
with sp_databases and sp_helprotect and I didn't see any problem with it;
all versions were working perfectly well on my system when using Integrated
Security and dbo.
Click to expand...

Now that IS interesting! Does that mean there's something different
between the behaviour of your system and mine? Just let me check that
I've got that right - we're both logging in to an Access ADP file as
"sa" (or similar) and your's behaves and mine doesn't . . . If you
can't reproduce the problem, this might be as far as we're going to get
on this one!
What's the connection string and the piece of VBA code that you are using to
make your calls to the database from ADP?
Click to expand...

I'm just creating an SP and running it. There is no VBA.
 
R

Robert Morley

For me as well, your z01 SP worked out okay, although I did have to refresh
the Query list in the ADP before it worked. I wonder if that might be the
problem? Access has the columns cached from whatever the last iteration you
ran was, and then gets confused when the column names differ? I dunno.



Rob
 
S

Sylvain Lafontaine

By VBA code, I would simply like to know how you are creating the SP and
running it.

If you run the SP from the Immediate (or Debug) window, do you see any error
message. Exemple of running it in the Debug window:

CurrentProject.Connection.Execute("dbo.test1 0")
 
P

PtrNrs

Thanks for your help, Robert. The problem persists at my end. I'm
slowly recognising that a work-around will be the only way to go.
 
P

PtrNrs

I'm using an ADP file (this is an ADP newsgroup at all :) ), so
tables, views & SPs are directly linked to the SQL Server database.
I'm executing the SPs directly from the Views tab of the database form
- no VBA at all.

As I mentioned in my response to Robert, I'm realising that I should
just work around the problem. Unless you see a glimmer of light, I
figure we should just let it go.

Thanks for your persistence!
 
M

Malcolm Cook

It works for me when called from ADP's 'Queries' window with both 0 and 1 as input , showing me grants or database in a datasheet
view accordingly.

I recommend turning on trace and see what ADP is sending to SQL Server in your case. Can't image why it should be different.

Wild guess: maybe your definition of sp_helprotect is doing a 'set nocount off'.

What version of SQL Server / Access?
 
P

PtrNrs

Malcolm, Thanks for the response.
I recommend turning on trace and see what ADP is sending to SQL Server in your case. Can't image why it should be >different.
Click to expand...
The trace is (of course) v compilicated - I can't see anything wrong
myself. Should you care to pursue this, please let me know and I'll
send you the trace.
Wild guess: maybe your definition of sp_helprotect is doing a 'set nocount off'.
Click to expand...
No, it include "set nocount on" statement.
What version of SQL Server / Access?
Click to expand...
SQL Server 2000 8.00.2039 & Access 2003 SP2
 
P

PtrNrs

Thanks, Malcolm.

--This failed (EXEC sp_helprotect):
SELECT N'Testing Connection...'
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
exec sp_provider_types_rowset NULL, NULL
exec [ASPPro]..sp_procedure_params_rowset N'z01', 1, N'dbo', NULL
SET NO_BROWSETABLE ON
declare @P1 int
set @P1=1
exec sp_prepare @P1 output, NULL, N' EXEC dbo.z01 ', 1
select @P1
SET FMTONLY ON exec sp_execute 1 SET FMTONLY OFF
exec sp_unprepare 1
SET NO_BROWSETABLE OFF
exec [ASPPro]..sp_procedure_params_rowset N'z01', 1, N'dbo', NULL
SET ROWCOUNT 10000 SET NO_BROWSETABLE ON
EXEC "z01"

--This succeeded (EXEC sp_databases):
SELECT N'Testing Connection...'
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
exec [ASPPro]..sp_procedure_params_rowset N'z01', 1, N'dbo', NULL
SET NO_BROWSETABLE ON
declare @P1 int
set @P1=2
exec sp_prepare @P1 output, NULL, N' EXEC dbo.z01 ', 1
select @P1
SET FMTONLY ON exec sp_execute 2 SET FMTONLY OFF
set fmtonly off
exec sp_unprepare 2
SET NO_BROWSETABLE OFF
exec [ASPPro]..sp_procedure_params_rowset N'z01', 1, N'dbo', NULL
SET ROWCOUNT 10000
EXEC "z01"
SET ROWCOUNT 0
SELECT *, sql_variant_property(value, 'basetype') AS type FROM
::fn_listextendedproperty(NULL,N'user',N'dbo',N'procedure',N'z01',NULL,NULL)

SELECT *, sql_variant_property(value, 'basetype') AS type FROM
::fn_listextendedproperty(NULL,N'user',N'dbo',N'procedure',N'z01',N'column',NULL)
ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC
select object_name(sofk.id), user_name(sofk.uid) from sysreferences
srfk, sysobjects sofk where srfk.constid = sofk.id
select object_name(sotblfk.id), user_name(sotblfk.uid),
object_name(sotblrk.id), user_name(sotblrk.uid) from sysreferences
srfk, sysobjects sofk, sysobjects sotblfk,
sysobjects sotblrk where srfk.constid = sofk.id and srfk.fkeyid =
sotblfk.id and srfk.rkeyid = sotblrk.id and user_name(sofk.uid) =
N'dbo' and object_name(sofk.id) =
N'Rel_Employee_EmpMemo'
--Several more similar statments followed...
 
P

PtrNrs

Stop Press: I tried z01 on another system (SQL Server 8.00.2039 &
Access 2003 SP1) and it worked! It will be interesting to see what
happens when they upgrade to SP2. Trace follows . . .

--This failed (EXEC sp_helprotect):
exec [ASPPro]..sp_procedure_params_rowset N'z01', 1, N'dbo', NULL
SET NO_BROWSETABLE ON
declare @P1 int
set @P1=2
exec sp_prepare @P1 output, NULL, N' EXEC dbo.z01 ', 1
select @P1
SET FMTONLY ON exec sp_execute 2 SET FMTONLY OFF
set fmtonly off
exec sp_unprepare 2
SET NO_BROWSETABLE OFF
exec [ASPPro]..sp_procedure_params_rowset N'z01', 1, N'dbo', NULL
SET ROWCOUNT 10000
EXEC "z01"
SET ROWCOUNT 0
SELECT *, sql_variant_property(value, 'basetype') AS type FROM
::fn_listextendedproperty(NULL,N'user',N'dbo',N'procedure',N'z01',NULL,NULL)

SELECT *, sql_variant_property(value, 'basetype') AS type FROM
::fn_listextendedproperty(NULL,N'user',N'dbo',N'procedure',N'z01',N'column',NULL)
ORDER BY
PATINDEX(N'MS_DisplayControl', name) DESC

--This succeeded (EXEC sp_databases):
exec [ASPPro]..sp_procedure_params_rowset N'z01', 1, N'dbo', NULL
SET NO_BROWSETABLE ON
declare @P1 int
set @P1=1
exec sp_prepare @P1 output, NULL, N' EXEC dbo.z01 ', 1
select @P1
SET FMTONLY ON exec sp_execute 1 SET FMTONLY OFF
exec sp_unprepare 1
SET NO_BROWSETABLE OFF
exec [ASPPro]..sp_procedure_params_rowset N'z01', 1, N'dbo', NULL
SET ROWCOUNT 10000 SET NO_BROWSETABLE ON
EXEC "z01"
SET ROWCOUNT 0
SELECT *, sql_variant_property(value, 'basetype') AS type FROM
::fn_listextendedproperty(NULL,N'user',N'dbo',N'procedure',N'z01',NULL,NULL)

SELECT *, sql_variant_property(value, 'basetype') AS type FROM
::fn_listextendedproperty(NULL,N'user',N'dbo',N'procedure',N'z01',N'column',NULL)
ORDER BY
PATINDEX(N'MS_DisplayControl', name) DESC
select object_name(sofk.id), user_name(sofk.uid) from sysreferences
srfk, sysobjects sofk where srfk.constid = sofk.id
select object_name(sotblfk.id), user_name(sotblfk.uid),
object_name(sotblrk.id), user_name(sotblrk.uid) from sysreferences
srfk, sysobjects sofk, sysobjects sotblfk,
sysobjects sotblrk where srfk.constid = sofk.id and srfk.fkeyid =
sotblfk.id and srfk.rkeyid = sotblrk.id and user_name(sofk.uid) =
N'dbo' and object_name(sofk.id) =
N'Rel_Employee_EmpMemo'
--etc . . .
 
M

Malcolm Cook

hmmm,

"Stop Press" sounds like you don't need any further help? Ok, then, I'm off it. (I was confused as the why your trace continues to
say 'This Failed' (perhaps this is not a new trace of the working instance?), but, no matter....)

Best - Malcolm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Split Forms and ADP issues 2
SET NOCOUNT ON, but SP does not return records 1
Stored Procedure Error Question 3
ADP code hangs while running the same query in query analyser works 3
SP: NOCOUNT ON, but still no records 3
Problem using a SP with temp tables as a record source for a Report in Access 2000 5
Stored Proc Hell 4
SQL Server SP equivalent 5

Top