asked on

Sporadic Sql Server Login Issue

I am taking full advantage of my subscription with these issues lately. I have scoured the web and I can find NO REFERENCE to why I would be getting this error SPORADICALLY (there are tons of references where this issue comes up but not on a sporadic basis that I have seen):

Cannot open database "xxx" requested by the login. The login failed.

It happens here: www.gopherstateevents.com

and it happens with this connection string/ query:

Dim conn							
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLNCLI10;Server=216.185.199.1,1433\SQLExpress;Database=xxx;Uid=xxx;Pwd=xxx;"


sql = "INSERT INTO Visitors (When_Visit, Page, IPAddress, Browser) VALUES ('" & Now() & "', 'default', '" 
sql = sql & Request.ServerVariables("REMOTE_ADDR") & "', '" & Request.ServerVariables("HTTP_USER_AGENT") & "')"
Set rs=conn.Execute(sql)
Set rs=Nothing

Open in new window

It also happen in other places but, again, SPORADICALLY. I am reasonably certain that the user has correct permissions and I don't even thing it is typically a high volume error.

Any help?

SOLUTION

Patrick Bogers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Schneider

ASKER

I agree that it sounds like a time out issue now that you mention it. However the link you pointed me to indicates that the connection timeout is 600 seconds. Can't be that. However if I click on the Connection Properties link on that screen I see that there is a connection timeout of 15 seconds. That is also long but if I wanted to change it I don't see how because it is disabled. Any ideas?

SOLUTION

Jim P.

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Schneider

ASKER

Autoclose was set to True. Thanks! Are there any other settings that I should be aware of that might cause this problem?

SOLUTION

Jim P.

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Schneider

ASKER

Should it be?

Bob Schneider

ASKER

I did a little research and found that it should be off, which it is.

Thanks

SOLUTION

Jim P.

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Schneider

ASKER

I have had absolutely no errors since I set Auto-Close to False. Thanks!

How do I control the log files?

ASKER CERTIFIED SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Schneider

ASKER

I have full once daily with hourly transaction logs and I have them going to a remote location. I recently had to completely restore the database for and it worked perfectly so that was nice.

I keep the bak files and trans logs for 1 week. The bak file sizes range from 17mb to 715mb with the trn files around 5mb or less.

I have unlimted growth and 10% set for my file sizes.

Thanks so much for all the help. Still no errors since I sent Auto-Close to False.

Bob Schneider

ASKER

Another great discussion.