Solved

MSSQL AND VB

Posted on 1998-07-28
7
301 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 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