powershell script for sql

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.