• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

crystal 8.5 designer and RMS database

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
aot2002
Asked:
aot2002
  • 9
  • 7
  • 2
5 Solutions
 
ebolekCommented:
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
 
aot2002Author Commented:
ok let me try

so its just a reboot thing huh
0
 
ebolekCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
frodomanCommented:
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
 
aot2002Author Commented:
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
 
frodomanCommented:
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
 
aot2002Author Commented:
no its on my local machine ?
no one is using this database ?
0
 
aot2002Author Commented:
didnt work ?

is there a log file for this program
0
 
frodomanCommented:
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
 
aot2002Author Commented:
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
 
frodomanCommented:
What are your datatypes for the TransactionEntry table?
0
 
aot2002Author Commented:
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
 
frodomanCommented:
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
 
aot2002Author Commented:
OLEDB connection

yes i used sa then blank pass

should i try a different way to connect ?
0
 
frodomanCommented:
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
 
aot2002Author Commented:
i'll try changing the pass and username
0
 
frodomanCommented:
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
 
aot2002Author Commented:
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
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

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 9
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now