How can I trigger a SQL2005 Stored Procedure from SQL 2000?

Question

How can I trigger a SQL2005 Stored Procedure from SQL 2000?

pallavikiran.deevi

SSC Rookie

Points: 37
More actions
October 14, 2008 at 6:55 am

#194309

I am using a trigger on a table in SQL SERVER 2000. When the trigger fires I need it to execute a Stored Procedure in SQL EXPRESS 2005.Can any please send me the solution.I need solution for this is very urgent.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Create a Linked Server between the two and call the stored procedure over the link. I would expect that you will have some performance issues with this since it runs synchronously on the SPID executing.

pallavikiran.deevi SSC Rookie Points: 37 More actions · Answer 2

Thank you very much for your reply.I tried to create a linked server,but it's not linking properly and i can't view the tables in linkes server.Can you please tell me how to create a linked server from SQL 2000 TO SQL EXPRESS 2005.Both SQL 2000 AND SQL EXPRESS 2005 are on the same machine.I need solution for this so urgently.Thank you very much.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

How have you set the linked server up? What errors is it giving?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pallavikiran.deevi SSC Rookie Points: 37 More actions · Answer 4

I created the linked server as follows: SQLSERVER 2000->EM->Security->Linked Server->New Linked Server->General ->Name of the linked server i gave the name of sql express 2005.And select SERVER TYPE as SQL SERVER.

I am getting the following error when i click tables in linked server Error 7399: OLEDB Provider 'SQL OLEDB' REPORTED AN ERROR .

Can you please help me how to create a linked server from SQL 2000 TO SQL EXPRESS 2005.

Thank you very much.

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 5

use sp_addlinkedserver command, for more information, see SQL BOL

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

There are a couple of ways for you to go about creating this linked server. The first thing you have to do though is ensure that the SQL Express Instance is configured to allow Remote TCP/IP connections. To do this, Remote Desktop to the machine that has SQL Express Installed.

Then Open the Surface Area Configuration Tool from the Start-> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration.

Then Click the Surface Area Configuration for Services and Connections link.

Click on the SQLEXPRESS Instance and Expand the Database Engine under the Instance.

Click on Remote Connections, and change it to Local and Remote Connections, and you have preference on this, but I generally just use TCP/IP only. You can enable both TCP/IP and named pipes if you want.

Click ok and close the tool.

Then go back and try and create the linked server again. When I am linking to a named instance like SQL Express, I find it is often easier to use a Client Alias following this blog post:

http://jmkehayias.blogspot.com/2008/10/using-sql-client-configuration-alias-to.html