How do I deal with 2 recordsets returned

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How do I deal with 2 recordsets returned

Author

Topic

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-02-22 : 13:20:12

I only want to identify things that depend on the searched object.

When I execute sp_depends and their are dependencies
the return result expects different structures.

How do I deal with this?

--has dependencies so proper structure is returned.
Create Table #sp_depends_table (name nvarchar(500), type nvarchar(16))

insert #sp_depends_table exec [SP_DEPENDS] 'FA_answers'
select * from #sp_depends_table
Drop Table #sp_depends_table

--nothing depends so it returns what depends on it.
Create Table #sp_depends_table2 (name nvarchar(3000), type nvarchar(16), updated nvarchar(3), selected nvarchar(8), [column] nvarchar(500))

insert #sp_depends_table2 exec [SP_DEPENDS] 'EMBA_Spreadsheet'
select * from #sp_depends_table2
Drop Table #sp_depends_table2

You can do anything at www.zombo.com

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-22 : 17:47:02

If you are willing to dig into the system tables You could report from the sysdepends table directly.
As a first step You could check out the code in sp_depends proc. ( find it in master database, edit in QA )

rockmoose

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-02-22 : 17:51:59

I am in the process of doing that now...

You can do anything at www.zombo.com

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-02-22 : 17:56:19

Having trouble getting the object_id from the remote server for the where clause

--this works
select object_id('Admissions.dbo.Postal_Codes')
-- remote returns null
select object_id('remoteserver.Admissions.dbo.Postal_Codes')

declare @objid int /* the id of the object we want */
Select @objid = object_id('Admissions.dbo.Postal_Codes')
select distinct
'name' = (s.name + '.' + o.name),
type = substring(v.name, 5, 16)
from
master.dbo.spt_values v,
remoteserver.Admissions.dbo.sysobjects o,
remoteserver.Admissions.dbo.sysdepends d,
remoteserver.Admissions.dbo.sysusers s
where
o.id = d.id
and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
and d.depid = @objid
and o.uid = s.uid
and deptype < 2

SELECT DISTINCT s.name + '.' + o.name AS name, SUBSTRING(v.name, 5, 16) AS type
FROM remoteserver.Admissions.dbo.sysdepends d INNER JOIN
remoteserver.Admissions.dbo.sysobjects o ON d.id = o.id INNER JOIN
master.dbo.spt_values v ON o.xtype = SUBSTRING(v.name, 1, 2) COLLATE database_default INNER JOIN
remoteserver.Admissions.dbo.sysusers s ON o.uid = s.uid
WHERE (v.type = 'O9T') AND (d.deptype < 2)

You can do anything at www.zombo.com

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-02-22 : 18:02:36

Got it
select * from remoteserver.Admissions.dbo.sysobjects o where o.name = 'Postal_Codes'

You can do anything at www.zombo.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources