Dynamic list of users is not populating on older SQL 2005

snteran · October 19th, 2011, 01:15 PM

Not sure if the reason my page is not working is because of the older environment, but that is really the only difference I see. I'm running a sql 2008 express environment where at work they are running SQL Server 2005.

When I just run the sql query it runs correctly and gives me exactly what I'm looking for, however when I run the query in the asp page, I don't get my names listed but do get the totals for the page.

Code:

Set objRS2 = objConn2.Execute("SELECT c.server_owner, COUNT(*) AS NumOccurrences, replace(c.server_owner,' ', '*') AS sFixed FROM serverreview c,server s where c.server_name = s.server_name and s.STATUS <> 'Decommissioned' and c.server_Owner is not null GROUP BY c.server_owner HAVING ( COUNT(*) > 0 )")
counttot=0
iRow=0
While Not objRS2.EOF
If iRow Mod 2 = 0 Then
response.write "<tr bgcolor=""#F7F7F7"">"
Else
response.write "<tr bgcolor=""#CCCCCC"">"
End If
counttot = counttot + objRS2.Fields("NumOccurrences")

Response.Write "<td class=clsBodyText>" & "<a class=linkroll href='cpserverlist.asp?serverowner=" & objRS2.Fields("server_owner") & "'>"
Response.Write objRS2.Fields("server_owner") & "</a></td>"
Response.Write "<td class=clsBodyText>" & objRS2.Fields("NumOccurrences") & "</td></tr>"

iRow=iRow + 1
objRS2.MoveNext
Wend
Response.Write "<tr>"
Response.Write "<td class=clsBodyText><b>Total Servers:</b></td><td class=clsBodyText><b>" & counttot & "</b></td></tr>"
objRS2.Close
Set objRS2 = Nothing

When I go take a look at the source code, I see:

Code:

"><td class=clsBodyText><a class=linkroll href='ownerserverlist.asp?serverowner='></a></td><

I get the page to populate but don't get a list of server_owner to appear. I'm not sure why it works on my development area and not my work area. I understand that they should be the same but limited resources makes that a tough reality.

What differences are there that might make it different or are there possible security settings that are in place that I need to check? Just not sure what to look for. Basically, I don't know so I don't know what to look for.

Please let me know if any other information is needed.

Thanks,

snteran · October 19th, 2011, 08:35 PM

I am still trying different ways to get my page to work successfully on our Production environment using sql 2005.

Any ideas of how I can achieve my goal using some different code. We have a table that will hold all our servers and with in that table there is a column with the server owner. So there are several unique server names that are associated with multiple server_owners. I need to know how many servers each server_owner has and then sum up all the individual totals to a grand server count.
Once I have my table with Server_owner = count of servers, I will need to click on the server_owner name and I have a page that displays all the servers associated with that server_owner name.

Any ideas would be greatly appreciated.

Thanks,

mat41 · October 20th, 2011, 01:20 AM

try this:

Code:

<%
Set objRS2 = objConn2.Execute("SELECT c.server_owner, COUNT(*) AS NumOccurrences, replace(c.server_owner,' ', '*') AS sFixed FROM serverreview c,server s where c.server_name = s.server_name and s.STATUS <> 'Decommissioned' and c.server_Owner is not null GROUP BY c.server_owner HAVING ( COUNT(*) > 0 )")
counttot=0
iRow=0
While Not objRS2.EOF
  If iRow Mod 2 = 0 Then
     response.write "<tr bgcolor=""#F7F7F7"">"  
  Else
     response.write "<tr bgcolor=""#CCCCCC"">"
  End If
  counttot = counttot + objRS2.Fields("NumOccurrences") %>
  <td class="clsBodyText"><a class="linkroll" href="cpserverlist.asp?serverowner=<%= objRS2(0) %>"><%= objRS2(0) %></a></td>
  <td class="clsBodyText"><%= objRS2(1)  %></td>
  </tr>
<%
  iRow=iRow + 1
  objRS2.MoveNext
Wend
Response.Write "<tr>"
Response.Write "<td class=clsBodyText><b>Total Servers:</b></td><td class=clsBodyText><b>" & counttot & "</b></td></tr>"
objRS2.Close
Set objRS2 = Nothing
%>

snteran · October 20th, 2011, 12:16 PM

Thank you for the reply. I did test your method and it worked great on my test area(Sql 2008 express) but not on the Prod side (sql server 2005). Are there any logs I could look at to see why this is not processing the way we are hoping?
Below is the source that is produced.

Code:

<tr bgcolor="#F7F7F7">
<td class="clsBodyText"><a class="linkroll" href="cpserverlist.asp?serverowner="></a></td> 
<td class="clsBodyText">60</td> 
</tr> 
<tr bgcolor="#CCCCCC"> 
<td class="clsBodyText"><a class="linkroll" href="cpserverlist.asp?serverowner="></a></td> 
<td class="clsBodyText">29</td> 
</tr>

mat41 · October 20th, 2011, 05:37 PM

No there are no logs for this type of thing. I am 99% convinced the data in the 2008 instance is diferent from the data in the 2005 instance which is why you are not seeing any result in the serverowner querystring value. Two suggestions:

1..Paste your query directly into your 2005 instance and run it, I am betting you will see NULL and or empty string values in the 'c.server_owner colum. What is your result?

2..Run this and see what VBScript has to say about the value, what does it say?

Code:

<%
Set objRS2 = objConn2.Execute("SELECT c.server_owner, COUNT(*) AS NumOccurrences, replace(c.server_owner,' ', '*') AS sFixed FROM serverreview c,server s where c.server_name = s.server_name and s.STATUS <> 'Decommissioned' and c.server_Owner is not null GROUP BY c.server_owner HAVING ( COUNT(*) > 0 )")
do until objRS2.eof %>
   <tr>
    <td>Is there a value and if so what is it :</td>
 <Td>
<%  if isNUll(objRS2(0)) then
    response.write "the c.server_owner value is NULL"
    elseif trim(objRS2(0)) = "" then 
       response.write "the c.server_owner value is ans empty string"
    elseif trim(objRS2(0)) <> "" then 
       response.write "There is a value it is : " & trim(objRS2(0))
 end if %>
 </td>
<%    objRS2.moveNext
loop %>

snteran · October 20th, 2011, 06:28 PM

I wish that was it, that was my first troubleshooting step. To copy and paste the query in the sql 2005 and it runs fine. The copy of the DB that I have in my test was taken from the Production server. Both DB's are the same.

I did run your new code and I get:

Is there a value and if so what is it:

Repeats for each user, but no values other than what you see above.

Thanks for your help...next thought?

mat41 · October 20th, 2011, 06:35 PM

;;;To copy and paste the query in the sql 2005 and it runs fine
We know it runs, I was looking to see the data. Can you copy n paste the data (justa few rows will be fine if there are many) so I can see an example?

;;;I did run your new code and I get: Is there a value and if so what is it:
Yes you should see that of course. So you are saying none of the code fires in the if elseif ??
If this is the case then the result cant be either null, empty string, nor can it be not equal to an empty string.

I need to see the result set from the query inside SQL Server. As I said above please post this

snteran · October 20th, 2011, 06:52 PM

Here is the result from the query when ran from the sql 2005 console:

Sam Vis 60 Sam*Vis

David Tunn 29 David*Tunn

Eric Davis 80 Eric*Davis

When I do a query in the serverreview for server_owner, the cell has either a employee name, NULL or "Please Select..."

Please let me know if you need anything else.

Thanks,

snteran · October 20th, 2011, 07:08 PM

I forgot to post the View Source, not sure if this helps but I did want to post for future reference in case others use this thread to learn from:

Code:

tr> 
<td>Is there a value and if so what is it :</td> 
<Td> 
</td>
<tr> 
<td>Is there a value and if so what is it :</td> 
<Td> 
</td>
<tr> 
<td>Is there a value and if so what is it :</td> 
<Td> 
</td>
<tr> 
<td>Is there a value and if so what is it :</td> 
<Td> 
</td>
<tr> 
<td>Is there a value and if so what is it :</td> 
<Td> 
</td>
<tr> 
<td>Is there a value and if so what is it :</td> 
<Td>
</td> 
<tr> 
<td>Is there a value and if so what is it :</td> 
<Td> 
</td>
<tr> 
<td>Is there a value and if so what is it :</td> 
<Td> 
</td>
<tr> 
<td>Is there a value and if so what is it :</td> 
<Td> 
</td>
<tr> 
<td>Is there a value and if so what is it :</td> 
<Td> 
</td>

mat41 · October 20th, 2011, 07:17 PM

mmmm no strange output there that I can think could be causing this. if you are getting results when running the query which calls a view, the view can not be the issue.

I have come accross this sort of this in the past which was caused by selecting a TEXT type data fields and displaying is before other varChar's (swapping rounf the order of the selected fields solves this). What are this three data types of these three fields. 2 x varchars and one int I assume.

Back to a a question in my last post. We need to find out what is in the invisible value. Try this:

Code:

 <%
Set objRS2 = objConn2.Execute("SELECT c.server_owner FROM serverreview c,server s where c.server_name = s.server_name and s.STATUS <> 'Decommissioned' and c.server_Owner is not null GROUP BY c.server_owner HAVING ( COUNT(*) > 0 )")
do until objRS2.eof %>
   <tr>
    <td>Is there a value and if so what is it :</td>
    <Td>
<%  if isNUll(objRS2(0)) then
       response.write "the c.server_owner value is NULL"
    elseif trim(objRS2(0)) = "" then 
       response.write "the c.server_owner value is ans empty string"
    elseif trim(objRS2(0)) <> "" then 
       response.write "There is a value it is : " & trim(objRS2(0))
 elseif len(objRS2(0))
    response.write "The value has " & len(objRS2(0)) & " number of chars"
 else
    response.write "the c.server_owner value is not null or empty"
 end if %>
    </td></tr>
<%    objRS2.moveNext
loop %>