question

SantoshUmarani-1390 avatar image
0 Votes"
SantoshUmarani-1390 asked SantoshUmarani-1390 commented

Executing stored procedure in power shell script

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: $LASTEXITCODE nn"

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered

Hi @SantoshUmarani-1390,

-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://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SantoshUmarani-1390 avatar image
0 Votes"
SantoshUmarani-1390 answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.