I've got an ODBC connection to a Sybase SQL Server (Sybase 11, to be exact). The data object using this connection executes a stored procedure on the server using the VendorRetrieval property of the data object. The stored procedure takes about 10 minutes to run, pulling together data from many different tables before returning it to the caller. The data object starts out with no columns, relying on the result set to create the columns automatically.

The problem is that most of the time I end up getting ViP Runtime error 5349 when the data object starts to retrieve data. At that point, the data object gives up on the transfer.

I've actually seen this work (i.e., the error doesn't occur, the data is loaded into the data object, the columns defined, etc.), so I know I've got a viable application here. What I don't understand is what is this error telling me? Can I recover from it & get the data transfered? Is there some setting on the Sybase server I need to change?

HELP!

Tony,

This is a complex issue, as you can guess. Doing stored procedures that return result sets is pretty tricky, and doesn't always work out correctly. A few questions first.

Which version ViP are you using, and is it 32-bit?

Are you sure of the error number 5349, because in looking at our code, there is no such error number defined. (5449 would make sense.)

Could you post both the Vendor Retrieval string and the text of the stored procedure that is executing?

I believe that the SQL Server driver is set for Transaction processing. It may be that there is a difficulty in our code that occurs for non-autocommits with stored procedures. Which ODBC SQL Server driver are you using?

I'm using ViP 2.2 for NT, so yes, it's 32 bit code. I'm sorry, the number returned is 5439, "Error fetching data".

The vendor retrieval string is "exec interface..MilanoGetIssuers"

The text of the stored procedure follows. Note that there are over 11,000 rows of data retured in our production database for this query!

create proc MilanoGetIssuers as

  /**************************************************************************

• Step 1: Create temporary tables which will hold the issuer

• data we"re going to output

• */

  create table #Issuer (issr_num         org_num   not null,

                        org_nam          org_nam       null,

                        old_nam          org_nam       null,

                        ticker           char_num      null,

                        domn_nam         org_nam       null,

                        ratg_grp_cd      code          null,

                        ult_parnt_num    org_num       null,

                        ult_parnt_nam    org_nam       null,

                        brd_busn_ln_cd   busn_code     null,

                        brd_busn_ln_txt  busn_text     null,

                        spec_busn_ln_cd  busn_code     null,

                        spec_busn_ln_txt busn_text     null,

                        watchlist        code          null,

                        watch_dt         datetime      null,

                        watchlist_cd     code          null,

                        cp_out_amt       amtm          null,

                        cp_out_dt        datetime      null,

                        bfs_ratg_txt     code          null,

                        bfs_ratg_dt      datetime      null,

                        bfs_ratg_dir     code          null,

                        st_ratg_desc     char(255)     null,

                        st_ratg_txt      char(9)       null,

                        st_ratg_rank     int           null,

                        st_ratg_dt       datetime      null,

                        st_ratg_dir      code          null,

                        st_ratg_senr_cd  code          null,

                        analyst_num      analyst_num   null,

                        Analyst          nam           null,

                        backup_num       analyst_num   null,

                        BackupAnalyst    nam           null,

                        ad_num           analyst_num   null,

                        AD               nam           null)

  create unique clustered index Issuers on #Issuer (issr_num)

  create table #tmp01 (issr_num       org_num  not null,

                       watch_dt       datetime     null,

                       watchlist_cd   code         null)

  create table #tmp02 (issr_num       org_num  not null,

                       watch_dt       datetime     null,

                       watchlist_cd   code         null)

  create table #tmp03 (issr_num       org_num  not null,

                       debt_num       debt_num     null,

                       ratg_class_num int          null,

                       ratg_dt        datetime     null,

                       ratg_txt       char(9)      null,

                       ratg_rank      int          null,

                       ratg_dirn_cd   code         null,

                       debt_senr_cd   code         null)

  create nonclustered index idx01 on #tmp03 (issr_num)

  create nonclustered index idx02 on #tmp03 (ratg_class_num)

  create nonclustered index idx03 on #tmp03 (issr_num, ratg_class_num)

  create table #tmp04 (issr_num       org_num  not null,

                       debt_num       debt_num     null,

                       ratg_dt        datetime     null,

                       ratg_txt       char(9)      null,

                       ratg_rank      int          null,

                       ratg_dirn_cd   code         null,

                       debt_senr_cd   code         null)

  create nonclustered index idx01 on #tmp04 (issr_num)

  /**************************************************************************

• Step 2: Insert the basic issuer information into the new table.

• */

  insert into #Issuer (issr_num,

                       org_nam,

                       ult_parnt_num,

                       domn_nam,

                       brd_busn_ln_cd,

                       spec_busn_ln_cd,

                       ratg_grp_cd,

                       watchlist,

                       analyst_num,

                       backup_num,

                       ad_num)

  select distinct

         iss.issr_num,

         org.org_nam,

         is2.ult_parnt_num,

         gov.domn_nam,

         is2.brd_busn_ln_cd,

         is2.spec_busn_ln_cd,

         is2.ratg_grp_cd,

         "NO",

         is2.analyst_num,

         is2.other_analyst_num,

         is2.ad_num

    from interface..MilanoIssuers iss,

         ratings..issuers         is2,

         ratings..org_names       org,

         ratings..govt_domain     gov

   where iss.issr_num      =is2.issr_num

     and iss.issr_num      *= org.org_num

     and org.nam_usage_cd  =LEG"

     and is2.domn_num      *= gov.domn_num

     and gov.domn_typ_cd   =CTY"

  /**************************************************************************

• Step 3: Get the issuer's previous name, Bloomberg ticker, and

• broad & specific business line text.

• */

  update #Issuer

     set old_nam         =org.org_nam,

         ticker          =iid.issr_id_num,

         ult_parnt_nam   =par.org_nam,

          brd_busn_ln_txt=bbl.brd_busn_ln_txt,

         spec_busn_ln_txt=sbl.spec_busn_ln_txt

    from #Issuer                  iss,

         ratings..brd_busn_lines  bbl,

         ratings..issuer_ids      iid,

         ratings..org_names       org,

         ratings..org_names       par,

         ratings..spec_busn_lines sbl

   where iss.issr_num        *= iid.issr_num

     and iid.issr_id_num_cd  =BLO"

     and iss.issr_num        *= org.org_num

     and org.nam_usage_cd    =PR"

     and iss.ult_parnt_num   *= par.org_num

     and par.nam_usage_cd    =LEG"

     and iss.brd_busn_ln_cd  *= bbl.brd_busn_ln_cd

     and bbl.busn_ln_typ_cd  =MDY"

     and iss.spec_busn_ln_cd *= sbl.spec_busn_ln_cd

     and iss.brd_busn_ln_cd  *= sbl.brd_busn_ln_cd

     and sbl.busn_ln_typ_cd  =MDY"

  /**************************************************************************

• Step 4: Determine the issuer-level WatchList information for

• each issuer

• Step 4a: Extract the subset of the debts in issuer_watchlist

• which are currently on watch. Also retrieve the

• WatchList date & code for these debts.

• */

  declare @tomorrow datetime

  select  @tomorrow=dateadd(day, 1, getdate())

  insert into #tmp01 (issr_num,

                      watch_dt,

                      watchlist_cd)

  select distinct

         i.issr_num,

         w.watch_dt,

         w.watchlist_cd

    from #Issuer                   i,

         ratings..issuer_watchlist w

   where i.issr_num         =w.watch_org_num

     and (w.watch_expire_dt ]= @tomorrow

     or   w.watch_expire_dt is NULL)

  /**************************************************************************

• Step 4b: For those debts extracted in step 4b, extract the

• single debt for each issuer which was put on watch

• before all of the others.

• */

  insert into #tmp02 (issr_num,

                      watchlist_cd,

                      watch_dt)

  select issr_num,

         watchlist_cd,

         watch_dt

    from #tmp01

   group by issr_num

  having watch_dt=min(watch_dt)

  drop table #tmp01

  /**************************************************************************

• Step 4c: Now copy the WatchList information for each issuer we

• just generated into our output table.

• */

  update #Issuer

     set watchlist   =YES",

         watch_dt    =t.watch_dt,

         watchlist_cd=t.watchlist_cd

    from #Issuer i,

         #tmp02  t

   where i.issr_num=t.issr_num

  drop table #tmp02

  /**************************************************************************

• Step 5: Copy the commercial paper information, if any, for each

• issuer into its row in the output table.

• */

  update #Issuer

     set cp_out_amt=d.out_us_amtm,

         cp_out_dt =d.out_us_amtm_dt

    from #Issuer             i,

         ratings..debt_oblgs d

   where i.issr_num     =d.issr_num

     and d.debt_class_cd=CP"

     and d.out_us_amtm is not NULL

  /**************************************************************************

• Step 6: Copy the Bank Financial Strength Rating information,

• if any, for each issuer into its row in the output

• table.

• */

  update #Issuer

     set bfs_ratg_txt=d.curn_lt_ratg_txt,

         bfs_ratg_dt =d.curn_lt_ratg_dt,

         bfs_ratg_dir=d.curn_lt_ratg_dirn_cd

    from #Issuer             i,

         ratings..debt_oblgs d

   where i.issr_num     =d.issr_num

     and d.debt_class_cd=BFS"

  /**************************************************************************

• Step 7: Compute the short term, most recently rated, senior-most

• debt information for each issuer

• Step 7a: Retrieve the short term most recently rated debts

• information for each issuer into a temp table

• */

  insert into #tmp03 (issr_num,

                      debt_num,

                      ratg_class_num,

                      ratg_dt,

                      ratg_txt,

                      ratg_dirn_cd,

                      debt_senr_cd)

  select distinct

         r.issr_num,

         r.debt_num_rep,

         r.ratg_class_num,

         r.ratg_dt,

         r.ratg_txt,

         r.ratg_dirn_cd,

         r.debt_senr_cd

    from #Issuer               i,

         ratings..issuer_ratgs r

   where r.issr_num      =i.issr_num

     and r.ratg_expire_dt=null

     and r.ratg_dirn_cd not in ("NA", "NR", "WDR")

   group by r.issr_num, ratg_class_num

  having (r.ratg_class_num between  1100 and  1999

      or  r.ratg_class_num between 11100 and 11999

      or  r.ratg_class_num between 21100 and 21999)

     and r.ratg_dt=max(ratg_dt)

  /**************************************************************************

• Step 7b: For those days where more than one short term debt was

• rated, pick the senior most one & put into another table

• */

  insert into #tmp04 (issr_num,

                      debt_num,

                      ratg_dt,

                      ratg_txt,

                      ratg_dirn_cd,

                      debt_senr_cd)

  select distinct

         issr_num,

         debt_num,

         ratg_dt,

         ratg_txt,

         ratg_dirn_cd,

         debt_senr_cd

    from #tmp03

   group by issr_num

  having ratg_class_num=min(ratg_class_num)

  drop table #tmp03

  /**************************************************************************

• Step 7c: Copy the short term, most recently rated debt info,

• if any, for each issuer into its row in the output

• table.

• */

  update #Issuer

     set st_ratg_desc   =l.field_txt,

         st_ratg_txt    =t.ratg_txt,

         st_ratg_rank   =r.ratg_rank,

         st_ratg_dt     =t.ratg_dt,

         st_ratg_dir    =t.ratg_dirn_cd,

         st_ratg_senr_cd=t.debt_senr_cd

    from ratings..debt_oblgs         d,

         #Issuer                     i,

         ratings..lookup_field_codes l,

         ratings..lookup_ratg_codes  r,

         #tmp04                      t

   where i.issr_num    =t.issr_num

     and t.debt_num    =d.debt_num

     and l.field_cd    =d.debt_typ_cd

     and l.table_num   =53

     and r.ratg_txt    =t.ratg_txt

     and r.ratg_typ_cd =d.ratg_typ_cd

     and r.ratg_org_num=3247

  drop table #tmp04

  /**************************************************************************

• Step 8: Copy the analyst, backup analyst, and AD information

• for each issuer into its row in the output table.

• */

  update #Issuer

     set Analyst      =RTrim(LTrim(IsNull(ana.last_nam  , "") + ", " +

                                     IsNull(ana.first_nam , "") + " "  +

                                     IsNull(ana.middle_nam, "")))

    from #Issuer           iss,

         ratings..analysts ana

   where iss.analyst_num is not NULL

     and iss.analyst_num *= ana.analyst_num

  update #Issuer

     set BackupAnalyst=RTrim(LTrim(IsNull(bua.last_nam  , "") + ", " +

                                     IsNull(bua.first_nam , "") + " "  +

                                     IsNull(bua.middle_nam, "")))

    from #Issuer           iss,

         ratings..analysts bua

   where iss.backup_num is not NULL

     and iss.backup_num *= bua.analyst_num

  update #Issuer

     set AD           =RTrim(LTrim(IsNull(aad.last_nam  , "") + ", " +

                                     IsNull(aad.first_nam, "") + " "   +

                                     IsNull(aad.middle_nam, "")))

    from #Issuer           iss,

         ratings..analysts aad

   where iss.ad_num is not NULL

     and iss.ad_num *= aad.analyst_num

  /**************************************************************************

• Step 9: Return all of the information we just assembled into

• the output table.

• */

  select issr_num         CoNum,

         org_nam          Company,

         old_nam          OldName,

         ticker           Ticker,

         domn_nam         CountryInc,

         ratg_grp_cd      RatingGroup,

         ult_parnt_num    UltParntNum,

         ult_parnt_nam    UltParntNam,

         brd_busn_ln_txt  BrdInd,

         spec_busn_ln_txt SpecInd,

         watchlist        WatchList,

         watch_dt         WatchListDate,

         watchlist_cd     WatchListDir,

         cp_out_amt       CPOutstanding,

         cp_out_dt        CPOutstandingAsOfDate,

         bfs_ratg_txt     BFSRating,

         bfs_ratg_dt      BFSRatingDate,

         bfs_ratg_dir     BFSRatingDir,

         st_ratg_desc     STMRRatedIssue,

         st_ratg_txt      STMRRating,

         st_ratg_rank     STMRRatingRank,

         st_ratg_dt       STMRRatingDate,

         st_ratg_dir      STMRRatingDir,

         st_ratg_senr_cd  STMRRatingSeniority,

         Analyst,

         BackupAnalyst,

         AD

    from #Issuer

    order by issr_num

go

Oh yea, ODBC version: Sybase System10 driver 2.12.0000

I've also got MS SQL Server 2.65.0240; can I use this driver?

Tony,

Sorry I've been so slow in responding again.

I guess I'm amazed that you have seen this work at all, considering the complexity of the stored procedure and the size of the result set.

As you noted, this is a "fetch" error, which only occurs one place in the code. There is really no way to determine additional information on why this is happening, since the ODBC driver is not returning additional information on the error. The message displayed is ours, and would have appended any additional message.

It occurs as each row of the result is being returned from the ODBC driver. Any such error could occur on a row, even though most of them might have been returned successfully already.

This could be something in the procedure that the database doesn't like, for instance trying to update a value, even before any rows are returned.

I'm curious under what circumstances you had a successful result. Was this with this particular stored procedure, and with a large database? If so, I'm inclined to think that there is just some underlying little error that occurs occasionally in this very large procedure that is terminating the whole process.

Please, don't take this the wrong way, but if I could reliably reproduce the circumstances where it worked, I probably wouldn't be looking for help!

The database in question happens to be a day-old copy of our main database. It's not anything like the gov't's census database in size, but it's not really terribly small either–a few hundred meg of data. The stored procedure only uses a handful of tables from the database. There are many more that are not used.

I have a suspiscion that the problem is related to the tempDB. I have seen the application work when there were no other processes running on the Sybase server. There's a task that runs every 10 minutes which would block on the ViP process. The few times I've seen it go all the way, the repeating process wasn't running or blocked.

I'll see what I can do to reduce the number of columns returned.

Have you considered breaking the procedure into two parts? The first part would construct the temporary db and tables. The second would just query and return a result set. Then the temp stuff could be deleted after (as part, or with third procedure.) I don't know if this would help, but at least you could isolate the problem a bit more. I don't think it would affect overall performance. (The first process is a ExecuteVendorCommand method, rather than VendorRetrieval.)

View this thread on the forum...