Execute store procedure with Openquery

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
temmelvAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
what is your database name? Is it "Order"?
0
RiteshShahCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
temmelvAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.