This same code used to work but doesn't anymore. The connection times out and the error points to the dbCMD.Execute command. Here same things I have found:
1. If I point the database calls to the development server it works. If I point the database calls to the production server it fails. I am to get to production through other calls just fine.
2. If I comment out the dbCmd.Execute or dbCmd.CommandText = "knet_user_setLastLogin" seperately the code works.
What is the dbCmd.Execute doing when it is called?
Could it be a problem with the store procedure?
Could it be a software upgrade on the Database server that is causing the problem?
The stored procedure that is being called does on update to a user table on the data base. A date field is being update with a getDate() call. I'm not sure exactly where getDate() is coming from but could that somehow be causing a problem if say getDate can't be found?
What error are you getting? This kind of resembles taking your car to the mechanic and telling them "It's broke"... The most likely response that I'd get would be "Yep, it probably is" until I gave them at least some symptoms to work on.
My guess would be a timeout error. The production system probably has a lot more data than the test system, which may be causing the stored procedure to choke. If your VB code doesn't return useful error information, try to run the procedure from Query Analyzer to see what it returns there.
I am a beginner to sql databases so any help you can offer would be appreciated. What is some code that I could add that would return more useful information? I use Query Analyzer to run reports from. How can I run a procedure from there?
This will execute the stored procedure, and return the results to your Query Analyzer window. It would probably help if you turn on the "Show Query Plan" option in Query Analyzer... Its output will look goofy at first, but as you look at it, it ought to start to make some sense, then give you some insight into where the problem lies (look for fat connecting lines or long execution times).
One thing that would help a bunch is if you could post the source code for the stored procedure. Beware: Posting this code may be a security problem, so think before you do this! You can see that code by using:
EXECUTE sp_helptext knet_user_setLastLogin
Another thing that would help would be the EXACT text of the error message(s) you get. There are often clues buried in there that might help too.
My suspicion is that all you need is an index. The problem is that I don't know enough about your problem to tell you what index you need yet!