SQL Server + OleDb

Topics: Developer Forum, User Forum
May 7, 2007 at 8:19 PM
I am having some trouble configuring the SQL Monitor part of PolyMon. The error I am getting back is "DBEERRORSOCCURRED(0x80040E21)" with no additional details. As best as I can tell, this is related to using the OleDb connection option in .Net. If SQL 2000/2005 is required (in some variation), then why not use the native SQL connector?

For the record, I've been trying different providers and can't seem to get anything to work right. I have no clue why this won't take..
Coordinator
May 8, 2007 at 12:14 PM
Edited May 8, 2007 at 12:14 PM
There's no specific requirement for MS SQL Server. The SQL monitor was written using the System.Data.OleDB library, not the SQLClient library.
Could you please post the XML you are defining for the monitor and also let me know which SQL database engine you are using.

There's a good starting point for troubleshooting this here: http://www.adopenstatic.com/faq/80040e21.asp.

But please post more details and I'll see if I can figure something else out looking at the code. Have you had no luck connecting to a MS SQL Server either?

Thanks.
May 9, 2007 at 12:46 PM
I know it's using the OleDb library, but my question was since it is monitoring SQL Server, why not use the built-in SQL library? I'm sure there is a specific reason behind it, but I haven't come up with it yet.

I am running SQL Server 2005 Standard Edition. The XML is below:

<SQLMonitor>
<ConnectionString>Provider=SQLNCLI;Data Source=ServerName;Initial Catalog=DBName;Integrated Security=True</ConnectionString>
<SP>
<Name>polymonselMonitorKPI</Name>
<Parameters />
</SP>
</SQLMonitor>

I know it is supposed to also have the two output parameters, but it doesn't seem to get that far (I had taken those out as an attempt to get this resolved). The exact error message when I run the test is:

Status: Fail
Status Message: No error message available, result code: DBEERRORSOCCURRED(0x80040E21).
Coordinator
May 9, 2007 at 3:02 PM
Edited May 9, 2007 at 3:03 PM
The reason for using the OleDB libraries is that you can use the SQL monitor to run stored procedures against other databases, not just MS SQL Server. The example that uses the polymonselMonitorKPI is indeed MS SQL Server based, but the monitor itself can in principle run a stored proc against other engines.

I believe your connection string is causing the problem (I was able to reproduce problem using your connection string format).
Try setting Integrated Security=SSPI instead of the True setting you currently have.

Hopefully this works. Please let me know if this resolves the problem.

Thanks.

P.S. The output parameters need to be defined in your stored proc but not in the XML - the XML parameters are for additional input parameters only. PolyMon's SQL monitor still expects the output parameters and optional result set containing the counter information. So your XML, apart from the connection string issue, is fine as is. Just add the output parameters back to the SP and you should be good to go.
May 10, 2007 at 12:41 PM
That worked perfectly!

I think that was one item that was confusing - where to put the output parameters in the XML but all looks good now.