Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Heterogeneous queries...

2 posts in DataWindow Last posting was on 2008-08-27 17:49:37.0Z
Douglas V. Posted on 2008-08-27 16:10:58.0Z
Sender: 5447.48b2f244.1804289383@sybase.com
From: Douglas V.
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Heterogeneous queries...
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <48b57c92.f95.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 27 Aug 2008 09:10:58 -0700
X-Trace: forums-1-dub 1219853458 10.22.241.41 (27 Aug 2008 09:10:58 -0700)
X-Original-Trace: 27 Aug 2008 09:10:58 -0700, 10.22.241.41
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87650
Article PK: 416908

Hi, I'm attempting to create a simple datawindow report that
accesses data in a table across a linked server in SQL2000
using the MSS native driver. I get the old "Heterogeneous
queries require the ANSI_NULLS and ANSI_WARNINGS options to
be set..." error. I can run the query with no problems in
an ISQL session. I've even tried putting the query inside
of a stored procedure that contained SET ANSI_NULLS ON and
SET ANSI_WARNINGS ON but it still fails. I can also execute
this procedure from ISQL with no problems. I know I can get
around this by explicity doing an "EXECUTE IMMEDIATE SET
ANSI_NULLS ON" in my powerscript before the SQL call, but my
question is, how do I actually create the datawindow in the
first place? I can't get around this error message in the
datawindow painter??


Scott Morris Posted on 2008-08-27 17:49:37.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <48b57c92.f95.1681692777@sybase.com>
Subject: Re: Heterogeneous queries...
Lines: 41
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48b593b1$1@forums-1-dub>
Date: 27 Aug 2008 10:49:37 -0700
X-Trace: forums-1-dub 1219859377 10.22.241.152 (27 Aug 2008 10:49:37 -0700)
X-Original-Trace: 27 Aug 2008 10:49:37 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87652
Article PK: 416907


<Douglas V.> wrote in message news:48b57c92.f95.1681692777@sybase.com...
> Hi, I'm attempting to create a simple datawindow report that
> accesses data in a table across a linked server in SQL2000
> using the MSS native driver. I get the old "Heterogeneous
> queries require the ANSI_NULLS and ANSI_WARNINGS options to
> be set..." error. I can run the query with no problems in
> an ISQL session. I've even tried putting the query inside
> of a stored procedure that contained SET ANSI_NULLS ON and
> SET ANSI_WARNINGS ON but it still fails. I can also execute
> this procedure from ISQL with no problems. I know I can get
> around this by explicity doing an "EXECUTE IMMEDIATE SET
> ANSI_NULLS ON" in my powerscript before the SQL call, but my
> question is, how do I actually create the datawindow in the
> first place? I can't get around this error message in the
> datawindow painter??

AFAIK, the stored procedure approach should work. Before re-trying this
approach, I suggest that you read the sql server documentation on these two
settings and pay special attention to the information related to the
different interfaces (and their default settings). Pay special attention to
the note in the ansi_nulls section about stored procedures. Attempting to
set ansi_nulls inside of the procedure has NO effect; the procedure inherits
the ansi_null setting from the connection when created. The
quoted_identifier setting is also inherited in the same manner. However,
you can set ansi_warnings inside of a procedure. Note - do NOT use
Enterprise Manager to create your procedures since you cannot see (nor have
any control over) the connection settings in use at the time of creation.
Use a script that contains the needed SET statements prior to the CREATE
PROCEDURE statement.

The only other alternative (without resorting to trickery), AFAIK, is to
connect to the database using either ODBC or OLE DB. I'm not aware of any
way within the IDE to change the connection settings once connected. Using
trickery, you could create the "foreign" table in the current database and
use it for design purposes. Once designed, just modify the syntax and
change the table name. You will still get a runtime error if you forget to
change the connection settings at runtime as needed. Of course, you won't
be able to modify the datawindow in the IDE - but that's the price of using
trickery and an obsolete DB interface.