We help IT Professionals succeed at work.

a better ms sql 2005 connection string

robrodp
robrodp asked
on
Medium Priority
341 Views
Last Modified: 2012-05-06
I connect to my sql server in my asp scipts. The connection sting is: application("connection")="Provider=SQLNCLI;Server=74.86.187.166\SQL2k5,1435;Database=database;Uid=user;Pwd=password;
Many times I get a timeout error with my queries. Apparenlty from the ms sql manager the connection has fewer problems.

The only thing I can thonk of is an ptimized connection string.
Comment
Watch Question

Commented:
are you getting connection timeout or query timeout erros?
your question says queries are timedout, means  you are able to create the connection without any error.
means your queries are taking more time to execute either optimize your queries or before executing the command increase the sqlcommand objects CommandTimeout property value, i think default is 30 sec. in sql manager the query timeout is unlimited.
robrodpProgrammer

Author

Commented:
Any details of how increase the sqlcommand objects CommandTimeout ?

Commented:
can you post your existing code?

suppose your sqlcommand objects name is sqlCmd
do this to increase the timeout

sqlCommand.CommandTimeout = 100 ' sets timeout to 100 seconds

sqlCommand.CommandTimeout = 0 ' for unlimited
robrodpProgrammer

Author

Commented:
Set MyRecordSet = Server.CreateObject("ADODB.RecordSet")
cn = application("connection")
sql="select count(*) as cuantos from people"
MyRecordSet.Open sql, cn
cuantos=MyRecordSet("cuantos")
MyRecordSet.Close
robrodpProgrammer

Author

Commented:
dnd
Set MyRecordSet = Nothing
Commented:
ok you are using adodb.recordset, try like this

Set MyRecordSet = Server.CreateObject("ADODB.RecordSet")
cn = application("connection")
cn.CommandTimeout = 100
sql="select count(*) as cuantos from people"
MyRecordSet.Open sql, cn
cuantos=MyRecordSet("cuantos")
MyRecordSet.Close

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
robrodpProgrammer

Author

Commented:
Hey cn is the connection string... CommandTimeout does not belong there
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.