April 8, 2008 at 6:49 pm
hi all,
i have this weird problem,or so it seems to me.there's this vb 6 application which runs evryday,on a remote server. it was fine till recently, where the app cannot write to the database at all. when this happens,it just hangs.:crying:
i have checked the source code for any bug that might be causing this,but i can't seem to find any.furthermore,the fact that this happens randomly is not helpful at all.:unsure:
here's my code snippet:
Public cn as New ADODB.Connection
Dim connStr as String
connStr ="";//i have checked that the connection string points to the correct database.
cn.Open connStr
cn.Execute sqlStatement//the program hangs at this line of code
after the program hangs,i get this message box with the error:Run-time error '-2147217871{80040e31}' Timeout expired.
what i can deduce from that is, the program cant write to the db for sum reason.i have checked the permissions also.and now im baffled to wat could be causing this..any suggestions would be appreciated.
thanks alot:)
April 8, 2008 at 7:12 pm
forgot to mention another weird symptom. the first time this happened, i thought it was a permission error.so i meddled with the permissions,and granted datareader and datawriter access to the user.and that solved the problem for like 3 days,and it cropped up again after that.then i changed the permission back to dbowner only,and it worked for a temporary time again..its driving me crazy:crazy:
April 9, 2008 at 9:11 am
[font="Arial"]Hello
This problem sounds more like an operating system level error. It's timing out when trying to establish the connection.
If I'm interpreting your description, this process runs in a daily operation that is called by a scheduler..? OR is it manually triggered? Whose login/password is used? These are things to find out for sure.
If a scheduler is used, then a different logon/password is used and that might be the problem.
If the operating system has changed the permissions to access the remote server for the scheduler logon/password then you should be getting a operating system level error message.
If its getting a connection but failing on the sproc execution call, then make sure that there is a grant execution to public ( or what ever secutiry design login is used ).
One other thing is to add some error checking code to see if the connection is successful before you try and execute the sproc sql.
I hope this gives you some ideas to check into.
Regards,
Terry
[/font]
April 10, 2008 at 11:43 pm
halo.. thanks so much for your suggestions. i will check the stuff u mentioned,and hope it'l solve the problem.. thanks again;)
April 10, 2008 at 11:59 pm
shwetha004 (4/8/2008)
Public cn as New ADODB.Connection
Dim connStr as String
connStr ="";//i have checked that the connection string points to the correct database.
cn.Open connStr
cn.Execute sqlStatement//the program hangs at this line of code
What's the string in the variable sqlStatement?
I would suggest you fire up pofiler, point it at the server that you're running this on and start a trace. That way you can see exactly what is happening on the server.
I would suggest you trace Audit login, audit logout, Stored procedures:RPC starting, Stored Procedures RPC completes, T-SQL: Batch starting, T-SQL: Batch completed.
Maybe also add in all of the error events
You will need sysadmin rights to start a profile trace. If you don't have, see if you can get one of the admins to run the trace for you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply