powershell script for sql

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 3 months ago.

  • Author
    Posts
  • #5515

    by muzz at 2013-02-05 15:09:39

    Could any one will tell me how to create similar script like below in powershell

    Set ObjFso = CreateObject("Scripting.FileSystemObject")

    StrFileName = "C:\ErrorLog.txt"

    sConnectString = "Provider=SQL1;Integrated Security=SSPI;Data Source=VSQL1;Initial Catalog=testing"

    set oRS = CreateObject("ADODB.Recordset")

    oRS.Open "EXEC testiog.dbo.spSCOM_GetLegacyDirectoryMonitors", sConnectString

    Do While Not oRs.EOF

    sRecID = oRS("RecID")

    sDescription = oRS("Description")

    sFolderPathUNC = oRS("FolderPath_UNC")

    sThresholdMins = oRs("ThresholdMins")

    DIM file, Date2, Date1, timeDiff, testDate

    dim fso: set fso = CreateObject("Scripting.FileSystemObject")

    dim folder:set folder = fso.getFolder(sFolderPathUNC)

    If folder.files.Count > 1 Then

    'wscript.echo ("more than one")

    by DonJ at 2013-02-05 15:21:30

    Sure. I'm going to point you to three books, all of which cover this from different approaches. The sample code for all three is free, so you can just download and borrow.

    MoreLunches.com – the PowerShell Toolmaking book
    SapienPress.com – PowerShell TFM
    manning.com/jones2 – PowerShell In Depth

    You're not going to be using ADO, as you did in VBScript, because PowerShell is built on .NET. Instead you'll use the System.Data.Sql classes, and the code will look almost identical to C# code. You can even look up the SqlConnection and SqlCommand and SqlDataReader classes on MSDN and probably pilfer examples from there also.

    by DonJ at 2013-02-05 15:22:53

    Oh, there is also code in my free Historical and Trend Reporting ebook at powershellbooks.com. You could probably pilfer that code as well, and the whole book is free. There are just a couple of specific functions you'd be grabbing code from. You'll see the connection string stuff and know you're in the right place.

You must be logged in to reply to this topic.