sp_depends 
Author Message
 sp_depends

Why don't I get results from sp_depends tf_agents and
sp_depends tw_agentsMake from the following stored
procedure?  And what do I need to do for sp_depends to
return results?

if exists (select * from sysobjects where id = object_id
(N'[dbo].[tw_AgentsMake]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[tw_AgentsMake]
GO

/*
**
**  TFC Get Agents from Accounting System
**      &cln 4/7/03
*/

CREATE PROCEDURE tw_AgentsMake
AS
BEGIN


TRUNCATE TABLE tf_Agents

CREATE TABLE #NUName (
        vendor_code nvarchar(12),
        FullName nvarchar(40),
        FName nvarchar(40),
        LName nvarchar(40),
        MName nvarchar(20),
        NName nvarchar(40),
        OldTIN nvarchar(20),
        NewTIN nvarchar(20))

INSERT #NUName  (vendor_code,FullName, FName, LName,
        MName, NName, OldTIN,NewTIN)
SELECT tf_reagt.vendor_code, tf_reagt.vendor_name,
tf_reagt.vendor_name,'','','',TAX_ID_NUM, ''
FROM tf_reagt LEFT OUTER JOIN apvend
ON tf_reagt.vendor_code = apvend.vendor_code

CREATE UNIQUE CLUSTERED INDEX NUName_ind_0
        ON #NUName ( vendor_code )


WHILE (1=1)
BEGIN

 UPDATE #NUName





END

UPDATE #NUName SET LName =
        ISNULL(LTRIM(SUBSTRING(FullName,1,CHARINDEX
(',',FullName)-1)),''),
        FName = ISNULL(LTRIM(SUBSTRING(FullName,CHARINDEX
(',',FullName)+1,
        DATALENGTH(FullName) - CHARINDEX
(',',FullName))),'')
        WHERE CHARINDEX(',',FullName) > 0 AND PATINDEX('%
INC.%',FullName) = 0

UPDATE #NUName SET NName = ISNULL(LTRIM(SUBSTRING
(FName,CHARINDEX('(',FName)+1,
        CHARINDEX(')',FName) - CHARINDEX('(',FName)-
1)),''),
        FName = ISNULL(LTRIM(SUBSTRING(FName,1,CHARINDEX
('(',FName)-1)),'')
        WHERE CHARINDEX('(',FName) > 0 AND CHARINDEX
(')',FName) > 0 AND CHARINDEX(',',FullName) > 0
        AND PATINDEX('%INC.%',FullName) = 0

UPDATE #NUName SET MName = ISNULL(LTRIM(SUBSTRING
(FName,CHARINDEX(' ',FName)+1,DATALENGTH(FName)-CHARINDEX('
        ',FName))),''),
        FName = ISNULL(LTRIM(SUBSTRING(FName,1,CHARINDEX
(' ',FName)-1)),'')
        WHERE CHARINDEX(',',FullName) > 0 AND CHARINDEX
(' ',RTRIM(FName)) > 0  AND PATINDEX('%INC.%',FullName) = 0

INSERT [dbo].[TF_agents] (
        [login] ,
        [deptID],
        [passwd],
        [fName],
        [lName],
        [mName],
        [nickName],
        [date_hired],
        [phone],
        [fax],
        [email],
        [hireStatus],
        [employeeTY],
        [jobTitleID],
        [modifiedDate],
        [portalID]
)
SELECT SUBSTRING(tf_reagt.vendor_code,1,10),
        convert(int,tf_reagt.department), CASE
                WHEN NewTin = '' THEN SUBSTRING
(tf_reagt.vendor_code,1,20)
                ELSE RIGHT(ISNULL(NewTIN,''),5)
                END ,
        SUBSTRING(#nuName.fname,1,50),SUBSTRING
(#nuName.lname,1,50),SUBSTRING(#nuName.mname,1,50),
        SUBSTRING(#nuName.nname,1,50),
        CASE WHEN date_hire > 722815 AND date_hire < 766645
        THEN
          cast(cast(datepart(mm,dateadd(dd,(date_hire -
657072),'1/1/1800')) as varchar(2))
          + '/' + cast(datepart(dd,dateadd(dd,(date_hire -
657072),'1/1/1800')) as varchar(2))
          + '/' + cast(datepart(yy,dateadd(dd,(date_hire -
657072),'1/1/1800')) as varchar(4))
        as smalldatetime)
        ELSE CONVERT(smalldatetime,GetDate()) END,
ISNULL(SUBSTRING(contact_phone,1,20),''),
        '',SUBSTRING(ISNULL(email_id,''),1,20),
        tf_reagt.status_type, broker_flag, 0,CONVERT
(smalldatetime,Getdate()), 2
FROM tf_reagt INNER JOIN #NUName ON tf_reagt.vendor_code =
#NUName.vendor_code
LEFT OUTER JOIN tf_reemail ON tf_reagt.vendor_code =
tf_reemail.vendor_code
LEFT OUTER JOIN apvend ON tf_reagt.vendor_code =
apvend.vendor_code

DROP TABLE #NUName

RETURN 0
END
GO
GRANT  EXECUTE  ON dbo.tw_AgentsMake TO Public
GO



Fri, 06 Jan 2006 18:48:46 GMT
 sp_depends

My personnal experience is if temp tables are in the sql statement then it
does not show up most of the time in sysdepends.

I normally work with SQL 7.0; I hope 8.0/2000 will be better.

Tim S


Quote:
> Why don't I get results from sp_depends tf_agents and
> sp_depends tw_agentsMake from the following stored
> procedure?  And what do I need to do for sp_depends to
> return results?

> if exists (select * from sysobjects where id = object_id
> (N'[dbo].[tw_AgentsMake]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[tw_AgentsMake]
> GO

> /*
> **
> **  TFC Get Agents from Accounting System
> ** &cln 4/7/03
> */

> CREATE PROCEDURE tw_AgentsMake
> AS
> BEGIN


> TRUNCATE TABLE tf_Agents

> CREATE TABLE #NUName (
> vendor_code nvarchar(12),
> FullName nvarchar(40),
> FName nvarchar(40),
> LName nvarchar(40),
> MName nvarchar(20),
> NName nvarchar(40),
> OldTIN nvarchar(20),
> NewTIN nvarchar(20))

> INSERT #NUName (vendor_code,FullName, FName, LName,
> MName, NName, OldTIN,NewTIN)
> SELECT tf_reagt.vendor_code, tf_reagt.vendor_name,
> tf_reagt.vendor_name,'','','',TAX_ID_NUM, ''
> FROM tf_reagt LEFT OUTER JOIN apvend
> ON tf_reagt.vendor_code = apvend.vendor_code

> CREATE UNIQUE CLUSTERED INDEX NUName_ind_0
> ON #NUName ( vendor_code )


> WHILE (1=1)
> BEGIN

>  UPDATE #NUName





> END

> UPDATE #NUName SET LName =
> ISNULL(LTRIM(SUBSTRING(FullName,1,CHARINDEX
> (',',FullName)-1)),''),
> FName = ISNULL(LTRIM(SUBSTRING(FullName,CHARINDEX
> (',',FullName)+1,
> DATALENGTH(FullName) - CHARINDEX
> (',',FullName))),'')
> WHERE CHARINDEX(',',FullName) > 0 AND PATINDEX('%
> INC.%',FullName) = 0

> UPDATE #NUName SET NName = ISNULL(LTRIM(SUBSTRING
> (FName,CHARINDEX('(',FName)+1,
> CHARINDEX(')',FName) - CHARINDEX('(',FName)-
> 1)),''),
> FName = ISNULL(LTRIM(SUBSTRING(FName,1,CHARINDEX
> ('(',FName)-1)),'')
> WHERE CHARINDEX('(',FName) > 0 AND CHARINDEX
> (')',FName) > 0 AND CHARINDEX(',',FullName) > 0
> AND PATINDEX('%INC.%',FullName) = 0

> UPDATE #NUName SET MName = ISNULL(LTRIM(SUBSTRING
> (FName,CHARINDEX(' ',FName)+1,DATALENGTH(FName)-CHARINDEX('
> ',FName))),''),
> FName = ISNULL(LTRIM(SUBSTRING(FName,1,CHARINDEX
> (' ',FName)-1)),'')
> WHERE CHARINDEX(',',FullName) > 0 AND CHARINDEX
> (' ',RTRIM(FName)) > 0  AND PATINDEX('%INC.%',FullName) = 0

> INSERT [dbo].[TF_agents] (
> [login] ,
> [deptID],
> [passwd],
> [fName],
> [lName],
> [mName],
> [nickName],
> [date_hired],
> [phone],
> [fax],
> [email],
> [hireStatus],
> [employeeTY],
> [jobTitleID],
> [modifiedDate],
> [portalID]
> )
> SELECT SUBSTRING(tf_reagt.vendor_code,1,10),
> convert(int,tf_reagt.department), CASE
> WHEN NewTin = '' THEN SUBSTRING
> (tf_reagt.vendor_code,1,20)
> ELSE RIGHT(ISNULL(NewTIN,''),5)
> END ,
> SUBSTRING(#nuName.fname,1,50),SUBSTRING
> (#nuName.lname,1,50),SUBSTRING(#nuName.mname,1,50),
> SUBSTRING(#nuName.nname,1,50),
> CASE WHEN date_hire > 722815 AND date_hire < 766645
> THEN
>   cast(cast(datepart(mm,dateadd(dd,(date_hire -
> 657072),'1/1/1800')) as varchar(2))
>   + '/' + cast(datepart(dd,dateadd(dd,(date_hire -
> 657072),'1/1/1800')) as varchar(2))
>   + '/' + cast(datepart(yy,dateadd(dd,(date_hire -
> 657072),'1/1/1800')) as varchar(4))
> as smalldatetime)
> ELSE CONVERT(smalldatetime,GetDate()) END,
> ISNULL(SUBSTRING(contact_phone,1,20),''),
> '',SUBSTRING(ISNULL(email_id,''),1,20),
> tf_reagt.status_type, broker_flag, 0,CONVERT
> (smalldatetime,Getdate()), 2
> FROM tf_reagt INNER JOIN #NUName ON tf_reagt.vendor_code =
> #NUName.vendor_code
> LEFT OUTER JOIN tf_reemail ON tf_reagt.vendor_code =
> tf_reemail.vendor_code
> LEFT OUTER JOIN apvend ON tf_reagt.vendor_code =
> apvend.vendor_code

> DROP TABLE #NUName

> RETURN 0
> END
> GO
> GRANT  EXECUTE  ON dbo.tw_AgentsMake TO Public
> GO



Sat, 07 Jan 2006 04:44:18 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Tree Hierarchy with SP_DEPENDS

2. sp_depends & cursors

3. sp_depends

4. Troubles with sysdepends and sp_depends

5. sp_depends does not show what it should

6. sp_depends doesn't return all dependents

7. SP_depends does not work

8. sp_depends for Columns

9. Database renames and sp_depends

10. sp_depends not dependable ??

11. Dependencies and sp_depends

12. SP_DEPENDS question?


 
Powered by phpBB® Forum Software