0

I've successfully created many connections in an Excel file to a Database so the issue is directly related only to this particular scenario. Server is 2012, Excel is 2013.

I have the following SP:

IF OBJECT_ID('usp_LockUnlockCustomer', 'P') IS NOT NULL
    DROP PROCEDURE usp_LockUnlockCustomer
GO
CREATE PROCEDURE usp_LockUnlockCustomer 
    @Lock AS CHAR(10), @Id AS nvarchar(50), @LockedBy AS nvarchar(50)=null
AS BEGIN
SET NOCOUNT ON;
IF @Lock = 'Lock'
    INSERT INTO IO_Call_DB..IdLock (Id, LockedBy, LockedDtTm)
    VALUES(@Id,@LockedBy,GETDATE())
;
IF @Lock = 'Unlock'
    DELETE FROM IO_Call_DB..IdLock
    WHERE Id = @Id
;
END;
--EXEC usp_LockUnlockCustomer 'Lock','123456789', 'Test User'

The above SP is called via some VBA as follows:

With ActiveWorkbook.Connections("usp_LockUnlockCustomer").OLEDBConnection
    .CommandText = "EXECUTE dbo.usp_LockUnlockCustomer '" & bLock & "','" & Id & "','" & LockedBy & "'"
End With

I have tested the string and the string is formatted correct & contains all required data.

The connection between Excel and SQL is created via "Data > From Other Sources > From SQL Server", it's a fairly standard process which has worked for all other SP's and general queries.

I think, because this connection is not returning data to Excel (I only set up the connection rather than specifying that Excel should return data to a cell) that this may be the issue.

Has anyone experienced this issue before?

EDIT1: I have resolved the issue but it's not a particularly great outcome. Some help would be appreciated. To resolve the issue, you have to include a "select * from" process at the end of the stored procedure and also tell Excel to output the data to a range within the workbook. This allows the .Refresh portion of the VBA to do whatever it does & submit the SP to SQL.

Essentially, you're being forced to create a data table - but I don't want any data, I just want to submit a command.

So, how do you submit a command and not have Excel require that you 1) explicitly state where the data should be put 2) include a SELECT statement within the stored procedure when I don't require any data to be returned.

My fix was to "select top 0" from the table, at least that way the data table being output to Excel won't grow.

Dan
  • 97
  • 1
  • 7

2 Answers2

0

In my experience if you generate the database connection in VBA, (there are multiple previous questions about that), rather than rely on an existing workbook connection, your stored procedure will execute regardless of what it returns.

Roie R
  • 139
  • 1
  • 7
0

The problem I have is that by merely creating the connection without specifying a cell to return data to, nothing happens.

In addition, if I specify a cell to return data to, nothing happens unless I use my 'fix' which is to create an empty table at the end of the SP.

Dan
  • 97
  • 1
  • 7