Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MSSQL AND VB

Posted on 1998-07-28
7
298 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL STANDARD CORE 7 31
LTrim & Double Space Correction 5 40
Upgrading my SSIS package in VS 2012 6 59
Transact SQL - Frequency of Length of Distinct Values 3 24
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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

861 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