Solved

Excel VBA calling stored proc

Posted on 2015-01-21
4
109 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
Comment Utility
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:
SimonAdept earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry SET COUNT should work, my mistake. Tks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

743 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

17 Experts available now in Live!

Get 1:1 Help Now