-1

I am trying to do a select with openrowset from a stored procedure with a parameter but it is not working.

SELECT *
FROM
    OPENROWSET(
    'SQLNCLI', 
    'Server=SERVERNAME;Trusted_Connection=yes;',
    'EXEC PROCEDURENAME @Set = 'Data')

If I run EXEC PROCEDURENAME @Set ='Data' it works OK.

Without parameters I have similar code that also runs well.

Regards Elio Fernandes

closed as off-topic by Erik Darling, Michael Green, mustaccio, RDFozz, Mr.Brownstone Jan 18 '18 at 16:08

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, Michael Green, RDFozz, Mr.Brownstone
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Based on your feedback to my answer I think you will have to supply more information in your question. Edit your question and add as much details as possible. How is Excel related? Which version of SQL are you using? What are the full detailed error messages? The more details you add to your question, the more likely the community will be able to assist you in reaching a solution. – hot2use Jan 17 '18 at 7:42
3

You might want to try...

SELECT *
FROM 
    OPENROWSET(
    'SQLNCLI', 
    'Server=SERVERNAME;Trusted_Connection=yes;',
    'EXEC PROCEDURENAME @Set = ''Data''')

...this should preserve the single quotes correctly.

As in the following example:

SELECT *
FROM 
    OPENROWSET(
    'SQLNCLI', 
    'Server=.;Trusted_Connection=yes;',
    'EXEC sp_helpsrvrole @srvrolename = ''sysadmin''')
  • It does not work. It returns an error "The metadata could not be determined because the statement 'SELECT ,,,, is not compatible with the statement ....". I know that the sp works well because I can refresh it with excel but I would like to make it work with SQL. – Elio Fernandes Jan 16 '18 at 19:25

Not the answer you're looking for? Browse other questions tagged or ask your own question.