Within Excel I have created a SQL connection to pull data by calling a stored procedure. I have the Command Type of the connection as SQL and the Command Text as follows: spReportWhoWhatWhere_TST '','',''
I have granted execute permissions on the SP and I'm able to query other tables and get a result set within Excel without a problem. The query takes close to a minute to run within Management Studio, however when run in Excel I get an error: "The query did not run, or the database table could not be opened" after almost exactly 1 min and I'm wondering if it has to do with Excel timing out. Or I'm just using bad syntax within the Command Text.
There was a suggestion to increase the Timout within Excel by using a macro but I am not sure how to implement this within Excel. Any suggestions?
Use the CommandTimeout property in Excel:
Dim objCommand As ADODB.Command
Set objCommand = New ADODB.Command
objCommand.CommandTimeout = 99 '
objCommand.ActiveConnection = cnConn
objCommand.CommandText = "DELETE Users WHERE IdLevel < 98"