Solved

Excel VBA calling stored proc

Posted on 2015-01-21
4
119 Views
Last Modified: 2015-01-22
I have a VBA code that call a stored proc. Both stored procedure works fine on the SQL Server. My VBA code calling #2 is fine but no record is returned. Both stored procedures can return records on the SQL Server using Management Studio.



#1 (Execution time : 29s )
---
Create PROCEDURE [dbo].[wbDisplayBook]
@INVENTBATCHID VARCHAR(3000)
AS
BEGIN
SELECT A.INVENTBATCHID, A.COSTGROUPID  FROM DynamicsAX_PRO.dbo.THK_LASTWIPMATERIALTRANS A
inner join dbo.split(@INVENTBATCHID,',') as e on A.INVENTBATCHID COLLATE DATABASE_DEFAULT = e.Data COLLATE DATABASE_DEFAULT
end

#2 (Execution time : 25s)
----
CREATE PROCEDURE [dbo].[sp_wbDisplayBook] @INVENTBATCHID VARCHAR(3000)
AS
BEGIN
SELECT A.INVENTBATCHID,D.ITEMID ,D.THK_INVENTD2ID, D.THK_INVENTD3ID, D.THK_INVENTD4ID, D.THK_INVENTDGOLDTYPEID, THK_REFBOMID,
CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN 4 ELSE B.STONETYPE END AS STONETYPE,
CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN SUM(OUTQTY) ELSE 0 END AS GoldCost,
CASE WHEN A.COSTGROUPID LIKE 'ST%' THEN CAST(CAST(SUM(OutQtySec) AS decimal) AS NVARCHAR(10))
                  +SUBSTRING(A.COSTGROUPID,4,3)+CAST(CAST(SUM(OUTQTY) AS decimal(20,2)) AS NVARCHAR(10)) Else '' END AS Material
into #TM1 FROM DynamicsAX_PRO.dbo.THK_LASTWIPMATERIALTRANS A
INNER JOIN DynamicsAX_PRO.dbo.BOMCostGroup B ON B.COSTGROUPID=A.COSTGROUPID
INNER JOIN DynamicsAX_PRO.dbo.INVENTBATCH C ON A.INVENTBATCHID=C.INVENTBATCHID AND C.DATAAREAID='OPR'
INNER JOIN DynamicsAX_PRO.dbo.BOMVERSION D ON C.THK_REFBOMID=D.BOMID AND C.ITEMID=D.ITEMID AND D.DATAAREAID='OPR'
inner join dbo.split(@INVENTBATCHID,',') as e on A.INVENTBATCHID COLLATE DATABASE_DEFAULT = e.Data COLLATE DATABASE_DEFAULT
GROUP BY SUBSTRING(A.COSTGROUPID,4,3),CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN 4 ELSE B.STONETYPE END,
A.COSTGROUPID,A.INVENTBATCHID,D.ITEMID,D.THK_INVENTD2ID,D.THK_INVENTD3ID,D.THK_INVENTD4ID,D.THK_INVENTDGOLDTYPEID,
THK_REFBOMID

select * from #TM1
END

VBA Code
--------------
'Connect to Database
conn.Open ConPro
conn.CommandTimeout = 0
cmd.ActiveConnection = conn

cmd.CommandTimeout = 0
cmd.Parameters.Append cmd.CreateParameter("INVENTBATCHID", adVarChar, adParamInput, 3000, stPar1)
cmd.CommandText = "sp_wbDisplayBook"
cmd.CommandType = adCmdStoredProc
Set Rs = cmd.Execute(, , adCmdStoredProc)
0
Comment
Question by:AXISHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 

Author Comment

by:AXISHK
ID: 40563761
Find a root for my problem, if I take away Temporary table and select the record directly, it works.

however, the about SQL is an extraction only, and I need to redirect it to few temporary table before getting a result. Any workaround solution ?

Tks
0
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40563771
Try using SET NOCOUNT ON in your SP and ensure that your SP is only returning one recordset. If you run it to grid in SSMS, how many result sets do you get? I think your VBA code will only take the first recordset returned from a stored proc.

Otherwise, you could try using CTEs (Common table expressions) or table-valued variables in your SP instead of temp tables, but I think your problem is multiple result sets.
0
 

Author Comment

by:AXISHK
ID: 40563778
What do you mean resultset , are you asking how many number of rows returned ? In Management Studio, it only have one windows with 34 rows returned.

Already use SET NOCOUNT ON but it doesn't help.


Tks
0
 

Author Closing Comment

by:AXISHK
ID: 40563877
Sorry SET COUNT should work, my mistake. Tks
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question