Post reply

use xp_cmdshell in OPENROWSET

  • August 2, 2012 at 8:53 am

    #261979

    Hi,

    I am trying to run below query on sql server 2005.

    SELECT *

    FROM OPENROWSET ('SQLOLEDB','SERVER=testserver;UID=sa;PWD=test;','exec master..xp_cmdshell ''dir c:\'' '

    ) t

    and got this error message:

    Cannot process the object "exec master..xp_cmdshell 'dir c:\' ". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

    The testserver is sql server 2000. I don't have a sql server 2008 to test at the moment, but I guess it will be the same on 2008?

    Any idea how to solve this (on sql server 2005)?

  • August 2, 2012 at 9:06 am

    #1520913

    First that the login you're using has access to the C drive on the testserver. I'd then also check that xp_cmdshell was enabled.

  • August 2, 2012 at 9:15 am

    #1520924

    benjamin.reyes (8/2/2012)

    First that the login you're using has access to the C drive on the testserver. I'd then also check that xp_cmdshell was enabled.

    I can use the sa account to connect to the testserver and run the command: exec master..xp_cmdshell ' c:\' without problem. Which also proves the xp_cmdshell is enabled. BTW, I remembered that xp_cmdshell is always enabled on Sql server 2000.

  • August 2, 2012 at 9:21 am

    #1520932

    nzhang6666 (8/2/2012)

    benjamin.reyes (8/2/2012)

    First that the login you're using has access to the C drive on the testserver. I'd then also check that xp_cmdshell was enabled.

    I can use the sa account to connect to the testserver and run the command: exec master..xp_cmdshell ' c:\' without problem. Which also proves the xp_cmdshell is enabled. BTW, I remembered that xp_cmdshell is always enabled on Sql server 2000.

    only members of the sysadmin group, or users explicitly granteded execute on xp_cmdshell can use xp_cmdshell;

    is the user TestUser granted either of those?

    doh nevermind; you showed it was sa....

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 2, 2012 at 9:24 am

    #1520935

    try this: adding SET FMTONLY OFF works for me:

    i tested from a 2005 to a 2008 openrowset:

    SELECT *

    FROM OPENROWSET ('SQLOLEDB','SERVER=DEV223;UID=sa;PWD=NotTheRealpassword;','Set FmtOnly OFF;exec master..xp_cmdshell ''dir c:\'' '

    ) t

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 2, 2012 at 9:35 am

    #1520945

    Thanks, that works!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply