ASP.NET Web Page to get result of query (stored procedure), compare then redirect to a sepecific web page based on the result

NOTE:  Trying to get the question changed upto 1,000 points, as soon as i figure out how todo it it will be updated.  Hence this question is now worth 1,000 points!

Please help me with this ASAP

There is a 500 point limit on any one question, as per the MA

I didn't realise, however this question is very important and if there is anyway (within the site rules) to give more than 500 points then i will do so if anyone is able to help me.

Dim conn As SQLConnection

        Dim strConn As String = "your connection string here"


        conn = New SQLConnection(strConn)
        conn.Open()
        Dim da As OleDbDataAdapter = New SQLDataAdapter("uspRecentCPULevel", conn)
        Dim ds As New DataSet

        Try
            da.Fill(ds)
            Conn.Close()
        Catch SQLerr As SQLException

            strErrMsg = "SQL error: " + SQLerr.ToString + ", SQL error message:" & SQLerr.Message
            ''handle your error
        Catch ex As Exception

            strErrMsg = "Error Retrieving Data: " + ex.ToString()
            ''handle your error
        Finally
            If Conn.State = ConnectionState.Open Then
                Conn.Close()
            End If
        End Try

What i need it todo is to connect to two seperate SQL servers, process the stored preocdure, store it somewhere and then compare the the different values from each server.... then based on that comparison... (i.e. which ever has the lowest) redirect to a website

GetServer1Load()
{
connection strind=1 sql server

return cpucycles;
}

GetServerLoad2()
{
connection string=2 sql server
return cpucycles
}

and then call both of them

int C1=GetServer1Load()
int C2=GetServer2Load();

if(c1>c2)
{
do whatever
}
else
{
something else
}

where r u having the problem doing it that way?

I'm not a programmer, I'm a network admin by trade and nature and i kind of need it spelling out a bit clearer, right down to how to redriect to a specific website using aspx and the code on the entire page.  I'm desperate to get it done ASAP, i've tried many ways of doing it myself, but without much programming knowledge i'm consistently hitting brick walls :(

Just as a thought I would consider looking at a bit more intelligent method here's some pseudo code ...

public class ConnectionStringFactory {

    private class ServerEntry {
         public string ServerName;
         public float Usage;
    }

    private static float GetServerCPU(string Server) {
         //call sproc and return the cpu ratio
    }
   
    private static void PollServers() {
          while(true) {
              ArrayList newList = new ArrayList() ;
              foreach(ServerEntry Entry in m_List) {
                    Entry.Usage = GetServerCPU(Entry.ServerName) ;
                    newList.Add(Entry);
              }
              lock(typeof(ConnectionStringFactory)) { //note that by copying the list we only need to lock during the refence copy as opposed to while we run the sprocs...
                    m_List = newList;
              }
          }
          sleep(60000);
    }

   private static ArrayList m_List;
    static ConnectionFactory() {
          m_List = new ArrayList(); //using generic list instead of typed collection for brevity
          //start up a thread running PollServers or remove the loop and put it on a timer.
    }

    public static string GetConnectionString() {
           lock(typeof(ConnectionStringFactory)) {
                  //iterate through list and grab the lowest entry to build your connection string
           }
    }
}

Then in your code you would just call ConnectionStringFactory.GetConnectionString();

This would prevent you from having to poll the server everytime someone hits the site thus it should be significantly faster, it would also allow you to dyncamically move connections without additional redirects since you can call the method every time you need a connection (i.e. it is more granular than just putting users to given servers).

There are a few changes that could be made to the above code ...
1) use a sorted list / maintain a copy of the best server to avoid the iteration
2) use a typed collection instead of an arraylist
3) add performance counters / other metrics to the factory so you can see whats going on :)
4) Allow servers to be externally registerred to the factory (just a matter of locking the list and adding a server entry)

Cheers,

Greg

Under normal circumastances I can see how that would be a VAST advantage greg, the problem i have is that the specific configuration only has SQL data being replicated in one way, the page before this actually checks the users IP, if they're rom a range at a particular site they get forced to server 1 (the main SQL Server) as only they make changes and as the replication is one-way i need the changes to only be made at server1.  So when a user hits the point of this page I want them to make a single check on cpu load, choose lowest then stick to that server as opposed to changing the connection string fro the whole .net app.

Apologies if i've confused your solution somehow, like i say I am a complete novice when it comes to any programming, and just getting the return data stored somewhere is trouble enough!

I basically need the data returned stored in two integer values, compared and then the user redirected a website based on that.

Hope some of this is making sense!  Abour ready to throw the servers out a window!

I guess there's no way to convince your boss(es) to go with a load balancing IIS cluster, eh? =)

Paul <--- perpetrator of another useless post

havent got the hardare or the money unfortunately.... :(

"So when a user hits the point of this page I want them to make a single check on cpu load, choose lowest then stick to that server as opposed to changing the connection string fro the whole .net app."

It would only be changing it for their current context (only for their current need of a connection string) depending how you did it ... there is nothing to prevent you from just holding onto that connection string for a while (i.e. drop it into viewstate/session)

The main point I was trying to illustrate was the use of a background thread to remove the overhead of having to hit multiple databases everytime someone came to the main page (should be more scalable).

As for the ip addresses going to a specific server, you could pass in an EndPoint object to determine those rules.

Greg

Hi greg,

Thanks for your response.... I see what you're getting at... I'm really looking for a lot more help with the coding... as i've said... I'm really not a programmer and this part of the project isn't something I was looking forward to.  If there is someone out there who could give me the neccesary code it would be MUCH appreciated, whenever i try to code it feels like i'm hitting my head against a brick wall (other than a basic VB6 app that is! - no laughing!).

Regarding the IP addresses to a specific server, this part has already been dealt with.

There are a lot of comments, and I don't quite know where to begin?  Where are you now?  What code do you need?

Bob

I'm pretty much still where i started... so starting at the original question would be great! :)

Actually, what are the versions for the Operating System, and the IIS where you are running your web site?

Bob

Windows 2003 Server with IIS V6 and ASP.NET 1.1.4233.

It might be possible to achieve load balancing without having to develop something yourself:

Achieving Massive Scalability with SQL Server:
http://www.sql-server-performance.com/dk_massive_scalability.asp

Bob

I'm not sure that this would solve the problem, if it's clustering you're looking at, then i don't have the hardware of the budget.  If it's Federated Database Design, then this would balance the database, but not the HTML.  I'm also in a real hurry for the solution, so major hardware or software changes aren't an option right now.  I basically have the two identical sites working and replicating, i just need the single page that will redirect using the already existent stored procedure, any help with this is MUCH appreciated (to a point where i'm now willing to pay for it!), I really am in a bind (with little-to-no programming skills!).

Thanks
James

CpuComparer.vb
===========

Imports System.Data
Imports System.Data.SqlClient

Public NotInheritable Class CpuComparer
      Private Sub New()
      Private Const StoredProcedureName As String = "uspRecentCPULevel"
      Private Shared ReadOnly Property FirstDataBase() As SqlConnection
            Get
                  Return New SqlConnection("<connection_string>")
            End Get
      End Property
      
      Private Shared ReadOnly Property SecondDataBase() As SqlConnection
            Get
                  Return New SqlConnection("<connection_string>")
            End Get
      End Property
      
      Private Shared Function GetCPUValue(ByVal connection As SqlConnection) As Double
            Dim cmd As SqlCommand = connection.CreateCommand()
            cmd.CommandText = EXEC " & StoredProcedureName
            Return DirectCast(cmd.ExecuteScalar(),Double)
      End Function
      
            
      Public Shared Function GetCpuDifference() As Double
            Dim first As Double = GetCPUValue(FirstDataBase)
            Dim second As Double = GetCPUValue(SecondDataBase)
            Return first - second
      End Function
      
End Class

too fast typing
========
amendment
=======
Private Shared Function GetCPUValue(ByVal connection As SqlConnection) As Double
          Dim cmd As SqlCommand = connection.CreateCommand()
          cmd.CommandText = EXEC " & StoredProcedureName
      connection.Open()
      Dim value As Double = DirectCast(cmd.ExecuteScalar(),Double)
      connection.Close()
      Return value
End Function

Now, all you do is call like so

If CpuComparer.GetCpuDifference() <= 0 Then
   'the first server connection string
Else
   'the second server connection string
End If

Firstly thanks for giving me a hand!

I've put it all into the page and it's coming with a compilation error

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30246: 'Private' is not valid on a local constant declaration.

Source Error:

 

Line 19: Public NotInheritable Class CpuComparer
Line 20:      Private Sub New()
Line 21:      Private Const StoredProcedureName As String = "uspRecentCPULevel"
Line 22:      Private Shared ReadOnly Property FirstDataBase() As SqlConnection
Line 23:           Get
 

Source File: c:\inetpub\wwwroot\loadbalance.aspx    Line: 21


This is what i have....

<%@ Page Language="VB" %>

Imports System.Data
Imports System.Data.SqlClient
<script runat="server">



Private Sub Page_Load(ByVal sender As Object,ByVal e As EventArgs) Handles MyBase.Load
 If CpuComparer.GetCpuDifference() <= 0 Then
   'the first server connection string
Else
   'the second server connection string
End If
End Sub



Public NotInheritable Class CpuComparer
     Private Sub New()
     Private Const StoredProcedureName As String = "uspRecentCPULevel"
     Private Shared ReadOnly Property FirstDataBase() As SqlConnection
          Get
               Return New SqlConnection("Data Source=XXXX;Initial Catalog=XXXXX;UserId=sa;Password=XXXXX;")
          End Get
     End Property
     
     Private Shared ReadOnly Property SecondDataBase() As SqlConnection
          Get
               Return New SqlConnection("Data Source=XXXX;Initial Catalog=XXXXX;UserId=sa;Password=XXXXX;")
          End Get
     End Property
     
  Private Shared Function GetCPUValue(ByVal connection As SqlConnection) As Double
          Dim cmd As SqlCommand = connection.CreateCommand()
          cmd.CommandText = EXEC " & StoredProcedureName
     connection.Open()
     Dim value As Double = DirectCast(cmd.ExecuteScalar(),Double)
     connection.Close()
     Return value
  End Function  
         
     Public Shared Function GetCpuDifference() As Double
          Dim first As Double = GetCPUValue(FirstDataBase)
          Dim second As Double = GetCPUValue(SecondDataBase)
          Return first - second
     End Function
     
End Class
</script>

I changed the End on line 10 to End If and have inputted my Connections strings, but it's now coming up that it doesn;t like "UserID"

Keyword not supported: 'userid'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: Keyword not supported: 'userid'.


<%@ Page Language="vb"%>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<script lang="vb" runat="server">
Sub Page_Load(ByVal sender As Object,ByVal e As EventArgs)
     If CpuComparer.GetCpuDifference() <= 0 Then
          ' the first database
     Else
          ' the second database
     End if
End Sub

Public NotInheritable Class CpuComparer
    Private Sub New()
    End Sub
    Public Const StoredProcedureName As String = "uspRecentCPULevel"
    Public Shared ReadOnly Property FirstDataBase() As SqlConnection
        Get
            Return New SqlConnection("Data Source=XXXXXX;Initial Catalog=XXXXX;UserId=XXXXXXX;Password=XXXXXX;")
        End Get
    End Property

    Public Shared ReadOnly Property SecondDataBase() As SqlConnection
        Get
            Return New SqlConnection("Data Source=XXXXXX;Initial Catalog=XXXXX;UserId=XXXXXXX;Password=XXXXXX;")
        End Get
    End Property

    Public Shared Function GetCPUValue(ByVal connection As SqlConnection) As Double
        Dim cmd As SqlCommand = connection.CreateCommand()
        cmd.CommandText = "EXEC " & StoredProcedureName
        connection.Open()
        Dim value As Double = DirectCast(cmd.ExecuteScalar(), Double)
        connection.Close()
        Return value
    End Function


    Public Shared Function GetCpuDifference() As Double
        Dim first As Double = GetCPUValue(FirstDataBase)
        Dim second As Double = GetCPUValue(SecondDataBase)
        Return first - second
    End Function

End Class
</script>

By default, SQL Server does not allow for Mixed Security (namely Windows and MSSQL Accounts)

1. To change this behaviour, you'd have to open up Enterprise Manager, and connect to the server, right click on it and select Properties,
2. Click on the Security Tab and select the security settings of Sql Server and Windows.
3. Stop and start SQL Server

My SQL Servers have always been setup in mixed mode and the main app connects to them fine (using sa).... any ideas?

Thanks
James

I mispelt userid so that would be why.....

Trying now....

...

I'm now getting a "Divide by zero" error... any ideas?

Divide by zero error encountered.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Divide by zero error encountered.

Source Error:

The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:

1. Add a "Debug=true" directive at the top of the file that generated the error. Example:

  <%@ Page Language="C#" Debug="true" %>

or:

2) Add the following section to the configuration file of your application:

<configuration>
   <system.web>
       <compilation debug="true"/>
   </system.web>
</configuration>

Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.

Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario.  

Stack Trace:


[SqlException: Divide by zero error encountered.]
   System.Data.SqlClient.SqlDataReader.Read() +176
   System.Data.SqlClient.SqlCommand.ExecuteScalar() +177
   ASP.CpuComparer.GetCPUValue(SqlConnection connection) +60
   ASP.loadbalance_aspx.Page_Load(Object sender, EventArgs e) +70
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +750

 

it'll be a SQL Server error,
and the error is because the @@IDLE - IDLE is zero


CREATE PROCEDURE uspRecentCPULevel
AS

     SET NOCOUNT ON

     SELECT     CAST(@@CPU_BUSY - CPU_BUSY AS float) / CAST(@@IDLE - IDLE AS float)
     FROM     tblActivity

     UPDATE     tblActivity
     SET          CPU_BUSY = @@CPU_BUSY,
               IDLE = @@IDLE

GO

change the procedure to like so:

ALTER PROCEDURE uspRecentCPULevel
AS
SELECT
      CASE
      WHEN (@@IDLE - IDLE) = 0 THEN CAST(0) AS float
      ELSE CAST(@@CPU_BUSY - CPU_BUSY AS float) / CAST(@@IDLE - IDLE AS float)
      END
FROM tblActivity
      
SET NOCOUNT ON
UPDATE tblActivity
SET
      CPU_BUSY = @@CPU_BUSY,
      IDLE = @@IDLE
SET NOCOUNT OFF
GO

It errors with... Incorrect Syntax near 'CAST', expected 'AS'...

ALTER PROCEDURE uspRecentCPULevel
AS
SELECT
     CASE
     WHEN (@@IDLE - IDLE) = 0 THEN CAST(0 AS float)
     ELSE CAST(@@CPU_BUSY - CPU_BUSY AS float) / CAST(@@IDLE - IDLE AS float)
     END
FROM tblActivity
     
SET NOCOUNT ON
UPDATE tblActivity
SET
     CPU_BUSY = @@CPU_BUSY,
     IDLE = @@IDLE
SET NOCOUNT OFF
GO

That's got it all working great!!!!

Thanks a lot!  You have no idea how much i appreciate it, You've done in a few hours what i spent months looking for!

Cheers!
James

Glad to be of help =)