0
votes

I am running a powershell script for 'dropping few databases' that are not in sync in my secondary server. (please refer the code snippet below)

[CmdletBinding()]
Param(
[Parameter(Mandatory = $True)] $DatabaseName
)

$serverInstanceP = "SQLServerTest"
Write-Host "Primary Server Instance is:"$serverInstanceP
#Validating whether the database name is empty string or `NULL` value
if (![string]::IsNullOrEmpty($DatabaseName)) {
    #Validating the presence of any special charater mentioned in the database name except "_" (uderscore)
    if ($DatabaseName -notmatch "[^a-zA-Z0-9_]") {
        Write-Host "A valid database has been provided, which is not null, nor an empty string neither contain any special character"
    }
    else {
        Write-Warning "Database name should not contain any special character"
    }
}
else {
    Write-Warning "Database name cannot be NULL"
}
Write-Host $DatabaseName "is the current database"

$syncQuery = "
:Connect SQLServerTest
USE [master]
GO
    DROP DATABASE [$($DatabaseName)];
GO "

Invoke-Sqlcmd -ServerInstance $serverInstanceP -Database master -Query $syncQuery -QueryTimeout 1200

While running the above script it is giving me an error 'ParserError: (:) [Invoke-Sqlcmd], ParserException'.

Especially ':Connect SQLServerTest' Do I need to send my query through @' '@

But when I run the same query on the database server through SQLCMD, it is working fine.

1
Why the line ":Connect SQLServerTest"? - SAS
There is a failover server as well, so I want to drop the database from both the server. So as a part of this activity, I am trying to drop it from the secondary server - Jyoti Prakash Mallick
The ":CONNECT" command is not supported, hence the parser error. - SAS
If you want to drop from two servers, you could first figure out first which one is active? - SAS
Ok gotcha, Thank you. Make sense as well - Jyoti Prakash Mallick

1 Answers

0
votes

Unfortunately, the command ":CONNECT" is not supported by "invoke-sqlcmd".

https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

The connection is done with the parameters "-ServerInstance $serverInstanceP -Database master".