Posted on 2008-06-18
Last Modified: 2008-09-10
Hi, I'm using SQL SERVER 2005...
is it possible to use OPENROWSET with a EXEC command?

Something like:

FROM OPENROWSET('SQLOLEDB','localhost';'sa';'P@ssw0rd',
'sp_executeSQL N''MyDataBase..sp_GetMyData 2008, null, 4'' ') TblA

Strangely enough (or not) this doesn't work :)

Is it possible to make it work?

Question by:Alexandre Simões

Accepted Solution

chuckatwork earned 500 total points
ID: 21816334
You have to enable it via Surface Area Configuration tool.
LVL 30

Author Comment

by:Alexandre Simões
ID: 22437018
Sorry for leaving this so long but I never actually tried the solution.
We ended using a different approach at the business level so ended up not needing this.

I'm accepting it as it could be a cause.

LVL 51

Expert Comment

by:Mark Wills
ID: 24453387
just happened across this, and thought you might like to see how :

select * from openrowset('SQLNCLI', 'Server=MY_SVR_NAME;Trusted_Connection=yes;','EXEC MY_DB_NAME..USP_MY_SP null,null,null,null') a

Now, it will fail unless the stored procedure does have a predefined (ie at create sp time) list of columns. It is quite "sensitive" (read fussy).

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 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

10 Experts available now in Live!

Get 1:1 Help Now