Using MASTER..XP_CMDSHELL to run store proc

  • Hi,

    Using the command prompt we use

    1. Open Run window

    2. Type SQLCMD enter

    3. Type USE TestDB enter

    4. Type GO enter

    5. EXEC dbo.sp_InsertTempValue 101

    6. Type GO enter

    The following steps insert values 101 in a certain table

    How can we do using MASTER..XP_CMDSHELL

    Following are the steps I have tried

    EXEC MASTER..XP_CMDSHELL 'SQLCMD'

    EXEC MASTER..XP_CMDSHELL 'USE TOOLDB'

    EXEC MASTER..XP_CMDSHELL 'GO'

    EXEC MASTER..XP_CMDSHELL 'EXEC dbo.InsertTempValue 10'

    EXEC MASTER..XP_CMDSHELL 'GO'

    But this doesn't execute

    Any other method to achieve this

  • The way to do this using xp_cmdhsell is as follows:

    EXEC master.dbo.xp_cmdshell 'sqlcmd -S ServerNameComesHere -E -d ToolDB -Q "EXEC dbo.InsertTempValue 101" ';

    Could you let us know why you are using xp_cmdshell to execute a stored procedure - depending on what you want to achieve there might be other options available.

  • I think I would try to do it something like this:

    DECLARE @myCmd VARCHAR(4000)

    SET @myCmd = 'sqlcmd -d AdventureWorks2008R2 -Q "SELECT FirstName, LastName FROM Person.Person WHERE LastName LIKE 'Whi%';" '

    EXEC master..XP_CMDSHELL @myCmd

  • rhd110 (3/1/2012)


    Hi,

    Using the command prompt we use

    1. Open Run window

    2. Type SQLCMD enter

    3. Type USE TestDB enter

    4. Type GO enter

    5. EXEC dbo.sp_InsertTempValue 101

    6. Type GO enter

    The following steps insert values 101 in a certain table

    How can we do using MASTER..XP_CMDSHELL

    Following are the steps I have tried

    EXEC MASTER..XP_CMDSHELL 'SQLCMD'

    EXEC MASTER..XP_CMDSHELL 'USE TOOLDB'

    EXEC MASTER..XP_CMDSHELL 'GO'

    EXEC MASTER..XP_CMDSHELL 'EXEC dbo.InsertTempValue 10'

    EXEC MASTER..XP_CMDSHELL 'GO'

    But this doesn't execute

    Any other method to achieve this

    I wouldn't. Just open a session and typw the SQL. xp_cmdshell is used in SQL to run command line statements that cannot be run in SQL. Why would you use SQL to send SQL to command line?

    Jared
    CE - Microsoft

Viewing 4 posts - 1 through 3 (of 3 total)

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