Problem executing stored proc from Excel QueryTable

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.

June 12th, 2014 8:45pm

Hello,

Did you have SET NOCOUNT ON at the start of the Stored Procedure? For example:

ALTER PROCEDURE [dbo].[spResourceForecast]
@ForecastStartDate date
,@ForecastEndDate smalldatetime
AS
Begin 
set nocount on
...
End 

SQL Server returns a message shows the count of the number of rows affected by each T-SQL statement executed in the stored procedure.When SET NOCOUNT is ON, the count is not returned. And the stored procedure only return the results of the last query.

The following thread is about similar issue, please refer to:
http://social.msdn.microsoft.com/forums/sqlserver/en-US/7ac5df70-02bd-4023-a9d1-26b920ae97a8/cannot-run-sql2005-stored-procedure-from-excel-2003

Regards,
Fanny Liu

Free Windows Admin Tool Kit Click here and download it now
June 17th, 2014 3:58am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics