DBExec - Query Time Out

[hoangvo81]

Hi,

I am running into a problem and can't seem to find out why its timing out.
It doesn't always time out but sometimes it does:
Here's the code.

Code: Select all

        Let>DB_COMMANDTIMEOUT=1200
        DBConnect>Provider=SQLNCLI.1;Password=%mypassword%;Persist Security Info=True;User ID=%myuser%;Data Source=localhost\sqlexpress,dbH
        let>Sql=Backup Database [%CompanyNo%Evolution] to disk='%thisbackupfolder%\Pre-APM %companyno%Evolution.dump'
        dbExec>dbH,sql,bkResult

the script created the file and size of the file, at time it will go through the process no issue, other times it will time out with macro scheduler message : LINE #, Query Time Out.


anyone has done a sql query database backup successfully over and over?

The curren tversion i m on is 14.0.16
Thanks,

Hoang

[Marcus Tettmar]

You can try setting a timeout in the connection string:

http://technet.microsoft.com/en-us/libr ... 30822.aspx

[hoangvo81]

I've try adding Timeout=1200 to the connection string
and still getting a query time out expired.

when running the same query via SQL Management Studio, it run without a problem.
using the same login I used in teh connection string to connect to the sql database.

the total time it took for a 4.5 gig was less than 6 mins

timeout is by second, 1200 should give me 20mins.

i tried with VBScript within the engine and still the same problem, query time out expired.

any other suggestion?

[Marcus Tettmar]

The server itself may have a timeout set for remote queries which might explain why you get the timeout from ODBC (whether from MS or VBScript) but not when direct.

See:
http://support.microsoft.com/kb/314530

There are two configurable timeout options that affect the execution of remote queries. The error messages occur when a query exceeds the timeout option values. Refer to the "More Information" section of this article for further details about the timeout options

In short it looks like this is due to a setting on the server itself.

When the query attempts to establish a connection to the remote server, the first error message occurs if the time it takes the query to establish a connection exceeds the remote login timeout option value.

By default, in Microsoft SQL Server 7.0, the timeout setting is zero (0 - infinite wait). By default, in SQL Server 2000 and in SQL Server 2005, the timeout setting is 600 (10 minutes).

"Configure the remote query timeout Server Configuration Option"
http://technet.microsoft.com/en-us/libr ... 89040.aspx

[hoangvo81]

Server Remote connection is 600 within sql server by default, which give me 6mins.
the time it took for the time out was less than 2mins.

I re-ran the code on a different server
MS DBExec - timed out,
MS VBscript - completed

the vb script version within macro scheduler - not a problem

Code: Select all

    dim constr
    constr="Provider=SQLOLEDB;Data Source=localhost\sqlexpress;User ID=myuserID;Password=mypassword;"
    set con=CreateObject("ADODB.Connection")
    con.open constr
    set comm=CreateObject("ADODB.Command")
    comm.ActiveConnection = con
    dim sql
    sql="Backup Database [" & dbname & "] to disk='" & bkUpName & "'"
    comm.CommandText=sql
    comm.Execute
    con.close
    set con=nothing
    set comm=nothing

i've posted the code above.
it's pretty much the same as the ms script version.


both mscript and vbscript fail on the one server, while the vbscript pass on the second server.

the differences between the two server:
first one is a VM with the storage hosted somewhere else
the second server is a physical server with its own storage.
so i m thinking it might just come down to network - issue especially with the vbscript as both sql server version are identical, data is different but setup are the same.