Power Shell extract from sql

I need a powershell script to update / replace a csv (although | may be better)from a microsoft SQL database with lines timestamped for the last 4 hours, I need it to do this every 2 min replacing the old data.
i have a couple  small snippets of code the 1st is to show the table location and where the file is going

Import-Module sqlps
$SQLquery='select * from CDISERV1.History0218.dbo.SignalHistory'
$result=invoke-sqlcmd -query $SQLquery -serverinstance fidev360bi02 -database bank03 
$result |export-csv Z:\OP\CSV\HIST.CSV -notypeinformation

Open in new window


[String] $sql = "SELECT [SignalID],[AccountID],[OperCode],[PortNum],[LineNum],[AlarmDate],[AlarmCode],[AlarmZones],[RawData] FROM [master].[sys].[databases] ORDER BY [AlarmDate];";
$result = $db.ExecuteWithResults($sql);
$table = $result.Tables[0];

foreach ($row in $table)
{
    Write-Host $row.Item("name") $row.Item("create_date");
}

Open in new window

LVL 1
csePixelatedAsked:
Who is Participating?
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Sure you can do it that way. If you don't have access to sqlps module or sqlserver module.  If you do there is a better way.

First way without it uses your code with one more thing.

$ds.Tables[0] | Export-Csv -Path c:\path\csv.csv -NoTypeInformation

Open in new window


With access to SQLPS or SqlServer modules
import-module sqlps # or sqlserver

$ServerName = "cdiserv1"
$Query = @"SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, 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.ID AND SignalHistory.AlarmZones = Zones.Number
   LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -4, GETDATE())
ORDER BY AlarmDate
"@

$dt = Invoke-SqlCmd -ServerInstance $ServerName -Database History0218 -Query $Query  # if sqlserver module then you would add -outputAs DataTable
$dt | Export-Csv -Path c:\path\csv.csv -force # use force if you want to overwrite the data in the CSV.

Open in new window

0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Once you have the right query (with the right criteria for the last 4 hours) then you can just run what you have in the first window and the Export-CSV will overwrite the file with the new data.

Is there something else you are looking to do?
0
 
csePixelatedAuthor Commented:
I suppose not, I guess I need to break down the issues I need addressed into smaller parts.
0
WEBINAR: 10 Easy Ways to Lose a Password

Join us on June 27th at 8 am PDT to learn about the methods that hackers use to lift real, working credentials from even the most security-savvy employees. We'll cover the importance of multi-factor authentication and how these solutions can better protect your business!

 
DBAduck - Ben MillerPrincipal ConsultantCommented:
The reality from my perspective is that once you can generate the right query/result set, exporting it is the easy part using PowerShell.  I have been doing that forever and it is simple with all the tools out there now.  If you get to the point that you have the exports, timeframes, etc. defined, I am happy to help you get things exporting the right way and keeping your files updated.
0
 
csePixelatedAuthor Commented:
It may take me a bit ill leave the code here when i have it, ty DBAduck.
0
 
csePixelatedAuthor Commented:
So my sql query is as follows
SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, 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.ID AND SignalHistory.AlarmZones = Zones.Number
   LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -4, GETDATE())
ORDER BY AlarmDate

Open in new window

How would i go about adding that in a ps1 script, and having that ps1 script export to Z:\OP\CSV\HIST.CSV?
0
 
csePixelatedAuthor Commented:
sorry im quite new to powershell, i tried to run the following but i got the error "the 'from' keyword is not supported in this version of the language."

Import-Module sqlps
[String] $sql =SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, 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.ID AND SignalHistory.AlarmZones = Zones.Number
   LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -4, GETDATE())
ORDER BY AlarmDate

Open in new window

0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
I will respond with the solution in about 15 minutes
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
You put “ quotes around the select statement.
0
 
csePixelatedAuthor Commented:
I missed the " around the query thanks DBAduck.
I got this to work, its still displaying the info, i hope it won't take as long to export as it takes to write in powershell, perhaps there is a better way? as it is listing each row as a paragraph
eg
AcctLineCode : TE
AcctNum         :          1234
Name               : bob dole
etc
$ServerName = "cdiserv1"
$Query = "SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, 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.ID AND SignalHistory.AlarmZones = Zones.Number
   LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -4, GETDATE())
ORDER BY AlarmDate"

#Timeout parameters
$QueryTimeout = 120
$ConnectionTimeout = 30

#Action of connecting to the Database and executing the query and returning results if there were any.
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$DatabaseName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables

Open in new window

0
 
csePixelatedAuthor Commented:
im getting error.png
import-module sqlps # or sqlserver

$ServerName = "cdiserv1"
$Query = @"SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, 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.ID AND SignalHistory.AlarmZones = Zones.Number
   LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -4, GETDATE())
ORDER BY AlarmDate
"@

$dt = Invoke-SqlCmd -ServerInstance $ServerName -Database History0218 -Query $Query  # if sqlserver module then you would add -outputAs DataTable
$dt | Export-Csv -Path Z:\OP\CSV\HIST.CSV -force # use force if you want to overwrite the data in the CSV.

Open in new window

0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Go to the beginning of the select statement and put the SELECT on a new line after the “
0
 
csePixelatedAuthor Commented:
it would seem i dont have the module necessary
error.png
import-module sqlps # or sqlserver

$ServerName = "cdiserv1"
$Query = @"
SELECT Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, 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.ID AND SignalHistory.AlarmZones = Zones.Number
   LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -4, GETDATE())
ORDER BY AlarmDate
"@

$dt = Invoke-SqlCmd -ServerInstance $ServerName -Database History0218 -Query $Query  # if sqlserver module then you would add -outputAs DataTable
$dt | Export-Csv -Path Z:\OP\CSV\HIST.CSV -force # use force if you want to overwrite the data in the CSV.

Open in new window


I was able to get it to work with
$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 Subscriber.AcctLineCode, Subscriber.AcctNum, Subscriber.Name,SignalHistory.AlarmDate, Codes.Description, 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.ID AND SignalHistory.AlarmZones = Zones.Number
   LEFT JOIN [MISCELLANEOUS].[dbo].[Signal Codes] Codes ON SignalHistory.AlarmCode = Codes.Code
WHERE SignalHistory.AlarmDate >= DATEADD(HOUR, -4, GETDATE())
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" -NoTypeInformation

Open in new window


so i have replaced the last line with the below, to be sure.
$dt | Export-Csv -Path Z:\OP\CSV\HIST.CSV -force # use force if you want to overwrite the data in the CSV.

Open in new window

0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Yup that works. Add the -NoTypeInformation too
0
 
csePixelatedAuthor Commented:
good point, ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.