John Mendez

So I recently came across the Internal connection fatal error for a report written in ASP.NET that I had inherited.  This error was a bit troubling because the logic in the report caught exceptions, but I kept getting this error message. Well after looking more closely at the code, it was making tons and tons of calls to the database that weren't necessary.  For example, for reach row returned from the main result set the gridview was using, on the RowDataBound it would make an additional 4 calls to the database for other data.  So in a sense say you have 100 records returned you're looking at 400+ connection calls to the database.  Imagine 200, 300 or more records returned, the connections it would need to make are unreasonable. 

So to solve this situation, I rewrote the code that connects to the database by limiting the connections to the database to only a few.  The reason this worked for my situation is because I just pulled in all the data I know I would need and use For Each blocks like the one below

For Each dr As DataRow In ds.Tables(0).Select(String.Format("SOME_ID = {0}", e.Row.DataItem("SOME_ID")))
  'You code here
Next

The previous code was something like this

Dim strSql As String = "sql statement here WHERE SOME_ID = {0}"

ds = class.GoGetSqlStatement(strSql)

So as you can see it was a making a call each time based off the ID, where I just brought in all the data and did a Select on the dataset based off the ID to eliminate connections.  Now, is the the correct solution to the problem, I'm not sure because it doesn't seem there is one.  However, knocking down the connections to the DB is an excellent start.  So analyze your code and see if that could be an issue.  Good luck - John

ASP.NET asp.net, sql

Hey everyone I wanted to post some code I had sometime back when on how I pulled SQL stats from a SQL Server using VB.NET and ASP.NET.  The code that will be in this post is actually part of a class that handles the SQL stats, but you can incorporate it however you would like.  There are a few things required for this. 

1. This is written in .NET 2.0
2. You need to add Imports System.Diagnostics   to your class or aspx page
3. You also have to have the rights to pull system stats from your SQL Server.  What this means is that you either need to enable Indentity Impersonate on the web application and make sure whoever uses the app has rights, or run the web application under an Application Pool in IIS that has access to the SQL Servers.
4. You'll see a variable in this class Me._Server.  Replace this with the server name you are trying to pull stats from or create a variable names _Server as a String
5. Add a reference to the COM object Microsoft SQLDMO Object Library 

Ok so here is some code that should get you by for a simple SQL Monitoring tool.  Again you can use this wherever you want in your app.  I had mine in a seperate class.

Again hope this helps those that need to gather sql stats in a quick manner or need to monitor a sql server for stat purposes / high overview of usage. 

ASP.NET, Microsoft SQL sql, asp.net

Unfortunately, in the Microsoft SQL world, sometimes you have to deal with using OPENQUERY.  This is difficult to use sometimes because you don't have as much flexibility with variables and parameters as you would using normal T-SQL.  Below is an example of how you can use OPENQUERY and still utilize a variable or parameter.

DECLARE @variable VARCHAR(10)
DECLARE @sqlQuery VARCHAR(8000)
DECLARE @finalQuery VARCHAR(8000)

SET @variable = 'yourStringValue'

SET @sqlQuery = 'SELECT * FROM tableName WHERE strValue = ' + '''' + '''' + @variable + '''' + ''''

SET @finalQuery = 'SELECT * FROM OPENQUERY(LINKEDSERVER,' + '''' + @sqlQuery + '''' + ')'

--used for debugging
SELECT @finalQuery

EXEC(@finalQuery)

NOTE: It may be hard to see but the '''' is 4 single quotes

I recommend commenting out EXEC(@finalQuery) until you see @finalQuery correctly in the results.  Running SELECT @finalQuery is very helpful because you can see what single quotes are missing if need be. 

I also recommend getting your OPENQUERY statement to work correctly before trying to make it "dynamic" so to say using the above method.

Anyway, I found this very useful to use and hope that it can help some others out there.

Microsoft SQL sql