Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert This CrossTab Stored Procedure to A Function (MS SQL Server 2000)

Posted on 2008-11-03
7
Medium Priority
?
454 Views
Last Modified: 2012-05-05
Hi..
Please help me convert this CrossTab Stored Procedure to A Function of MSSQL Server 2000.

Objective:
I want to display these results in my ASP webpage. I tried to execute the stored procedure in my ASP page, but I am not getting any results.

Code:
http://www.paladn.com/dynamic-crosstab-improved.htm

Regs
Jawahar
0
Comment
Question by:jawahar_prasad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22872342
Is the problem that you want this re written as a function, or that you want it to work in your ASP page? Becasue we could rewrite it as a function but if the proble is with your data or your calling code then you'll still get no data.
0
 
LVL 8

Author Comment

by:jawahar_prasad
ID: 22873394
I want to call it from asp page and display the results! But learnt from sources that, if it is a stored procedure returning a table then it is not possible to fetch data from ASP. So, I thought I will convert this to a function...

It tried:
mysql=" exec up_crossTab 'select * from tbl', ....... "

But it returns no result ! But, If i execute the same in Query Analyzer, then I get the results in the results window :-(
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 1400 total points
ID: 22873857
Thats untrue. You can call a stored procedure from ASP and see its returning recordset.
Here's one example:
http://authors.aspalliance.com/stevesmith/articles/sprocs.asp
 
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 8

Author Comment

by:jawahar_prasad
ID: 22875607
It doesnt return any rows when executed thru asp! But returns when executed through Query analyzer! The database is the same.

I have attached my code snippet!
<table width="100%"  border="0" cellpadding="5" cellspacing="1" class="tbl" id="tbl_filter">
<%
Dim objConn
Dim objRs
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.Recordset")
objConn.Open ConnString
mysql="exec iprompt..up_CrossTab 'SELECT * from rmon_ctab','comp_name','sum(results)[]','chk_item'"
objRs.Open mysql, objConn, adOpenStatic
if objRs.eof=false then
	iRecFirst=0
	iRecLast=objRs.recordcount
%>
  <tr>
    <td align="center" class="heading1">Checklist Item </td>
	<%for j=1 to objRs.Fields.count-2%>
    <td align="center" class="heading1"><%=objRs(j).Name%></td>
	<%next%>
  </tr>
<%
	For I = iRecFirst To iRecLast-2
%>
    <tr>
    <td width="50" align="center" class="heading"><%=objRs(0)%></td>
		<%for j=1 to objRs.Fields.count-2%>
		<td width="50" align="center" class="heading"><%if trim(objRs(j))="1" then
			response.Write "Yes"
		elseif trim(objRs(j))="0" then
			response.Write "No"
		elseif trim(objRs(j))="-1" then
			response.Write "NA"
		end if%></td>
		<%next%>
  </tr>
<%
	objRs.movenext
	next
%>
<%
end if
objRs.Close
Set objRs = Nothing
objConn.Close
Set objConn = Nothing
%>
</table>

Open in new window

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22882363
It doesnt return any rows when executed thru asp! But returns when executed through Query analyzer! The database is the same.
That means there is an error in your ASP code. There may be a number of reasons that its not working. Here's two that I can think of:
1. objRs.recordcount may return 0 even though there are records in the recorset
2. You need to use a command and parameter object to call the SP
I have altered and attached code which will work out whether 1 is the issue. It doesn't rely on RecordCount to extract the records.
Really the best way to troubleshoot this is to use debugging in Visual Studio to inspect the recordset object and see if it really does or doesn't have data.
 
 

<table width="100%"  border="0" cellpadding="5" cellspacing="1" class="tbl" id="tbl_filter">
<%
Dim objConn
Dim objRs
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.Recordset")
objConn.Open ConnString
mysql="exec iprompt..up_CrossTab 'SELECT * from rmon_ctab','comp_name','sum(results)[]','chk_item'"
objRs.Open mysql, objConn, adOpenStatic
if objRs.eof=false then
	iRecFirst=0
	iRecLast=objRs.recordcount
%>
  <tr>
    <td align="center" class="heading1">Checklist Item </td>
	<%for j=1 to objRs.Fields.count-2%>
    <td align="center" class="heading1"><%=objRs(j).Name%></td>
	<%next%>
  </tr>
<%
	Do Until objRs.eof
%>
    <tr>
    <td width="50" align="center" class="heading"><%=objRs(0)%></td>
		<%for j=1 to objRs.Fields.count-2%>
		<td width="50" align="center" class="heading"><%if trim(objRs(j))="1" then
			response.Write "Yes"
		elseif trim(objRs(j))="0" then
			response.Write "No"
		elseif trim(objRs(j))="-1" then
			response.Write "NA"
		end if%></td>
		<%next%>
  </tr>
<%
	objRs.movenext
	loop
%>
<%
end if
objRs.Close
Set objRs = Nothing
objConn.Close
Set objConn = Nothing
%>
</table>

Open in new window

0
 
LVL 8

Author Comment

by:jawahar_prasad
ID: 22883281
its still not returning any rows...
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 22883517
OK next thing to try is to use a command object to run the SP. Try the attached code... we're basically debugging your ASP code here.
There still may actually be records in the objRS recordset - ASP and VBScript is not my first calling, there might be bugs in my code. Really the best thing to do is try and inspect objRS from Visual Studio if you can work out how.

<table width="100%"  border="0" cellpadding="5" cellspacing="1" class="tbl" id="tbl_filter">
<%
Dim objConn
Dim objRs
Dom oCom
 
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.Recordset")
Set oCom = CreateObject("ADODB.Command")
 
objConn.Open ConnString
objConn.DefaultDatabase = "iprompt"
Set oCom.ActiveConnection = objConn
oCom.CommandText = "up_CrossTab"
oCom.CommandType = 4
oCom.Parameters.Refresh
oCom.Parameters(1) = "'SELECT * from rmon_ctab'"
oCom.Parameters(2) = "'comp_name'"
oCom.Parameters(3) = "'sum(results)[]'"
oCom.Parameters(4) = "'chk_item'"
 
Set objRs. = oCom.Execute
 
objRs.Open mysql, objConn, adOpenStatic
if objRs.eof=false then
	iRecFirst=0
	iRecLast=objRs.recordcount
%>
  <tr>
    <td align="center" class="heading1">Checklist Item </td>
	<%for j=1 to objRs.Fields.count-2%>
    <td align="center" class="heading1"><%=objRs(j).Name%></td>
	<%next%>
  </tr>
<%
	For I = iRecFirst To iRecLast-2
%>
    <tr>
    <td width="50" align="center" class="heading"><%=objRs(0)%></td>
		<%for j=1 to objRs.Fields.count-2%>
		<td width="50" align="center" class="heading"><%if trim(objRs(j))="1" then
			response.Write "Yes"
		elseif trim(objRs(j))="0" then
			response.Write "No"
		elseif trim(objRs(j))="-1" then
			response.Write "NA"
		end if%></td>
		<%next%>
  </tr>
<%
	objRs.movenext
	next
%>
<%
end if
objRs.Close
Set objRs = Nothing
objConn.Close
Set objConn = Nothing
%>
</table>

Open in new window

0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question