DataWindow Error - Heterogeneous queries

[sybase]

Dear All:

I am attempting to add a SQL query to a datawindow that
joins my database with another SQL Server (distributed
query). Both servers are SQL 2000 and are linked using the
SQL Server Linked Server functionality. When I run the
query
from SQL, it executes fine. But when I add it to the
datawindow
I get the following message: "Heterogeneous queries require
the ANSI_NULLS and ANSI_WARNINGS options to be set for the
connection. This ensures consistent query semantics.
Enable
these optinos and then reissue your query." Where would I
set
the options (ANSI Nulls, etc.) that are mentioned in the
attached error message? Does PB 7 allow these distributed
queries? Can you help?

Thank You

[Bruce Armstrong [TeamSybase]]

Try doing it in an EXECUTE IMMEDIATE prior to retrieving the
datawindow.

Also, if you're doing this based on a stored procedure, you should be
able to specify it at stored procedure creation time:

SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YadaYadaYada

On 4 Oct 2006 03:57:53 -0700, "sybase" <choy-ch...@elken.com.my>
wrote:

[sybase]

Thanks for the advices,

I had included the SET ANSI_NULLS ON & SET ANSI_WARNINGS ON in my store
procedure and in my program also included EXECUTE IMMEDIATE "SET ANSI_NULLS
ON" and EXECUTE IMMEDIATE "SET ANSI_WARNINGS ON" command as below

EXECUTE IMMEDIATE "SET ANSI_NULLS ON" ;
EXECUTE IMMEDIATE "SET ANSI_WARNINGS ON";
dw_pick.Retrieve('ICV', '000', 'zzz', '000', 'zzz', 1, 12000, 'MY',
datetime(TODAY()),datetime(TODAY()))
EXECUTE IMMEDIATE "SET ANSI_NULLS OFF" ;
EXECUTE IMMEDIATE "SET ANSI_WARNINGS OFF";

BUT, the message "Heterogeneous queries require the ANSI_NULLS and

ANSI_WARNINGS options to be set for the
connection. This ensures consistent query semantics. Enable these optinos

and then reissue your query." STILL OCCUR...:(

What can do to resolved this problem??

Thank You

"Bruce Armstrong [TeamSybase]" <NOCANSPAMbru...@teamsybase.com>
wrote in message news:kua7i2hoikn74g4mk...@4ax.com...

[Scott Morris]

"sybase" <choy-ch...@elken.com.my> wrote in message
news:45246881$1@forums-1-dub...

> Thanks for the advices,
>
> I had included the SET ANSI_NULLS ON & SET ANSI_WARNINGS ON in my store
> procedure and in my program also included EXECUTE IMMEDIATE "SET
> ANSI_NULLS ON" and EXECUTE IMMEDIATE "SET ANSI_WARNINGS ON" command as
> below
>
> EXECUTE IMMEDIATE "SET ANSI_NULLS ON" ;
> EXECUTE IMMEDIATE "SET ANSI_WARNINGS ON";
> dw_pick.Retrieve('ICV', '000', 'zzz', '000', 'zzz', 1, 12000, 'MY',
> datetime(TODAY()),datetime(TODAY()))
> EXECUTE IMMEDIATE "SET ANSI_NULLS OFF" ;
> EXECUTE IMMEDIATE "SET ANSI_WARNINGS OFF";
>
> BUT, the message "Heterogeneous queries require the ANSI_NULLS and
> ANSI_WARNINGS options to be set for the
> connection. This ensures consistent query semantics. Enable these optinos
> and then reissue your query." STILL OCCUR...:(
>
> What can do to resolved this problem??
>
> Thank You

If dw_pick uses a stored procedure as a datasource (not obvious from the
first post), then I recommend you read the documentation under "create
procedure" from MS sql server. There is important information about stored
procedures and options that are generally called "sticky". One of these
options is the ansi_nulls setting, which overrides the current setting for
your connection; you can't simply set it on inside of the procedure.

Secondly, there are certain requirements that you must meet before you can
use a linked server - obviously. It would be useful to review the
documentation about linked servers. Personally, I think you will have a
better, more stable, more predictable application if you decide what
connection settings you need to use for your application and implement them
correctly and consistently. I can see no generally useful purpose in
running your application with either ansi_nulls or ansi_warnings off.

[Bruce Armstrong [TeamSybase]]

I checked some technotes, and they suggest the very thing you're
doing. The one difference is that they set SQLCA.AutoCommit = TRUE
immediately before issuing the EXECUTE IMMEDIATES...

Another option is to modify the server configuration. These are
options you can set globally for the server.

http://msdn2.microsoft.com/en-us/library/ms179472.aspx

On 4 Oct 2006 19:05:53 -0700, "sybase" <choy-ch...@elken.com.my>