Newb - Is what I want possible? SQL automation

PowerShell Help
1

Hi All,

Before this morning I was completely oblivious to the fact that powershell existed so please excuse the myriad of perhaps obvious questions I need to ask.
I have a problem and I simply cannot solve it through any of my existing tools which is why I have hunted down powershell as a solution but I don’t know how to go about solving said problem with powershell…

I have been looking on the web but it is still completely baffling so I ask for your help in building my understanding of this.

I need to…

Connect to a web server 10.xx.xx.xx
Goto a database schema called “Interactive_Reporting”
run an SQL script (please see below) on the web server this script is called reportpositions.sql
export the results from that query to a network file location L:\Interactive_Reporting
if it could attach the date of this export that would be fantastic also…
The report I need to run at 04:00 am (system time each day) so somewhere I am thinking windows task scheduler will be required also?

Is this possible and if so how would I even go about attempting it?

Thank you in advance for any help…

SO the SQL Script is fairly meaty;

DECLARE @startdate CHAR(8)
DECLARE @finaldate CHAR(8)

SELECT @startdate=CONVERT(CHAR(8),GETDATE()-1,112),
@finaldate=CONVERT(CHAR(8),GETDATE(),112)

Select VL3ADVICE1NUM As “Advice Num”,
VL3advice1num as “Bargain Number”,
VL3CLIENTNUM As “Account”,
CLISHORTNAME2 As “Account Name 1”,
CLISHORTNAME1 As “Account Name 2”,
VL3DEPOTNUM As “Depot”,
DEPNAME1 As “Depot Name 1”,
DEPNAME2 As “Depot Name 2”,
VL3SECURITYNUM As “Security”,
SECDESCRIPTION1 As “Security Name 1”,
SECDESCRIPTION2 As “Security Name 2”,
VL3BROKERNUM As “Broker”,
PARNAME1 As “Broker Name”,
VL3DATE1 As “Trade Date”,
VL3DATE2 AS “Settlement Date”,
VL3certregistereddate AS “Trade Status”,
SUM(CAST(vl2beneficial AS NUMERIC (20,6)) - CAST(vl2settledbalance AS NUMERIC (20,6))) As “Unsettled Quantity”,
VL3certregistereddate AS “Unsettled Consideration”,
vl3consideration As “Consideration”,
vl3currency1 AS “Currency”,
vl3quantity as “Quantity”,
VL3MENUNUM As “Transaction Type”,
vl3currency1 AS “Country Of Register”,
VL3BROKERCOMMISSION AS “Broker Commission”,
VL3TIMESTAMP AS “Timestamp”
from t5vaultsl3 inner join t5client
on t5vaultsl3.vl3clientnum = t5client.clicode
INNER JOIN T5DEPOT
ON T5VAULTSL3.VL3DEPOTNUM = T5DEPOT.DEPNUM
INNER JOIN T5SECURITY
ON T5VAULTSL3.VL3SECURITYNUM = T5SECURITY.SECSECURITYNUM
INNER JOIN T5PARTICIPANT
ON T5VAULTSL3.VL3BROKERNUM = T5PARTICIPANT.PARPDRPARTICIPANTID
inner join t5vaultsl2
On T5VAULTSL3.vl3clientnum = t5vaultsl2.vl2clientnum And T5VAULTSL3.VL3SECURITYNUM = t5vaultsl2.vl2securitynum And T5VAULTSL3.vl3depotnum = t5vaultsl2.vl2depotnum
WHERE VL3DEPOTNUM IN (‘COFUNDNOMINE’, ‘COFUNDWRAP’, ‘WBSWRAP’)
AND LEFT(VL3timestamp,8) >= ‘20140203’
(VL3timestamp,8) Between @Startdate And @Enddate ’
AND VL3MENUNUM NOT LIKE ‘830’
AND VL3MENUNUM NOT LIKE ‘232’
AND VL3MENUNUM NOT LIKE ‘369’
AND VL3MENUNUM NOT LIKE ‘341’
AND VL3MENUNUM NOT LIKE ‘831’
GROUP BY VL3ADVICE1NUM, VL3CLIENTNUM, CLISHORTNAME2, CLISHORTNAME1, VL3DEPOTNUM, DEPNAME1, DEPNAME2, VL3SECURITYNUM, SECDESCRIPTION1, SECDESCRIPTION2,
VL3BROKERNUM, PARNAME1, VL3DATE1, VL3DATE2, VL3BROKERCOMMISSION, VL3TIMESTAMP, VL3CONSIDERATION, VL3REPORTCONSIDERATION, VL3CURRENCY1,
VL3CURRENCY2, VL3CURRENCY3, VL3QUANTITY, VL3MENUNUM, VL3CERTREGISTEREDDATE

2

hi,

That was quite a task to start with when you have never heard about Powershell before. My opinion is that it is possible to do with powershell, however a lot is undetermined and you need to dig into the details a bit more. My suggestion would be to split this task up into smaller subtasks/scripts/functions and get to work.

  1. What kind of “connection” to a web-server are we talking about? Are you connecting to a webservice, if so which type WDSL/WCF or are you planing on parsing html? Could you get the data you need from the webserver some other way?
  2. What kind of SQL-server are you connecting to (Microsoft/MYSQL/Oracle/etc)?
  3. How should the data “look like” when it is saved to the networks location? (XML/CSV/PlainText)
  4. How would you attach the date? In the filename or as an attribute in the file? (this is easy :-))

The scheduling bit is the last piece of the puzzle and when you have all the other stuff ready, it should be a breeze to start your powershell script/function/module with the taskscheduler.

So when you are satisfied with your investigation/design of the solution do one or a combination of the following items:

  1. Start to learn Powershell by reading tutorials/watching youtube/reading blogs (here at powershell.org)/buy Don Jones CBS nuggets on powershell (I have not watched them, however I suspect they are pretty good)
  2. Hire someone to help you achieve your goal/script/soution
  3. Get someone of your co-workers and create a taskforce and learn of each other while you develop the solution. You will probably learn faster and more if you work in a group.
  4. Look for an off-the-shelf-solution that comes close to what you want

Best of luck, it looks like a cool project. Please post any specific questions you might have.

Cheers

Tore

3

Agree with Tore, we need a lot more information about your scenario. Specifically what you’re saying about connecting to the web server.

If you need to get started, begin here with a connection to the SQL server. First, create $conn, on the second line fill in your connection string to this server.

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = “Data Source=SQLSERVER;Initial Catalog=SYSINFO;Integrated Security=SSPI;”

Check Don’s article here on getting started with SQL and PowerShell. You can see in his example how you’d go about defining your SQL Query (pull the code from reportpositions.sql and paste it into $cmd.commandtext) and you’ll end up with the results of your query stored in $cmd.

From there, you can export your data into whatever format you need using PowerShell commands. For example, to store the values from $cmd into a file called ‘Report Positions 03.10.2014.csv’ use this command:

$date = get-date -uformat “%m.%d.%Y”
$cmd | Export-CSV -Path “L:\Interactive_Reporting\Report Positions $date.csv”

So, that should be a number of clues to point you in the right direction.

4

Richard,

Why do you need to connect to the webserver to run the SQL in the first place? Is the database residing on the webserver?

If yes to the DB residing on the webserver then the first thing to do is to move it immediately to a dedicated server. You do not want to be running complex T-SQL scripts on your webserver.

Then where ever you run this use Task Scheduler, correct. Then check this
nice blog article with Github Repo and examples. Then dump out the results to your share as Stephen specified above.