• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 826
  • Last Modified:

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
0
Programmers
Asked:
Programmers
1 Solution
 
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
 
Eugene ZCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now