calling stored proc in my vb program

  • Hi everyone,

    I have this VB 6 program calling a stored procedure. For a clear view, I will put an illustration of my 3 stored procedures.

    SP1                                                                                         

    Insert tblNSIFile(A,B,C)                                                

    Select A,B,C from tblA

    SP2                                                                                         

    Insert tblHybridFile(C,D,E)                                              

    Select C,D,E from tblB

    SP3                                                                                         

    Insert tblMatchFil(A,B,C,D,E)        

    SELECT     dbo.tblNSIFile.*, dbo.tblHybridFile.*

    FROM         dbo.HybridFile RIGHT OUTER  JOIN

        dbo.tblNSIFile ON dbo.tblHybridFile.C = dbo.tblNSIFile.C

    where  C is not null

    In my VB code, my coding is like this:

    conn.executeSP1                                                                                          

    conn.execute SP2                                               

    conn.execute SP3

    My problem lies in executing SP3. The processing is no longer responding if it will already performing the file matching of the tables that I previously executed.   

    Is there any issue on that? Kindly teach me the right syntax how to handle this.

    Thanks in advance.              

  • I suppose it depends how your VB program connects to SQL Server. If the program does not create a new connection when executing the procedures, it will have hold of one SPID on SQL Server, which will be busy with the last request.

    I would suggest that a new connection be made by the program for each run of this group of procedures. This way new SPIDs will be allocated for each connection and there should be no contention issues with processes.

    Although this may cause locking problems on SQL Server itself.

    How often do these procedures run and how long to they take?



    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • Hello,

    This is how I declare my stored proc

    Dim strSP1, strSP2,strSP3 as string

    strSP1= "SP1"

    strSP2= "SP2"

    strSP3= "SP2"

    .... so on

    conn.open ("DSN=Hybrid")

    conn.begintrans

    conn2.open("DSN=NSI")

    conn2.begintrans

    ...this is where where my problems lies which I already figure out.

    conn.execute strSP1

    conn.execute strSP2

    ...I need to close the connection in order to release

    conn.commitrans

    conn.close

    ....then execute the SP3 that will match the table in SP1 and SP2. sorry made mistake in my previous post that I also use conn for the connection. Then I successfully executed the SP.

    conn2.execute strSP3

    Thank you so much for your continuous help.

Viewing 3 posts - 1 through 2 (of 2 total)

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