Solved

Execute store procedure with Openquery

Posted on 2009-06-29
3
2,107 Views
Last Modified: 2012-05-07
Hello,

I have created a linked server between an sql server 2000 and sql server 2005 express, named svr02\svr02

In the linked server svr02 i have create a simple Stored Procedure 'sp_CreateOrder' with no parameters which inserts a row into a test table 'Orders'. This stored procedure works from the second server svr02.

I'm trying to use this from the first server using openquery
SELECT * FROM OPENQUERY([svr02\svr02], 'Order.dbo.sp_CreateOrder');

this gives the following error
OLE DB error trace [Non-interface error:  OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query=Order.dbo.sp_CreateOrder'].
Msg 7357, Level 16, State 2, Line 1
Could not process object 'Order.dbo.sp_CreateOrder'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

Openquery works fine when i do select statements.

Thank you
Teo
0
Comment
Question by:temmelv
  • 2
3 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24743038
what is your database name? Is it "Order"?
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24743071
if you have add linked server and wants to call it's stored procedure than you have to use following syntax

exec [svr02\svr02].[databaseName].dbo.sp_CreateOrder

use four part while calling SP. first is server name, second is database name which has your SP, third is owner name, and finally stored procedure name.
0
 

Author Comment

by:temmelv
ID: 24744153
This worked
exec [svr02\svr02].[databaseName].dbo.sp_CreateOrder

but is also needed

exec sp_serveroption @server='myserver', @optname='rpc', @optvalue='true'
exec sp_serveroption @server='myserver', @optname='rpc out', @optvalue='true'

Thank you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now