Solved

Execute store procedure with Openquery

Posted on 2009-06-29
3
2,118 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

829 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