xp_cmdshell problem

  • Imke Cronje

    SSCarpal Tunnel

    Points: 4822

    Hi there,

    I am currently deploying a stored proc on the master database on one of our servers.

    The stored proc deployed fine, but when I add the following command:

    EXEC master.dbo.xp_cmdshell @cmd in the proc, the altering of the proc takes quite long and then afterwards gives me the following error:

    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The specified network name is no longer available.)

    Can anybody please help me with understanding this problem. Deployment on other servers works fine. What do I need to check for?

    Regards

    IC

  • Steve Jones – SSC Editor

    SSC Guru

    Points: 713713

    That is very strange. If you create a new proc, same code, including the cmdshell, does it work?

  • Imke Cronje

    SSCarpal Tunnel

    Points: 4822

    Yes, it works on databases on other servers but not in this particular one.

  • ALZDBA

    SSC Guru

    Points: 134254

    probably access via cmdshell is prohibited.

    I’ve seen installations that raise a system error and log it into an audit log.

    Contact the sysadmin(s).

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • SQL ORACLE

    One Orange Chip

    Points: 27807

    The following link may help.

    http://msdn2.microsoft.com/en-us/library/ms190693.aspx

  • Brandie Tarvin

    SSC Guru

    Points: 172402

    Verify via the SQL Server Surface Area Configuration Tool that you have xp_cmdshell enabled on this server. Usually when something takes that long before returning an error, it’s stuck check to see if it can “communicate” with that particular command.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • ALZDBA

    SSC Guru

    Points: 134254

    -if the cmdshell hadn’t been actived, the exec would never work.

    – put a print statement right befor the exec statement

    print ‘cmd: ‘ + @cmd

    this way you’d actualy see what’s being presented for the cmdshell

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • DonaldW

    SSCarpal Tunnel

    Points: 4366

    Imke Cronje (1/23/2008)


    Yes, it works on databases on other servers but not in this particular one.

    I presume you mean that the code you provided works on other servers but not this server. Does it work on other databases on this server?

    At any rate, as other people have said, the cmdshell option is probably disabled on this server. I have used the following code to temporarily enable it then set it back to its original value when done. It does require sa privileges. If cmdshell is enabled to begin with it does a little processing that it doesn’t have to, but I find that’s better than code failing.

    EDIT: WARNING – use this only when performing upgrades to the database, not in daily processing. I would consider that to be a dangerous practice. If the cmdshell is required for daily processing then review it with the server administrator and enable it.

    2nd EDIT (I’ll get my act together eventually :blush:): Wrap the code that peforms the actions you need to take in a Try…Catch block to ensure that failure does not leave the cmdshell in an undesirable state. What I usually do is run the code that sets cmdshell in one batch or script, the code I need to run in a different batch or script, then the code that resets cmdshell in yet another batch or script. That way I ensure that the cmdshell is reset no matter what happens to the code.

    Enable the cmdshell:

    — Get configuration

    select name, value, value_in_use

    into original_config

    from sys.configurations

    where name = ‘show advanced options’

    or name = ‘xp_cmdshell’;

    declare @advanced_config int;

    declare @cmdshell_config int;

    select @advanced_config = cast(value_in_use as int)

    from original_config

    where name = ‘show advanced options’;

    select @cmdshell_config = cast(value_in_use as int)

    from original_config

    where name = ‘xp_cmdshell’;

    –If xp_cmdshell is turned off, turn it on.

    if @cmdshell_config = 0

    begin

    if @advanced_config = 0

    begin

    EXEC sp_configure ‘show advanced options’, 1;

    RECONFIGURE;

    end

    EXEC sp_configure ‘xp_cmdshell’, 1;

    RECONFIGURE;

    end

    (do the work that requires cmdshell here)

    Set cmdshell status to its original value:

    declare @advanced_current_config int;

    declare @cmdshell_current_config int;

    — Get current configuration

    select @advanced_current_config = cast(value_in_use as int)

    from sys.configurations

    where name = ‘show advanced options’;

    select @cmdshell_current_config = cast(value_in_use as int)

    from sys.configurations

    where name = ‘xp_cmdshell’;

    — Get original configuration

    declare @advanced_original_config int;

    declare @cmdshell_original_config int;

    select @advanced_original_config = cast(value_in_use as int)

    from original_config

    where name = ‘show advanced options’;

    select @cmdshell_original_config = cast(value_in_use as int)

    from original_config

    where name = ‘xp_cmdshell’;

    if @cmdshell_original_config <> @cmdshell_current_config

    begin

    EXEC sp_configure ‘xp_cmdshell’, @cmdshell_original_config;

    RECONFIGURE;

    end

    if @advanced_original_config <> @advanced_current_config

    begin

    EXEC sp_configure ‘show advanced options’, @advanced_original_config;

    RECONFIGURE;

    end

    drop table original_config;

    go

  • Bob Fazio

    SSChampion

    Points: 10654

    I know you said altering, but I am assuming you meant executing.

    What is the command that you are trying to run? The reason I ask is because it sounds like the issue is a UNC that isn’t accessable from the machine that you are trying to run the command.

    Something like \\MyPersonalPC\C$\tmp

    Realize that all commands run as the user that owns the service. It is possible that all of your other systems are installed as a domain account, but that one is as local service or something.

  • timster

    SSC Eights!

    Points: 923

    Did anyone find a solution to this?

  • Brandie Tarvin

    SSC Guru

    Points: 172402

    UnicornsRreal (7/18/2012)


    Did anyone find a solution to this?

    Start a new thread with all relevant details (including the answers to the questions on this thread). You’ll get more hits that way. There are a lot of Threadzians who look at posts that haven’t been answered before they look at old posts.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • call.copse

    SSCoach

    Points: 16617

    If anyone still cares BTW I came across this today; the ALTER or CREATE PROC statements don’t work from remote SSMS. Run them directly on the server and you’re OK.

  • Brandie Tarvin

    SSC Guru

    Points: 172402

    call.copse - Tuesday, February 19, 2019 8:44 AM

    If anyone still cares BTW I came across this today; the ALTER or CREATE PROC statements don't work from remote SSMS. Run them directly on the server and you're OK.

    Can you please post a link to the source of this information?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • call.copse

    SSCoach

    Points: 16617

    Brandie Tarvin wrote:

    <strong class="d4pbbc-bold">call.copse - Tuesday, February 19, 2019 8:44 AM

    If anyone still cares BTW I came across this today; the ALTER or CREATE PROC statements don't work from remote SSMS. Run them directly on the server and you're OK.

    Can you please post a link to the source of this information?

    Just personal experience, which makes it not definitive of course, simply the symptoms were the same and that’s what I found worked.

Viewing 14 posts - 1 through 14 (of 14 total)

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