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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculated columns 13 60
Nested cursor  in SQL 9 94
System Center 2016 SQL Srv 1 24
SQL server is using more virtual memory. 5 63
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

929 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