Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

a better ms sql 2005 connection string

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.
0
robrodp
Asked:
robrodp
  • 4
  • 3
1 Solution
 
appariCommented:
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.
0
 
robrodpAuthor Commented:
Any details of how increase the sqlcommand objects CommandTimeout ?
0
 
appariCommented:
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
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
robrodpAuthor 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
0
 
robrodpAuthor Commented:
dnd
Set MyRecordSet = Nothing
0
 
appariCommented:
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
0
 
robrodpAuthor Commented:
Hey cn is the connection string... CommandTimeout does not belong there
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now