asked on

Adjust ps1 sql script as new databases are added

the below works for me, however every month (UTC) a new database is created in the format [HistoryMMYY], Im looking for a way i can change the database to the current when it changes, so when the new database [History0318] is started the loop will automatically adjust to using it, is there a way to accomplish this?

#infinite loop
    while(1)
{
$DBServer = "cdiserv1"
$databasename = "History0218"
$Connection = new-object system.data.sqlclient.sqlconnection
$Connection.ConnectionString ="server=$DBServer;database=$databasename;trusted_connection=True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlQuery = "SELECT IsNull(Subscriber.AcctLineCode, 'XX'), Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, SignalHistory.AlarmZones, IsNull(Zones.Description, ''), Subscriber.Address, Subscriber.City, Subscriber.State, Subscriber.Zip, SignalHistory.PortNum, SignalHistory.LineNum, SignalHistory.RawData
FROM [History0218].[dbo].[SignalHistory] SignalHistory
   LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
   LEFT JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones ON SignalHistory.AccountID = Zones.AccountID AND LTRIM(SignalHistory.AlarmZones) = LTRIM (Zones.Number)
   LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY AlarmDate"
    
$Connection.open()
$SqlCmd.CommandText = $SqlQuery
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$SqlCmd.Connection = $Connection
$dt = New-Object System.Data.DataTable
$SqlAdapter.Fill($dt) | Out-Null
$Connection.Close()
$dt | Export-Csv -Path Z:\OP\CSV\HIST.CSV -force -NoTypeInformation # use force if you want to overwrite the data in the CSV.

#just for butler
$DBServer = "cdiserv1"
$databasename = "History0218"
$Connection = new-object system.data.sqlclient.sqlconnection
$Connection.ConnectionString ="server=$DBServer;database=$databasename;trusted_connection=True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlQuery = "SELECT IsNull(Subscriber.AcctLineCode, 'XX'), Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, SignalHistory.AlarmZones, IsNull(Zones.Description, ''), Subscriber.Address, Subscriber.City, Subscriber.State, Subscriber.Zip, SignalHistory.PortNum, SignalHistory.LineNum, SignalHistory.RawData
FROM [History0218].[dbo].[SignalHistory] SignalHistory
   LEFT JOIN [SUBSCRIBER].[dbo].[Subscriber Data] Subscriber ON SignalHistory.AccountID = Subscriber.AccountID
   LEFT JOIN [SUBSCRIBER].[dbo].[Zone Lists] Zones ON SignalHistory.AccountID = Zones.AccountID AND LTRIM(SignalHistory.AlarmZones) = LTRIM (Zones.Number)
   LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -1, GETUTCDATE())AND (Subscriber.AcctLineCode ='DS'OR Subscriber.AcctLineCode ='DSA')
ORDER BY AlarmDate"
    
$Connection.open()
$SqlCmd.CommandText = $SqlQuery
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$SqlCmd.Connection = $Connection
$dt = New-Object System.Data.DataTable
$SqlAdapter.Fill($dt) | Out-Null
$Connection.Close()
$dt | Export-Csv -Path Z:\OP\CSV\HISTDS.CSV -force -NoTypeInformation # use force if you want to overwrite the data in the CSV.

   start-sleep -seconds 10

}

Open in new window

Shaun Kline

Since you are connecting to the history database for that month, you do not need to include the database name in your SQL script. In the FROM clause, just remove "[History0218]."

As for the script, you should be able to build "$databasename = "History0218" " by getting the current month and year and appending them to "History".

ASKER CERTIFIED SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csePixelated

ASKER

As far as i can see its working, thanks