Red Gate forums :: View topic - EXEC sproc
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Dependency Tracker 2
SQL Dependency Tracker 2 forum

EXEC sproc

Search in SQL Dependency Tracker 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
wdevine



Joined: 29 Sep 2006
Posts: 3
Location: FL, USA

PostPosted: Fri Sep 29, 2006 12:14 am    Post subject: EXEC sproc Reply with quote

Hello,

I have a stored procedure that EXECs another stored procedure like this:

EXEC spRankDataSet @EntityId, @TypeID

But for some reason, the stored procedure that has this above code doesn't show the spRankDataSet stored procedure as a dependency.

Maybe I haven't set an option appropriately. Any help would be appreciated.

Thank you,

Bill
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Wed Oct 04, 2006 4:32 pm    Post subject: Reply with quote

Hello Bill,

This should definitely work. When you hover over this particular stored procedure, do you see something in a tooltip similar to 'NOT SHOWN:spRankDataSet '?
Back to top
View user's profile Send private message
wdevine



Joined: 29 Sep 2006
Posts: 3
Location: FL, USA

PostPosted: Wed Oct 04, 2006 7:31 pm    Post subject: Reply with quote

No, it doesn't show me any extra information when I hover over the stored procedure. When I create my project, I just select the 'stored procedures' group.

I've pasted a portion of the stored procedure below and how the EXEC call is being done. Let me know if you need any other information!

Code:

   IF (@UseCache)=1   -- our DataSet has already been defined
   BEGIN
      EXEC spRankDataSet @EntityId, @TypeID
   END
   ELSE -- use the entire Entity table as DataSet
   BEGIN      
      -- create the temp table

      CREATE TABLE #DataSet
      (
         numID INTEGER IDENTITY(1,1),
         FkItemEntityID bigint not null,
         ComparisonValue decimal(10,4) not null,
         ComparisonSlice int,
         PRIMARY KEY CLUSTERED (numID),
         UNIQUE (FkItemEntityID, numID)
      )

      -- exec spNewRankUsers
      INSERT INTO #DataSet EXEC spNewRankUsers @EntityID, @TypeID, @MaxRecords, @PageNum
   END
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Thu Oct 05, 2006 11:45 am    Post subject: Reply with quote

Hello,

Can you script the spRankDataSet stored procedure as well?
Back to top
View user's profile Send private message
wdevine



Joined: 29 Sep 2006
Posts: 3
Location: FL, USA

PostPosted: Wed Oct 11, 2006 5:52 pm    Post subject: spRankDataSet Script Reply with quote

Hi Brian,

Sorry I didn't get back to you quickly. I was travelling. Here's the spRankDataSet script that you asked for.

Also, if you'd like the whole db schema, give me an email you'd like it sent to.

Let me know if you need any other information.

Thank you,

Bill

Code:

CREATE    Procedure [dbo].[spRankDataSet]
(
@E bigint,
@T int = sfEntityTypeVisualizerValue   -- default, this is the typeId for Visualizer
)
as
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;



SELECT e.EntityId as FkItemEntityId, cast(count(wd.WordText)*avg(cast(ww.XLoc as numeric(9,4))) * 0.01 as numeric(9,4)) as ComparisonValue,
        null as ComparisonSlice
      INTO #DataSet2 FROM #DataSet ds
   INNER JOIN Entity e ON ds.FkItemEntityId = e.EntityId
   INNER JOIN WordDef wd
   INNER JOIN WordSelection ws
   INNER JOIN #entityWords ww
      ON ws.WordId = ww.WordId
      ON wd.WordID=ws.WordID
      ON ws.EntityID = e.entityid
   where wd.WordText in (select t.WordText from #entityWords t)
      group by e.entityid


Update ds
Set ComparisonValue = ds2.ComparisonValue
From #DataSet ds
JOIN #DataSet2 ds2 on (ds.FkItemEntityId = ds2.FkItemEntityId)

drop table #DataSet2


GO
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group