Solved

crystal 8.5 designer and RMS database

Posted on 2004-08-24
18
338 Views
Last Modified: 2008-02-01
im looking to select where time = ''
from one of the database tables....
table is named "transaction"
the column is named "time" and has "datetime" type length="8"

in sql enterprise manager i can select all from the table and see the date and time but when i go to use report export in crystal i get no rowset was returned for this table, query, or procedure?

yet i know that theres data in there how can i use that field in my report to query against a date?

i cant even browse the data from crystal either ?



SELECT
    Item."ItemLookupCode",
    Item."Quantity",
    Item."ExtendedDescription",
    TransactionEntry."Cost",
    TransactionEntry."Price",
    TransactionEntry."Quantity",
    TransactionEntry."DBTimeStamp",
    Supplier."SupplierName"
FROM
    (mydb.dbo.Item Item INNER JOIN mydb.dbo.TransactionEntry TransactionEntry ON
        Item."ID" = TransactionEntry."ItemID")
    INNER JOIN mydb.dbo.Supplier Supplier ON
        Item."SupplierID" = Supplier."ID"
ORDER BY
    Supplier."SupplierName" ASC
0
Comment
Question by:aot2002
[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
  • 9
  • 7
  • 2
18 Comments
 
LVL 10

Assisted Solution

by:ebolek
ebolek earned 100 total points
ID: 11884897
I had this problem before. Restart your computer then . Start  from scratch. Whole deal. Create  a new connection to the database. Use OLEDB provider. Then select the table from the connection and make it your datasource. use the wizard. You will be able to see the fields to pick and design your report

Regards
Emre
0
 
LVL 1

Author Comment

by:aot2002
ID: 11884928
ok let me try

so its just a reboot thing huh
0
 
LVL 10

Expert Comment

by:ebolek
ID: 11884951
I created it again and worked fine for me, sometimes it doesnt show the fields from the wizard and you cant do anything with the report, I have been there
0
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

 
LVL 42

Expert Comment

by:frodoman
ID: 11885023
You may be able to just select Database -> Verify Database and if that doesn't work try Database -> Log On/Off Service (twice - first off, then on) and then verify database.

I have had to shutdown and restart as Emre said, but usually 'verify' will handle it fine.

frodoman
0
 
LVL 1

Author Comment

by:aot2002
ID: 11888158
still no luck

the verify database returns that it is up to date
i tried rebooting the machine ????
no luck

anything else to try also i cannot use logon and logoff cause it says a report is in use?

wouldnt it not make a difference since i rebooted ?
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11888664
Sounds like you've got something tied up.  You may have to stop the SQL-Server service and restart it.

Are you editing a report that's on a server that may be in use by remote clients?
0
 
LVL 1

Author Comment

by:aot2002
ID: 11888806
no its on my local machine ?
no one is using this database ?
0
 
LVL 1

Author Comment

by:aot2002
ID: 11888856
didnt work ?

is there a log file for this program
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11891086
If it's a connection type error you may find a sqlnet.log file.

Have you determined which of the three tables you're using is giving you the problem?  If not, try creating a new report with each of them in ture to see which one fails and help isolate the problem.  Look at that table and let us know if there are any uncommon datatypes - anything other than the standard number/varchar type fields.

frodoman
0
 
LVL 1

Author Comment

by:aot2002
ID: 11899457
no log found it seems to be the TransactionEntry table only causing this but i need to use this table to make this report
0
 
LVL 42

Assisted Solution

by:frodoman
frodoman earned 400 total points
ID: 11903354
What are your datatypes for the TransactionEntry table?
0
 
LVL 1

Author Comment

by:aot2002
ID: 11905914
I put all tables in script for you


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TransactionEntry]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TransactionEntry]
GO

CREATE TABLE [dbo].[TransactionEntry] (
      [Commission] [money] NOT NULL ,
      [Cost] [money] NOT NULL ,
      [FullPrice] [money] NOT NULL ,
      [StoreID] [int] NOT NULL ,
      [ID] [int] IDENTITY (1, 1) NOT NULL ,
      [TransactionNumber] [int] NOT NULL ,
      [ItemID] [int] NOT NULL ,
      [Price] [money] NOT NULL ,
      [PriceSource] [smallint] NOT NULL ,
      [Quantity] [float] NOT NULL ,
      [SalesRepID] [int] NOT NULL ,
      [Taxable] [bit] NOT NULL ,
      [DetailID] [int] NOT NULL ,
      [Comment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [DBTimeStamp] [timestamp] NULL ,
      [DiscountReasonCodeID] [int] NOT NULL ,
      [ReturnReasonCodeID] [int] NOT NULL ,
      [TaxChangeReasonCodeID] [int] NOT NULL ,
      [SalesTax] [money] NOT NULL ,
      [QuantityDiscountID] [int] NOT NULL
) ON [PRIMARY]
GO

















if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Transaction]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Transaction]
GO

CREATE TABLE [dbo].[Transaction] (
      [ShipToID] [int] NOT NULL ,
      [StoreID] [int] NOT NULL ,
      [TransactionNumber] [int] IDENTITY (1, 1) NOT NULL ,
      [BatchNumber] [int] NOT NULL ,
      [Time] [datetime] NOT NULL ,
      [CustomerID] [int] NOT NULL ,
      [CashierID] [int] NOT NULL ,
      [Total] [money] NOT NULL ,
      [SalesTax] [money] NOT NULL ,
      [Comment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [ReferenceNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [DBTimeStamp] [timestamp] NULL ,
      [Status] [int] NOT NULL ,
      [ExchangeID] [int] NOT NULL ,
      [ChannelType] [int] NOT NULL ,
      [RecallID] [int] NOT NULL ,
      [RecallType] [int] NOT NULL
) ON [PRIMARY]
GO








if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Item]
GO

CREATE TABLE [dbo].[Item] (
      [BinLocation] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [BuydownPrice] [money] NOT NULL ,
      [BuydownQuantity] [float] NOT NULL ,
      [CommissionAmount] [money] NOT NULL ,
      [CommissionMaximum] [money] NOT NULL ,
      [CommissionMode] [int] NOT NULL ,
      [CommissionPercentProfit] [real] NOT NULL ,
      [CommissionPercentSale] [real] NOT NULL ,
      [Description] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [FoodStampable] [bit] NOT NULL ,
      [HQID] [int] NOT NULL ,
      [ItemNotDiscountable] [bit] NOT NULL ,
      [LastReceived] [datetime] NULL ,
      [LastUpdated] [datetime] NOT NULL ,
      [Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [QuantityCommitted] [float] NOT NULL ,
      [SerialNumberCount] [int] NOT NULL ,
      [TareWeightPercent] [float] NOT NULL ,
      [ID] [int] IDENTITY (1, 1) NOT NULL ,
      [ItemLookupCode] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [DepartmentID] [int] NOT NULL ,
      [CategoryID] [int] NOT NULL ,
      [MessageID] [int] NOT NULL ,
      [Price] [money] NOT NULL ,
      [PriceA] [money] NOT NULL ,
      [PriceB] [money] NOT NULL ,
      [PriceC] [money] NOT NULL ,
      [SalePrice] [money] NOT NULL ,
      [SaleStartDate] [datetime] NULL ,
      [SaleEndDate] [datetime] NULL ,
      [QuantityDiscountID] [int] NOT NULL ,
      [TaxID] [int] NOT NULL ,
      [ItemType] [smallint] NOT NULL ,
      [Cost] [money] NOT NULL ,
      [Quantity] [float] NOT NULL ,
      [ReorderPoint] [float] NOT NULL ,
      [RestockLevel] [float] NOT NULL ,
      [TareWeight] [float] NOT NULL ,
      [SupplierID] [int] NOT NULL ,
      [TagAlongItem] [int] NOT NULL ,
      [TagAlongQuantity] [float] NOT NULL ,
      [ParentItem] [int] NOT NULL ,
      [ParentQuantity] [float] NOT NULL ,
      [BarcodeFormat] [smallint] NOT NULL ,
      [PriceLowerBound] [money] NOT NULL ,
      [PriceUpperBound] [money] NOT NULL ,
      [PictureName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [LastSold] [datetime] NULL ,
      [ExtendedDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [SubDescription1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [SubDescription2] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [SubDescription3] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [UnitOfMeasure] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [SubCategoryID] [int] NOT NULL ,
      [QuantityEntryNotAllowed] [bit] NOT NULL ,
      [PriceMustBeEntered] [bit] NOT NULL ,
      [BlockSalesReason] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [BlockSalesAfterDate] [datetime] NULL ,
      [Weight] [float] NOT NULL ,
      [Taxable] [bit] NOT NULL ,
      [DBTimeStamp] [timestamp] NULL ,
      [BlockSalesBeforeDate] [datetime] NULL ,
      [LastCost] [money] NOT NULL ,
      [ReplacementCost] [money] NOT NULL ,
      [WebItem] [bit] NOT NULL ,
      [BlockSalesType] [int] NOT NULL ,
      [BlockSalesScheduleID] [int] NOT NULL ,
      [SaleType] [int] NOT NULL ,
      [SaleScheduleID] [int] NOT NULL ,
      [Consignment] [bit] NOT NULL ,
      [Inactive] [bit] NOT NULL ,
      [LastCounted] [datetime] NULL ,
      [DoNotOrder] [bit] NOT NULL ,
      [MSRP] [money] NOT NULL ,
      [DateCreated] [datetime] NOT NULL ,
      [Content] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [UsuallyShip] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO







if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Supplier]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Supplier]
GO

CREATE TABLE [dbo].[Supplier] (
      [Country] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [HQID] [int] NOT NULL ,
      [LastUpdated] [datetime] NOT NULL ,
      [State] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [ID] [int] IDENTITY (1, 1) NOT NULL ,
      [SupplierName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Address1] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Address2] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [City] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Zip] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [EmailAddress] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [WebPageAddress] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Code] [nvarchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [DBTimeStamp] [timestamp] NULL ,
      [AccountNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [TaxNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CurrencyID] [int] NOT NULL ,
      [PhoneNumber] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [FaxNumber] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CustomText1] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CustomText2] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CustomText3] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CustomText4] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CustomText5] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CustomNumber1] [float] NOT NULL ,
      [CustomNumber2] [float] NOT NULL ,
      [CustomNumber3] [float] NOT NULL ,
      [CustomNumber4] [float] NOT NULL ,
      [CustomNumber5] [float] NOT NULL ,
      [CustomDate1] [datetime] NULL ,
      [CustomDate2] [datetime] NULL ,
      [CustomDate3] [datetime] NULL ,
      [CustomDate4] [datetime] NULL ,
      [CustomDate5] [datetime] NULL ,
      [Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Terms] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

0
 
LVL 42

Assisted Solution

by:frodoman
frodoman earned 400 total points
ID: 11906035
I don't see anything wrong.  How did you setup your connection from Crystal to the database?  Are you certain you used authentication credentials with authority to read each of these tables?
0
 
LVL 1

Author Comment

by:aot2002
ID: 11906052
OLEDB connection

yes i used sa then blank pass

should i try a different way to connect ?
0
 
LVL 42

Accepted Solution

by:
frodoman earned 400 total points
ID: 11906099
Nope - I just haven't seen anything that would be causing the symptoms so I was wondering if it was a permissions issue.

I'll think about it but I've got no idea why it isn't working...
0
 
LVL 1

Author Comment

by:aot2002
ID: 11909587
i'll try changing the pass and username
0
 
LVL 42

Assisted Solution

by:frodoman
frodoman earned 400 total points
ID: 11915609
Yea, maybe use the "sa" login if you can - not a good permanent solution but will at least indicate if it is a permissions problem if it works.

frodoman
0
 
LVL 1

Author Comment

by:aot2002
ID: 11916699
ahhh

public tables were here for a reason !

i had to use the public tables not the private ones
ahh for the love of god
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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