execute a stored proc within a string through xp_cmdshell
-
Hello,
I am trying to execute a stored proc like this
Declare @cmd varchar(1000)
Declare @Path1 varchar (1000)
Declare @user_id varchar (50)
Declare @password varchar (50)
set @cmd = 'exec' + ' ' + stored_proc_name' + ', ' + @user_id +', ' + @password + ', ' + @Path1
exec master..xp_cmdshell @cmd
ERROR I am getting = 'exec' is not recognized as an internal or external command,
Please help !!!!:unsure:
Thanks,
-
Why are you trying to execute a stored procedure from the command line?
-
Hello Lynn,
Because I have to exec the same proc many times for different Paths and different Databases in multiple servers. so its easier to use and control variables in a table if they change in future.
-
Still doesn't answer the question. EXEC isn't a command or program that the operating system understands.
-
So how can I execute what I am trying to execute above from within SQL Server? Please...
-
I know this isn't going to answer your actual question but the one you posted is like this:
exec stored_proc_name @user_id, @password, @Path1 -
Leaving aside the question of whether this is a good idea or not, you can use the command line utility SQLCMD to log in to servers and run stored procedures that exist there:
http://msdn.microsoft.com/en-us/library/ms162773.aspx
So instead of building the string with 'EXEC' you'd build it with SQLCMD with the appropriate parameters.
And then again, I might be wrong ...
David Webb -
Lynn Thank YOU so much..... 4 eyes better than 2 ...:-)
David you are absolutely right... I will use this in another task though
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply