Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

SQL Server Syntax not fast enough for Crystal Reports .NET


Good Day:

I am using SQL Server 2000 for a VB.NET application.

The following script works great when I manually execute the first Delete Script, wait a minute and then execute the second Insert Script.  If I do not wait then it reports my old data instead of the new data.  In other words, SQL Server does not process the data fast enough for the Crystal Reports document.  Is there a way to fix this because I need to automate the process by using a stored procedure?  

---Delete Script
BEGIN TRAN
DELETE FROM PackSlip
WHERE SONumber=@SONumber
COMMIT TRAN

---Insert Script
BEGIN TRAN
INSERT INTO PackSlip
Select * from vwPackSlipCreate
WHERE SONumber=@SONumber
COMMIT TRAN

Sample Data:
PackSlip Table  (Old Data)
Order #:      1234
QtyOrdered:      10
QtyShipped:        5
QtyBO:        5      

PackSlip Table  (New Data)
Order #:      1234
QtyOrdered:      5
QtyShipped:      5
QtyBO:      0        

Any suggestions?
Thanks,
Denise

0
DeniseGoodheart
Asked:
DeniseGoodheart
2 Solutions
 
cyberkiwiCommented:
For the delete, make sure there is a good index on PackSlip.SONumber.
Other factors that may affect it are triggers, index updates and indexed views.

For the insert, it looks like you are using a view; maybe the view is not optimized?
Pick an SONumber, and run this in SSMS and check the execution plan (Ctrl-L).

Select * from vwPackSlipCreate WHERE SONumber= 123

If you need help, you can do this and we can check the view performance.
Open a new Query Window, run the tsql below

set showplan_text on;
Select * from vwPackSlipCreate WHERE SONumber= 123

Close the new query window.
0
 
HainKurtSr. System AnalystCommented:
after deleting make sure you re select / re run your select query...
there is no way to get the old data unless sql has some select bugs ;)
0
 
mlmccCommented:
Does the report have data saved?

How are you running the report?

mlmcc
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
mattibuttCommented:
2000 is 10 years old vb itsel is a slow language
0
 
DeniseGoodheartAuthor Commented:
mattibutt:

Yes I undestand 2000 is 10 years old but it is easier to use than 2005 and 2008 because the GUI changed.  I will upgrade to SQL 2008 when we are ready to go into production.  VB.NET is a slow  language compared to what other lanuages?
0
 
DeniseGoodheartAuthor Commented:
mlmcc:

I'm using Crystal Reports.NET
0
 
DeniseGoodheartAuthor Commented:
Hi All,

I did a quick fix to fix this  problem, but now I am getting the same problem in another stored procedure.  It might  be that I need to extend my connection time on VB.NET.  I will try this and if it does not work, then I will post a new thread.

Thanks
0
 
DeniseGoodheartAuthor Commented:
It did not fix the problem, but they did give me valuable information.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now