title: adodb execute sql command timeout
body: <p>I have this code below and it is hanging on me.  It worked fine for several files...but it is now hanging on me and times out with error (-2147217871:Query timeout expired).  </p>
  
  <p>'This is what it looks like in SQL Server when I print the string in the immediate window: </p>
  
  <pre><code>BEGIN TRAN; 
  INSERT INTO [dbo].[XLSHdr] WITH(TABLOCK) ([HdrID],[TabName],[FileHyperLink],[FileName],[DocumentType],[EmailID],[Subject],[SentDate],[AttachmentName],[TextFileLink],[DateReceived],[Vendor],[ContactName],[ContactNumber],[SupplierName],[VendorNum],[Increase],[Decline],[EDLP],[Rollbk],[Prepaid],[Collect],[CostKeyed],[RetailKeyed],[Fixed],[Variable],[BuyerComments],[PriorMktPrice],[CurrentMktPrice],[BellyMktUsed],[MktDate],[Perc_Chg],[Cost_EffDate],[Cost_EndDate],[Retail_EffDate],[Retail_EndDate],[SubmittedDate],[aTitle],[bTitle],[cTitle],[dTitle],[COOP],[TypeOfChange],[InTransit],[WHSE],[Store],[WeeksInvOwned] ) 
  VALUES ('34454','RDC &amp; DSD Cost Change Form','\\amer.prgx.com\auditfiles\WalMart-US\Public\MTX\Rollback\D\DFDD-1718\Hormel Pepperoni Price Increase Effective August 2 2017 (2).xlsx','Hormel Pepperoni Price Increase Effective August 2 2017 (2).xlsx','Promo','DFDDC3A1202C3E1AF7332F1BB4BEAC00FA09498320170822191718','Hormel Pepperoni Price Increase Effective August 2 2017 (2).xlsx','8/22/2017 3:17:18 PM','Hormel Pepperoni Price Increase Effective August 2 2017 (2).xlsx','\\amer.prgx.com\auditfiles\WalMart-US\Public\MTX\Rollback\D\DFDD-1718\Hormel Pepperoni Price Increase Effective August 2 2017 (2)_met.txt','','','','','','','','','','','','','','','','','','','','','','','08/02/17','','08/02/17','','','WAL-MART COST CHANGE FORM','','','','','','','','','')
  COMMIT TRAN;
  </code></pre>
  
  <p>vba code below that builds the sql string:</p>
  
  <pre><code> Dim ConUpd As Object
   Set ConUpd = CreateObject("ADODB.Connection")
      strConn = GetConnectionString()
      ConUpd.Open strConn
      ConUpd.CommandTimeout = 60
  
   SQL = "INSERT INTO [dbo].[XLSHdr] WITH(TABLOCK) "
          SQL = SQL &amp; "([HdrID],[TabName],[FileHyperLink],[FileName],[DocumentType]"
          SQL = SQL &amp; ",[EmailID],[Subject],[SentDate],[AttachmentName],[TextFileLink],[DateReceived],[Vendor]"
          SQL = SQL &amp; ",[ContactName],[ContactNumber]"
          SQL = SQL &amp; ",[SupplierName],[VendorNum],[Increase],[Decline]"
          SQL = SQL &amp; ",[EDLP],[Rollbk],[Prepaid],[Collect],[CostKeyed]"
          SQL = SQL &amp; ",[RetailKeyed],[Fixed],[Variable],[BuyerComments],[PriorMktPrice],[CurrentMktPrice]"
          SQL = SQL &amp; ",[BellyMktUsed],[MktDate],[Perc_Chg],[Cost_EffDate],[Cost_EndDate],[Retail_EffDate],[Retail_EndDate],[SubmittedDate]"
          SQL = SQL &amp; ",[aTitle],[bTitle],[cTitle],[dTitle],[COOP],[TypeOfChange],[InTransit],[WHSE],[Store],[WeeksInvOwned] )"
  
          SQL = SQL &amp; " VALUES ('" &amp; HdrID &amp; "','" &amp; Replace(TabName, "'", " ") &amp; "','" &amp; Replace(FileHyperLink, "'", "''") &amp; "','" &amp; Replace(FileName, "'", " ") &amp; "','" &amp; fReplaceToOne(Replace(DocumentType, "'", " ")) &amp; "'"
          SQL = SQL &amp; ",'" &amp; fReplaceToOne(Replace(sTxtEmailID, "'", " ")) &amp; "','" &amp; Replace(sTxtSubject, "'", " ") &amp; "','" &amp; fReplaceToOne(Replace(sTxtDate, "'", " ")) &amp; "','" &amp; Replace(sTxtAttName, "'", " ") &amp; "'"
          SQL = SQL &amp; ",'" &amp; Replace(sTxtFileLink, "'", " ") &amp; "','" &amp; fReplaceToOne(Replace(sDateReceived, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(sTxtVendorName, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(contactInfo, "'", " ")) &amp; "'"
          SQL = SQL &amp; ",'" &amp; fReplaceToOne(Replace(contactInfo, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(SupplierName, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(VendorNum, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(Increase, "'", " ")) &amp; "'"
          SQL = SQL &amp; ",'" &amp; fReplaceToOne(Replace(Decline, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(EDLP, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(nRollback, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(Prepaid, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(Collect, "'", " ")) &amp; "'"
          SQL = SQL &amp; ",'" &amp; fReplaceToOne(Replace(CostKeyed, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(RetailKeyed, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(nFixed, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(wtVariable, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(BuyerComments, "'", " ")) &amp; "'"
          SQL = SQL &amp; ",'" &amp; fReplaceToOne(Replace(PriorMktPrice, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(CurrentMktPrice, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(BellyMktUsed, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(MktDate, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(PercChg, "'", " ")) &amp; "'"
          SQL = SQL &amp; ",'" &amp; fReplaceToOne(Replace(hCostEffDate, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(hCostEndDate, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(hRetEffDate, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(hRetEndDate, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(SubmittedDate, "'", " ")) &amp; "'"
          SQL = SQL &amp; ",'" &amp; Left(fReplaceToOne(Replace(aTitle, "'", " ")), 150) &amp; "','" &amp; Left(fReplaceToOne(Replace(bTitle, "'", " ")), 150) &amp; "','" &amp; Left(fReplaceToOne(Replace(cTitle, "'", " ")), 150) &amp; "','" &amp; Left(fReplaceToOne(Replace(dTitle, "'", " ")), 150) &amp; "','" &amp; Left(fReplaceToOne(Replace(COOPh, "'", " ")), 50) &amp; "','" &amp; Left(Replace(TypeOfChange, "'", " "), 100) &amp; "'"
          SQL = SQL &amp; ",'" &amp; fReplaceToOne(Replace(inTransith, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(WHSEh, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(Storeh, "'", " ")) &amp; "','" &amp; fReplaceToOne(Replace(WeeksInvOwnedh, "'", " ")) &amp; "')"
  
          Call ConUpd.Execute("BEGIN TRAN; " &amp; vbCrLf &amp; SQL &amp; vbCrLf &amp; " COMMIT TRAN;")
  </code></pre>