October 27, 2008 at 1:03 am
Hi All
How do i pass parameters to command prompt through sql?
Thanks
It's better to fail while trying, rather than fail without trying!!!
October 27, 2008 at 5:36 am
do you mean
'xp_cmdshell'
have a look on books on line or type into google, I think this might be what you are looking !
🙂
October 28, 2008 at 1:16 am
Thanks for your response.
xp_cmdshell it's part of my code but now the tricky part is, i'm using replication then i have a job that validates all subscribed articles, if one of the articles are out of sync normally i will use a tool within SQL05 called Tablediff.exe, then i will edit the cmd file with neccessary parameters like your source server and destination server etc but now i need to automate that process. what should happen is after the job that validates the articles has completed then follow the second step where now the other job should read the distribution database table named MS_distribution_history to see if there are aticles out of sync, if there is then i need to pass those parameters to a bat file located on the publisher with parameters. Then the tablediff will generate a SQL code.
I hope i make sense
Thanks!!!
It's better to fail while trying, rather than fail without trying!!!
October 28, 2008 at 3:28 am
Hi,
You could build your command string containing parameters for the batch file in a variable (safely assuming it's under 8000 characters) and then execute it using xp_cmdshell.
This code, if saved in a batch file called setServer.bat, is a example of simple parameter handling. If you have a search about for batch file parameters you can find more info on this.
echo off
echo Setting the server...
echo ...Server Set to: %1
Then your SQL code can do something like:
CREATE PROC spExecCmd(
@cmdParam1 VARCHAR(200)
)
AS
BEGIN
DECLARE @cmdString VARCHAR(300)
SET @cmdString = '\\myMachine\myShare\setServer.bat ' + @cmdParam1
EXEC master.dbo.xp_cmdshell @cmdString
END
GO
EXEC spExecCmd 'myServer'
This would echo the statements:
Setting the server...
Server Set to: myServer
HTH,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply