pass variable from command line to xp_send

• rae van dyken

  Grasshopper

  Points: 15

  More actions

  July 3, 2002 at 1:47 pm

  #79396

  I am a newbie so please forgive a basic question.

  I need to send an e-mail back to the specific user who initiated a SQL Server Agent job. The

  job exists and is fixed except for the user’s e-mail address. Getting the job to start via

  OSQL has worked great but I can’t seem to get that a value to the @recipients arguement of

  xp_sendmail.

  Thanks to all in advance for your help. My code is shown below.

  Rae Van Dyken, MCP NT & 2000

  cONTENTS OF COMMAND LINE / BATCH FILE

  -----------------------------------------------------

  Osql -S fls03 -E -i

  “O:\INFOTECH\GROUP\SalesDownloadRewrite-Mar2002\testemail.txt”

  --------------------------------------------------------

  Contents of testemail.txt

  ------------------------------------

  Use msdb

  Exec sp_start_job @job_name = ‘atestemail’

  Go

  exit -------------------------------------------------------

  Job step in SQL Server agent “ that doesn’t work”

  exec xp_sendmail @recipients = ‘%eaddress%’, @message = ‘Sales Analysis Update has completed successfully’

  Rae Van Dyken

  American Renolit Corporation

  Voice: (219)324-6886 x254

  Fax: (219)324-1516

  ravandyken@americanrenolit.com

• danw

  SSCommitted

  Points: 1842

  More actions

  July 7, 2002 at 11:44 pm

  #432158

  Have you tried generating your testemail.txt file from a different batch file first to fill in the DOS variable %eaddress% with the value?

  echo Use msdb > testemail.txt

  echo Exec sp_start_job @job_name = ‘atestemail’ >> testemail.txt

  echo Go >> testemail.txt

  echo exit ---------------------------------------- >> testemail.txt

  echo exec xp_sendmail @recipients = ‘%eaddress%’, @message = ‘Sales Analysis Update has completed successfully’ >> testemail.txt