Solved

SQL Code SP runs produces results but Crystal Reports does not allow me to open it and create a report.

Posted on 2013-10-30
5
356 Views
Last Modified: 2013-11-13
I get the following error when I open and create a new Crystal Report (see image)

The SP below is seen in the window when creating a new report in Crystal Reports. I am not sure why I get the error when the SP runs in SSMS just fine but wont even let me start the creation of a new report in Crystal Reports without the error.

USE [WebSnakDS_SNYB_Test]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_SnyderAccountP&LSummary_RB]    Script Date: 10/22/2013 11:47:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Declare 
	ALTER PROCEDURE [dbo].[Rpt_SnyderAccount_PL_Summary_RB] 
      (
      @DB_Include int  -- 0 = WebSNAK Only, 1 = RAMS Only, 2 = WebSNAK and RAMS 
       )AS

-----------------------------------------------------------------------------------
-- Creating TEMP Table Bringing Macola RAMS and WebSNAK together
-----------------------------------------------------------------------------------

CREATE TABLE #TEMPTABLE
		
(
	[Data_Source] VARCHAR(12),--  what database is the data coming from  macola or rams?
	[InvoiceDate] DATETIME,
	[SNAK_SaleTypesID] CHAR (10),
	[CustPromoAmt] MONEY,
	[MFGCostAmt] MONEY, 
	[GrossAmt] MONEY,
	[CustName] VARCHAR(60),
	[SNAK_CustomerID] VARCHAR(12),
	[ChainName] VARCHAR(42),
	[SNAK_ChainID] VARCHAR(100)
	
	
)

 
-----------------------------------------------------------------------------------
-- WEBSNAK DATA
-----------------------------------------------------------------------------------

if (@DB_Include = 0 or @DB_Include = 2) -- 0 = WebSNAK Data only; 2 = Both WebSNAK and RAMS data
	BEGIN
	
INSERT  INTO #TEMPTABLE

SELECT
	'WEBSNAK',
	Invoices.InvoiceDate AS InvoiceDate,
	InvoiceDetails.SNAK_SaleTypesID AS SNAK_SaleTypesID, 
	InvoiceDetails.CustPromoAmt AS CustPromoAmt,
	InvoiceDetails.MFGCostAmt AS MFGCostAmt,
	InvoiceDetails.GrossAmt AS GrossAmt,
	Customers.CustName AS CustName, 
	Customers.SNAK_CustomerID AS SNAK_CustomerID,
	Chains.ChainName AS ChainName, 
	Chains.SNAK_ChainID AS SNAK_ChainID
FROM
    { oj ((dbo.Invoices Invoices 
     INNER JOIN dbo.Customers Customers 
			ON	Invoices.Customers_Syskey = Customers.Customers_Syskey)
     INNER JOIN dbo.InvoiceDetails InvoiceDetails 
			ON	Invoices.Invoices_Syskey = InvoiceDetails.Invoices_Syskey)
     INNER JOIN dbo.Chains Chains 
			ON	Customers.Chains_Syskey = Chains.Chains_Syskey}
WHERE
	--((Invoices.InvoiceDate >= {ts '2012-01-10 00:00:00.00'} AND
    --Invoices.InvoiceDate < {ts '2012-09-11 00:00:00.00'}) OR
    (Invoices.InvoiceDate >= {ts '2013-01-01 00:00:00.00'} AND
    Invoices.InvoiceDate < {ts '2013-01-31 00:00:00.00'})
    --SNAK_SaleTypesID IN ('S','R','D','F')
GROUP By 
	InvoiceDate,
	Chains.SNAK_ChainID,
	InvoiceDetails.SNAK_SaleTypesID,
	InvoiceDetails.CustPromoAmt,
	InvoiceDetails.MfgCostAmt,
	InvoiceDetails.GrossAmt,
	Customers.CustName,
	Customers.SNAK_CustomerID,
	Chains.ChainName

END
-----------------------------------------------------------------------------------
--RAMS DATA--
-----------------------------------------------------------------------------------

if (@DB_Include = 1 or @DB_Include = 2) -- 1 = RAMS Data only; 2 = Both WebSNAK and RAMS data
	BEGIN
	
INSERT  INTO #TEMPTABLE

SELECT
    	'RAMS',
    	ROSS_SALESHDR.OH_DELIVERY_DATE AS InvoiceDate,
    	ROSS_SALESDTL.OD_RECORD_TYPE AS SNAK_SalesTypesID,
 		CustPromoAmt = SUM(ROSS_SALESDTL.OD_TOTAL_DELIV_QUANTITY * ROSS_SALESDTL.OD_NET_PROMO_PRICE),
 		MFGCostAmt = SUM(ROSS_SALESDTL.OD_TOTAL_DELIV_QUANTITY * ROSS_SALESDTL.OD_PRODUCT_COST_PRICE),
 		GrossAmt = SUM(ROSS_SALESDTL.OD_TOTAL_DELIV_QUANTITY * OD_BASE_PRICE),
 		ROSS_CUSTOMER.CM_FULLNAME AS CustName,
    	RAMS_CrossRef.RAMS_CHAIN_NO AS SNAK_CustomerID,
    	RAMS_CrossRef.SNAK_CHAIN_NO AS ChainName,
    	RAMS_CrossRef.CHAIN_IDENT AS SNAK_ChainID
    	
    	
FROM
     CHLROS05.RAMSDB.dbo.ROSS_SALESHDR ROSS_SALESHDR 
	INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER ROSS_CUSTOMER 
		ON ROSS_SALESHDR.OH_CUSTOMER_NUMBER = ROSS_CUSTOMER.CM_CUSTKEY
    INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_SALESDTL ROSS_SALESDTL 
		ON ROSS_SALESHDR.OH_TICKET_NUMBER = ROSS_SALESDTL.OD_TICKET_NUMBER
		--AND ROSS_SALESDTL.OD_RECORD_TYPE IN ('S','R','M')
    INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS ROSS_PRODUCTS 
		ON ROSS_SALESDTL.OD_PRODUCT = ROSS_PRODUCTS.PRD_PRODUCT
	LEFT OUTER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE1
        ON ROSS_CUSTOMER.CM_SELLZONE = ROSS_IDCODE1.ID_FIELD
    LEFT OUTER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE2
        ON ROSS_PRODUCTS.PRD_A_GLTYPE = ROSS_IDCODE2.ID_FIELD 
	INNER JOIN WebSnakDS_SNYB_test.dbo.RAMS_CrossRef RAMS_CrossRef
		ON ROSS_CUSTOMER.CM_CHAINID = RAMS_CrossRef.RAMS_CHAIN_NO
		
WHERE
    ROSS_SALESHDR.OH_DELIVERY_DATE >= {ts '2013-01-01 00:00:00.00'} AND
    ROSS_SALESHDR.OH_DELIVERY_DATE < {ts '2013-01-31 00:00:00.00'} --AND
    --ROSS_SALESDTL.OD_PRODUCT >= '0*' AND
    --ROSS_SALESDTL.OD_PRODUCT <= '9999*' AND
    --ROSS_SALESDTL.OD_RECORD_TYPE = 'O' --AND
    --(ROSS_CUSTOMER.CM_DELIVZONE = '400002' OR
    --ROSS_CUSTOMER.CM_DELIVZONE = '400001')
    
GROUP BY
        ROSS_SALESHDR.OH_DELIVERY_DATE,
        ROSS_SALESDTL.OD_RECORD_TYPE,
        ROSS_CUSTOMER.CM_FULLNAME,
        RAMS_CrossRef.RAMS_CHAIN_NO,
        RAMS_CrossRef.SNAK_CHAIN_NO,
        RAMS_CrossRef.CHAIN_IDENT

END
    
    SELECT * FROM #TEMPTABLE

Open in new window

SPError.png
0
Comment
Question by:ruavol2
5 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 100 total points
ID: 39611945
Can you build a report against table dbo.Invoices?

mlmcc
0
 
LVL 18

Accepted Solution

by:
vasto earned 200 total points
ID: 39611995
Use SQL Profiler to see what exactly was sent to the database. Also check if the report is talking to the right database

Try use OLEDB instead of ODBC- at least the error will show some details
0
 
LVL 9

Assisted Solution

by:Valliappan AN
Valliappan AN earned 100 total points
ID: 39612092
Try removing the 'dbo.' part in your queries, as the user the report logged in the DB, might be different with different rights.

Also, check if the rights are there for the Crystal Reports DB connection user.
0
 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 100 total points
ID: 39612122
Are you using the same user when you connect to SSMS as you are with the Crystal? Maybe the crystal user does not have select access on dbo.invoices
0
 

Author Closing Comment

by:ruavol2
ID: 39646432
It was the connection switching to the database. Wish they could keep that setting from TAB to TAB otherwise I have to keep opening multiple instances of SSMS. Thanks for the four most likely culprits.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Backup 24 72
SQL Server issue connecting to named instance 6 45
Stored procedure 23 25
SQL Query 2 0
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 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

12 Experts available now in Live!

Get 1:1 Help Now