Creating a Datawindow from a Stored Procedure

[karenbailey]

Hi,

A client of ours has written their own reports using stored
procedures against a MS SQL Server 2000 database. They have
asked that we integrate their reports into our Powerbuilder
8.0.3 application.

I have attempted to create a datawindow from a stored
procedure but i get the following error message:

Cannot create DataWindow

'SQLSTATE = 22005
[Microsoft][ODBC SQL Server Driver]Invalid character value
for cast specification

1 execute dbo.alldata;1 @ProjectNumber =:ProjectNumber,
@Year = :Year'

This stored procedure has 2 arguments (@ProjectNumber
varchar(250), @Year varchar(50))

Does powerbuilder support creating a datawindow from a
stored procedure that has parameters? If yes, please
explain/provide steps on creating the datawindow.

Is there a cutoff size for the stored procedure? I noticed
while viewing the stored procedure in the database window in
PB8 that it is truncated, but in PB10, it is not truncated.

Thanks for the help,

Karen

[Jim O'Neil [Sybase]]

Sorry StripParmNames (plural)

On 23 Jan 2007 12:22:43 -0700, "Jim O'Neil [Sybase]"
<joneil_@_sybase_dot_com> wrote:

>Set the connection parameter (DBParm) StripParmName='yes'
>
>MS ODBC doesn't like the syntax to be sent with the parameter names
>included.

[Jim O'Neil [Sybase]]

Set the connection parameter (DBParm) StripParmName='yes'

MS ODBC doesn't like the syntax to be sent with the parameter names
included.

On 23 Jan 2007 10:17:21 -0700, Karen Bailey wrote:

[Chris Pollach]

Karen;

PB only supports dynamic DWO's from native SQL syntax. However, if your PB
application could read the DBMS's system tables to read the SP source, it
could extract the SP's SQL statement. With the extracted SQL, you could use
that for the DW generation.

Food for thought.

Regards ... Chris

<Karen Bailey> wrote in message news:45b65131.74...@sybase.com...

[Karen]

Hi Guys,

I am sorry. I should have been more specific. I am
attempting to create the datawindow using the datawindow
painter.

I select the grid datwindow type, then i select 'Stored
Procedure' for the datasource. I then select the stored
procedure i would like to use. Move through the defaults
and get the error when it tries to build the dw.

I will strip the arguments from the stored procedure and try
to run it again, but i will require those arguments to
correctly filter the data. I would prefer this to happen in
the stored procedure when the data is retrieved if possible.

Thanks,

Karen

[dawntbrowneyes]

I am using sql server 2000, parameters, PB10.5... works
great. When I get these errors it is from the stored
procedure. PB tries does something like a pre-run with the
stored procedure when you create a dw. So if there are any
errors it will return those errors. In the Query painter it
will keep on running but PW stops. Try running the stored
procedure with the query analyzer with no parameters. See
what if any errors are returned. You may have to search the
output for: 'cast specification'... When you find the error,
fix it or create defaults so you won't get this error when
you are creating the datawindow.

HTH Dawn

> > , >>@Year = :Year'

[Jim O'Neil [Sybase]]

My recommendation does not change. That option is available on the
SYntax tabs in the Database Profile dialog.

It's not the arguments that are the problem, it's the syntax
@ProjectNumber that is being prepended to the call to the MS driver.
It doesn't like that, whereas other ODBC drivers actually require it.
The parameter in PB is there to control what's being sent to the
backend database.

[Van]

Please make sure , if the retrieval options - > as Retrieve as needed &&
Retrieve to Disk are selected , then PB10.5 creates an another session at
the database server.
Regards, Van
<Karen> wrote in message news:45b673f6.d9...@sybase.com...

[Karen]

Jim,

Strip Parameter Names worked perfectly. Thank you very
much.

Thanks everyone for their comments/suggestions.

Karen

[M. Searer]

Retrieve as needed will put read locks on the rows.
Not a problem for oracle, but I wouldn't use it for SQL Server. Exception might
be for sql 2005 and the new transaction feature to make it oracle like.

"Van" <VanJa...@hotmail.com> wrote in message news:45b72f60@forums-1-dub...

[Jim O'Neil [Sybase]]

Great! Good luck.

[Van]

Well , it creates a new session on Informix 9.0 database :)
Was in an intention that it does for the database involved.

Regards, Van
"M. Searer" <nos...@nospam.com> wrote in message
news:45b796a8$1@forums-1-dub...