Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MSSQL AND VB

Posted on 1998-07-28
7
Medium Priority
?
304 Views
Last Modified: 2010-03-19
We are running VB 4.0 Enterprise Edition with SQL server 6.5.  I am calling a stored procedure from within the VB code.  Everything works, runs, and updates fine for a given amount of time(possibly a day or weeks).  Then, for some reason, our people are running the VB program and no updates happen to the data during the execution of the program.  All I do is simply go into the stored procedure  in SQL Server and save it with the save object button.  Things are then back to normal for awhile till I get my next phone call from a user.  My question is why must I periodically go into SQL Server and save  these stored objects periodically.  My co-worker thought that additional memory was needed, it was added, but the problem still persists.  As a side question, if an answer can not be found to this rebuild problem, is there a way to get the server to rebuild the stored procedures automatically through some type of batch job.(As a short term solution)
0
Comment
Question by:jwcoop
7 Comments
 

Author Comment

by:jwcoop
ID: 1089272
Adjusted points to 200
0
 
LVL 7

Expert Comment

by:Victor Spiridonov
ID: 1089273
Try adding With_recompile option to create statement  -procedure will be recompiled every time it is called.
0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1089274
You might also perform an update statistics for your modified table after some decent interval of changes.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Expert Comment

by:zimmy
ID: 1089275
Spiridonov's suggestion is a good way to find out if the save/recompile is actually fixing the problem.

I was wondering if you have all your users log off when the error occurs. If you are, that could be masking some other causes of the problem.

Is your VB program checking for errors? Is your stored procedure returning error codes?

Is your transaction log filling up?

What is the lock status of the table(s) in question when the error occurs?

Does the stored procedure do a BEGIN TRANS/COMMITT TRANS?

Good luck. Sometimes these can be real bears to figure out.
0
 

Author Comment

by:jwcoop
ID: 1089276
I tried the statement WITH RECOMPILE.  The problem did not go away.  I do not have to make any of the users log off, I just simply click the SAVE OBJECT button of the stored procedure, then the problem goes away for awhile.  The user gets no error message. Actually what is going on here is the user is seeing the data in a grid in VB.  They type in a change in the grid, the stored procedure then updates the database, and the user should then see the changed data when the screen is refreshed.  The problem is that the user types in the changed data, when the screen is refreshed, the changed data is not there, and the data is like is was before the user changed it.  Also there is no error message of any kind that happens.  I am trapping for errors in the VB code.
0
 

Expert Comment

by:zimmy
ID: 1089277
Just because the results don't get back to the VB app doesn't mean that the db isn't getting updated. Time to narrow the problem down a bit.

Have you looked at the data in the database with an ISQL query to see if it's updated, or have you been relying on what shows up in the grid?

If an ISQL query shows that the data isn't being updated, then your next step might be to turn on an ODBC trace.

Or, you might try running the stored procedure from an ISQL window to see what happens.

If the data IS being updated in the db, then you need to look at the refresh side of the transaction.

Good luck. Have fun!
0
 
LVL 3

Accepted Solution

by:
gmoriak earned 420 total points
ID: 1089278
Here's the patch.  Create a text file with the SQL to create your stored proc

drop procedure sp_procname
go
create procedure sp_procname
as
select....
update...
go

Save this text file and then add a process to the SQL Scheduler to run isql.exe  Check the help via isql /? and you can specify how to have it run the script the drop/create your stored proc periodically.

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question