Select Statement - just want the value, not the header

Question

Hi

I'm running a straight forward SQL statement to extract a database state. This is fine but I only want to return the database state and at the moment I'm getting the header.

E.g.

$DBState = Invoke-SQLCmd "Select state_desc From master.sys.databases where name='DBName'"

When I look at the contents of $DBState I have:

State_Desc

------------

ONLINE

All I want as the contents of $DBState is ONLINE

Is there anyway to do this?

Thanks in advance

Graham

---

Regards Graham New System Center 2012 Blog! - http://www.systemcentersolutions.co.uk
View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/

Answer 1

So where is this Invoke-SQLCmd coming from? This forum is from Transact-SQL questions, but you obviously have a problem with the environment you are using (Powershell?) and you are better off asking in a forum for that environment.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Thanks for the quick response - Is there anything I can put in the SQL statement to make it return just the data item?

I don't have to use PShell to run the script - I just need to automate the SQL statement and return just the data item without any header.

Cheers

Graham

---

Regards Graham New System Center 2012 Blog! - http://www.systemcentersolutions.co.uk
View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/

Answer 3

Inovke-sql is much the same as sqlcmd utility. Try sqlcmd option -h-1 to supress columns headers.

---

Serg

Answer 4

Thanks for the quick response - Is there anything I can put in the SQL statement to make it return just the data item?

No. The engine always returns metadata about the result set. It's up to the client what to do with the metadata. That is why I referred to a forum to the environment you are using. If you don't need to use Powershell, you can write a C# program. Or use ADO .Net from Powershell for that matter.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

PowerShell returns .NET object (array) representing results of the query.

To solve it you need to modify PowerShell statement:

Use following code to get ONLINE only, it will return value of the first column in first row:

$DBState[0].ItemArray[0]

PS SQLSERVER:\SQL\SQLSERVER2012\DEFAULT>  $DBState = Invoke-SQLCmd "Select state_desc From master.sys.databases"
WARNING: Using provider context. Server = SQLSERVER2012.
PS SQLSERVER:\SQL\SQLSERVER2012\DEFAULT> $DBState

state_desc
----------
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE


PS SQLSERVER:\SQL\SQLSERVER2012\DEFAULT>  $DBState[0].ItemArray[0]
ONLINE
PS SQLSERVER:\SQL\SQLSERVER2012\DEFAULT>

Answer 6

Many Thanks - works for me as:

$DBState.ItemArray[0]

Cheers

Graham

---

Regards Graham New System Center 2012 Blog! - http://www.systemcentersolutions.co.uk
View OpsMgr tips and tricks at http://systemcentersolutions.wordpress.com/