ADO XML

Question

ADO XML

chisholmd

SSCrazy

Points: 2490
More actions
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 %>

<%
   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)

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

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

chisholmd SSCrazy Points: 2490 More actions · Answer 1

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)

chisholmd SSCrazy Points: 2490 More actions · Answer 2

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)

chisholmd SSCrazy Points: 2490 More actions · Answer 3

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)

chisholmd SSCrazy Points: 2490 More actions · Answer 4

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)