Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 507
  • Last Modified:

Sql Server 2000 store procedure with a date parameter.

This must be easy.
For me this has always been difficult.
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.

I have a store procedure namely usp_LowFatSpread  sql server 2000 with
a date parameter.

When I write in QueryAnalyzer

exec usp_LowFatSpread '2005-03-17'

The top 5 rows I get are

loom_no a       b       c      d        e       f       g       h  
------- ----   -----   -----   -----   ------   -----   -----  ----
23      0      16      0      768      6416      260      1405      0
46      0      0      0      0      11170      0      3326      0
69      0      56      0      2725      22705      450      5283      0
77      0      42      0      2350      14002      38      5406      0
31      0      0      0      0      17466      379      8890      0


I want to call the above procedure from within powerbuilder to get
the above resultset.


The ['2005-03-17'] of ( exec usp_LowFatSpread '2005-03-17') will
be replaced by the date provided by the user from within powerbuilder.

Please help.


0
Mateen
Asked:
Mateen
  • 15
  • 15
  • 14
  • +1
2 Solutions
 
sajuksCommented:
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
http://www.experts-exchange.com/Programming/Programming_Languages/PowerBuilder/Q_21244750.html
0
 
MateenAuthor Commented:
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.
0
 
diasroshanCommented:
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

<I cannot use it as datasource because data will be determined after the user has inputted the
date from powerbuilder.>
 as per ur above line i wud like to comment that the whole idea abt passin retrieval arguments in PB is that we get the data for a DW only after we pass arguments from powerbuilder...

also,
any SP which returns a select statment can be used as a datasource to make a DW... well i have not yet seen the complications involved in ur SP... 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)

let me know if u need more assistance...

Cheers,
Rosh


0
 
MateenAuthor Commented:
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 ??????????????

0
 
sajuksCommented:
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

0
 
diasroshanCommented:
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
0
 
sajuksCommented:
would using the loop to insert it into a datawindoiw be a simpler and effective solution ?
0
 
MateenAuthor Commented:
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 ??????????????
0
 
diasroshanCommented:


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
0
 
diasroshanCommented:
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
0
 
MateenAuthor Commented:
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.


0
 
sajuksCommented:
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

0
 
MateenAuthor Commented:
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.
0
 
sajuksCommented:
"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
0
 
diasroshanCommented:
well.... the temp table is for that particular session only... so no issue abt other users i guess...
0
 
sajuksCommented:
" 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 ?
0
 
sajuksCommented:
"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
0
 
diasroshanCommented:


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
0
 
MateenAuthor Commented:
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.
0
 
sajuksCommented:
are u using odbc drivers ? u need to switch to native drivers to get full support for sp as datasource
0
 
MateenAuthor Commented:
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.>>

0
 
diasroshanCommented:
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
0
 
MateenAuthor Commented:
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??

0
 
sajuksCommented:
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)
0
 
MateenAuthor Commented:
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"
0
 
diasroshanCommented:
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
0
 
diasroshanCommented:
well,

the datawindow created!!!

thats the first break thru...

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

Cheers,
Rosh
0
 
MateenAuthor Commented:
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.....
0
 
sajuksCommented:
Set the DBParm StripParmNames='Yes' and CallEscape='No' when connecting to the database.
0
 
sajuksCommented:
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' "
0
 
diasroshanCommented:
but... which version on PB do u have mateen...
0
 
MateenAuthor Commented:
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.

0
 
sajuksCommented:
" but the retrieve option is dimmed or disabled."
what retrieve option r u talking about ?
0
 
sajuksCommented:
if u run this from ur application do u get any errors ?
0
 
diasroshanCommented:
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
0
 
diasroshanCommented:
hi mateen,

does ur SP compile properly...


Cheers,
Rosh
0
 
MateenAuthor Commented:
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.

0
 
diasroshanCommented:
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
0
 
sajuksCommented:
"I restarted the machine to see everything working fine."
seems to happen a lot with microsoft OS
0
 
MateenAuthor Commented:
Thanks sajuks
0
 
MateenAuthor Commented:
Hi Dia:

Its ok now.
Thanks 4 remaining so long.

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

0
 
kathotiaCommented:
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 15
  • 15
  • 14
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now