Post reply

Passing SQL Parameters to command prompt

  • October 27, 2008 at 1:03 am

    #154853

    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

    #889908

    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

    #890419

    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

    #890460

    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,


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

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

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