I'm having issues executing a stored procedure under a querytable and can't determine the cause. I can execute other stored procedures with parameters without issue but this one store procedure won't execute at all. Message returned "The query did not run, or the database could not be opened".
Stored proc header:
ALTER PROCEDURE [dbo].[spResourceForecast]
@ForecastStartDate date
,@ForecastEndDate smalldatetime
AS
...
VBA Code:
Sub ChangeDataSource()
Dim ws As Worksheet
Dim lo As ListObject
Dim lvStartDate As Date
Dim lvEndDate As Date
Set ws = ActiveSheet
Set lo = ws.ListObjects(1)
lvStartDate = ws.Range("E1").Value
lvEndDate = ws.Range("F1").Value
With lo.QueryTable
.CommandType = xlCmdSql
.CommandText = "exec rcov.dbo.spResourceForecast @ForecastStartDate='" & lvStartDate & "', @ForecastEndDate='" & lvEndDate & "';"
.Refresh
End With
End Sub
If I execute the query string in sql server it works just fine.
An example of one that works:
Stored Procedure:
ALTER PROCEDURE [dbo].[spTest]
@ClientName varchar(50),
@ProjectNumber nvarchar(50)
AS
select * from tContract where ClientID = @ClientName and [Project Number] = @ProjectNumber
VBA Code similar to above but just passing the exec string and it works.