topic How to run oSQL queries in Server Management - Systems Insight Manager

How to run oSQL queries

Daniel Caron_3 — Sat, 21 Aug 2004 10:42:58 GMT

Can someone help me figure out what basic tsql commands I should be using to transpose "Show SQL Queries" sample output from SIM console, to execute properly from CLI osql.exe MSDE tool?

I figure this is what is required:
1-"USE databasename"
2-cut'n'pasted query command
3-"GO" command

How can we figure out what database name to use, and am I missing anything in the above recipe?

Thanks!

Re: How to run oSQL queries

Rob Buxton — Sun, 22 Aug 2004 15:39:38 GMT

I use OSQL as it's a bit better.
I also put the Select Query into a separate file.

Typically you need the following:

osql -S YourSQLServer -U UserAccount -P Password
-d INSIGHT_v1_0_15758423 -w 132 -n -h-1
-i d:\Server_Description.osql
-o d:\Server_Description.txt

You can often replace the -U and -P construct with -E (no paramaeters) if it is being triggered from a suitable privileged account.

My Input file would look something like:

SET NOCOUNT ON
go
select etc. etc.
go
quit

Re: How to run oSQL queries

Daniel Caron_3 — Mon, 23 Aug 2004 08:57:09 GMT

#1- After a bit of research, I found out that we can first locate the Insight Manager database name by issueing the following: osql -E -dmaster -Q"select * from sysdatabases"

#2- Then, use that variable database name in an osql command with -U -P options, or -E if currently admin priveleges, and -Q option with cut'n'pasted SQL query withint double-quotes, such as in :

osql -E -dInsight_v1_0_222955644 -Q "select R_Inventory.DeviceName, R_Inventory.ProductType, R_Inventory.ProductName, R_Inventory.SerialNumber, R_Inventory.OSName from R_Inventory WHERE ((R_Inventory.SnapShotID=-1) OR (R_Inventory.SnapShotID is NULL)) AND DeviceKey in (select devices.deviceKey from devices JOIN notices ON devices.deviceKey = notices.deviceKey and notices.NoticeType = 1 JOIN consolidatedNodeAuths ON devices.mxGuid = consolidatedNodeAuths.NodeId and consolidatedNodeAuths.userID = '00000000864162de0000000400000006' WHERE ( ( devices.productType = 1) ) ) order by R_Inventory.DeviceName ASC"