PowerShell command to Run SQL file Fails asking for a variable

• sunshine-587009

  SSCrazy Eights

  Points: 8981

  More actions

  October 1, 2020 at 2:52 pm

  #3792274

  Good morning team,

  I can run a .sql file that contains a SQLCMD command, with no errors via SSMS. This is a job script.

  When i call it to run from PowerShell it fails asking to define a variable when it should be a literal inside a job step. Is there any code i can change in either powershell command or sqlcmd so that it is read as a literal, within the job step?

  PowerShell Command

  Invoke-Sqlcmd -InputFile "D:\folder\Job.sql" | Out-File -FilePath "D:\folder\Output.log"

  Code within .sql file that fails (this is part of the job step)

  declare @cmd ='sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR))...

  Error

  Invoke-Sqlcmd : 'SRVR' scripting variable not defined

  ¤ §unshine ¤

• Jeffrey Williams

  SSC Guru

  Points: 90346

  More actions

  October 1, 2020 at 6:28 pm

  #3792336

  Why are you invoking a SQL command - and then within that script executing sqlcmd?  What is the purpose of this script?

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738043

  More actions

  October 1, 2020 at 6:52 pm

  #3792351

  Likely you need to show more code, as well as explain how you are executing both.