A bit of Crystal Reports 8.5 help

Hi all, first post!

I'm working for a company that runs a business management system which uses an old version of Crystal Reports (version 8.5!) for reporting on pretty much anything the system can output.

I don't know how much it differs from the newer versions, but i'm new to the system (two days experience and counting) and I'm after a couple of pointers.

1) I am trying to output stock levels for products which are both in and out of stock. Now, due to some sillyness when the BMS was designed, if a product is out of stock, it has doesn't have a row in the bins_lots table (where stock levels are kept), rather than 0.
Therefore, if I do a report which outputs the stock levels of a product (part, items that are out of stock are missing from the report, rather than having a 0 next to their name.
Is it possible to tell Crystal that if a product exists but doesn't have an entry in bins_lots (where stock amounts are kept) then it should be 0?
I'd find this easy if it were a normal If Statement, but Crystal has to be difficult with its wierd way of doing conditionals.

2) Secondly, again due to the BMS design, if a product is on order, the amount in the order is logged in multiple purchase orders. Producing a query will give me duplicate entries for products. I know I can 'hide duplicates', but it results in a messy report.
Can I make Crystal add all of these numbers together (i.e. where product = 'AD-03', add quantity of due products together) and output the final result without using the subtotal/group functions.

All help appreciated :)1) Left join to the bins_lots table. When the amount column is null then there's no stock.
e.g. a formula to return the stock level would be (assuming your column is called amount)
if isnull({bins_lots.amount}) then 0 else {bins_lots.amount}

2) Well, I don't really know how you're going to add numbers without grouping / total functions. Why is grouping an issue? Why can't you group on the product, hide the detail, and print in the product group footer to get 1 record per product? Nothing messy there.

800a0cb3 Error in DTS Package

I've been working on a DTS Package that runs every 10 minutes to check for new records to a table and sends out notification emails accordingly. After the email is sent, the new record is updated with a flag so it will not be pulled and emailed again.

This package worked fine for a week (and is currently working - exact same code - in another SQL Server).

As of last night, this process started throwing the following error:

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= ADODB.Recordset
Error Description: Current Recordset does not support updating. This may be a limitation of the provider,
or of the selected locktype.

Error on Line 110
(ADODB.Recordset (800a0cb3): Current Recordset does not support updating. This may be a limitation
of the provider, or of the selected locktype.)
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

Thinking I may have screwed something up, I moved the functioning code from the other SQL Server back to the non-functioning SQL Server, and the problem persists. I stopped and restarted the SQL Server (but have not yet rebooted the server it's on), also with no results.

The package is a single ActiveX object written in VB to open up a recordset of unprocessed records (KeySet and Lock Optomistic), send out notification emails, flag the files, then close. The error occurs at the updating of the flag.

What is even more strange, is that this is happening on our development SQL server. Our production SQL server seems unaffected. I originally developed and tested this code on the dev box, and when I was satisfied, I moved it to production for testing. Then last night the dev server stopped functioning, but the production server shows no sign of problems.

I was curious if anyone else has had an experience with a DTS package throwing a similar error.

Any ideas?I took the ActiveX code and copied it to VB and ran it.. when it's pointing to the server in question, the error occurs... when I change the server name to point to the production server, the same code functions properly... amazing....|||I temporarily managed to resolve the problem by rebuilding the packages from scratch. I do not know if this fixed it, or if it was just coincidence that I did this at the same time something else happened, but it began working again.

Unfortunately, as of this afternoon, the production and staging environments redeveloped the problem and my "solution" did not fix it.

Thinking my transaction log may be filled I checked the settings. The DB and the Transaction log are both set to unlimited growth and to grow automatically.

At this point I'm baffled. I have a DTS package on 3 servers... all three running successfully for days on end... spontaneously two of them stop working giving the error in the original post... very very troubling...|||Would you believe I came across something that may have fixed it right after I posted? It wouldn't be the first time...

I found a reference to the same error code, but different error message, in regard to an Oracle error. The solution was to make the recordset a client-side recordset. I had originally thought of this, but the DTS package is in SQL Server, so my thinking was, when this runs, there is no client, so it has to run as a server-side recordset. When I changed ADO to use a client-side recordset (which is still the SQL Server), it started working again. I changed it back, it failed, put it back in again, it worked.

Very strange... why would you need to declare a recordset to be client-side, even though it's running in the SQL Server itself? Since DTS runs under a separate agent, does that count as a "client"?

7.0 vs 2000?

I hope I can explain this correctly...

I have a developer that runs a batch file to delete the current parts from the database. The batch file calls a prorietary Java class file (Trilogy) to delete the parts and it simply returns a "null". I don't know whats making it return null.

Note: We have the same DB on a 7.0 Instance and a 2000 Instance. The batch file works against the 7.0 Instance and not against the 2000 Instance.

I'm wondering if possibly it's a Server setting? I'm not sure where to begin with this one.The first thing I'd check would be the logs. Any output (logfile, console, or other) from the Java app would be first, then the SQL Server log file, the NT logs, and anything else that seems appropriate.

The next thing I'd do would be to sic the SQL Profiler on the little beastie, and watch what it is doing. The exact steps will vary a lot depending on your configuration, but the process isn't too complex.

If none of those give you any clues, then you have a real puzzle! Those can still be solved, but they are a lot harder.

-PatP

6.5 upgrade

I am having trouble upgrading from SQL 6.4 to SQL 2000
using the wizard.
The wizard runs nicely until I get to 'Export and Import
via Named pipes', then I get the following message:
Export.exe on the remote machine was hung...
Export Exit Code: 259 - export.exe -CodePage 1252 -
DeviceType Pipe -AllTables yes -MasterPath
C:\MSSQL\DATA\MASTER.DAT -DatabaseName Comp2 -DevicePath
\\.\pipe\~cnvpipe0
Import Exit Code: -1 - ~cnvpipe0
Msg 4854, Level 21, State 1, Server RLINKDEST, Procedure ,
Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]****
Time:10-14-2003 10:27:21.125
Error return from function CreateFile
in file CnvPipe.cpp
Line=143
rc=5
Msg=Access is denied.
I have the same SA password on all accounts, and I have
set all ODBC drivers to connect in the same way, via named
pipes.
Please help!
Thank you
SusanneIf this is a 2 machine upgrade, then the SQL Server startup account on the
SQL 6.5 box may not have permission to the SQL Server 2000 machine.
Rand
This posting is provided "as is" with no warranties and confers no rights.