Solved

Executing T-SQL Stored Procedures with VBA, many work except one

Posted on 2016-10-26
5
28 Views
Last Modified: 2016-10-26
Hello Experts,
I have written many XLSM embedded VBA scripts which use ADODB to execute and read T-SQL stored procedures. All of them work except one, and the one that currently doesn't work used to work just fine. It wasn't used for several months and no changes were made to the front or back end to my knowledge. I'm at a loss. I wrote a quick test script to isolate the problem (below). When I change the stored procedure name to any other one in the same database, everything works. For the stored procedure in question, the Recordset returns null. When I execute it from an SSMS script using Exec stored_procedure_name, it works fine. SQL security settings for all of the stored procedures are the same (and I've re-implemented them), as is the connection string used by the VBA. I've dropped the stored procedure and recreated it with no benefit. Anyone have any tips on what to look out for in this situation?

Option Explicit
Sub Update()

Const connStrFile As String = "\\server\ConnStr\ConnStr.txt"
Const spName As String = "Proc_SalesBy"

Dim WB As Workbook
Dim WS As Worksheet
Dim connStrFileStr As String
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim lastRow As Integer, i As Integer


Set WB = ActiveWorkbook
Set WS = ActiveSheet

Open connStrFile For Input As #1
Line Input #1, connStrFileStr
Close #1
Set con = New ADODB.Connection
con.Open connStrFileStr

Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = con
    .CommandType = adCmdStoredProc
    .CommandText = spName
    .CommandTimeout = 0
    .NamedParameters = True
End With

Set rs = New ADODB.Recordset
Set rs = cmd.Execute

If Not (rs.State And adStateOpen) = adStateOpen Then GoTo noResults

lastRow = 1
Do While Not rs.EOF
    For i = 0 To rs.Fields.Count - 1
        If lastRow = 1 Then
            WS.Cells(lastRow, i + 1).Value = rs.Fields(i).Name
        Else
            WS.Cells(lastRow, i + 1).Value = rs.Fields(i).Value
        End If
    Next
    lastRow = lastRow + 1
    rs.MoveNext
Loop

If 0 = 1 Then
noResults:
    MsgBox "No Results"
End If

con.Close
Set con = Nothing
Set cmd = Nothing

MsgBox "Done"

End Sub

Open in new window

0
Comment
Question by:Filonowst
  • 3
  • 2
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41860893
>in question, the Recordset returns null.

<Wild guess>  
Post the SP Proc_SalesBy into this question.  A common occurance is if there are actions such as <x rows updated> that a stored procedure generates because they are not turned off with 'SET NOCOUNT ON', 'SET ANSI_WARNINGS OFF', etc., then calling that SP via code such as ADODB can mistakenly use that message as the expected return set.
0
 

Author Comment

by:Filonowst
ID: 41860971
Thanks for the reply. Here's the SP. It's a bit convoluted. These parameters I usually pass using ADODB, but in while debugging I am letting them default.
CREATE PROCEDURE Proc_SalesBy
	@ProductMainCategoryCode NVARCHAR(60) = '%',
	@DateStart DATETIME = '1/1/2015',
	@DateEnd DATETIME = '2/2/2015',
	@CustBillingGroupCustomerCode NVARCHAR(60) = '%',
	@ProductCodeExclusionsBool BIT = 1
AS
	DECLARE @ProductCodeExclusions AS TABLE (ProductCode NVARCHAR(12))
	IF @ProductCodeExclusionsBool = 1 BEGIN
		INSERT INTO @ProductCodeExclusions VALUES('       14580'),('        8510'),('        9020'),('        9792'),('        9793'),('        9030')-- should have used trim	
	END
	
	IF OBJECT_ID('tempdb..##CustBillingGroups') IS NOT NULL
		DROP TABLE #CustBillingGroups
		
	SELECT DISTINCT
		IC_HistoryProducts.CustomerKey,
		IC_HistoryProducts.ProductKey,
		BillingCusts.CustomerCode
	INTO #CustBillingGroups
	FROM
		Database.dbo.IC_HistoryProducts
		INNER JOIN Database.dbo.IC_Products
			ON IC_Products.ProductKey = IC_HistoryProducts.ProductKey
		INNER JOIN Database.dbo.IC_CodeProductCategories
			ON IC_CodeProductCategories.ProductCategoryCode = IC_Products.ProductCategoryCode
		INNER JOIN Database.dbo.IC_CodeProductMainCategory
			ON IC_CodeProductMainCategory.ProductMainCategoryCode = IC_CodeProductCategories.ProductMainCategoryCode
		
		INNER JOIN Database.dbo.AR_Customers  AS HistProdCusts
			ON HistProdCusts.CustomerKey = IC_HistoryProducts.CustomerKey
		LEFT JOIN Database.dbo.AR_Customers AS BillingCusts
			ON BillingCusts.CustomerKey = HistProdCusts.CustomerKey_BillingMaster
	WHERE
		IC_CodeProductMainCategory.ProductMainCategoryCode LIKE @ProductMainCategoryCode
		AND TransactionDate >= @DateStart
		AND TransactionDate <= @DateEnd
		AND ISNULL(BillingCusts.CustomerCode,'') LIKE '%' + @CustBillingGroupCustomerCode
	
		
	SELECT
		*
	FROM(
	
		SELECT
			CustomerCode,
			Name,
			CASE WHEN (CustomerClassCode <> 100 AND CustomerClassCode <> 110 AND CustomerClassCode <> 120)
				THEN NULL
				ELSE CustomerClassCode
				END AS CustomerClassCode,
			ProductCode,
			Description1 AS ProductDescription,
			weightProdSubtotals.ProductMainCategory,
			Lbs,
			Sales
		FROM(
			SELECT
				IC_HistoryProducts.CustomerKey,
				IC_HistoryProducts.ProductKey,
				IC_CodeProductMainCategory.Display AS ProductMainCategory,
				SUM((IC_HistoryProducts.Quantity_Prc * IC_HistoryProducts.UnitPrice) *
					CASE WHEN OE_CodeSalesCategories.SaleType = 3
						THEN -1
						ELSE 1
						END) AS Sales
			FROM
				Database.dbo.IC_HistoryProducts
				INNER JOIN Database.dbo.OE_CodeSalesCategories 
					ON OE_CodeSalesCategories.SalesCategoryCode = IC_HistoryProducts.SalesCategoryCode
				INNER JOIN Database.dbo.IC_Products
					ON IC_Products.ProductKey = IC_HistoryProducts.ProductKey
				INNER JOIN Database.dbo.IC_CodeProductCategories
					ON IC_CodeProductCategories.ProductCategoryCode = IC_Products.ProductCategoryCode
				INNER JOIN Database.dbo.IC_CodeProductMainCategory
					ON IC_CodeProductMainCategory.ProductMainCategoryCode = IC_CodeProductCategories.ProductMainCategoryCode
				INNER JOIN #CustBillingGroups
					ON #CustBillingGroups.CustomerKey = IC_HistoryProducts.CustomerKey
					AND #CustBillingGroups.ProductKey = IC_HistoryProducts.ProductKey
	            
			WHERE
				IC_CodeProductMainCategory.ProductMainCategoryCode LIKE @ProductMainCategoryCode
				AND TransactionDate >= @DateStart
				AND TransactionDate <= @DateEnd
			GROUP BY
				IC_HistoryProducts.CustomerKey,
				IC_HistoryProducts.ProductKey,
				IC_CodeProductMainCategory.Display
		) AS salesProdSubtotals
		
		LEFT JOIN(
			SELECT
				IC_HistoryProducts.CustomerKey,
				IC_HistoryProducts.ProductKey,
				IC_CodeProductMainCategory.Display AS ProductMainCategory,
				SUM(IC_HistoryProducts.Quantity_Stk *
					CASE WHEN OE_CodeSalesCategories.SaleType = 3
						THEN -1
						ELSE 1
						END) AS Lbs
			FROM
				Database.dbo.IC_HistoryProducts
				INNER JOIN Database.dbo.OE_CodeSalesCategories 
					ON OE_CodeSalesCategories.SalesCategoryCode = IC_HistoryProducts.SalesCategoryCode
				INNER JOIN Database.dbo.IC_Products
					ON IC_Products.ProductKey = IC_HistoryProducts.ProductKey
				INNER JOIN Database.dbo.IC_CodeProductCategories
					ON IC_CodeProductCategories.ProductCategoryCode = IC_Products.ProductCategoryCode
				INNER JOIN Database.dbo.IC_CodeProductMainCategory
					ON IC_CodeProductMainCategory.ProductMainCategoryCode = IC_CodeProductCategories.ProductMainCategoryCode
				INNER JOIN #CustBillingGroups
					ON #CustBillingGroups.CustomerKey = IC_HistoryProducts.CustomerKey
					AND #CustBillingGroups.ProductKey = IC_HistoryProducts.ProductKey
	            
			WHERE
				IC_CodeProductMainCategory.ProductMainCategoryCode LIKE @ProductMainCategoryCode
				AND TransactionDate >= @DateStart
				AND TransactionDate <= @DateEnd
				AND ProductCode NOT IN (SELECT ProductCode FROM @ProductCodeExclusions)
			GROUP BY
				IC_HistoryProducts.CustomerKey,
				IC_HistoryProducts.ProductKey,
				IC_CodeProductMainCategory.Display
		) AS weightProdSubtotals
			ON weightProdSubtotals.CustomerKey = salesProdSubtotals.CustomerKey
			AND weightProdSubtotals.ProductKey = salesProdSubtotals.ProductKey

		INNER JOIN Database.dbo.AR_Customers
			ON AR_Customers.CustomerKey = weightProdSubtotals.CustomerKey
		INNER JOIN Database.dbo.IC_Products
			ON IC_Products.ProductKey = weightProdSubtotals.ProductKey
			
	UNION ALL

		SELECT
			CustomerCode,
			Name,
			NULL AS CustomerClassCode,
			'SUBTOTAL' AS ProductDescription,
			'' AS ProductMainCategoryCode,
			'' AS ProductCode,
			weightCustSubtotals.Lbs,
			salesCustSubtotals.Sales
		FROM(-- salesSubtotal has no "WHERE ProductCode NOT IN (SELECT ProductCode FROM @ProductCodeExclusions)" clause
			SELECT
				IC_HistoryProducts.CustomerKey,
				SUM((IC_HistoryProducts.Quantity_Prc * IC_HistoryProducts.UnitPrice) *
					CASE WHEN OE_CodeSalesCategories.SaleType = 3
						THEN -1
						ELSE 1
						END) AS Sales
			FROM
				Database.dbo.IC_HistoryProducts
				INNER JOIN Database.dbo.OE_CodeSalesCategories 
					ON OE_CodeSalesCategories.SalesCategoryCode = IC_HistoryProducts.SalesCategoryCode
				INNER JOIN Database.dbo.AR_Customers
					ON AR_Customers.CustomerKey = IC_HistoryProducts.CustomerKey
				INNER JOIN Database.dbo.IC_Products
					ON IC_Products.ProductKey = IC_HistoryProducts.ProductKey
				INNER JOIN Database.dbo.IC_CodeProductCategories
					ON IC_CodeProductCategories.ProductCategoryCode = IC_Products.ProductCategoryCode
				INNER JOIN Database.dbo.IC_CodeProductMainCategory
					ON IC_CodeProductMainCategory.ProductMainCategoryCode = IC_CodeProductCategories.ProductMainCategoryCode
				INNER JOIN #CustBillingGroups
					ON #CustBillingGroups.CustomerKey = IC_HistoryProducts.CustomerKey
					AND #CustBillingGroups.ProductKey = IC_HistoryProducts.ProductKey
			WHERE
				IC_CodeProductMainCategory.ProductMainCategoryCode LIKE @ProductMainCategoryCode
				AND TransactionDate >= @DateStart
				AND TransactionDate <= @DateEnd
			GROUP BY
				IC_HistoryProducts.CustomerKey
		) AS salesCustSubtotals
		
		LEFT JOIN(
			SELECT
				IC_HistoryProducts.CustomerKey,
				SUM(IC_HistoryProducts.Quantity_Stk *
					CASE WHEN OE_CodeSalesCategories.SaleType = 3
						THEN -1
						ELSE 1
						END) AS Lbs
			FROM
				Database.dbo.IC_HistoryProducts
				INNER JOIN Database.dbo.OE_CodeSalesCategories 
					ON OE_CodeSalesCategories.SalesCategoryCode = IC_HistoryProducts.SalesCategoryCode
				INNER JOIN Database.dbo.AR_Customers
					ON AR_Customers.CustomerKey = IC_HistoryProducts.CustomerKey
				INNER JOIN Database.dbo.IC_Products
					ON IC_Products.ProductKey = IC_HistoryProducts.ProductKey
				INNER JOIN Database.dbo.IC_CodeProductCategories
					ON IC_CodeProductCategories.ProductCategoryCode = IC_Products.ProductCategoryCode
				INNER JOIN Database.dbo.IC_CodeProductMainCategory
					ON IC_CodeProductMainCategory.ProductMainCategoryCode = IC_CodeProductCategories.ProductMainCategoryCode
				INNER JOIN #CustBillingGroups
					ON #CustBillingGroups.CustomerKey = IC_HistoryProducts.CustomerKey
					AND #CustBillingGroups.ProductKey = IC_HistoryProducts.ProductKey
			WHERE
				IC_CodeProductMainCategory.ProductMainCategoryCode LIKE @ProductMainCategoryCode
				AND TransactionDate >= @DateStart
				AND TransactionDate <= @DateEnd
				AND ProductCode NOT IN (SELECT ProductCode FROM @ProductCodeExclusions)
			GROUP BY
				IC_HistoryProducts.CustomerKey
		) AS weightCustSubtotals
			ON salesCustSubtotals.CustomerKey = weightCustSubtotals.CustomerKey

		INNER JOIN Database.dbo.AR_Customers
			ON AR_Customers.CustomerKey = weightCustSubtotals.CustomerKey
		
) AS unionQuery

ORDER BY
	unionQuery.CustomerCode,
	unionQuery.ProductCode
;

Open in new window

0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41860989
Hmm.  Could be the case, as there will be 1 or 2 INSERT statements executing before the final SELECT that is returned to the calling code.  Continuing the wild guess add the SET NOCOUNT ON to your SP, re-execute, then re-run your code and see if it behaves differently.

ALTER PROCEDURE Proc_SalesBy
	@ProductMainCategoryCode NVARCHAR(60) = '%',
	@DateStart DATETIME = '1/1/2015',
	@DateEnd DATETIME = '2/2/2015',
	@CustBillingGroupCustomerCode NVARCHAR(60) = '%',
	@ProductCodeExclusionsBool BIT = 1
AS

SET NOCOUNT ON  -- Add this line 

-- Why not add some meaningful code comments to your SP here.. 

-- The rest of your SP here

Open in new window

0
 

Author Comment

by:Filonowst
ID: 41861019
Wow, that seems to have done it. Thanks much! I am going to research proper usage of that command for the future.

Any quick advice on the meaningful comments you recommended? I know it's just my familiarity with it, but to me it the code reads as self-documenting. I am the only one that will touch any code here so it hasn't been an issue, but I'd hate to leave my eventual successor a huge pain.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41861028
My usual goes something like this...

/*
One-line description of what the heck this SP is. 
Any technical or business contacts to ask questions to. 

2016-10-01  jhorn  Original
2016-10-15  jhorn  Had to change some damn thing to handle some other thing. 
2016-10-15  jhorn  Added some other thing for project Screaming Banshee. 
*/

Open in new window


>I am the only one that will touch any code here so it hasn't been an issue
Understood, but nothing's worse then looking at your own code six months after you created it and wondering 'What the hell was I thinking??'

Thanks for the grade.  Good luck with your project.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

760 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