Running SQL command with powershell issue

Question

I have the following powershell command i have converted from VB script:

$conn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=SERVER; Initial Catalog=DATABASE; Integrated Security=SSPI”) 
$conn.Open() 
$cmd1 = $conn.CreateCommand() 
$cmd1.CommandType = [System.Data.CommandType]::Text 
$cmd1.CommandText =“SELECT guid, name, [Serial Number] FROM dbo.wrksta as a inner join Inv_AeX_HW_Serial_Number as b on a.GUID=b._resourceGUID ORDER BY Name” 
$data = $cmd1.ExecuteReader() 
$cmd2 = $conn.CreateCommand() 
$cmd2.CommandType = [System.Data.CommandType]::Text 
$cmd2.CommandText =“SELECT top 1 [User] FROM dbo.Evt_aex_client_logon WHERE _resourceGuid = (SELECT top 1 Guid FROM dbo.Wrksta WHERE Name='" + $data["name"] + "' ORDER BY WrkstaID DESC) AND Event = 'logon' AND _eventTime > getdate()-60 GROUP BY [User] ORDER BY count([User]) desc” 
$data2 = $cmd2.ExecuteReader() 
$dt = new-object “System.Data.DataTable” 
$dt.Load($data2) 
$dt | format-table 
$conn.Close()

If you look at the second select statement I am wanting to use a value from the first variable $data but im unsure how to write it. in VB it was like this WHERE Name='" + workstation("name").value + "' is the following the correct way to write this in powershell? '" + $data["name"] + "'. Im after the value from the name column stored in the $date variable

The VB used to do this previously is below

connString = "Provider=sqloledb;Data Source=SERVER;Initial Catalog=DATABASE;Integrated Security=SSPI;"
objSQL.Open connString 

sqlWorkstations = "SELECT guid, name, [Serial Number] FROM dbo.wrksta as a inner join Inv_AeX_HW_Serial_Number as b on a.GUID=b._resourceGUID ORDER BY Name"
Set workstation = objSQL.Execute(sqlWorkstations)

Do Until (workstation.EOF)

sqlUser = "SELECT top 1 [User] FROM dbo.Evt_aex_client_logon WHERE _resourceGuid = (SELECT top 1 Guid FROM dbo.Wrksta WHERE Name='" + workstation("name").value + "' ORDER BY WrkstaID DESC) AND Event = 'logon' AND _eventTime > getdate()-60 GROUP BY [User] ORDER BY count([User]) desc"
set user = objSQL.Execute(sqlUser)

if not user.eof then
  userName = user("User").value
else
  username = "??????"
end if

meeeeeeeeee · Accepted Answer · 2012-09-07 13:07:34Z

2

Make things a lot easier on yourself and load the SqlServerCmdletSnapin100 snap-in. If you're dealing with SQL Server, it handles most of the boilerplate for you and distills running queries to a single cmdlet.

Your code above assumes that the first query returns only a single row. This may or may not always be the case; I don't know your database. If it returns multiple records, then your code will only give you data corresponding to the first record from the first query.

Using SqlServerCmdletSnapin100's Invoke-SQLCmd and handling the case of multiple records being returned, do this:

$data1 = Invoke-Sqlcmd -ServerInstance SERVER -Database DATABASE -Query “SELECT guid, name, [Serial Number] FROM dbo.wrksta as a inner join Inv_AeX_HW_Serial_Number as b on a.GUID=b._resourceGUID ORDER BY Name”;
foreach ($row in $data1) {
    $name = $row.name;
    $data2 = invoke-sqlcmd -ServerInstance SERVER -Database DATABASE -Query “SELECT top 1 [User] FROM dbo.Evt_aex_client_logon WHERE _resourceGuid = (SELECT top 1 Guid FROM dbo.Wrksta WHERE Name='$name' ORDER BY WrkstaID DESC) AND Event = 'logon' AND _eventTime > getdate()-60 GROUP BY [User] ORDER BY count([User]) desc” ;
    $data2 |ft -AutoSize
}

It's also possible that you could retrieve all of your data with a single properly-written query but again, without knowing the data that's hard to say. You really ought to consult with your DBA regarding these queries to make sure they're written properly and match your requirements properly.

edited Sep 7, 2012 at 13:07

meeeeeeeeee

3514 gold badges8 silver badges18 bronze badges

answered Sep 7, 2012 at 12:59

alroc

28.3k6 gold badges55 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

meeeeeeeeee Over a year ago

Thats great, how do I get/register this sql snapin on a server without SQL as the script does a lot of other bits too. by the way the first query will return around 400-500 records. I have added the VB used previously to do this job.

alroc Over a year ago

You need to install SQL Server Management Studio to get the snap-in. It's worth it - IMHO you shouldn't ever re-invent something that someone else has written and made available. In this case, that's Invoke-SQLCmd vs. the code you ported from VB.Net.

alroc Over a year ago

Even with the first query returning 400-500 records, you may be able to do the work of both of these queries with a single, better-written query. And possibly reduce load on the server & improve execution time/memory footprint of your script.

meeeeeeeeee Over a year ago

what version(s) are powershell aware is it 2008+?

alroc Over a year ago

I think so. Invoke-SQLCmd can query servers at least as old as 2000 though. Install the current/latest SSMS and you'll be OK.

|

devio · Accepted Answer · 2012-09-07 13:00:07Z

0

ExecuteReader only creates the reader, but you need to call $data.Read() to retrieve the first record. (see sample in linked MSDN)

answered Sep 7, 2012 at 13:00

devio

37.3k7 gold badges85 silver badges147 bronze badges

Collectives™ on Stack Overflow

Running SQL command with powershell issue

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related