March 25, 2003 at 9:06 am
For this script "Use DELETE On Many Tables With Foreign Keys"
Does anybody have more info on the parameters of sp_MSdependencies. This script will not work on SQL7 but dropping last param will allow it to work
This works: sp_MSdependencies NULL, 3, NULL, NULL
I'm just not sure what the params are and the impact of using the above in SQL7
any help appreciated.
TIA
Brian Lockwood
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
March 25, 2003 at 2:46 pm
there are only 3 params...
sp_MSobject_dependencies name = NULL, type = NULL, flags = 0x01fd
name: name or null (all objects of type)
type: type number (see below) or null
if both null, get all objects in database
flags is a bitmask of the following values:
0x10000 = return multiple parent/child rows per object
0x20000 = descending return order
0x40000 = return children instead of parents
0x80000 = Include input object in output result set
0x100000 = return only firstlevel (immediate) parents/children
0x200000 = return only DRI dependencies
power(2, object type number(s)) to return in results set:
0 (1 - 0x0001) - UDF
1 (2 - 0x0002) - system tables or MS-internal objects
2 (4 - 0x0004) - view
3 (8 - 0x0008) - user table
4 (16- 0x0010) - procedure
5 (32- 0x0020) - log
6 (64 - 0x0040) - default
7 (128- 0x0080) - rule
8 (256- 0x0100) - trigger
12 (1024- 0x0400) - uddt
shortcuts:
29 (0x011c) - trig, view, user table, procedure
448(0x00c1) - rule, default, datatype
4606 (0x11fd) - all but systables/objects
4607 (0x11ff) - all
March 26, 2003 at 11:03 am
thx for reply.
when I run sp_helptext on SQL7 I get the 4 params
create procedure sp_MSdependencies
@objname nvarchar(517) = null,
@objtype int = null,
@flags int = 0x01fd,
@objlist nvarchar(128) = null
as ...
When I run on SQL2K I get 5
create procedure sp_MSdependencies
@objname nvarchar(517) = null,
@objtype int = null,
@flags int = 0x01fd,
@objlist nvarchar(128) = null,
@intrans int = null ...
but my bigger problem now is that I can not execute the call using ADO on SQL7. It fires without error but returns and invalid recordset using this syntax in VB:
objrs.Open strSQL, g_objCn,adOpenDynamic,adLockReadOnly
Does anyone have VB/ADO syntax that works to execute this against SQL 7 (not SQL 2K which works fine)
sp_MSdependencies NULL, 3, NULL, NULL
Brian Lockwood
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
March 27, 2003 at 11:51 am
here is some additional info from one of my developers
===========================
I have tried "sp_MSdependencies NULL, 3, NULL, NULL"
on both sql7.0 and sql2000. It has worked normal.
But if you have a problem with the 4 param you can
omit it because it has the default value "NULL".
"sp_MSdependencies NULL, 3, NULL" works.
A problem may be with the cursor "adOpenDynamic" because
dyn cursor need the primary or unique key. But the temporary
table, which builds output recordset inside this sp, doesn't
contain neither primary key nor unique key. I propose to change
"adOpenDynamic" to "adStatic".
===========================
but it still didn't solve the problem ...
Brian Lockwood
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply