1

I have to schedule a jams job for sending a report to a client. But my job failed and I get an error in the log file

The term 'sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

This is my PowerShell script:

param (
    [string]$DatabaseServer = "COREP02P.centiv.com",
    [string]$SFTPPath = "/home/cdibrm02/",
    [string]$SFTPServer = "test-externalftp.prudential.com",
    [string]$SFTPCredential = "PrudentiaReport-SFTP-Test",
    [string]$JamsServerName = "jams01d.brandmuscle.local"
)

Import-Module JAMS

$localDataDirectory = "C:\temp\$(New-Guid)"
New-Item $localDataDirectory -itemType directory

$Prudential_EmailCampaignReport = "$localDataDirectory\Prudential_EmailCampaignReport.txt"
$Prudential_EmailCampaignReportFile = "$localDataDirectory\Prudential_EmailCampaignReportFile.txt"

# run reports
sqlcmd -S $Databaseserver -E -d "CentivPos" -Q "Exec [dbo].[Prudential_EmailCampaignReport]" -o $Prudential_EmailCampaignReport -W -s "," -h -1

(get-content $Prudential_EmailCampaignReport | select -Skip 1) | select-string -pattern "Warning" -notmatch |  select-string -pattern "affected" -notmatch  | select-string -pattern "--------" -notmatch  | set-content $Prudential_EmailCampaignReport

$Data = Get-Content $Prudential_EmailCampaignReport -Raw
$Cleanup = $Data.Replace("`n`r`n","").Replace("`n`n","").Replace("`r","")
$Cleanup = $Cleanup | Set-Content $Prudential_EmailCampaignReport -Force

#get filename
$filename = (Get-Content $Prudential_EmailCampaignReport -First 1).Substring(44, 45)
Raymond Camden
  • 10,661
  • 3
  • 34
  • 68
Sunil Singh
  • 31
  • 1
  • 3
  • Does this answer your question? [Invoke-Sqlcmd' is not recognized as the name of a cmdlet](https://stackoverflow.com/questions/37825030/invoke-sqlcmd-is-not-recognized-as-the-name-of-a-cmdlet) – Michael Freidgeim May 11 '21 at 01:21

2 Answers2

0

You need to locate the executable path for sqlcmd and add it to your user path environment variable. See this link.

Alternatively you can create the path to the exe as a variable for your script, like so:

$sqlCmd = "C:\Path\To\sqlcmd.exe"

And then you could call it like so in your script

$sqlcmd -S $Databaseserver -E -d "CentivPos" -Q "Exec [dbo].[Prudential_EmailCampaignReport]" -o $Prudential_EmailCampaignReport -W -s "," -h -1
AutomatedOrder
  • 501
  • 4
  • 14
  • Could we use Invoke-Sqlcmd at the place of $sqlcmd – Sunil Singh May 19 '20 at 19:46
  • Yes you could. You would need to make sure the SQLPS module is loaded, as well as rewrite the line of code so that the parameter names match what the command expects. See https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps – AutomatedOrder May 19 '20 at 20:21
0

Alternatively , we can use Invoke-sqlcmd to run any sql statements.

sqlcmd is an alias name for Invoke-sqlcmd

Ex:

Invoke-Sqlcmd -ServerInstance $Databaseserver -Database $database -Query $sql -QueryTimeout "2000"

dronavalli. srichandra
  • 1
  • 2