CODEWITHSUNDEEP
powershell
Immortal
Immortal

Reputation: 1180

Incorrect Syntax in Nested Loop

I have a power-shell script to drop databases that have no connections in the last 30 days. I have coded the script as below:

$SQLInstances = "sql2016", "sql2014", "sql2012"

$SQLQuery = "exec usp_ConnectionsCount"

foreach ($sqLInstance in $SQLInstances) {
    Invoke-Sqlcmd -ServerInstance $sqLInstance -Database "master" -Query $SQLQuery
}

Write-Output "Any database that has not been used in the last 30 days will be dropped"
Write-Output "Here is a list of SQL Instances being monitored:" $SQLInstances

$DBQuery =  "SELECT Name as DatabaseName FROM [SQLConnections] WHERE number_of_connections = 0 AND DATEDIFF(day,  timestamp, GetDate()) > 30"

$DropDb = "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '$UnusedDB') DROP DATABASE $UnusedDB"

foreach ($sqLInstance in $SQLInstances) {
    $Databases = Invoke-Sqlcmd -ServerInstance $sqLInstance -Database "master" -Query $DBQuery

    Write-Output "List of Databases to be dropped"
    $Databases

    foreach ($UnusedDB in $Databases) {
        Invoke-Sqlcmd -ServerInstance $sqLInstance -Database "master" -Query $DropDb
    }
}

My code looks cool to me but when I run it, it says

Invoke-Sqlcmd : Incorrect syntax near 'DATABASE'.

I can't figure out where the problem is. Additionally the code for creating SQLConnections table is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SQLConnections](
 [server] [nvarchar](130) NOT NULL,
 [name] [nvarchar](130) NOT NULL,
 [number_of_connections] [int] NOT NULL,
 [timestamp] [datetime] NOT NULL
) ON [PRIMARY]
GO

And the code for the usp_ConnectionsCount is as follows:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE usp_ConnectionsCount 
AS
BEGIN
 SET NOCOUNT ON;
INSERT INTO [SQLConnections]
  SELECT @@ServerName AS server
 ,NAME AS dbname
 ,COUNT(STATUS) AS number_of_connections
 ,GETDATE() AS timestamp
FROM sys.databases sd
LEFT JOIN master.dbo.sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1
  AND 4
GROUP BY NAME
END

Can someone tell me where the incorrect syntax is coming from? I have been looking at this for hours and cant figure out?

Upvotes: 0

Views: 92

Answers (2)

Immortal
Immortal

Reputation: 1180

So, I have abandoned the idea of letting powershell do most of the work. I have managed to use SQL code to do most of the work:

$SQLInstances = "sql2016", "sql2014", "sql2012"

Write-Output "Any database that has not been used in the last 30 days will be dropped"
Write-Output "Here is a list of SQL Instances being monitored:" $SQLInstances
$DropDb = " --Exec stored procedure to gather information regarding DB connections
            EXEC usp_fpmConnectionsCount

            IF OBJECT_ID('tempdb..#TempSQLConnections1') IS NOT NULL DROP TABLE #TempSQLConnections

            SELECT Distinct(fund.name)
            INTO #TempSQLConnections 
            FROM FundSQLConnections AS fund
            INNER JOIN
            sys.databases as sdb
            on fund.name = sdb.name
            WHERE number_of_connections = 0 AND DATEDIFF(day,  timestamp, GetDate()) > 30 

            GO
            DECLARE @dbnames nvarchar(max)
            DECLARE @statement nvarchar(max)
            SET @dbnames = ''
            SET @statement = ''
            SELECT @dbnames = @dbnames + ',[' + name + ']' from #TempSQLConnections 

            IF len(@dbnames) = 0
                begin
                print 'no databases to drop'
                end
            ELSE
                begin
                set @statement = 'drop database ' + substring(@dbnames, 2, len(@dbnames))
                print @statement
                exec sp_executesql @statement
                print @dbnames + 'Has been dropped'
                end

            DROP TABLE #TempSQLConnections "

    foreach($sqLInstance in $SQLInstances) {

        Invoke-Sqlcmd -ServerInstance $sqLInstance -Database "master" -Query $DropDb

    }

Works like a Hammer!!!!!!!!!!!!

Upvotes: 0

Moerwald
Moerwald

Reputation: 11254

You've a string quoting problem in :

$DropDb = "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '$UnusedDB') DROP DATABASE $UnusedDB"

Powershell won't replace variables in a single quoted string, there Powershell won't replace $UnusedDB and keeps it as text in the string.

You can change it to:

$DropDb = "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = `"$UnusedDB`") DROP DATABASE $UnusedDB"

You can find more about quoting rules here.

You can also try formatting the string via the -f operator, documentation is here.

Hope that helps.

Upvotes: 1

Related Questions