Hi there,
I have been having a nightmare with one of ASP .Net 2.0 app which works fine
in our development environment. WHen I move to the production server the
database is getting wacked. I am trying to establish a connection thro a XML
string. Everything works fine and I am able to connect to the specific
database and make queries with that connection object. But once I move to the
production server and try to connect to a specific database I am getting
screwed. It is establishing the database connection correctly in the
prodcution server but when I execute a query it returns 0 and I dont know
where I am going wrong. I am attaching herewith code snippet which is
misbehaving in the production server.

Sub getAWPRODDBValues()
Dim cmd As New SqlCommand
Dim magStatus As String = ""
Dim mag3dStatus As String = ""
Dim strException As String = ""
Dim rs As SqlDataReader

Try
con = New SqlConnection(Session("ConnString").ToString)
con.Open()
Catch ex As Exception
strException = "Error.aspx?Exception=" & ex.Message
Response.Redirect(strException)
Return
End Try
Response.Write("Connection String from AWPROD" &
Session("ConnString").ToString)
cmd = New SqlCommand("select status,GU_MsgCount,alt_msgcount from
CP_componentDetails where comp_id=2 and Control_Type='MAG3D'", con)
rs = cmd.ExecuteReader()

If (rs.Read) Then
CylMag3DAlert.Message = "Status :" & rs.GetString(0) &
"<br/>Messages : " + rs.GetValue(2).ToString
CylMag3DGU.Message = "Status :" & rs.GetString(0) &
"<br/>Messages : " + rs.GetValue(1).ToString
Response.Write("No of messages in MAG 3D Queue" &
rs.GetValue(1).ToString)
Else
CylMag3DAlert.Message = "Status :Not Running or <br/>it is in
Idle state"
CylMag3DGU.Message = "Status :Not Running or <br/> it is in Idle
state"

End If
cmd.Dispose()
rs.Close()

cmd = New SqlCommand("select status,GU_MsgCount,alt_msgcount from
CP_componentDetails where comp_id=2 and Control_Type='MAG'", con)
rs = cmd.ExecuteReader()
If (rs.Read) Then
CylMagAlertQueue.Message = "Status :" & rs.GetString(0) &
"<br/>Messages : " + rs.GetValue(2).ToString
CylMagGUQueue.Message = "Status :" & rs.GetString(0) &
"<br/>Messages : " + rs.GetValue(1).ToString
Else
CylMagAlertQueue.Message = "Status :Not Running or <br/> it is
in Idle state"
CylMagGUQueue.Message = "Status :Not Running or <br/> it is in
Idle state"

End If
cmd.Dispose()
rs.Close()

cmd = New SqlCommand("select status from CP_ComponentDetails where
Comp_Id=1 and Control_Type='MAG'", con)
rs = cmd.ExecuteReader()
If (rs.Read) Then
magStatus = "MAG Status :" & rs.GetString(0)

End If
cmd.Dispose()
rs.Close()

cmd = New SqlCommand("select status from CP_ComponentDetails where
Comp_Id=1 and Control_Type='MAG3D'", con)
rs = cmd.ExecuteReader()
If (rs.Read) Then
mag3dStatus = "MAG3D Status :" & rs.GetString(0)
End If
cmd.Dispose()
rs.Close()
'RectEMailConv.Message = magStatus & "<br/>" & mag3dStatus

cmd = New SqlCommand("select status from CP_componentDetails where
comp_id=3 and Control_Type='MAG3D'", con)
rs = cmd.ExecuteReader()

If (rs.Read) Then
CylMag3DGUParser.Message = "Status : " + rs.GetString(0)
CylMag3DAlertParser.Message = "Status :" & rs.GetString(0)
End If
cmd.Dispose()
rs.Close()

cmd = New SqlCommand("select status from CP_componentDetails where
comp_id=3 and Control_Type='MAG'", con)
rs = cmd.ExecuteReader()

If (rs.Read) Then
CylMagGUParser.Message = "Status : " + rs.GetString(0)
CylMagAlertParser.Message = "Status :" & rs.GetString(0)
End If
cmd.Dispose()
rs.Close()
con.Close()
End Sub


Here the Session("ConnString").ToString value gets different Database
connection values (there are three servers). I am able to set the correct
value in the session.
But when the SQL statement gets executed in one server I am getting always 0
value. But in other servers I get the correct information. Is there any way I
can track this problem down.
Thanks
Any help would be greatly appreciated.

John

The problem may not be with your code. To get more information you need to
pass the inner exception to your error page and display it too.
Use: ex.InnerException.ToString That should give more info from the
database if it is a database error.

Run the actual SQL query from the apropriate database tool (I work with
Oracle so for me it is SQL PLUS, I have no idea what SQL Server or other
databases use) and see if the query succeeds on that server when run under
the same ID and password that your program uses. If it does work then the
user permissions are ok and the data does exist. If it fails, then have the
DBA check user permissions for that user on the relevant table(s). If they
are ok then have the table owner's user ID run the query. If that fails run
a less specific query and then search to see if the expected records
actually exist on the database.

In the past 3 months I have dealt with the following issues that cause the
same behaviour you are seeing:
1. User did not have select rights on the table. ex.InnerException will
generally include a message claiming the table does not exist but ex.Message
will not.
2. A data load had failed so the records were not actually there on the
production database. The ex object will not show an error at all (the query
scceeded but returned nothing).
3. The query was too specific and eliminated the desired records. On a text
field I added wildcard characters to the front and back of the search string
and discovered a trailing space. Also case sensitivity on text fields can
sometimes be an issue.

Good luck

"John @ X" <JohnX (AT) discussions (DOT) microsoft.com> wrote