remote call of sys.object_definition()

[obiron via SQLMonster.com]

Hi guys.

I am trying to compare the db objects on our new 2008 server with those on
the 2005 server. we have the 2005 server set up as a linked server using NT
authentication.

here is my query to get all of the objects which don't match
[code]
with bfld2008 as
(
select o.object_ID,coalesce(OBJECT_DEFINITION(o.[object_id]),t.name) as
'description',o.name, o.type,o.type_desc from [sys].objects o left join sys.
tables t on o.object_id = t.object_id where o.type in ('P','V','IF','FN',
'TR','U')
)
, bfld2005 as
(
select o.object_ID,coalesce(OBJECT_DEFINITION(o.[object_id]),t.name) as
'description',o.name, o.type,o.type_desc from [lic-py-bf-01].bluefish.[sys].
objects o left join [lic-py-bf-01].bluefish.sys.tables t on o.object_id = t.
object_id where o.type in ('P','V','IF','FN','TR','U')
)

select
bfld2008.object_id as '08_obj',
bfld2008.description as '08_desc',
bfld2008.name as '08_name',
bfld2008.type as '08_type',
bfld2008.type_desc as '08_td',
bfld2005.object_id as '05_obj',
bfld2005.description as '05_desc',
bfld2005.name as '05_name',
bfld2005.type as '05_type',
bfld2005.type_desc as '05_td'
from
bfld2008
full outer join
bfld2005 on bfld2008.description collate
SQL_Latin1_General_CP1_CI_AS = bfld2005.description collate
SQL_Latin1_General_CP1_CI_AS-- where bfld2005.description <> bfld2008.
description
where
bfld2005.object_id is null or bfld2008.object_id is null
order by
coalesce( bfld2008.name collate SQL_Latin1_General_CP1_CI_AS , bfld2005.
name collate SQL_Latin1_General_CP1_CI_AS )
[/code]

I am getting some mismatched data because the object id in 2005 is not the
same as the object id in 2008, but i am always trying to get the
OBJECT_DEFINITION from the 2008 database.

How can I access the remote OBJECT_DEFINITION() of the linked server database.

If I try to 4 part name it (i.e. [LIC-PY-BF-01].bluefish.sys.
object_definition(objectID) then I get the error message
Remote function reference 'LIC-PY-BF-01.bluefish.sys.object_definition' is
not allowed, and the column name 'LIC-PY-BF-01' could not be found or is
ambiguous.

--
Message posted via http://www.sqlmonster.com

[ML]

You can create a view in the remote database referencing the
OBJECT_DEFINITION system function. As you've already seen for yourself the
function assumes the object_id passed as its sole argument refers to an
object in the current database.

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

[obiron via SQLMonster.com]

Thanks for that ML, That solution occurred to me on the way home.

Although it is possible, it will result in a record being reported because
the view will exist in one database and not the other.

Obiron

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201003/1