Using MASTER..XP_CMDSHELL to run store proc

• rhd110

  Ten Centuries

  Points: 1062

  More actions

  March 1, 2012 at 6:55 am

  #252370

  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

• winash

  SSChampion

  Points: 11446

  More actions

  March 1, 2012 at 7:29 am

  #1453612

  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.

• mark_copley

  Ten Centuries

  Points: 1126

  More actions

  March 1, 2012 at 7:52 am

  #1453635

  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

• Jared

  SSC Guru

  Points: 61793

  More actions

  March 1, 2012 at 8:55 am

  #1453706

  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