exec dynamic_pivot2 'select a.activityfinished ,s.studentsid,s.firstname,s.lastname,s.email, q.quizid, q.quizname, REPLACE(REPLACE(REPLACE(aq.questselected, CHAR(10) + CHAR(13), '' ''),CHAR(10), '' ''), CHAR(13), '' '') as questselected from activityquiz aq inner join activity a on a.activityid = aq.activityid inner join students s on s.studentsid = a.studentsid inner join quizquestions qq on qq.questid = aq.questid inner join quizzes q on q.quizid = qq.quizid where q.quizid = 6158','substring(REPLACE(REPLACE(REPLACE(questquestion, CHAR(10) + CHAR(13), '' ''),CHAR(10), '' ''), CHAR(13), '' ''),1,125)','max(questselected)','activityfinished'

Set oCon = Server.CreateObject("ADODB.Connection")
Set getResults = Server.CreateObject("ADODB.RecordSet")
oCon.Open MM_LMSdb_STRING

sQuizID = "6158"

sParam1 = "select a.activityfinished ,s.studentsid,s.firstname,s.lastname,s.email, q.quizid, q.quizname, REPLACE(REPLACE(REPLACE(aq.questselected, CHAR(10) + CHAR(13), ' '),CHAR(10), ' '), CHAR(13), ' ') as questselected from activityquiz aq inner join activity a on a.activityid = aq.activityid inner join students s on s.studentsid = a.studentsid inner join quizquestions qq on qq.questid = aq.questid inner join quizzes q on q.quizid = qq.quizid where q.quizid = " & sQuizID
sParam2 = "substring(REPLACE(REPLACE(REPLACE(questquestion, CHAR(10) + CHAR(13), ' '),CHAR(10), ' '), CHAR(13), ' '),1,125)"
sParam3 = "max(questselected)"
sParam4 = "activityfinished"

Set cmd = server.CreateObject("ADODB.Command")

'response.Write sParam1
'response.End

With cmd
        Set .ActiveConnection = oCon
        .CommandType = 4
        .CommandText = "dynamic_pivot2"
        
        .Parameters.Append .CreateParameter("@Parm1", 200, 1, 1512, sParam1)
        .Parameters.Append .CreateParameter("@Parm2", 200, 1, 1512, sParam2)
        .Parameters.Append .CreateParameter("@Parm3", 200, 1, 1255, sParam3)
        .Parameters.Append .CreateParameter("@Parm4", 200, 1, 1255, sParam4)        
End With

getResults.CursorLocation = 3
getResults.Open cmd, , 3, 1

dim x
x = 0
do while x < getResults.fields.count
    oString.Append getResults.fields(x).name
    oString.append ","
    x = x + 1
loop

quote:

---

Originally posted by tkizer

Do you have "SET NOCOUNT ON" in the stored procedure at the top?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---