Post reply

Data from a DB2 procedure call (using exec)

  • February 15, 2007 at 4:36 pm

    #172258

    All,

    In SQL2005 I have a linked server to a DB2 database. I can run the following statement from the query window and get results.

    What I am having a problem with is getting the results into a cursor or temporary table where I can process the results.

    The basic statement in vwDBPayments is below - the rest format the parameters (which replace 'X2006','X0000228').

    exec ('CALL SHPLIB.ICOL10 (?, ?)', 'X2006','X0000228') AT DB2ServerName

    If I try to open a cursor I get a syntax error at 'exec' as below.

    DECLARE curpymt CURSOR

    exec vwDBPayments '2006','228'

    WHILE (@@FETCH_STATUS = 0)

    BEGIN;

    FETCH NEXT FROM curpymt;

    END;

    CLOSE curpymt;

    DEALLOCATE curpymt;

    I created a stored procedure (vwDBPayments) that returns the result set when it is executed (< 1 second) but when I try to insert that into a tempTable it runs forever. CREATE TABLE #tmpDBPymnts
    (BillYear char(5), BillNumber char(8), Assessment Int, AmountDue money, Payment money, PMonth int, PDay int, PYear int)

    INSERT INTO #tmpDBPymnts
    exec vwDBPayments '2006','228'

    Any ideas or help. If I didn't make myself clear, let me know.

    Thanks
    George

  • February 19, 2007 at 8:00 am

    This was removed by the editor as SPAM

  • February 19, 2007 at 1:20 pm

    #690539

    I presume that the columns names in the temp table match the column names being returned from the stored procedure?

    Could it be something as bizarre as having to declare the column list in the insert statement? e.g.

    INSERT INTO #tmpDBPymnts

    (BillYear, BillNumber, Assessment, AmountDue, Payment, PMonth, PDay, PYear)

    exec vwDBPayments '2006','228'

    Seems very strange that you have a significant difference in performance when trying to store the returned data in a table.

    As for the Cursor, it just looks like SQL doesn't want to let you use an exec to get at the data.

    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • February 19, 2007 at 1:49 pm

    #690541

    The column names for the Insert Into statement didn't match but changing them didn't help either.

    I am afraid that "SQL doesn't want to let me use an exec to get at the data."

    Just running:

    exec vwDBPayments '2006', '228'

    works well, but how can I get it into a cursor or temp table to process the lines.

    Any body tried using VB.NET for writing a SQL Stored Proc. I understand that SQL now supports the CLR in .NET as a Stored Proc. I would like to know if any one has tried it & got it working.

    Thanks,

    George

  • February 19, 2007 at 2:21 pm

    #690543

    I have done this before with c#. So it shouldn't be too difficult to get it done in VB.Net.

    Here is a link that might be helpful

    http://aspalliance.com/1081_CLR_Integration_in_SQL_Server_2005

    I haven't tried this one but it coevers the concept of how to create then deploy the dll for use as a stored procedure

     

     

     

    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • February 19, 2007 at 2:30 pm

    #690546

    Thanks, I will give it a try & post the results as soon as I get the research / coding done.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply