Prev: SSIS:Date converting error
Next: Error 5149 Trying to expand transaction log (not enough space)
From: Sam Bendayan on 11 Dec 2008 16:56
Greetings,

I'm trying out the sys.sql_expression_dependencies view in
SQL2008 to track dependencies of our database objects. The
ultimate purpose of this is to be able to generate a list
of DB objects in the order in which they need to be applied
to avoid dependency errors.

However, I'm encountering a strange situation. In our
software product we use multiple databases, so I'm looping
through all the databases on a server and running SELECTs
on this sys.sql_expression_dependencies in each of these
databases via Dynamic SQL inside of an SP. However, not
all of the rows are being returned when I call the SP.
But, if I just have the SP print out the Dynamic SQL and
then open up a separate query window and execute that, then
all the rows are being returned.

Any ideas? Has anyone seen this?

Thanks,

SB

Sam Bendayan
DB Architect
Ultimate Software
sam.bendayan(a)gmail.com

*** Sent via Developersdex http://www.developersdex.com ***
From: Erland Sommarskog on 11 Dec 2008 18:45
Sam Bendayan (sam_bendayan(a)ultimatesoftware.com) writes:
> I'm trying out the sys.sql_expression_dependencies view in
> SQL2008 to track dependencies of our database objects. The
> ultimate purpose of this is to be able to generate a list
> of DB objects in the order in which they need to be applied
> to avoid dependency errors.
>
> However, I'm encountering a strange situation. In our
> software product we use multiple databases, so I'm looping
> through all the databases on a server and running SELECTs
> on this sys.sql_expression_dependencies in each of these
> databases via Dynamic SQL inside of an SP. However, not
> all of the rows are being returned when I call the SP.
> But, if I just have the SP print out the Dynamic SQL and
> then open up a separate query window and execute that, then
> all the rows are being returned.

Could you post the code to your procedure?
--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Sam Bendayan on 12 Dec 2008 08:42
Here you go. If you EXEC this SP with @Debug = 1 it will
print out the SQL. If you EXEC it with @Debug = 0 it will
execute the SQL. I get different result sets when doing
the above 2 operations, which shouldn't be the case.

--
IF OBJECT_ID('dbo.HRMS_DBA_SQLDependencies') IS NOT NULL
DROP PROCEDURE dbo.HRMS_DBA_SQLDependencies
GO

/*
EXEC dbo.HRMS_DBA_SQLDependencies @Debug = 1

dbcc freeproccache
*/

CREATE PROCEDURE dbo.HRMS_DBA_SQLDependencies
@Debug BIT = 0
AS
BEGIN
SET NOCOUNT ON;

DECLARE @SQL VARCHAR(MAX),
@CurrentDBName VARCHAR(30)

DECLARE DB_Cur CURSOR FAST_FORWARD FOR
SELECT name
FROM master.sys.databases
OPEN DB_Cur
FETCH NEXT FROM DB_Cur INTO @CurrentDBName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL =
' SELECT DISTINCT
o.name AS ObjectName,
''' + @CurrentDBName + ''' AS
ObjectDBName,
OBJECT_NAME(i.referenced_id) AS
ReferencedObjectName,

ISNULL(UPPER(referenced_database_name), ''' +
@CurrentDBName + ''') AS ReferencedObjectDBName
FROM ' + @CurrentDBName + '.sys.objects o
LEFT JOIN ' + @CurrentDBName +
'.sys.sql_expression_dependencies i ON o.name =
OBJECT_NAME(i.referencing_id)
WHERE o.type IN (''FN'', ''IF'', ''P'',
''SQ'', ''TF'', ''TR'', ''V'')
ORDER BY ObjectName'

IF @Debug = 1
BEGIN
PRINT @SQL
END
ELSE
BEGIN
EXEC(@SQL)
END

FETCH NEXT FROM DB_Cur INTO @CurrentDBName
END

CLOSE DB_Cur
DEALLOCATE DB_Cur
END
GO

Thanks for your help,


Sam Bendayan
DB Architect
Ultimate Software
sam.bendayan(a)gmail.com

*** Sent via Developersdex http://www.developersdex.com ***
From: Sam Bendayan on 15 Dec 2008 11:35
Any luck? Are you seeing what I'm seeing?

Thanks,

Sam Bendayan
DB Architect
Ultimate Software
sam.bendayan(a)gmail.com

*** Sent via Developersdex http://www.developersdex.com ***
From: Sam Bendayan on 15 Dec 2008 12:24
OK...never mind...I found the problem.

The problem was that I was using the OBJECT_NAME() function
while doing cross-database calls....which doesn't work with
this function. One of the joins in the query used this
function, so it was returning inconsistent results because
the 2 connections were using different databases.

I changed the Dynamic SQL to have a USE @CurrentDatabase
statement before the SELECT and this fixed the problem. I
now get consistent results since the OBJECT_NAME function
is always in the proper database context.

Thanks,

Sam Bendayan
DB Architect
Ultimate Software
sam.bendayan(a)gmail.com

*** Sent via Developersdex http://www.developersdex.com ***
 |  Next  |  Last
Pages: 1 2
Prev: SSIS:Date converting error
Next: Error 5149 Trying to expand transaction log (not enough space)