Solved

Excel VBA calling stored proc

Posted on 2015-01-21
4
112 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
  • 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now