Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

OPENROWSET and EXEC...

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

Something like:

SELECT *
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?

Thanks,
Alex
0
Comment
Question by:Alexandre Simões
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 2

Accepted Solution

by:
chuckatwork earned 2000 total points
ID: 21816334
You have to enable it via Surface Area Configuration tool.
0
 
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.

Thanks,
Alex
0
 
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).
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

661 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