remote call of sys.object_definition()

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

0
obiron
3/19/2010 3:49:00 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
750 Views

Similar Articles

[PageSpeed] 32

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/
0
Utf
3/19/2010 8:17:02 PM
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

0
obiron
3/22/2010 1:14:15 PM
Reply:

Similar Artilces:

call me slow, but where is the magnifier in excel?
Where is the Magnifier in Excel? What version? In 2007, on the View tab, click "Zoom". "cgotzeff" <cgotzeff@discussions.microsoft.com> wrote in message news:A46D6E4B-BE8A-4DFC-AEB6-117762AA9441@microsoft.com... > Where is the Magnifier in Excel? View>Zoom on versions<2007 Gord Dibben MS Excel MVP On Wed, 6 Feb 2008 12:09:00 -0800, cgotzeff <cgotzeff@discussions.microsoft.com> wrote: >Where is the Magnifier in Excel? ...

Query to Pull Most Recent Call with a Callback Date Scheduled
Hello, I'm trying to create a query that only pulls in patients that have a scheduled callback date, but only if the callback date was scheduled on there most recent call. So far I've been unsuccessful. Below is the SQL that I'm attempting to use- SELECT [Patients Table].[Patient ID], [Patients Table].[First Name], [Patients Table].[Last Name], [Patients Table].[Home Phone], [Patients Table].[Day Phone], Max([Call]) AS Expr1, [Calls Table].[Call Outcome], [Calls Table].[Callback Date] FROM [Patients Table] INNER JOIN [Calls Table] ON [Patients Table].[Patient ...

Remote Access Connection (RAS) over satellite on XP3
Client Machine(where I connect from) - XP Service Pack 3, PcAnywhere 10.5/11. Host Machine(where I connect to) - XP Service Pack 3, PcAnywhere 11. When I connect using PcAnywhere 10.5/11 to a host machine, I have problem whereby the remote connection is super slow. When the connection is establised, you need to count of 20 - 30 before it gets to load other windows/screens/pages. For your information, how the host receive a signal is via satellite. So how I connect to the host basically, the remote access has to go through the satellite dish > Indoor Unit (IDU) > 3Com Swi...

recover messages when remote session expires before sending
I was typing a long message and when finished wanted to send it via Outlook but noticed my remote session via my office has expired. Can I recover the message "atesemin" <atesemin@discussions.microsoft.com> wrote in message news:ACAA0F43-0FE1-4D8B-9ED2-1538D0445429@microsoft.com... >I was typing a long message and when finished wanted to send it via Outlook > but noticed my remote session via my office has expired. Can I recover the > message If you had a remote session that timed out or otherwise dropped, then just reconnect to the remote machine and...

Access CRM remotely via internet browser
hi, We have a few users in Hong Kong which we will like them to use MSCRM; Currently, our main MSCRM server is located in London, UK. Can CRM be accessed remotely via internet browser? At the moment, we can only access them via internet browser if we are within the local network. tks Alan You would need to expose the CRM website through your firewall. This could be a security issue though with this approach. If you can, you would be safer to establish a VPN connection. At a minimum, require SSL on the exposed web port. -- Matt Parks MVP - Microsoft CRM "Alan" &l...

how to enter call option transaction
I sold some call options that were eventually exercised. Under list of selections available, there is an "exercise" option, but it's for the purpose of ME exercising the option. The call options were exercised by someone else. What I ended up doing is entering a "buy to close" for the option for $0, then sold the underlying stock at the strike price without commission. There must be a better way to do this. In microsoft.public.money, namsilat wrote: >I sold some call options that were eventually exercised. Under list of >selections available, there is an "e...

Execute Procedure on a remote Access Db file
I am trying to open another Access DB from my current Access DB and trying to execute a SUB programatically. But I get a message that Microsoft Office Access can't find macro "ParseMnAFileAGH" message. Any help on this is much appriciated. Here is my code: SUB RunMacro() dim objAccess as Access.Application Set objAccess=CreateObject("Access.Application") objAccess.OpenCurrentDatabase ("C:\MnAAH.mdb") objAccess.docmd.RunMacro "ParseMnAFileAH" objAccess.DoCmd.Minimize Set objAccess = Nothing End sub "Kumar" <Kumar@disc...

CMyWinApp::InitInstance is never called in console app.
Hello, I'm using MFC, MSVC60. I created simple console application with MFC support with MSVC wizard. Everything is cool and working. However, if I inherit from CWinApp and override InitInstance, it doesn't have any effect. The method is never called. I searched sources of MFC and noticed that InitInstance is called either from DLLMain or AfxWinMain. Both of them obviously don't exist in console application. Now my quaestions are: Should I call InitInstance manually? Should I somehow to cause the MFC to call InitInstance indirectly? Or should I abandon InitInstance at all and pu...

Big problem with huge pagefile.sys on Exchange server
Win 2003 Standard 4GB RAM Exchange 2003 SP2 Symantec Email security (Antivirus) We've been ahving problem with low virtual memory on our exchange server. Last time I check, the system used up all VM (4GB) and Exchange went to a halt temporarily. We use the /3GB switch in boot.ini as recommended by MS but not helpful. Any tip is greatly appreciated. Bill On Thu, 15 Dec 2005 13:06:32 -0800, "Bill Nguyen" <billn_nospam_please@jaco.com> wrote: >Win 2003 Standard 4GB RAM >Exchange 2003 SP2 >Symantec Email security (Antivirus) > >We've been ahving p...

Can't verify publisher for mf.sys
I've got a multiportSerial inf that uses mf.inf/mf.sys to enumerate x number of serial ports. When I use this inf for my device, Server 2008 complains that "Windows can't verify the publisher of this driver software". If I say to install the driver software anyway then it will enumerate all my serial ports correctly and it's happy to install the serial driver I have written for the child devices. What do I need to do in order to get the mf.inf/mf.sys portion of my install to work without user interaction? You need to sign your mf.sys file (and mf.cat) w...

Application crashes in a call to PageSetupDlg
Hi, I have an application that uses the PageSetupDlg API. I fill in a PAGESETUPDLG object and pass it on the address of it to the API. The filling up is done with the following (VC++) code: PAGESETUPDLG psd; // Initialize PAGESETUPDLG ZeroMemory(&psd, sizeof(PAGESETUPDLG)); psd.lStructSize = sizeof(PAGESETUPDLG); psd.hwndOwner = hwndOwner; // Handler to the owner of the dialog. The owner is a VB app HGLOBAL devIn = GlobalAlloc(GMEM_MOVEABLE, dev->dmSize + dev->dmDriverExtra); //dev is the device passed on to this piece of code by t...

quick call on excel
Can I command a call to the pc's modem by a simple clic on a cell? I work as a sallesman. I've so many calls to do every day and it's really hard to me. Thanks for any help. Fernando try this. It could be converted to a double click event. Sub CellToDialer() 'John Walkenbach ' Transfers active cell contents to Dialer ' And then dials the phone ' Get the phone number CellContents = ActiveCell.Value If CellContents = "" Then MsgBox "Select a cell that contains a phone number." Exit Sub End If ' Activate (...

Best way to setup Sharepoint for remote sites
I have a question about SharePoint setup. We currently have a site setup with information at our home office however we have several other offices and users in these offices that need to access information from SharePoint. Dragging that information across the wire is slow. I am trying to figure out what's the best way to setup a remote SharePoint site that could either replicate data or if it was possible just to setup a front-end SharePoint site in this location and point the database to the one at the home site? Which one works adequately? We are going to be migrating our fi...

Remote Connection
I'm currently utilizing Great Plains 7.5 with SQL Server 2000. I have ten maintenance foreman who access the service call management module via a broadband virtual private network; average bandwidth of 400Kbps. The computers the maintenance foreman have are 1.6Ghz processor with 256Mb RAM. There is considerable latency when the maintenance foreman access the service call management module, is there any way to increase the performance. Any ideas? Thanks! Hi Have you thought about using terminal services to host your client applications for remote users? Regards James --...

Dialog loosing focus when called from Tree Control
Hello All, In our application a dialog has to be invoke if user select any item from the tree control(CTreeCtrl Class variable ) The dialog is created properly as a parent of tree control dialog but its loosing focus , as per our requirement this dialog should be at the top of the window but tree control is coming at the top. Can anyone tell us where i am doing wrong. I have tried all calls like ShowWindow, killFocus from the tree control.. etc... but it didn't work. Regards Amit Let me see if got this correctly. 1. User selects an item from a tree control. 2. At that point you wan...

Setting Up Relay for Remote Users
I am trying to setup my Exchange server (5.5 SP4)to allow relaying for several people in a remote office. They are using Outlook 2000 and access their email via POP3. They are able to receive all email but can only send email to recipients "in-house". How do I enable relaying for select users? "Toney Barber" <tbarber@removethis@lgpatlaw.com> wrote: >I am trying to setup my Exchange server (5.5 SP4)to allow relaying for several people in a remote office. They are using Outlook 2000 and access their email via POP3. They are able to receive all email but can only...

Position form near control that called it
Howdy: I would like to position a form (a calendar) near the control that issued a call to open the calendar form. So, wherever on my calling form there is a control that requires a date, the calendar form will open just beneath that control. Is this a job for MoveSize? Any examples around to show the way? I look forward to your input. Thanks, Barry "Barry" <Barry@discussions.microsoft.com> wrote in message news:B23F347F-C87D-4636-9237-5D101E7B629D@microsoft.com... > Howdy: > I would like to position a form (a calendar) near the control that issued ...

Calling function pointers
Hello all, I was looking at the Dynamic Array functions exported by commctl32.dll (prefixed DPA_ and DSA_) http://msdn.microsoft.com/en-us/library/ff485930%28VS.85%29.aspx Although there doesn't seem to be anything there that really couldn't be achieved in VB, I was still slightly disappointed that some of the more interesting functions (DPA_Merge for example) are exported by ordinal only. I'm assuming that this means those ones aren't callable from straight VB. Robert As far as I know, we can call API using ordinal number. Example: Private Declare Fun...

New so-called features in Outlook 2002 -- can they be defeated?
Hello all. Our office recently got "upgraded" to Outlook 2002. It has a couple of so-called "features" which I find incredibly annoying and impossible to defeat. I'd be interested if anyone has found a way, other than the ridiculous workarounds listed on Microsoft's website. The two features which annoy me most are: Displaying both the name and the email address in email message headers; Displaying two entries in the "Select Names" dialog box for everyone who has both a fax and an email address, that is, virtually everybody. If anyone can te...

Calling a DLL from the wrokflow
Hi all, I am new to CRM. I need a clarification with respect to a error code that appears to haunt the eventvwr often, when i am trying to call a .Net DLL from the CRM workflow. The error description looks like this in the eventvwr"<code> 0 X 80040217 </code>" it says that the workflow is stopped and i need to check the workflow monitor for more details. When i do the same the workflow monitor displays "Ok" to all the workflow events including the call to DLL. I am confused Please help. ...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

Calling Workflow using onchange, onload or onsave
Can workflow be called using onchange, onload or onsave? I would like to update the accoutn record when a bit field is selected on one of the contacts (parent to the account). Thanks James A better approach would be to have a callout created that would be triggered when when the contact is saved. The callout would check that particular field and update the parent account accordingly. ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "JamesE" <JamesE@discussions.microsoft.com> wrote in message news:B13630D9-C6FD-4A41-9BDC-4316...

System Calls from MFC Application
using system() command from mfc application, the black screen pops-up before my dialog box is very annoying, how do I automatically minimize it or close it and only show my dialog window? Thanks Instead of using system() , use any of the following API's. CreateProcess() ShellExecute[Ex]() -- Cheers Check Abdoul [ VC++ MVP ] ----------------------------------- "Arun" <arunkk@yahoo.com> wrote in message news:081201c361f9$60b5bd20$a401280a@phx.gbl... > using system() command from mfc application, the black > screen pops-up before my dialog box is very ann...

event 8250 win32 api call 'dsGetNamew" returned error code 0x862
I am getting the above event on my logs when starting up my exc2003 server on server 2003. The rest of the message states that the specified component cannon be found in the configureation information. When I click for more information, it says that this error can occur occasionaly and can be safely ignored however, I am getting it constantly and I beleive this is related to anothre problem I am having where my inetinfo.exe flakes out and uses up all the processor time, thereby bringing the server to an absolute halt. Any insite would be appreciated. Harry Poorman hpoorman@psu.edu ...

Backup of remote Exchange Server, any Gotchas?
I administer a multisite network. Our head office is in Perth, a branch office is in Sydney. The two sites are connected via 2Mbit VPN link. The head office has approx 100 users, the branch around 20. We run an Exchange 2007 server in Perth. I am considering placing an Exchange 2007 server in the branch office, and using DPM in Perth to back it up. The total mailbox data size in Sydney will be about 6Gb. Questions: 1. Is this a valid configuration? 2. How well does DPM manage bandwidth in this sort of scenario? I hope to be able to just do a delta copy each night after h...