Sql Server 2000 store procedure with a date parameter.

are u using the stored procedure as datasource ?when u create the datawindow you could just select the sp as ur datasource and in ur code u'd 've it as
dw_report.Retrieve(em_date.text)

or
https://www.experts-exchange.com/questions/21244750/Stored-Procedures-Sql-Server-with-Power-Builder-8.html

Here is my procedure in SqlServer 2000

CREATE procedure usp_LowFatSpread @input datetime
as

DECLARE @START DATETIME
dECLARE @END DATETIME

SELECT @START=CONVERT(CHAR(11),@INPUT)
      ,@END=cONVERT(CHAR(11),@iNPUT) + ' 23:59:59PM'

SELECT LOOM_NO,AT_DATE_TIME,CASE WHEN SUB_STATUS_CODE = 8 THEN -1 ELSE SUB_STATUS_CODE END
         AS sTATUS
      ,IDENTITY(INT,1,1) AS ROWNO
  INTO #TEMP
  FROM Loom_Pick_Count
 wHERE AT_DATE_TIME BETWEEN @START AND @END
   AND SUB_STATUS_CODE IN (1,2,4,8,16,64,127,128)
 ORDER BY LOOM_NO,at_DATE_TIME
 
sELECT X .LOOM_NO
,SUM(CASE STATUS WHEN 1 THEN LOST ELSE 0 END) AS  [ Knotting Stop]
,SUM(CASE STATUS WHEN 4 THEN 1 ELSE 0 END) AS  [ weft count]
,SUM(CASE STATUS WHEN 2 THEN LOST ELSE 0 END) AS  [ Article Stop]
,SUM(CASE STATUS WHEN 4 THEN LOST ELSE 0 END) AS  [ Weft Stop]
,SUM(CASE STATUS WHEN 16 THEN LOST ELSE 0 END) AS  [ Warp Stop]
,SUM(CASE STATUS WHEN 64 THEN LOST ELSE 0 END) AS  [ Leno Stop]
,SUM(CASE STATUS WHEN 127 THEN LOST ELSE 0 END) AS  [ Other Stop]
,SUM(CASE STATUS WHEN 128 THEN LOST ELSE 0 END) AS  [ Electro-Mechanical Stop]

  FROM (
sELECT a.LOOM_NO
      ,A.STATUS
      ,DATEDIFF(S,A.AT_DATE_TIME,COALESCE(B.AT_DATE_TIME,@end)) AS LOST

  fROM ( SELECT ROWNO,LOOM_NO,sTATUS ,AT_dATE_TIME  FROM #temp wHERE sTATUS > -1 ) AS a
 lEFT oUTER JOIN (
  SELECT ROWNO,LOOM_NO ,sTATUS ,AT_dATE_TIME   FROM #temp ) AS B   ON A.LOOM_NO=B.LOOM_NO  AND B.ROWNO=A.ROWNO + 1
    ) AS X

 GROUP BY LOOM_NO

-- exec usp_LowFatSpread '2005-03-17'
GO

I cannot use it as datasource because data will be determined after the user has inputted the
date from powerbuilder.

hi,


try,

Datetime ldt_to

Declare ur_procedure Procedure for usp_LowFatSpread
                  @to_date = :ldt_to
                        Using SQLCA ;
            
            Execute ur_procedure ;
            
            If SQLCA.SQLCode < 0 then
                  MessageBox("", "Error executing db procedure usp_LowFatSpread - " + string(SQLCA.sqlcode) + " " + SQLCA.sqlerrtext)
                  Close ur_procedure ;
                  Return
            End If
            
            Close ur_procedure ;


let me know if u need more assistance...

Cheers,
Rosh

Hi Dia:
As u suggest, If I declare and execute , then how will get the data in datawindow to preview/report it.

Hi dia:
<< but by the looks of it i feel u can go ahead and create a DW with ur above SP and call the dw as,
dw_report.Retrieve(ldt_date) >> 

I exactly want this but I will get somewhere struck.
Please instruct me step by step as
Create datawindow->Sql Select->Store Procedure and
then ??????????????

from the pb help file
CREATE PROCEDURE proc2 AS
SELECT emp_name FROM employee
      SELECT part_name FROM parts
PowerBuilder provides access to both result sets:
// Declare the procedure.
DECLARE emp_proc2 PROCEDURE FOR proc2;
// Declare some variables to hold results.
string      emp_name_var
string      part_name_var
// Execute the stored procedure.
EXECUTE emp_proc2;
// Fetch the first row from the first result

// set.
FETCH emp_proc2 INTO :emp_name_var;

// Loop through all rows in the first result

// set.
DO WHILE sqlca.sqlcode = 0

// Fetch the next row from the first result set.

FETCH emp_proc2 INTO :emp_name_var;
LOOP

// At this point we have exhausted the first

// result set. After this occurs,
// PowerBuilder notes that there is another
// result set and internally shifts result sets.
// The next FETCH executed will retrieve the
// first row from the second result set.
// Fetch the first row from the second result
// set.
      FETCH emp_proc2 INTO :part_name_var;

// Loop through all rows in the second result

// set.
DO WHILE sqlca.sqlcode = 0

// Fetch the next row from the second result

// set.
      FETCH emp_proc2 INTO :part_name_var;
LOOP


so once u execute the proc u need to've a loop as shown above and u could then set the valus in  a external dataobject since the fetch is givin u the values

hi,

if we have a real complicated SP and need the data in a DW and cannot do it by making the SP as a datasource we normally make a table and insert values of our final select statement of the SP to that table...
next its the simple task of making that table the datasource of our DW...

this case is normally used in scenerios where there is a batch of data... this data can
be inserted into the new table... then it gets easy to retrieve data with other retrieval
arguments... the call for what approach u take is all dependent on ur Business Functionality


Cheers,
Rosh

would using the loop to insert it into a datawindoiw be a simpler and effective solution ?

Hi Rosh:

I don't want any select. Any pb scripting. The SP is giving me results with all the columns.
I want to call it into datawindow with store procedure option like
Create datawindow->Sql Select->Store Procedure and
then ??????????????

well load on the backend is a better option then doing it from Powerbuilder using a loop...
theres no doubt that it wud give better performance than looping...
in mateens case i prefer inserting values thru the SP into a table and then creatin a DW of it...
what do u think sajuks...

Cheers,
Rosh

Create datawindow->Sql Select->Store Procedure and
then ??????????????


then select ur SP...

thats it,,, rest is self explanatory... unless u get an error...

Cheers,
Rosh

Hi Sajuks:

<<would using the loop to insert it into a datawindoiw be a simpler and effective solution ?>>
Why such things.

My SP give complete results with all the columns needed. (The result has been pasted in the question). Why one cannot call this result into datawindow.

rosh the problem that i've is in creating one more temporary table. now if there are more than one users whoc execute the same thing then that means we've to take that into consideration when retriveing from the temp table. so a insertinto is much faster and efficient

Hi Rosh:

I selected my SP , there I see this line in bottom box
<<The database does not currently support retrieval of procedure text.>>
Further there are two check boxes. System Procedure and Manual Result Set.
How to go ahead.

"would using the loop to insert it into a datawindoiw be a simpler and effective solution ?"
shodul be read as
wouldnt using the loop to insert it into a datawindoiw be a simpler and effective solution ?

create new datawindow>sql select > procedure > ur procedure name shoudl work

in ur pb script as i mentioned earlier u just need to pass the date argument .
are u getting any errors ?
the sp will be executed with the date as an argument. thats it

well.... the temp table is for that particular session only... so no issue abt other users i guess...

" well.... the temp table is for that particular session only... so no issue abt other users i guess..."
are you talking about hash tables or permanet temp tables ?

"Further there are two check boxes. System Procedure and Manual Result Set."
can u see the procedure list ?
ensure none of the checkboxes are seelcted

im talkin abt the hash tables...


well from a little search i just came across this link.... surprisingly its namasi helping out 'Mateen' !

mateen... just check an older post of urs... i guess that helped u...

let me know if u need more assistance..

Cheers,
Rosh

Dear experts:
My problem is that
I dont know how to create a datawindow with store procedure as its datasource.
I agree it must be easy. For me it is difficult because Whenever I have tried I have failed
and left the matter for some other time. This time I don't want so.
I have already specified my fear in my question.

sorry...
heres the link...
https://www.experts-exchange.com/questions/20882384/How-to-call-a-sql-server-stored-procedure-from-powerbuilder.html

are u using odbc drivers ? u need to switch to native drivers to get full support for sp as datasource

Hi Rosh:
<<surprisingly its namasi helping out 'Mateen' >>
I already cleared in my question
<<I have asked it before , I have got answer, the answer must have been ok,
but unfortunately I have yet not been able to do this. If the same
happens this time, please be patient with me.>>

ok...

this time we're gonna see to it that u create ur DW with the SP and no work around...

so ur gettin the error...
<<The database does not currently support retrieval of procedure text.>>

Also, make sure "manual result set" option is NOT CHECKED when associating your stored proc with the procedure.

Cheers,
Rosh

Hi Sajuks:
<<are u using odbc drivers ? u need to switch to native drivers to get full support for sp as datasource >>
I am using odbc drivers currently. I am ready to switch to native driver. Then??

then u shouldnt be facing any problems.
once u r able to select the procedure , u will see the paarameters which u'd defined which u can format ( like u do with normal dw)

Hi rosh:
the datawindow created!!!

When clicking retrieve button, retrieval box opened,
wrote '2005-03-17'  and clicked ok. Getting error

"Select errror 22005
Microsoft ODBC SQL SERVER DRiver Invalid character value for cast specification"

also mateen hope u have the latest patch of the PB version that ur using... thats what i always recommend and it helps!!!
btw,
i just came across some writeups that talked abt using native drivers for full support... i guess u gotta take sajuks suggestion and give it a try...

Cheers,
Rosh

well,

the datawindow created!!!

thats the first break thru...

now its just abt some small tuning... be right back...

Cheers,
Rosh

Hi sajuks:
Do U want to say I wouldnt get this error
"Select errror 22005
Microsoft ODBC SQL SERVER DRiver Invalid character value for cast specification"
if i use native driver.
Meanwhile u answer, I am testing.....

ur connc profile would lok something like this
SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
SQLCA.DBParm = "ConnectString='DSN=My-SQL;UID=sa;PWD=abc',StripParmNames='Yes',CallEscape='No' "

but... which version on PB do u have mateen...

Hi Sajuks:
Set the Dbparms as u suggested.
Whe I create dw without checking manual resultset I get this error before the dw is created.
Invalid character value for cast specification.

1 execute dbo.usp_lowfatspread; 1 @input=:input

when I create dw with checking manual resultset the dw is created but the retrieve option
is dimmed or disabled.

So dw is halfly created.

" but the retrieve option is dimmed or disabled."
what retrieve option r u talking about ?

if u run this from ur application do u get any errors ?

hi,

i feel its got something to do with ur identity column here...
SELECT LOOM_NO,AT_DATE_TIME,CASE WHEN SUB_STATUS_CODE = 8 THEN -1 ELSE SUB_STATUS_CODE END
         AS sTATUS
      ,IDENTITY(INT,1,1) AS ROWNO
  INTO #TEMP
  FROM Loom_Pick_Count
 wHERE AT_DATE_TIME BETWEEN @START AND @END
   AND SUB_STATUS_CODE IN (1,2,4,8,16,64,127,128)
 ORDER BY LOOM_NO,at_DATE_TIME

just for testing try without using the identity function...

Cheers,
Rosh

hi mateen,

does ur SP compile properly...


Cheers,
Rosh

Hi experts
As suggested by sajuks
I changed the dbparms but did not reconnect ODBC previously.
I restarted the machine to see everything working fine.

hi,

just went thru ur procedure again and felt something wrong with the first few lines...

DECLARE @START DATETIME
dECLARE @END DATETIME

SELECT @START=CONVERT(CHAR(11),@INPUT)
      ,@END=cONVERT(CHAR(11),@iNPUT) + ' 23:59:59PM'

u have decalred variables @start and @end as datetime but,
using convert u are settin char to them...

u might need to use ,
DECLARE @START DATETIME
dECLARE @END DATETIME
DECLARE @ST DATETIME
dECLARE @ED DATETIME


SELECT @ST=CONVERT(CHAR(11),@INPUT)
      ,@ED=cONVERT(CHAR(11),@iNPUT) + ' 23:59:59PM'

SELECT @START=CAST( @ST AS DATETIME )
      ,@END=CAST( ,@ED AS DATETIME )

instead of the above lines....
i guess this might be the issue but not sure...

Cheers,
Rosh

"I restarted the machine to see everything working fine."
seems to happen a lot with microsoft OS

Thanks sajuks

Hi Dia:

Its ok now.
Thanks 4 remaining so long.

<< BTW. the procedure should be ok as it is written by EE champion lowfatspread>>

Hi sanjuks,

I am also facing the same prole f error 22005. I am using PB 10.2 build 8011 and ADO .NET for connecting to theSQL Server.

He datawindow retrives properly when i am editing it or if it is in preview mode. However the error creeps up when it is retrieved throught powerscript dw_1.retieve(datetiem_variable_name) . I tried changing the parameter varable to string in place of datetime but still the same problem. However this same datawaindo runs flawlessly when using PB 6.51 and MSS driver to access the data.

Can you help.

Regards
kathotia