Post reply

ADO XML

  • February 17, 2005 at 2:09 am

    #85474

    Ok The really frustrating part is that this was working for a week or more then stopped. Yes I did change something, at one point I restarted my sql2000 server, and I also uninstalled some server monitoring software I was checking out.

    win2003, ado v2.8

    proc works from query analyser just find

    running command to an recordset works fine

    It only seems to break when performing the execute with the stream option.

    ADO Gives me a very useful "Exception Error" at

    -->objComm.Execute , , 1024 'adExecuteStream = 1024

    I've been going over it for an hour or so and I can't develop any clues.  All suggestions on how to debug appriciated.

    <%@ Language=VBScript %>

    <!--#include virtual="/xxxxxx/cn.asp"-->

    <%

       Response.ContentType = "text/xml"

       Dim objComm

       Set objComm   = Server.CreateObject("ADODB.Command")

       Set objComm.ActiveConnection = cn

       Response.Write "<trainmark>"

       objComm.CommandText = "EXEC [dbo].[tm2_webServiceVendors]"

       objComm.Properties("Output Stream") = Response

       objComm.Execute , , 1024 'adExecuteStream = 1024

       Response.Write "</trainmark>"

       Set objComm = Nothing

       cn.Close

       Set cn = Nothing

    %>

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

  • February 17, 2005 at 2:16 am

    #542737

    Oh ya, It is important to add that 4 or 5 ASP pages that retrieve "FOR XML" data from sql all broke at the same time. So that would strongly suggest that either something was reset when I restarted SQL or a dependency was removed when I uninstalled the server monitoring software.

    Trouble is I have no idea where to start to figure it out...I guess I could re-intall opManager and see....

     

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

  • February 17, 2005 at 10:08 am

    #542873

    EUREKA!!

    Remembering the last time I felt this frustrated lead me to the problem. 

    SET ANSI_WARNINGS OFF

    I assume that at some point in the past I had turned ANSI warning off in the property sheet for the server but didn't 'save' them properly (maybe thats what RECONFIGURE is for)

    So anyway when I restarted SQL that global setting was not restored. 

    The next time something works fine in QueryAnalyser but not from ADO this is the first thing I will try

    Thanks

     

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

  • February 17, 2005 at 10:35 am

    #542880

    False alarm. I added warnings off and it worked, but now it stopped working again.  It just keeps getting weirder and weirder. 

     

    Im stumped.

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

  • February 17, 2005 at 12:02 pm

    #542909

    Well I guess I am having a conversation with myself

    Here is an update for what it's worth.  I restarted the IIS services and it started working again. During shutdown IIS reported some stuck process that it had to kill.

    Just to be sure I made sure to close and destroyed my connection and command objects despit Eric Lippert's excellent blog about how this is unneccessary to do just before the objects go out of scope.

    http://weblogs.asp.net/ericlippert/archive/2004/04/28/122259.aspx

    Fingers crossed that this issue does not return. Programming by pray

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply