adodb execution of sql command timeout (1 Viewer)

A

abenitez77

Registered User.
Local time
Today, 02:12
Joined
Apr 29, 2010
Messages
141
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). But When I copy the sql string to SQL Server and run it, it runs in less than 1 second without errors.

Code: 
'This is what it looks like in SQL Server when I print the string in the immediate window: 

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 & 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;

' vba code below that builds the sql stri:
 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 & "([HdrID],[TabName],[FileHyperLink],[FileName],[DocumentType]"
        SQL = SQL & ",[EmailID],[Subject],[SentDate],[AttachmentName],[TextFileLink],[DateReceived],[Vendor]"
        SQL = SQL & ",[ContactName],[ContactNumber]"
        SQL = SQL & ",[SupplierName],[VendorNum],[Increase],[Decline]"
        SQL = SQL & ",[EDLP],[Rollbk],[Prepaid],[Collect],[CostKeyed]"
        SQL = SQL & ",[RetailKeyed],[Fixed],[Variable],[BuyerComments],[PriorMktPrice],[CurrentMktPrice]"
        SQL = SQL & ",[BellyMktUsed],[MktDate],[Perc_Chg],[Cost_EffDate],[Cost_EndDate],[Retail_EffDate],[Retail_EndDate],[SubmittedDate]"
        SQL = SQL & ",[aTitle],[bTitle],[cTitle],[dTitle],[COOP],[TypeOfChange],[InTransit],[WHSE],[Store],[WeeksInvOwned] )"
        
        SQL = SQL & " VALUES ('" & HdrID & "','" & Replace(TabName, "'", " ") & "','" & Replace(FileHyperLink, "'", "''") & "','" & Replace(FileName, "'", " ") & "','" & fReplaceToOne(Replace(DocumentType, "'", " ")) & "'"
        SQL = SQL & ",'" & fReplaceToOne(Replace(sTxtEmailID, "'", " ")) & "','" & Replace(sTxtSubject, "'", " ") & "','" & fReplaceToOne(Replace(sTxtDate, "'", " ")) & "','" & Replace(sTxtAttName, "'", " ") & "'"
        SQL = SQL & ",'" & Replace(sTxtFileLink, "'", " ") & "','" & fReplaceToOne(Replace(sDateReceived, "'", " ")) & "','" & fReplaceToOne(Replace(sTxtVendorName, "'", " ")) & "','" & fReplaceToOne(Replace(contactInfo, "'", " ")) & "'"
        SQL = SQL & ",'" & fReplaceToOne(Replace(contactInfo, "'", " ")) & "','" & fReplaceToOne(Replace(SupplierName, "'", " ")) & "','" & fReplaceToOne(Replace(VendorNum, "'", " ")) & "','" & fReplaceToOne(Replace(Increase, "'", " ")) & "'"
        SQL = SQL & ",'" & fReplaceToOne(Replace(Decline, "'", " ")) & "','" & fReplaceToOne(Replace(EDLP, "'", " ")) & "','" & fReplaceToOne(Replace(nRollback, "'", " ")) & "','" & fReplaceToOne(Replace(Prepaid, "'", " ")) & "','" & fReplaceToOne(Replace(Collect, "'", " ")) & "'"
        SQL = SQL & ",'" & fReplaceToOne(Replace(CostKeyed, "'", " ")) & "','" & fReplaceToOne(Replace(RetailKeyed, "'", " ")) & "','" & fReplaceToOne(Replace(nFixed, "'", " ")) & "','" & fReplaceToOne(Replace(wtVariable, "'", " ")) & "','" & fReplaceToOne(Replace(BuyerComments, "'", " ")) & "'"
        SQL = SQL & ",'" & fReplaceToOne(Replace(PriorMktPrice, "'", " ")) & "','" & fReplaceToOne(Replace(CurrentMktPrice, "'", " ")) & "','" & fReplaceToOne(Replace(BellyMktUsed, "'", " ")) & "','" & fReplaceToOne(Replace(MktDate, "'", " ")) & "','" & fReplaceToOne(Replace(PercChg, "'", " ")) & "'"
        SQL = SQL & ",'" & fReplaceToOne(Replace(hCostEffDate, "'", " ")) & "','" & fReplaceToOne(Replace(hCostEndDate, "'", " ")) & "','" & fReplaceToOne(Replace(hRetEffDate, "'", " ")) & "','" & fReplaceToOne(Replace(hRetEndDate, "'", " ")) & "','" & fReplaceToOne(Replace(SubmittedDate, "'", " ")) & "'"
        SQL = SQL & ",'" & Left(fReplaceToOne(Replace(aTitle, "'", " ")), 150) & "','" & Left(fReplaceToOne(Replace(bTitle, "'", " ")), 150) & "','" & Left(fReplaceToOne(Replace(cTitle, "'", " ")), 150) & "','" & Left(fReplaceToOne(Replace(dTitle, "'", " ")), 150) & "','" & Left(fReplaceToOne(Replace(COOPh, "'", " ")), 50) & "','" & Left(Replace(TypeOfChange, "'", " "), 100) & "'"
        SQL = SQL & ",'" & fReplaceToOne(Replace(inTransith, "'", " ")) & "','" & fReplaceToOne(Replace(WHSEh, "'", " ")) & "','" & fReplaceToOne(Replace(Storeh, "'", " ")) & "','" & fReplaceToOne(Replace(WeeksInvOwnedh, "'", " ")) & "')"
        
        Call ConUpd.Execute("BEGIN TRAN; " & vbCrLf & SQL & vbCrLf & " COMMIT TRAN;")
 
S

sonic8

AWF VIP
Local time
Today, 08:12
Joined
Oct 27, 2015
Messages
998
abenitez77 said:
Code: 
BEGIN TRAN; 
 INSERT INTO [dbo].[XLSHdr] WITH(TABLOCK) ([HdrID],[TabName], ...
VALUES ('34454','RDC & DSD Cost Change Form',...
COMMIT TRAN;
Click to expand...
What's the purpose of the table lock?
I hardly see any use in this for a single record insert statement.

However, it will dramatically increase the possibility of resource contention. I guess your query is timing out while waiting for the lock on the table. - If this is legit or reason for concern I cannot say without knowing all that's going on with your db.

BTW: The explicit transaction for this single statement is superfluous, but it does not cause any harm.
 
A

abenitez77

Registered User.
Local time
Today, 02:12
Joined
Apr 29, 2010
Messages
141
The table is compressed, so TabLock is needed there...so the dba's told me. : )
 
A

abenitez77

Registered User.
Local time
Today, 02:12
Joined
Apr 29, 2010
Messages
141
I found my answer...I had a window in sql studio where i did a select statement on that table...and it had a hold on the table...so that is why it hung and didn't allow me to insert with tablock.

Sonic8, Thanks for pointing that out...it led me to finding the issue.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom