Solved

crystal 8.5 designer and RMS database

Posted on 2004-08-24
18
331 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
  • 9
  • 7
  • 2
18 Comments
 
LVL 10

Assisted Solution

by:ebolek
ebolek earned 100 total points
Comment Utility
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
Comment Utility
ok let me try

so its just a reboot thing huh
0
 
LVL 10

Expert Comment

by:ebolek
Comment Utility
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
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
no its on my local machine ?
no one is using this database ?
0
 
LVL 1

Author Comment

by:aot2002
Comment Utility
didnt work ?

is there a log file for this program
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Author Comment

by:aot2002
Comment Utility
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
Comment Utility
What are your datatypes for the TransactionEntry table?
0
 
LVL 1

Author Comment

by:aot2002
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
i'll try changing the pass and username
0
 
LVL 42

Assisted Solution

by:frodoman
frodoman earned 400 total points
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now