Executing stored procedure in power shell script

Santosh Umarani 81 Reputation points
2021-09-16T13:14:54.653+00:00

Hi,

I have a stored procedure "sp_TestBackupDatabase" and executing in powershell like below:

$query = "exec sp_TestBackupDatabase @dataBaseName=" + $dataBase + ", @backupType=" + $backupType + ", @backupFolder='" + $backupFolder + "'"
$emailContent += sqlcmd.exe -S serverame\SQLEXPRESS2014 -E -V 11 -d master -Q $query
$emailContent += "nLASTEXITCODE: $LASTEXITCODEn`n"

When I directly execute the stored procedure, its working fine. But in powershell, I am getting exit code as 16 and error message says stored procedure "sp_TestBackupDatabase" could not be found.

Can anyone let me know what is wrong with command "sqlcmd.exe -S serverame\SQLEXPRESS2014 -E -V 11 -d master -Q $query" ?
I have recently reinstalled newer version of SQL server management studio and changed the path of power shell script.
Before these changes, this script was working fine.

I basically want to take back up of database and delete back ups older than two days. So any point of time I should have only two back ups.
This I want it to automate through task or any mechanism. If you have any better solution please let me know.

Kindly waiting for your response.

Thanks,
Santosh

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,755 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,901 Reputation points
    2021-09-16T13:28:50.05+00:00

    sqlcmd.exe -S serverame\SQLEXPRESS2014 -E -V 11 -d master -Q $query

    With the parameter "-d master" you connect to the system database "master"; is the stored procedure in there? By the error message I guess not.
    If not, then use the database name where you created the stored procedure.

    P.S.: Using SQL Server PowerShell is easier to handle.

    0 comments
  2. YufeiShao-msft 7,056 Reputation points
    2021-09-17T06:08:38.763+00:00

    Hi @Santosh Umarani ,

    -S connect to the database engine by specifying the instance name, protocol, IP address, TCP/IP port number and so one, perhaps you are connected to the wrong object?
    https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15

    0 comments
  3. Santosh Umarani 81 Reputation points
    2021-09-17T12:30:39.857+00:00

    Thanks OlafHelper-2800. I was connecting to wrong DB instance. It worked after changing.

    However, I want to copy to folder "E:\DBBackup". I am passing the value to @backupFolder as "E:\DBBackup". But the back up is getting copied to "E:\" drive only.
    I am using the stored procedure mentioned in the Microsoft site: https://raw.githubusercontent.com/microsoft/mssql-support/master/sample-scripts/backup_restore/SQL_Express_Backups.sql

    Can suggest what could be the issue ?
    Kindly waiting for your response.

    0 comments