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?
-
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