Re: ODBC timeout when xp_cmdshell execute a 'long' script
From: Narayana Vyas Kondreddi (answer_me_at_hotmail.com)
Date: 05/17/04
- Next message: Sohail: "Log File"
- Previous message: Julie: "Re: ISOLATION LEVEL setting at different trancounts"
- In reply to: ECee: "ODBC timeout when xp_cmdshell execute a 'long' script"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 17 May 2004 10:02:24 +0100
You'd want to change the CommandTimeout property of the object. More
information on timeouts at:
http://vyaskn.tripod.com/watch_your_timeouts.htm
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
-- HTH, Vyas, MVP (SQL Server) http://vyaskn.tripod.com/ Is .NET important for a database professional? http://vyaskn.tripod.com/poll.htm "ECee" <anonymous@discussions.microsoft.com> wrote in message news:1EAF7064-F2F6-4FB3-BECC-D4D3AADC881F@microsoft.com... Hi all, I have this problem that seems to be related to ODBC connection timeout. But after some research online and applying some identified recommendation, mainly by setting the connection object's timeout value to 0 (infinity), the problem persist. Here's my setup. MDAC 2.8 W2K3 Servers SQL Svr 2000 SP3 I have an ASP page connecting to a SQL server retrieving data via a single stored procedure call. Here's the snippet of my ASP ... **************************************************************************** * Set mobjRS = CreateObject("ADODB.Recordset") Set mobjAdoConnection = CreateObject("ADODB.Connection") With mobjAdoConnection .ConnectionString = ConnectionString .ConnectionTimeout = 0 .CursorLocation = 3 .Properties("Prompt") = 4 .Open If .State = 1 Then blnSuccess = True End If End With sSQL = "exec usp_storedproc '" & strvar1 & "','" & strvar2 & "','" & strvar3 & "'" Set mobjRS = mobjAdoConnection.Execute(sSQL) ' this is the line reported by my error description below **************************************************************************** * usp_storedproc is a stored procedure that will call xp_cmdshell to invoke a vbscript... which looks something like below: **************************************************************************** * ... create table #temp1 (gname varchar(255)) select @cmd = 'cscript //NOLOGO C:\script.vbs ' + @vchvar1 + ' ' + @vchcar2 insert into #temp1 ... **************************************************************************** * script.vbs contain ADSI code to query domain information base on var1 and var2. For some query, it can be as fast as near instanteneous. However, there are also some which may take as long as 30-50 seconds, or even more. I read somewhere that xp_cmdshell is a synchronous call, and hence will be affected by how fast the query in my vbs finishes... but this shouldn't affect how my ASP runs. Wrong? Having said all that, here are some of my findings... 1) if the vbs finished instantaneously, the ASP page display correctly. 2) if the vbs took longer, say 30-50 seconds, the ASP page displays the following error. **************************************************************************** * Microsoft OLE DB Provider for ODBC Drivers error '80040e31' [Microsoft][ODBC SQL Server Driver] Timeout expired /script.vbs, line XXX (see the comment tag in above code) **************************************************************************** * I have tried running the "cscript..." line found in stored procedure within a cmd window - it executed fine, just that it takes a while. I have also tried trapping the stored proc call with profiler and run the exact same line using exact user context in query analyzer - it executed fine, just that it takes a while... Lastly, I have also tried setting the ASP timeout setting from the current 90 seconds to 900 seconds... the problem persists. CaAny advise on other troubleshooting approaches? Or if at all possible, let me know what actually went wrong? Thanks in advance.
- Next message: Sohail: "Log File"
- Previous message: Julie: "Re: ISOLATION LEVEL setting at different trancounts"
- In reply to: ECee: "ODBC timeout when xp_cmdshell execute a 'long' script"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
