Solved

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

Posted on 2008-11-03
7
446 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
  • 4
  • 3
7 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
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
Comment Utility
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 350 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 8

Author Comment

by:jawahar_prasad
Comment Utility
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
Comment Utility
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
Comment Utility
its still not returning any rows...
0
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now