ADP: Error calling SP with "EXEC sp_helprotect" statement

PtrNrs · Feb 24, 2006

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.

PtrNrs · Feb 24, 2006

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

Sylvain Lafontaine · Feb 26, 2006

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.

PtrNrs · Feb 27, 2006

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 . . .

Sylvain Lafontaine · Feb 27, 2006

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?

PtrNrs · Feb 27, 2006

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.

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:

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

Yes, both with the "sa" user.

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

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!

Sylvain Lafontaine · Feb 27, 2006

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?

PtrNrs · Feb 28, 2006

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

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.

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?

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

Robert Morley · Feb 28, 2006

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

Sylvain Lafontaine · Feb 28, 2006

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")

PtrNrs · Mar 1, 2006

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.

PtrNrs · Mar 1, 2006

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!

Malcolm Cook · Mar 1, 2006

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?

PtrNrs · Mar 2, 2006

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.

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'.

No, it include "set nocount on" statement.

What version of SQL Server / Access?

SQL Server 2000 8.00.2039 & Access 2003 SP2

Malcolm Cook · Mar 2, 2006

sure, post it, I'll take a quick squiz, if you like...

--Malcolm

PtrNrs · Mar 2, 2006

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...

PtrNrs · Mar 2, 2006

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 . . .

Malcolm Cook · Mar 2, 2006

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

Karen Yarber · Apr 29, 2006

hh

--

Karen Yarber
University of Phoenix Online
(e-mail address removed)

ADP: Error calling SP with "EXEC sp_helprotect" statement

PtrNrs

PtrNrs

Sylvain Lafontaine

PtrNrs

Sylvain Lafontaine

PtrNrs

Sylvain Lafontaine

PtrNrs

Robert Morley

Sylvain Lafontaine

PtrNrs

PtrNrs

Malcolm Cook

PtrNrs

Malcolm Cook

PtrNrs

PtrNrs

Malcolm Cook

Karen Yarber