Reading a variable from sqlcmd script back into a stored procedure

• Weegee2017

  SSC Eights!

  Points: 994

  More actions

  November 15, 2019 at 12:04 pm

  #3697996

  Hi there

  I have written a stored procedure which generates a statement to execute a SQLCMD script

  as follows:

  sqlcmd -E -h-1  -S LONDEVSQL01

  -dStaging

  -i"\\LONDEVSQL01 \Segmentation\Segmentation1\ScriptToRunOnLiveServer\sqlcmdTransferExtract.sql"

  -v varOfferingFileName="01_ExtractImportParameters.csv"

  This is the content of sqlcmdTransferExtract.sql

  Insert into StoredProcedureRunTracker

  (StoredProcedureName,JobID,UserID,DefectNumber,[Timestamp],[Parameters],Results)

  Values

  ('sSupportGenerateExtractFile',-1,9026,0,GetDate(),'','')

  set @StoredProcedureRunTrackerID = SCOPE_IDENTITY()

  Exec sSupportGenerateExtractFile @StoredProcedureRunTrackerID = @StoredProcedureRunTrackerID,

  @OfferingFileName = '$(varOfferingFileName)'

  How can I pass the variable @StoredProcedureRunTrackerID  back to the stored procedure which called

  the script, so that I can use @StoredProcedureRunTrackerID ?

  Can I do something like

   

  sqlcmd -E -h-1  -S LONDEVSQL01

  -dStaging

  -i"\\LONDEVSQL01 \Segmentation\Segmentation1\ScriptToRunOnLiveServer\sqlcmdTransferExtract.sql"

  -v varOfferingFileName="01_ExtractImportParameters.csv"

  - o @StoredProcedureRunTrackerID  = '$(StoredProcedureRunTrackerID)'

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  November 15, 2019 at 7:53 pm

  #3698160

  Can you direct the output from the SQLCMD script to a text file, then open it using OpenRowset??

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills