|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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 [ at ]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
[ at ]Switch INT = 0
AS
BEGIN
SET NOCOUNT ON
SET DATEFORMAT dmy
IF([ at ]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.
|
|
As promised here is the more detailed test code. This behaves
similarly to the simple test code already posted . . .
CREATE PROCEDURE dbo.Test1
[ at ]Switch INT = 0
AS
BEGIN
SET NOCOUNT ON
SET DATEFORMAT dmy
IF([ at ]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
|
|
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 [ at ]tblTemp1. Since the table is
small, the second choice is probably better in your case.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
<PtrNrs[ at ]yahoo.com.au> wrote in message
news:1140753686.174573.171400[ at ]p10g2000cwp.googlegroups.com...
[Quoted Text] > As promised here is the more detailed test code. This behaves
> similarly to the simple test code already posted . . .
>
> CREATE PROCEDURE dbo.Test1
> [ at ]Switch INT = 0
> AS
> BEGIN
> SET NOCOUNT ON
> SET DATEFORMAT dmy
>
> IF([ at ]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
>
|
|
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 [ at ]??? approach) - same result as before;
BTW - Am I not posting to the best newsgroup? I though there'd be
more response . . .
|
|
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?
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
<PtrNrs[ at ]yahoo.com.au> wrote in message
news:1141004161.573493.4530[ at ]t39g2000cwt.googlegroups.com...
[Quoted Text] > 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 [ at ]??? approach) - same result as before;
>
> BTW - Am I not posting to the best newsgroup? I though there'd be
> more response . . .
>
|
|
Thanks for sticking with me, Sylvain!
[Quoted Text] >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!
|
|
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?
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
<PtrNrs[ at ]yahoo.com.au> wrote in message
news:1141032468.539807.310730[ at ]t39g2000cwt.googlegroups.com...
[Quoted Text] > 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!
>
|
|
|
[Quoted Text] >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.
|
|
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
<PtrNrs[ at ]yahoo.com.au> wrote in message
news:1141118415.359302.50660[ at ]p10g2000cwp.googlegroups.com...
[Quoted Text] > >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.
>
|
|
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")
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
<PtrNrs[ at ]yahoo.com.au> wrote in message
news:1141118415.359302.50660[ at ]p10g2000cwp.googlegroups.com...
[Quoted Text] > >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.
>
|
|
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.
|
|
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!
|
|
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?
--
Malcolm Cook - mec[ at ]stowers-institute.org
Stowers Institute for Medical Research - Kansas City, MO USA
|
|
Malcolm, Thanks for the response.
[Quoted Text] >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
|
|
sure, post it, I'll take a quick squiz, if you like...
--Malcolm
<PtrNrs[ at ]yahoo.com.au> wrote in message news:1141284781.992386.107790[ at ]u72g2000cwu.googlegroups.com...
[Quoted Text] > 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
>
|
|
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 [ at ]P1 int
set [ at ]P1=1
exec sp_prepare [ at ]P1 output, NULL, N' EXEC dbo.z01 ', 1
select [ at ]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 [ at ]P1 int
set [ at ]P1=2
exec sp_prepare [ at ]P1 output, NULL, N' EXEC dbo.z01 ', 1
select [ at ]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...
|
|
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 [ at ]P1 int
set [ at ]P1=2
exec sp_prepare [ at ]P1 output, NULL, N' EXEC dbo.z01 ', 1
select [ at ]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 [ at ]P1 int
set [ at ]P1=1
exec sp_prepare [ at ]P1 output, NULL, N' EXEC dbo.z01 ', 1
select [ at ]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 . . .
|
|
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
<PtrNrs[ at ]yahoo.com.au> wrote in message news:1141332453.695688.18600[ at ]p10g2000cwp.googlegroups.com...
[Quoted Text] > 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 [ at ]P1 int
> set [ at ]P1=2
> exec sp_prepare [ at ]P1 output, NULL, N' EXEC dbo.z01 ', 1
> select [ at ]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 [ at ]P1 int
> set [ at ]P1=1
> exec sp_prepare [ at ]P1 output, NULL, N' EXEC dbo.z01 ', 1
> select [ at ]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 . . .
>
|
|
hh
--
Karen Yarber
University of Phoenix Online
kyarber[ at ]email.phoenix.edu
<PtrNrs[ at ]yahoo.com.au> wrote in message
news:1141195298.141326.298930[ at ]u72g2000cwu.googlegroups.com...
[Quoted Text] > 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.
>
|
|
|