CODEWITHSUNDEEP
sql-servert-sqlms-access
Anthony Griggs
Anthony Griggs

Reputation: 1641

Executing Stored Procedure From MS Access Causes Timeout

The weirdest thing. I have a simple procedure that I developed in Microsoft Access 2010 with a SQL Server 2012 backend. I am now trying to deploy this into production which is Access 2016 and a SQL Server 2014 backend.

I've compiled, compact and repaired in the new environment... but I can not get Access to execute this simple stored procedure. Even worse it still executes several other stored procedures fine... but a couple of them it times out and refuses to execute?

Here is my VBA and stored procedure:

 Private Sub GenerateUnitKey(UnitColumns As String)
    Dim Msg, Style, Title, Response As Variant
    Dim lngProcessID As Long
    Dim Conn As ADODB.Connection
    Dim Cmd As ADODB.Command
    Dim CurrentConnection As String

    CurrentConnection = LinkMasterConnection()

    Msg = "Are you sure you want to update the UnitKey with the selected columns?"
    Style = vbYesNo + vbCritical + vbDefaultButton2
    Title = "Save Campaign?"
    Response = MsgBox(Msg, Style, Title)

    If Response = vbYes Then
    Call OpenSixHatLoader("Generating Unit Key Across Campaign Records", 1, "")

    Set Conn = New ADODB.Connection
    Conn.Open CurrentConnection
    Set Cmd = New ADODB.Command
    With Cmd
        .ActiveConnection = CurrentConnection
        .CommandText = "usp_GenerateUnitKey"
        .CommandType = adCmdStoredProc
        .CommandTimeout = 30
        .Parameters.Append .CreateParameter("@UnitColumns", adVarChar, adParamInput, 4000, UnitColumns)
        .Execute
        End With
    End If
End Sub

And stored procedure: 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GenerateUnitKey]
@UnitColumns AS VARCHAR(4000)
AS      
SET NOCOUNT ON
DECLARE @SQL AS VARCHAR(MAX)
UPDATE tblStagingTable SET UnitKey =''

SET @SQL = 'UPDATE tblStagingTable SET UnitKey = ' + @UnitColumns + ' FROM tblStagingTable st'
EXEC(@SQL)

-- UPDATE Interests to match Staging Table
UPDATE tblInterests SET UnitKey = st.[UnitKey] FROM tblInterests i
INNER JOIN tblStagingTable st ON i.StagingTableID = st.StagingTableID

I am fairly confident there is nothing wrong with the code... as I said it worked fine in my development environment... even more I am manually able to execute the stored procedure within SQL Server. My SQL Server Native Client 11.0 connection works in executing other stored procedures... but for a couple of them it does not work. I am thinking I need to configure something within SQL Server itself or maybe within the Native Client 11.0 driver?

Unfortunately it gives no exception. I've set the CommandTimeout property to 0 and let it chug for a few hours hoping it would throw and exception to give me a clue but nothing... it just was frozen trying to execute. Any suggestions or ideas would be greatly appreciated because this one has me really stumped because it should be fine!

Upvotes: 0

Views: 1286

Answers (2)

Anthony Griggs
Anthony Griggs

Reputation: 1641

This was a difficult one that took me about 3 solid days of troubleshooting to get a solution to. Although I am not satisfied with the end solution as it should have just worked... but in the end my theory of the server being an Virtual Machine proved correct. When I deployed this exact same setup to Microsoft Access 2016 32 bit and SQL Server 2014 32 bit on a dedicated server it worked exactly as it was supposed to compared to the Azure VM and 1&1 Cloud Servers I had attempted to deploy to.

SQL Server integration with VM's is getting better from what all I have read, but apparently there is a ways to go. Maybe SQL Server needs to release a special VM version. Thank you to all those who took the time to look into this.

Upvotes: 0

Albert D. Kallal
Albert D. Kallal

Reputation: 49039

I would first launch SSMS, and from the SQL studio type in Exec xxxxx ''

And ensure it runs (and use the SAME logon and connection to SSMS that you currently have for Access.

I would also consider creating a pass-though query, and saving that query in access. (set returns records = false if the sp does not return records). Then in code to run any proc, you can go:

With CurrentDb.QueryDefs("qryPass")
  .SQL = "exec usp_GenerateUnitKey '" & UnitColumns & "'"
  .Execute
End With

You note how simple the above code is - so if sp works from SSMS, then try the above code.

Upvotes: 3

Related Questions