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.

Retrieve function fails with distributed query

5 posts in DataWindow Last posting was on 2009-05-15 19:37:07.0Z
J Franklin Posted on 2009-05-13 21:18:30.0Z
Sender: 56b5.4a0b2216.1804289383@sybase.com
From: J Franklin
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Retrieve function fails with distributed query
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a0b3926.5a26.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 May 2009 14:18:30 -0700
X-Trace: forums-1-dub 1242249510 10.22.241.41 (13 May 2009 14:18:30 -0700)
X-Original-Trace: 13 May 2009 14:18:30 -0700, 10.22.241.41
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89279
Article PK: 418530

Hello there,

I am trying to retrieve information (in PB 9.0.3) through a
datawindow with a select statement that performs a join on
two tables sitting in different SQL Server 2000 databases on
different servers ie:

select a.fields, b.fields
from
servera.databasea.dbo.tablea a
inner join serverb.databaseb.dbo.tableb b on a.somefield =
b.somefield;

The servers have been linked correctly in SQL Server and I
have no problems running this query in query analyzer. The
system DSN has been configured to use ANSI quoted
identifiers and use ANSI nulls, paddings, and warnings. I
can also run the same query successfully through the
database painter in Powerbuider. When I create my datawindow
object I can use the statement above as the backend and I do
not encounter any errors when I attempt to save. Despite all
this when I call the retrive function against the datawindow
when I am running my code the retrieve function fails
returning a -1. I also tried to place this sql in a stored
procedure and then connected the SP to my datawindow but I
am still getting the same result. Does anyone know how you
can get this distributed query to run from Powerbuilder?
Thanks for your help...


Scott Morris Posted on 2009-05-14 12:17:13.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4a0b3926.5a26.1681692777@sybase.com>
Subject: Re: Retrieve function fails with distributed query
Lines: 21
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a0c0bc9$1@forums-1-dub>
Date: 14 May 2009 05:17:13 -0700
X-Trace: forums-1-dub 1242303433 10.22.241.152 (14 May 2009 05:17:13 -0700)
X-Original-Trace: 14 May 2009 05:17:13 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89284
Article PK: 418536


> database painter in Powerbuider. When I create my datawindow
> object I can use the statement above as the backend and I do
> not encounter any errors when I attempt to save. Despite all
> this when I call the retrive function against the datawindow
> when I am running my code the retrieve function fails
> returning a -1. I also tried to place this sql in a stored
> procedure and then connected the SP to my datawindow but I
> am still getting the same result. Does anyone know how you
> can get this distributed query to run from Powerbuilder?
> Thanks for your help...

Step 1 is to post the complete and exact error message. Step 2 is to post
the values you set in the transaction object before connecting. BTW - the
fact that you can create the datawindow (and, presumably, view the preview
pane) successfully indicates that your DB profile you use in the IDE has
different values from those of your transaction object. A simple and easy
test is to use those same values - exactly - in your transaction object. In
addition, you might want to search the newsgroups for suggestions since it
has been discussed previously.


J Franklin Posted on 2009-05-14 20:18:15.0Z
Sender: 5a8d.4a0b3cbe.1804289383@sybase.com
From: J Franklin
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Retrieve function fails with distributed query
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a0c7c87.7cab.1681692777@sybase.com>
References: <4a0c0bc9$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 May 2009 13:18:15 -0700
X-Trace: forums-1-dub 1242332295 10.22.241.41 (14 May 2009 13:18:15 -0700)
X-Original-Trace: 14 May 2009 13:18:15 -0700, 10.22.241.41
Lines: 60
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89295
Article PK: 418547

Hi there,

Step 1]

if l_ds.retrieve() = -1 then
f_error("Could not retrieve data.")
halt close
end if

The code above returns true

Step 2]

l_transaction.DBMS = "ODBC"
l_transaction.AutoCommit = False
l_transaction.DBParm =
"ConnectString='DSN=mydsn;UID=myuserid;PWD=mypassword',CommitOnDisconnect='No'"

l_ds = create datastore
l_ds.dataobject = "d_datawindow"
l_ds.settransobject(l_transaction)

Note: I setup the DBMS, AutoCommit, and DBParm properties of
my transaction object using the database connection syntax
in the preview tab of my DB Profile in the IDE which would
imply to me that the DB Profile and the transaction object
are using the same settings. Also if I modify the SQL of my
datawindow so that it is not doing a table level join across
servers then the retrive function call does not return an
error (ie. if there was a problem with the transaction
object this should fail as well). It is only when the SQL
performs a join across servers that the retrieve function
fails. I have been through the newsgroups but have not seen
a posting that helped me resolve this issue. In any case
thanks for the reply

> > database painter in Powerbuider. When I create my
> > datawindow object I can use the statement above as the
> > backend and I do not encounter any errors when I attempt
> > to save. Despite all this when I call the retrive
> > function against the datawindow when I am running my
> > code the retrieve function fails returning a -1. I also
> > tried to place this sql in a stored procedure and then
> > connected the SP to my datawindow but I am still getting
> > the same result. Does anyone know how you can get this
> > distributed query to run from Powerbuilder? Thanks for
> your help...
>
> Step 1 is to post the complete and exact error message.
> Step 2 is to post the values you set in the transaction
> object before connecting. BTW - the fact that you can
> create the datawindow (and, presumably, view the preview
> pane) successfully indicates that your DB profile you use
> in the IDE has different values from those of your
> transaction object. A simple and easy test is to use
> those same values - exactly - in your transaction object.
> In addition, you might want to search the newsgroups for
> suggestions since it has been discussed previously.
>
>


Scott Morris Posted on 2009-05-14 20:50:53.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4a0c0bc9$1@forums-1-dub> <4a0c7c87.7cab.1681692777@sybase.com>
Subject: Re: Retrieve function fails with distributed query
Lines: 54
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a0c842d@forums-1-dub>
Date: 14 May 2009 13:50:53 -0700
X-Trace: forums-1-dub 1242334253 10.22.241.152 (14 May 2009 13:50:53 -0700)
X-Original-Trace: 14 May 2009 13:50:53 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89296
Article PK: 418548


<J Franklin> wrote in message news:4a0c7c87.7cab.1681692777@sybase.com...
> Hi there,
>
> Step 1]
>
> if l_ds.retrieve() = -1 then

Is this a datastore? If so, you need to catch the error in the dberror
event - which is probably empty if you created a plain ole datastore.
Without knowing the actual error message, it's going to be difficult to
diagnose what exactly is occurring. To go a step further, it helps to have
proper error handling since many things can go wrong in that you cannot
reasonably anticipate in your application. A plain ole datastore has none
and simpley ignores all errors that occur.

> f_error("Could not retrieve data.")
> halt close
> end if
>
> The code above returns true
>
> Step 2]
>
> l_transaction.DBMS = "ODBC"
> l_transaction.AutoCommit = False
> l_transaction.DBParm =
> "ConnectString='DSN=mydsn;UID=myuserid;PWD=mypassword',CommitOnDisconnect='No'"
>
> l_ds = create datastore
> l_ds.dataobject = "d_datawindow"
> l_ds.settransobject(l_transaction)
>
> Note: I setup the DBMS, AutoCommit, and DBParm properties of
> my transaction object using the database connection syntax
> in the preview tab of my DB Profile in the IDE which would
> imply to me that the DB Profile and the transaction object
> are using the same settings. Also if I modify the SQL of my
> datawindow so that it is not doing a table level join across
> servers then the retrive function call does not return an
> error (ie. if there was a problem with the transaction
> object this should fail as well). It is only when the SQL
> performs a join across servers that the retrieve function
> fails. I have been through the newsgroups but have not seen
> a posting that helped me resolve this issue. In any case
> thanks for the reply

Use the Profiler tool that comes with the MS client install and monitor your
application. You should see the connection settings - which should allow
you to duplicate the problem in query analyzer AND let you see any errors or
messages that are currently hidden by your code. In fact, the profiler is
the easiest way to diagnose problems with sql server and a PB application.


J Franklin Posted on 2009-05-15 19:37:07.0Z
Sender: 5a8d.4a0b3cbe.1804289383@sybase.com
From: J Franklin
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Retrieve function fails with distributed query
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a0dc463.2209.1681692777@sybase.com>
References: <4a0c842d@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 May 2009 12:37:07 -0700
X-Trace: forums-1-dub 1242416227 10.22.241.41 (15 May 2009 12:37:07 -0700)
X-Original-Trace: 15 May 2009 12:37:07 -0700, 10.22.241.41
Lines: 72
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89306
Article PK: 418557

Hi there,

I trapped the error I'm getting at runtime which is:

"[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]"

I have played around with my MSDTC settings (looking at some
MS articles) to see if I could get this to work at runtime
but I keep getting the same error. Do you know why this
would not be a problem when the query is ran from the PB IDE
but is a problem at runtime? Thanks a lot.

> <J Franklin> wrote in message
> > news:4a0c7c87.7cab.1681692777@sybase.com... Hi there,
> >
> > Step 1]
> >
> > if l_ds.retrieve() = -1 then
>
> Is this a datastore? If so, you need to catch the error
> in the dberror event - which is probably empty if you
> created a plain ole datastore. Without knowing the actual
> error message, it's going to be difficult to diagnose
> what exactly is occurring. To go a step further, it helps
> to have proper error handling since many things can go
> wrong in that you cannot reasonably anticipate in your
> application. A plain ole datastore has none and simpley
> ignores all errors that occur.
>
> > f_error("Could not retrieve data.")
> > halt close
> > end if
> >
> > The code above returns true
> >
> > Step 2]
> >
> > l_transaction.DBMS = "ODBC"
> > l_transaction.AutoCommit = False
> > l_transaction.DBParm =
> > "ConnectString='DSN=mydsn;UID=myuserid;PWD=mypassword'
> ,CommitOnDisconnect='No'" >
> > l_ds = create datastore
> > l_ds.dataobject = "d_datawindow"
> > l_ds.settransobject(l_transaction)
> >
> > Note: I setup the DBMS, AutoCommit, and DBParm
> > properties of my transaction object using the database
> > connection syntax in the preview tab of my DB Profile in
> > the IDE which would imply to me that the DB Profile and
> > the transaction object are using the same settings. Also
> > if I modify the SQL of my datawindow so that it is not
> > doing a table level join across servers then the retrive
> > function call does not return an error (ie. if there was
> > a problem with the transaction object this should fail
> > as well). It is only when the SQL performs a join across
> > servers that the retrieve function fails. I have been
> > through the newsgroups but have not seen a posting that
> > helped me resolve this issue. In any case thanks for the
> reply
>
> Use the Profiler tool that comes with the MS client
> install and monitor your application. You should see the
> connection settings - which should allow you to duplicate
> the problem in query analyzer AND let you see any errors
> or messages that are currently hidden by your code. In
> fact, the profiler is the easiest way to diagnose
> problems with sql server and a PB application.
>
>