3

I experience unexpected behavior of execution of a stored procedure on SQL Server through Excel ADO. Stored procedure has a string parameter. If parameter of sp is short - up to round 4 milion characters - everything works fine.

Problem starts when string parameter is e.i. 9 milion characters. I have determined that definitely ADO can pierce to SQL server as I capture the parameter by this code in the beginning of sp: 

create procedure MyStoredProcedure(@String nvarchar(max)) 
as 
if OBJECT_ID('dbo.temp', 'U') is not null drop table dbo.temp;
select [String]=@String into dbo.temp

However, parameter cannot be processed further in the sp which is called through Excel ADO. Detailed sp might not be important but just in case it is presented on Stackoverflow in answer here. Definitely, sp itself is ok because I can call it from SSMS like this:

declare @String nvarchar(max)
set @String=(select top 1 [String] from dbo.temp);
exec dbo.MyStoredProcedure @String

and the query executes correctly without any limits to parameter size.

Are there any limits to execution of stored procedures through Excel ADO - like parameter length limit, query limit, time limit?

Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • 1
    A different technique may benefit you better, but I would do some research/ask another question that specifies what you are trying to actually do. there are lots of ETL tools and depending on if it has to be done with Excel open or if you can use scheduled loads or...there are a lot of different answers. 9 million characters is a significantly sized parameter to pass to a SQL server. Also your other question was about splitting string and this one is tagged SQL 2016 which has a STRING_SPILT() function https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql – Matt Nov 06 '17 at 17:18

1 Answers1

0

Figured that out. I have turned on the option Break on all errors in VBA error trapping and it showed me the rascal at once. The error message was Run-time error '-2147217908 (80040e31)'. I guess that default value for time out must be 30 seconds. The query executed through ADO connection goes beyond that time as parameter string length passes 9 milion characters. I just set time out to 120 seconds and problem was solved. 

Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")  
    cmd.CommandTimeout = 120 'This line solved the problem

Anyway, it would be still valuable to know the list of the limits on ADO connection, even those limits which are set up with default values.

Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191