Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSSQL AND VB

Posted on 1998-07-28
7
Medium Priority
?
303 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
[X]
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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

715 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