Solved

MSSQL AND VB

Posted on 1998-07-28
7
292 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: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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 210 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

919 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now