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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.