Click here to Skip to main content
15,663,154 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
I have made a stored procedure to build a temporary table and then return the end results.

When i test it in SSMS it returns a single recordset and works ok, when i do it in VB the recordset will not open after passing everything but no errors are returned in either VB or ADODB.

Stored Procedure
SQL
USE [MainDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SYS_Purchasing_CDOrder] @PONo as varchar(10) AS
BEGIN
	DECLARE @HeaderID int
	DECLARE @FrameID int
	DECLARE @RowOn int = 1
	DECLARE @RowCount int
	DECLARE @tmpParts TABLE(HeaderID int, FrameID int, SetCode varchar(3), PCode varchar(99))
	DECLARE @tmpPurchase TABLE(LineID int IDENTITY(1,1) NOT NULL, CustRef varchar(99), HeaderID int, FrameID int, PONo varchar(10), Width int, Height int, Qty int, OrdDate DATE, DelDate DATE, CDPart varchar(99), LB varchar(3), Opener varchar(99), Handed varchar(99))
	
	INSERT INTO @tmpPurchase SELECT JobNo+'_'+CustRef, HeaderID, FrameID, PONo, Width, Height, Qty, CAST(OrdDate AS DATE), CAST(DelDate AS DATE), null, null, null, null FROM SYS_Purchasing WHERE PONo = @PONo AND PartType = 'CDOOR'
	SET @RowCount = (SELECT MAX(LineID) FROM @tmpPurchase)
	WHILE @RowOn <=@RowCount
	BEGIN
		SET @HeaderID = (SELECT HeaderID FROM @tmpPurchase WHERE LineID = @RowOn)
		SET @FrameID = (SELECT FrameID FROM @tmpPurchase WHERE LineID = @RowOn)
		INSERT INTO @tmpParts SELECT HEADER_ID, FRAME_ID, SetCode, PCode FROM PARTS WHERE (HEADER_ID = @HeaderID AND FRAME_ID = @FrameID) AND (SetCode = 'CDR' OR SetCode = 'LB')
		UPDATE @tmpPurchase SET CDPart = (SELECT PCODE FROM @tmpParts WHERE HeaderID = @HeaderID AND FrameID = @FrameID AND SetCode = 'CDR') WHERE LineID = @RowOn
		UPDATE @tmpPurchase SET LB = (SELECT SETCODE FROM @tmpParts WHERE HeaderID = @HeaderID AND FrameID = @FrameID AND SetCode = 'LB') WHERE LineID = @RowOn
		UPDATE @tmpPurchase SET OPENER = (SELECT SASHPCODE FROM SASHES WHERE Header_ID = @HeaderID AND Frame_ID = @FrameID AND SASHPCODE LIKE 'CDS%') WHERE LineID = @RowOn
		UPDATE @tmpPurchase SET Handed = (SELECT SASHType FROM SASHES WHERE Header_ID = @HeaderID AND Frame_ID = @FrameID AND SASHPCODE LIKE 'CDS%') WHERE LineID = @RowOn
		SET @RowOn += 1
	END
	SELECT CustRef, HeaderID, FrameID, PONo, Width, Height, Qty, OrdDate, DelDate, CDPart, LB, Opener, Handed, SupplierCode, ColourExt, ColourInt, Cassette, SpecialRequirements  FROM @tmpPurchase LEFT JOIN SYS_PURCHASING_CDMatrix ON CDPart = SYS_PURCHASING_CDMatrix.WDProdCode 
END


VB Code
VB
Set Cmd1 = New ADODB.Command
Set rs = New ADODB.Recordset
Set sqlServer = New ADODB.Connection
'extra code here to open the connection, this opens ok
sqlServer.Open
Cmd1.ActiveConnection = sqlServer
Cmd1.CommandType = adCmdStoredProc
Cmd1.CommandText = "[SYS_Purchasing_CDOrder]"
Set Parm1 = Cmd1.CreateParameter("@PONo", adVarChar, adParamInput, 10, OrderNo)
Cmd1.Parameters.Append Parm1
rs.Open Cmd1
Debug.Print rs.State 'This always returns 0


If i change the stored procedure to just return data straight from a table it works ok, but when im using the temp tables it just falls over and dies.

The reason for doing it this way is because the Parts table has over 11 million records (over 5gb of data) and can be slow.

Thanks in advance

Ray
Posted
Updated 27-Feb-23 5:29am

A few ideas:

  • Create your command without the square brackets: Cmd1.CommandText = "SYS_Purchasing_CDOrder"
  • Create your parameter without the 'at': Cmd1.CreateParameter("PONo", ...
  • Near the top of your procedure, call SET NOCOUNT ON; I've seen sometimes that this helps.
  • Check the ARITHABORT setting.


Also, if you can use SQL Profiler (i.e., you're not using SQL Express), you may find something there: what SQL code does actually run?
By the way, you may also want to optimize your procedure. Try to get the same results with a query that joins all sources.

Hope this helps,

Pablo.
 
Share this answer
 
v2
OK i have sorted this by linking some views and stored procedures.

Regards

Ray
 
Share this answer
 
Pablo, thanks for your reply.

Done all of them and still no luck, I had already put nocount in but it does not show when I modify it.

I did mention in my post that running this as just joined tables would take to long as 1 of the tables has over 11 millions records in it which is why i return just the ones I need.

Regards

Ray
 
Share this answer
 
I've found that a "Select blah, blah, blah,* from #TempTable" for the final select of a stored procedure, and returning the recordset causes the recordset to be empty when it gets back to VB6. The procedure works 100% and returns exactly the desired result. But wen you call it with VB6, it's empty and the recordset never gets opened.

BUT !!

If you change the stored Procedure to use a regular table, then it works and VB6 is happy with the results..

So if you change your procedure to

DROP IF EXISTS SOMETABLE
CREATE TABLE SOMETABLE(col1, col2, etc..)
...
some query here to extract data and add to sometable from 15million recordsd..
...
-- when everything is done
select * from SOMETABLE

-- notice no # or @ in front of the table name... so it's just a regular table, don't namespace it, or either use your username.dbo.namespace
 
Share this answer
 
v2
Comments
Dave Kreskowiak 27-Feb-23 12:07pm    
<cough>11 year old question.</cough>
Richard Deeming 27-Feb-23 12:10pm    
You've resurrected a question eleven years later to suggest a solution that will not work if more than one user accesses your database at the same time.

The whole point of temporary tables is that they are unique to the session. Replacing them with a regular table that you dynamically drop and create every time you run your query will only cause problems.

Stick to answering new questions unless you have a new and interesting solution that won't cause more problems than it solves.
Mike Dickerson 27-Feb-23 13:17pm    
Then your answer would be?
CHill60 28-Feb-23 6:27am    
"Then your answer would be?" Mine would be "Don't use VB6 for new code" - I would have said the same 11 years ago

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900