PB and MS SQL Server stored procs

Hi ,

We are using MS SQL Server 2000 with PowerBuilder 6.5 . There is a stored 
procedure that returns four columns from a table ( max 1 row).
The procedure has 2 input parameters and it returns 4 columns and 1 row as 
part of a select statement .

We are calling this procedure from PowerBuilder using DECLARE and 
trying to get the result using EXECUTE and FETCH . 
But there are no rows returned to PowerBuilder . 

The same EXECUTE gives the 1 row and 4 columns from with MS SQL SERVER ( 
using the Query Analyzer) . What do we do ?

We also tried using a datawindow with this procedure as the source .
It works at design time , but fails at run-time . . . what do we do ?


Thanks in Advance,
Denise 
0
Denise_Whitmore
11/19/2002 3:21:01 AM
sybase.powerbuilder.general 62418 articles. 17 followers. Follow

4 Replies
432 Views

Similar Articles

[PageSpeed] 3

PB 6.5 Native driver?  I got similar problem in PB7.0.3.

Please consider to upgrade PB and use OLE DB for MS SQL 2000.

Best regards,
David Wong

--
Best Regards


<Denise_Whitmore>
???????:0182EA1F418BFD110012677085256C76.0012679B85256C76@webforums...
> Hi ,
>
> We are using MS SQL Server 2000 with PowerBuilder 6.5 . There is a stored
> procedure that returns four columns from a table ( max 1 row).
> The procedure has 2 input parameters and it returns 4 columns and 1 row as
> part of a select statement .
>
> We are calling this procedure from PowerBuilder using DECLARE and
> trying to get the result using EXECUTE and FETCH .
> But there are no rows returned to PowerBuilder .
>
> The same EXECUTE gives the 1 row and 4 columns from with MS SQL SERVER (
> using the Query Analyzer) . What do we do ?
>
> We also tried using a datawindow with this procedure as the source .
> It works at design time , but fails at run-time . . . what do we do ?
>
>
> Thanks in Advance,
> Denise


0
Michael
11/19/2002 9:35:32 AM
>>Please consider to upgrade PB and use OLE DB for MS SQL 2000.

This doesn't always work, either.

I just discovered that you cannot create a new DW using the new
DW wizard from a stored proc (such as a system stored proc).  I
never realized it, but PB around here is SO legacy that I had no
need to create a new one since PB 6.

In 8.0.2 at least, the system procedures checkbox remains
disabled!!!  You can't even get started.

I went back to 7.0.3 and tried to create it, but it kept
complaining that @objname was not a parameter to the stored proc.
Whatever the first parameter was, it objected to it regardless.

I ended up copying the SP to a new SP that had no arguments and
was not in master.  Then I could use PB 7 to create the DW,
export, and import to 8.  Modify the source to change the name
of the SP and add back the arguments.  When I copied the SP,
I took the arguments off and declared them in the SP just as
regular variables.

This seemed to work, but with Windows Integrated Security, 8 is
still busted.  Tried MSS, too, both PB7 and 8, to no avail.

I think it's impossible to create a DW directly from an MS
SQL Server 2000 DB SP.  What might work is an inline SQL
statement (possibly DYNAMIC syntax).  Then hand copy the
results into an external datastore.

#$%^&*!!!
0
woz
11/19/2002 4:53:34 PM
As someone else said, go to 8.0.3. If you do go to ole db all procedures 
that attach to datawindows will have to be owned by dbo. Why, I don't have 
a clue.
If you use MSS native driver, you will lose some sql 2000 
functionality, like varchar greater than 255.

Good Luck
0
SB
11/19/2002 6:43:53 PM
Yes, so I am using PB6.5 to create the DW and import to PB7.0.3.

--
Best Regards


<woz> ???????:44839CC2EFC48B2E005CCB6B85256C76.003AAF6685256C76@webforums...
> >>Please consider to upgrade PB and use OLE DB for MS SQL 2000.
>
> This doesn't always work, either.
>
> I just discovered that you cannot create a new DW using the new
> DW wizard from a stored proc (such as a system stored proc).  I
> never realized it, but PB around here is SO legacy that I had no
> need to create a new one since PB 6.
>
> In 8.0.2 at least, the system procedures checkbox remains
> disabled!!!  You can't even get started.
>
> I went back to 7.0.3 and tried to create it, but it kept
> complaining that @objname was not a parameter to the stored proc.
> Whatever the first parameter was, it objected to it regardless.
>
> I ended up copying the SP to a new SP that had no arguments and
> was not in master.  Then I could use PB 7 to create the DW,
> export, and import to 8.  Modify the source to change the name
> of the SP and add back the arguments.  When I copied the SP,
> I took the arguments off and declared them in the SP just as
> regular variables.
>
> This seemed to work, but with Windows Integrated Security, 8 is
> still busted.  Tried MSS, too, both PB7 and 8, to no avail.
>
> I think it's impossible to create a DW directly from an MS
> SQL Server 2000 DB SP.  What might work is an inline SQL
> statement (possibly DYNAMIC syntax).  Then hand copy the
> results into an external datastore.
>
> #$%^&*!!!


0
Michael
11/20/2002 7:12:59 AM
Reply: