Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel VBA calling stored proc

Posted on 2015-01-21
4
Medium Priority
?
121 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 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

610 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