Running bat file using T-SQL

Hi,
I am trying to run a bat file which starts the merge synchronization between the client and the server.  Running the bat file by double clicking the file works without any errors.
The process failed when I am trying to execute using T-SQL:
execute master..xp_cmdShell 'c:\bin\PullMergeSubscription.bat'
below is the content in the bat file:
/*
@ECHO OFF

SET Subscriber=%computername%\EDS
SET Publisher=Server01
SET PublisherDB=ServerDB
SET SubscriberDB=ClientDB
SET PubName=MergePublication
SET log=c:\bin\test\MergLog.txt

:Synchronize
rem ECHO Synchronize the subscription
"C:\Program Files\Microsoft SQL Server\90\COM\REPLMERG.EXE" -Publisher %Publisher% -Subscriber %Subscriber% -Distributor %Publisher% -PublisherDB %PublisherDB% -SubscriberDB %SubscriberDB% -Publication %PubName% -PublisherSecurityMode 1 -OutputVerboseLevel 0 -Output %log% -SubscriberLogin TestLogin -SubscriberPassword ****** -SubscriptionType 1 -DistributorSecurityMode 1 -Hostname %computername%
IF ERRORLEVEL 1 GOTO error_stop
rem ECHO .
rem PAUSE


REM ECHO Synchronisation complete Successfullly
rem PAUSE

goto :EOF

:error_stop
rem echo Error: Program stopped
rem PAUSE
*/

the process failed with the following error:

2010-04-19 03:50:41.796 The timestamps prepended to the output lines are expressed in terms of UTC time.
2010-04-19 03:50:41.796 User-specified agent parameter values:
                  -Publisher Server01
                  -Subscriber TestUser
                  -Distributor Server01
                  -PublisherDB ServerDB
                  -SubscriberDB ClientDB
                  -Publication MergePublication
                  -PublisherSecurityMode 1
                  -OutputVerboseLevel 0
                  -Output c:\bin\test\MergLog.txt
                  -SubscriberLogin TestLogin
                  -SubscriberPassword ********
                  -SubscriptionType 1
                  -DistributorSecurityMode 1
                  -Hostname Client01
2010-04-19 03:50:42.062 The process could not connect to Distributor 'Server01'.

Can some one help so I can start merge synchronization using stored procedure.

Regards,

CL
ProgrammersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
Hi there,

I would check the account under which the SQL Agent is running (if scheduled) or SQL Server itself (if query is run manually).  When you run the batch file, it will be authenticating as your Windows user, but not when run by SQL Server/Agent.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
you need to include login info in your script
Replication Merge Agent
 http://msdn.microsoft.com/en-us/library/ms147839.aspx
--
-DistributorLogindistributor_login
Is the Distributor login name.
 -DistributorPassworddistributor_password
Is the Distributor password.
-DistributorSecurityMode [ 0| 1]
Specifies the security mode of the Distributor. A value of 0 indicates SQL Server Authentication Mode (default), and a value of 1 indicates Windows Authentication Mode.
----
see examples
http://msdn.microsoft.com/en-us/library/ms147343.aspx 
 
http://www.sqlservercentral.com/Forums/Topic634756-291-1.aspx#bm637307 
 
set @cmdset @cmd='"C:\Program Files\Microsoft SQL Server\90\COM\REPLMERG.EXE" -Publication ' + @publication + ' -Publisher ' + @publisher + ' -Subscriber ' + @publisher + ' -Distributor  ' + @publisher + ' -PublisherDB ' + @publicationDB + ' -SubscriberDB ' + @subscriptionDB +' -PublisherSecurityMode 1 -PublisherLogin ' + @login + ' -PublisherPassword '+ @password + '-OutputVerboseLevel 2 -SubscriberSecurityMode 1 -SubscriberLogin  ' + @login + ' -SubscriberPassword ' + @password + ' -SubscriptionType 1 -DistributorSecurityMode 1 -DistributorLogin  ' + @login + '  -DistributorPassword  ' + @password + '  -Validate 3 -ParallelUploadDownload 0 '

='"C:\Program Files\Microsoft SQL Server\90\COM\REPLMERG.EXE" -Publication ' + @publication + ' -Publisher ' + @publisher + ' -Subscriber ' + @publisher + ' -Distributor  ' + @publisher + ' -PublisherDB ' + @publicationDB + ' -SubscriberDB ' + @subscriptionDB +' -PublisherSecurityMode 1 -PublisherLogin ' + @login + ' -PublisherPassword '+ @password + '-OutputVerboseLevel 2 -SubscriberSecurityMode 1 -SubscriberLogin  ' + @login + ' -SubscriberPassword ' + @password + ' -SubscriptionType 1 -DistributorSecurityMode 1 -DistributorLogin  ' + @login + '  -DistributorPassword  ' + @password + '  -Validate 3 -ParallelUploadDownload 0 '

 --
you may need to check
 
HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted Domains or Across the Internet
http://support.microsoft.com/kb/321822 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.