Hi,
Our conservatory came across an old report that they'd like to run again. The only problem is the report comes up blank when you run it. I've done some basic troubleshooting and found that it's joining a local table lt_class_sched to t_perf and tr_season
just for clarity:
into #temp
from lt_class_sched a
join t_perf b on
a.inv_no = b.perf_no
join tr_season c on
b.season = c.id
where b.season = @season
and
b.perf_code = @perf_code
I've done some investigation and found the report is blank because there is no corresponding inv_no in the lt_class_sched. The perf_no does exist in t_perf however.
My question is, how do I find what populates LT_CLASS_SCHED?
I've checked dependencies for the table in SQL and LT_CLASS_SCHED shows no dependencies, though a number of things depend on it.
Any ideas/suggestions are greatly appreciated.
Thanks
Hi Nick, I inherited a large web of confusing (and occasionally crazy) custom tables/views/functions/stored procedures, and have gone on to create scores more, so I created a handy stored procedure to do a fairly exhaustive search of database objects for references to other objects. I can't remember if there's a way to share files through the network site, but you could send me an email address and I could pass it along to you. Or paste the text of it in here. --Gawain From: Nick Torres <bounce-nicktorres3067@tessituranetwork.com>Sent: 3/18/2016 11:42:38 AMHi, Our conservatory came across an old report that they'd like to run again. The only problem is the report comes up blank when you run it. I've done some basic troubleshooting and found that it's joining a local table lt_class_sched to t_perf and tr_season just for clarity: into #temp from lt_class_sched a join t_perf b on a.inv_no = b.perf_no join tr_season c on b.season = c.id where b.season = @season and b.perf_code = @perf_code I've done some investigation and found the report is blank because there is no corresponding inv_no in the lt_class_sched. The perf_no does exist in t_perf however. My question is, how do I find what populates LT_CLASS_SCHED? I've checked dependencies for the table in SQL and LT_CLASS_SCHED shows no dependencies, though a number of things depend on it. Any ideas/suggestions are greatly appreciated. ThanksThis message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Hi Nick,
I inherited a large web of confusing (and occasionally crazy) custom tables/views/functions/stored procedures, and have gone on to create scores more, so I created a handy stored procedure to do a fairly exhaustive search of database objects for references to other objects.
I can't remember if there's a way to share files through the network site, but you could send me an email address and I could pass it along to you. Or paste the text of it in here.
--Gawain
From: Nick Torres <bounce-nicktorres3067@tessituranetwork.com>Sent: 3/18/2016 11:42:38 AM
/*[Gawain performs quick scan to ensure there isn't too much profanity in the SP comments*/
/* stored procedure written by Gawain Lavers on 2007/11/13 */
/* searches through system tables to find objects dependent upon the object
whose name is passed in */
use impresario
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('[dbo].[LP_CPSMA_FIND_DEPENDENCIES]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[LP_CPSMA_FIND_DEPENDENCIES]
CREATE PROCEDURE [dbo].[LP_CPSMA_FIND_DEPENDENCIES](
@search_string varchar(1000),
@dependency_filter varchar(1000) = null
)
AS
SET NOCOUNT ON
BEGIN
declare @whitespace varchar(255)
select @whitespace = '[ ' + char(9) + char(10) + char(13) + ']'
declare @whitespace_and_punctuation varchar(255)
select @whitespace_and_punctuation = '['
+ ' '
+ '.'
+ '('
+ ')'
+ char(9)
+ char(10)
+ char(13)
+ ']'
declare @obj_id int
set @obj_id = (select object_id from sys.objects where name = @search_string)
--RAISERROR if the object is not found
if @obj_id is null
begin
declare @error_string varchar(2000)
set @error_string = 'Could not find object named ' + @search_string + ' in sys.objects.'
RAISERROR (@error_string, 16, 1)
return 1
end
else
select
x.dependent, x.object_id, x.type, x.type_desc, x.found_in
from (
o.name as dependent, o.object_id, o.type, o.type_desc, 'sys.sql_modules' as found_in
from
sys.objects as o
inner join
sys.sql_modules as sm on sm.object_id = o.object_id
where
/*
sm.definition like '%' + @whitespace + @search_string + @whitespace + '%'
or sm.definition like @search_string + @whitespace + '%' --beginnning of the field
or sm.definition like '%' + @whitespace + @search_string --end of the field
or sm.definition like '%\[' + @search_string + '\]%' --bracketed nomenclature, e.g. "[table_name]"
*/
sm.definition like '%' + @whitespace_and_punctuation + @search_string + @whitespace_and_punctuation + '%'
or sm.definition like @search_string + @whitespace_and_punctuation + '%' --beginnning of the field
or sm.definition like '%' + @whitespace_and_punctuation + @search_string --end of the field
escape '\'
union
o.name as dependent, o.object_id, o.type, o.type_desc, 'sys.foreign_keys' as found_in
sys.foreign_keys as fk on fk.parent_object_id = o.object_id
fk.referenced_object_id = @obj_id
sj.name + ': ' + sjs.step_name as dependent, 0 as object_id, ' ' as type, 'Agent Job: Step' as type_desc, 'msdb.dbo.sysjobsteps' as found_in
-- sjs.name as dependent, o.object_id, o.type, o.type_desc, 'sys.sql_modules' as found_in
msdb.dbo.sysjobsteps as sjs
inner join msdb.dbo.sysjobs as sj on sj.job_id = sjs.job_id
sjs.command like '%' + @whitespace_and_punctuation + @search_string + @whitespace_and_punctuation + '%'
or sjs.command like @search_string + @whitespace_and_punctuation + '%' --beginnning of the field
or sjs.command like '%' + @whitespace_and_punctuation + @search_string --end of the field
or sjs.command like '%\[' + @search_string + '\]%' --bracketed nomenclature, e.g. "[table_name]"
) as x
((@dependency_filter is null) or (x.dependent like @dependency_filter))
order by
x.dependent
END
GRANT EXECUTE ON [dbo].[LP_CPSMA_FIND_DEPENDENCIES] TO [ImpUsers]