Incomplete response from executescalar()

[noisymime]

I'm using a stored procedure that utilises the FOR XML command to retrieve information from an SQL Server 7 database.

 

The stored procedure is accessed using the executescalar method within VB.NET code.

 

The whole thing works fine except that when the XML returned by the stored procedure is too long (approx 2030 characters or more) its just cut off. Is there some limit on the number of characters that can be returned by the executescalar method is a similar limit on how much data can be returned using FOR XML?

 

VB Code:

 

XMLFile = "<?xml version='1.0'?><carSearch xmlns='http://www.ballarat.edu.au/schema/CarSearch'>" + cmSearch.ExecuteScalar + "</carSearch>"

 

SQL:

CREATE PROCEDURE dbo.carSearch

@carName nvarchar(25),

@manufacturer nvarchar(50),

@maxPower smallint,

@minPower smallint

 

AS

SELECT

1 AS Tag,

NULL AS Parent,

CarName AS [car!1!name!element],

name AS [car!1!engine!element],

manufacturer AS [car!1!manufacturer!element],

cars.maxPower AS [car!1!maxPower!element]

FROM engine, cars

WHERE

(cars.carname LIKE @carName) AND

(engine.manufacturer LIKE @manufacturer) AND

(cars.MaxPower > @maxPower) AND

(cars.MaxPower < @minPower)AND

(engine.name = cars.Engine)

FOR XML EXPLICIT

 

RETURN

 

The </carSearch> tag is added at the end so it is definitely a problem with either executeScalar or the text returned by FOR XML.

 

Is anyone able to assist??

[Derek Stone]

You should use the ExecuteReader method instead. Keep calling its Read method (which truncates data beyond 4K) until all the data is read. You can retrieve the data using GetString(0).

[noisymime]

Perfect

 

Worked like a charm.

 

Thanks s a heap