Executing sp_addlinkedserver

[Spenello]

We're running SQL Server 2005 with BizTalk 2006.

In the Application Event Viewer, there's a recurring warning Event ID 208: [SQL Server Scheduled Job 'TrackedMessages_Copy_BizTalkMsgBoxDb' (0xA63DAFB99BCC5743A8B28374C371F803) - Status: Failed - Invoked on: 2007-03-27 09:24:00 - Message: The job failed. The Job was invoked by Schedule 7 (Schedule). The last step to run was step 1 (Purge).].

So, I took a look at the Log File History for TrackedMessages_Copy_BizTalkMsgBoxDb job under COBIZDEV-2K3-2 > SQL Server Agent > Jobs in SQL Server Management Studio.

The tracked message reads as follows: Executed as user: NT AUTHORITY\SYSTEM. Could not find server 'COBIZDEV-2K3-2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. [SQLSTATE 42000] [Error 7202]. The step failed.

The workaround seems to be running this statement in sp_addlinkedserver: exec sp_addlinkedserver 'COBIZDEV-2K3-2', 'sql server'

However, I cannot find ,and not one seems to be able to specify, where sp_linkedserver is, how to locate it or how to use it.

Any help is appreciated.

 

[ptheriault]

sp_addlinkedserver is a system procedure that you can use to add a link server. My quesiton is.

Is COBIZDEV-2K3-2 a server in your domain? Can you ping it? You will also need a login. I would contact the vendor with this error as it appears to be a configuration error with BizTalk

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Spenello]

COBIZDEV-2K3-2 is set up as a stand alone local server testing applications in a dev environment. The domain is it's own and not linked to any other machine.

We've never had to add a link server. What's the process to use sp_addlinkedserver as a system procedure to add a link server?

 

[ptheriault]

Here is the syntax for adding a link server.

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ] 
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ]

Check out BOL for a more indepth explantion of each parameter.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Spenello]

Thanks for your input. But, I think I need a lot more hand holding on this issue. BOL ??? and where exactly do you run the syntax.

 

[ptheriault]

No problem.

BOL = Book On-Line. It is installed when you install SQL Server tools and select documentation.

Here is an example.

[COLOR=blue]USE[/color] master;
[COLOR=blue]GO[/color]
[COLOR=blue]EXEC[/color] sp_addlinkedserver 
   [COLOR=red]'COBIZDEV-2K3-2'[/color],
   N[COLOR=red]'SQL Server'[/color]
[COLOR=blue]GO[/color]

I don't know how the program is trying to connect.




- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Run this through Query Analyzer

- Paul
- If at first you don't succeed, find out if the loser gets anything.