Problem with empty resultsets from SQL 2005 and RDO

We have an application written in VB6 using RDO that we're trying to migrate to SQL 2005 from 2000. We've encountered a problem where a stored procedure returns a resultset and calls another stored procedure that returns another resultset. If the second resultset does not contain any rows and the second stored procedure generates a warning message ("Warning: Null value is eliminated by an aggregate or other SET operation."), the MoreResults method fails in trying to access the second resultset.

If the second resultset is not empty or I modify the stored procedure to eliminate the warning message, then I am able to access the second resultset. The exact same code works against a SQL 2000 server with identical stored procedures and data, regardless of the the resutlset being empty or the warning message being generated. I can also execute the stored procedure calls from Query Analyzer and the results look identical in SQL 2000 and 2005, but somehow there's something different in how RDO sees the results.

Has anyone else seen this problem or have I missed something? SET ANSI_WARNINGS OFF would eliminate the message, but we use indexed views, which requires ANSI_WARNINGS ON.

Can you provide us with sample repro code for this issue?

Thanks
Suroor

Problem with dynamic SQL syntax

What exactly do you mean by an empty field? Generally if
your field allows null and you don't set a value into
that field for a row, the field is set to NULL and you
can test for this by using "type IS NULL" in your where
clause.
"<>" is the operator for not equals and it is not a
singleton operator, it is a comparison operator. You
need something on both sides of the "<>" to compare to
each other.
For instance, if you are looking for rows where the type
field is not equal to a space, you could try "type <> ' '"
I hope that this helps.
Matthew Bando
bandoM@.CSCTechnologies-dot-com

>--Original Message--
>I' m having a problem with the syntax when I'm trying to
run a dynamic SQL
>statement.
>The code -
>set @.sql = 'SELECT *
INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
>
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
le_name+' Where
>Date_ >='+'2001-01-01'+'
> And CompanyNo Is Not NULL And Type <>'
>exec sp_executesql @.sql
>- gives me the error "Incorrect syntax near '>'." The
purpose of the last <>
>is to find the records where this field is empty (that's
my understanding of
>it...). The basic structure of the query is from a DTS
Transform task, but
>I'm trying to "convert" this whole task to TSql.
>I've tried all sorts of different combinations of <>
and ' but it still
>won't do it. If I just prins the @.sql var. it looks fine.
>The CompanyNo field is int(4) and the Type field is
varchar(30).
>Is there any other ways to check for an empty varchar
field of can some of
>you guide me to what it is I'm missing in my "set
@.sql...." statement?
>Best Regards
>Steen
>
>.
>Hi
Sorry if I wasn't very clear. I assume that the purpose is to check for an
empty field. The "original" code that's being used in the DTS Transform task
is "...AND CompanyNo is not NULL and Type <>'' ". It's not me that have
written the transform task, but I assume that this last piece checks if
there're any empty fields. It might be my understanding of it that's wrong,
but then I'd be happy to hear about it.
This SQL statement runs fine in the DST task and also when I run it in Query
analyser using fixed values, but when I do it with variables/dynamic SQL it
seems to fail and not accept this last bit.
Regards
Steen
"Matthew Bando" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:071c01c46e4c$74405700$a501280a@.phx.gbl...[vbcol=seagreen]
> What exactly do you mean by an empty field? Generally if
> your field allows null and you don't set a value into
> that field for a row, the field is set to NULL and you
> can test for this by using "type IS NULL" in your where
> clause.
> "<>" is the operator for not equals and it is not a
> singleton operator, it is a comparison operator. You
> need something on both sides of the "<>" to compare to
> each other.
> For instance, if you are looking for rows where the type
> field is not equal to a space, you could try "type <> ' '"
> I hope that this helps.
> Matthew Bando
> bandoM@.CSCTechnologies-dot-com
>
> run a dynamic SQL
> INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
> le_name+' Where
> purpose of the last <>
> my understanding of
> Transform task, but
> and ' but it still
> varchar(30).
> field of can some of
> @.sql...." statement?|||Sorry. It was the missing second single quote I was
referring to as missing.
Aaron is correct. You need to repeat the single quotes
since they are inside of a quoted expression.

>--Original Message--
>Hi
>Sorry if I wasn't very clear. I assume that the purpose
is to check for an
>empty field. The "original" code that's being used in
the DTS Transform task
>is "...AND CompanyNo is not NULL and Type <>'' ". It's
not me that have
>written the transform task, but I assume that this last
piece checks if
>there're any empty fields. It might be my understanding
of it that's wrong,
>but then I'd be happy to hear about it.
>This SQL statement runs fine in the DST task and also
when I run it in Query
>analyser using fixed values, but when I do it with
variables/dynamic SQL it
>seems to fail and not accept this last bit.
>Regards
>Steen
>"Matthew Bando" <anonymous@.discussions.microsoft.com>
skrev i en meddelelse
>news:071c01c46e4c$74405700$a501280a@.phx.gbl...
if[vbcol=seagreen]
type[vbcol=seagreen]
<> ' '"[vbcol=seagreen]
to[vbcol=seagreen]
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab[vbcol=seagreen]
(that's[vbcol=seagreen]
fine.[vbcol=seagreen]
>
>.
>