Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

I need some help with runnign a SQL Query in APM

I am trying to run the following query against any one of our SQL database servers.

select
d.name 'database',
SUM(m.size)'size_mb'
from
sys.master_files m
left outer join
sys.databases d
on m.database_id = d.database_id
WHERE d.state = 0---ONLINE
GROUP BY
d.name

I using the SQL user experience to try and create a new template and I am getting the following error.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have tried several different username and passwords that have rights to login and run a query on the SQL server but nothing I have tried works.

I am doing this because the RPC object for database size is returning the size in KB and we need this to be in MB which is what the query is suppose to do list all the databases and then return the size in MB.

I have opened a support ticket 188299 but that has not helped when they refer me to these 2 posts and say they don't know SQL any better then I do which is not very helpfull since I am not a SQL guru at all.

http://blogs.msdn.com/b/sql_protocols/archive/2005/10/22/483684.aspx

http://blogs.msdn.com/b/sql_protocols/archive/2005/10/29/486861.aspx

I am able to connect to the database from outside of Orion just fine, I am getting this error on all our SQL servers even my Orion server. I am hoping someone around here has found a way to run a SQL query in APM and can either talk me through how and where to set this up or can upload a shared template.

Thanks for any help.

Find more posts tagged with

Accepted answers

All comments

jiri.tomek

Hello,

first thing you should know is that SQL User experience monitor gives you only first value of first row returned from query. You can't get list of values as you are trying to do.

Regarding connection issue, you should check allowed protocols on your SQL server. Go to Start->Programs->Microsoft SQL Server 2005->SQL Server configuration manager. There open "SQL Server 2005 Network Configuration"->"Protocols ...". Check that you have "Named Pipes" and "TCP/IP" enabled for your instance.

profzoom1

Yes my DBA thought that may be the case after we looked at it and it failed to run but the connection issue needs to be resolved and I am not sure why I am getting that error when the logins being uses are admins on the server and in the database. I am verifying the connection settings you posted which I hope resolve the issue.

I still am trying to verify that a SQL query can even be run against the SQL database. I am hearing from Solarwinds this not possible.

Below is what Solarwinds has suggested.

have a script of your own design run in order to populate a Custom Table in the Orion DB and then use report writer to create a resource that CAN be displayed in Orion. This option would be the most flexible with regards to your requirements.

profzoom1

WTH????

The post has not been answered I still can not run a query against the SQL database from within APM -Application Performance Monitor if that is correct than I would think since this is a SQL backend that we should be able to run a query against the database.

How is this doen within APM? I have not seen anything and the Solarwinds tech that was helping me was having me go into reports and that is not what I want or need to do.

Why was this marked as answered?

jiri.tomek

Hello profzoom1,
Have you verified that your SQL server has Named Pipes and TCP/IP protocols enabled as I suggested in my previous post? If yes, do you have support ticket opened for this?