Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using MASTER..XP_CMDSHELL to run store proc Expand / Collapse
Author
Message
Posted Thursday, March 1, 2012 6:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:03 AM
Points: 42, Visits: 267
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
Post #1260093
Posted Thursday, March 1, 2012 7:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
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.



Post #1260122
Posted Thursday, March 1, 2012 7:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:27 PM
Points: 264, Visits: 230
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
Post #1260149
Posted Thursday, March 1, 2012 8:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:07 PM
Points: 2,691, Visits: 3,371
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?


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1260233
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse