Report SQL Not Updating

[Modica82]

Hi All,

I have a crystal report that i am messing around with. It is an existing crystal report and not one that i created myself, so it already had a datasource setup etc. I have updated the datasource to represent the new connection, but i am still having problems with the SQL that gets past to the SQL Server (checked this using the SQL profiler to ensure that the SQL was the same as that in crystal reports). The SQL being sent down the wire has the wrong database, and is pointing to the wrong database for its data. This is causing me problems as i cant test against live data, and i cant seem to find a way to update the database in the SQL to point to the new database, does anyone know how i can do this?


Rob

---------------------------------------

http://www.thatssolastseason.co.uk

 

[Modica82]

Soz i am using Crystal Reports 10, if that is any better

Rob

---------------------------------------

http://www.thatssolastseason.co.uk

 

[synapsevampire]

To repoint a database use Database->Set Location and point to the new datasource.

This may vary Depending upon how the SQL is created and your Crystal version.

-k

 

[Modica82]

Hi,

I have done that, but it doesnt seem to update my database in the SQL view which i dont understand?

Rob

---------------------------------------

http://www.thatssolastseason.co.uk

 

[synapsevampire]

It may be that they used a Command Object to manually place the SQL, you need to post what is in the Database->Show SQL, and whether you are using the Crystal GUI to link to the data source (is it a view), or you are using a Command object, or other.

-k

 

[Modica82]

Hi,

I changed the connection from an ODBC link to a direct ADO link. The report is linked to a view and two tables. How would i check that the report is based on a command object?? Sorry about this, i know how to create reports, but sometimes the data connection part of this program confuses the hell out of me.

SELECT "v_Cerulean_SL_Trans"."ST_HEADER_REF", "v_Cerulean_SL_Trans"."ST_DATE", "v_Cerulean_SL_Trans"."ST_COPYCUST", "v_Cerulean_SL_Trans"."ST_ORDER_NUMBER", "v_Cerulean_SL_Trans"."DET_QUANTITY", "STK_STOCK_2"."STK_SELL_NAME1", "STK_STOCK_2"."STK_SELL_NAME2", "STK_STOCK_2"."STK_SELL_NAME3", "STK_STOCK_2"."STK_SELL_NAME4", "STK_STOCK_2"."STK_SELL_NAME5", "STK_STOCK_2"."STK_SELL_NAME6", "STK_STOCK_2"."STK_SELL_NAME7", "STK_STOCK_2"."STK_SELL_NAME8", "STK_STOCK_2"."STK_SELL_NAME9", "STK_STOCK_2"."STK_SELL_NAME10", "v_Cerulean_SL_Trans"."DET_STOCK_CODE", "v_Cerulean_SL_Trans"."ST_DESCRIPTION", "v_Cerulean_SL_Trans"."DET_UNIT_PRICE", "v_Cerulean_SL_Trans"."DET_NETT", "v_Cerulean_SL_Trans"."DET_VAT", "v_Cerulean_SL_Trans"."OD_TABLECODE", "v_Cerulean_SL_Trans"."STKNAME", "v_Cerulean_SL_Trans"."DET_L_DISCOUNT", "v_Cerulean_SL_Trans"."CUNAME", "v_Cerulean_SL_Trans"."AD_ADDRESS", "v_Cerulean_SL_Trans"."AD_ADDRESS_USER1", "v_Cerulean_SL_Trans"."AD_ADDRESS_USER2", "v_Cerulean_SL_Trans"."AD_POSTCODE", "v_Cerulean_SL_Trans"."CUADDRESS", "v_Cerulean_SL_Trans"."CU_ADDRESS_USER1", "v_Cerulean_SL_Trans"."CUPOSTCODE", "v_Cerulean_SL_Trans"."CU_ADDRESS_USER2", "ORD_HEADER"."OH_ORDER_REF", "ORD_HEADER"."OH_DATE", "v_Cerulean_SL_Trans"."ST_TRANTYPE", "v_Cerulean_SL_Trans"."ST_YEAR"
 FROM   ("HARBRINE_TEST"."dbo"."v_Cerulean_SL_Trans" "v_Cerulean_SL_Trans" INNER JOIN "HARBRINE_TEST"."dbo"."STK_STOCK_2" "STK_STOCK_2" ON "v_Cerulean_SL_Trans"."DET_STOCK_CODE"="STK_STOCK_2"."STKCODE2") INNER JOIN "HARBRINE_TEST"."dbo"."ORD_HEADER" "ORD_HEADER" ON "v_Cerulean_SL_Trans"."ST_ORDER_NUMBER"="ORD_HEADER"."OH_ORDER_NUMBER"
 WHERE  "v_Cerulean_SL_Trans"."ST_HEADER_REF"='80940'
 ORDER BY "v_Cerulean_SL_Trans"."ST_HEADER_REF"

---------------------------------------

http://www.thatssolastseason.co.uk

 

[synapsevampire]

Why did you change to ADO?

It would appear that you're using the conventional means of allowing Crystal to connect directly to the tables, so I would guess that you are not correctly pointing to the new datasource.

I see that it is connecting to:

HARBRINE_TEST"."dbo"."v_Cerulean_SL_Trans

Which is the test database, once you repoint this should change.

-k

 

[Modica82]

Hi synapsevampire,

I changed to ADO becuase where this report is being moved to is on a server that i cant setup DSNs so i have to use a direct connection. i have already re-pointed the database several times, but it doesnt seem to update the HARBRINE_TEST part. The database location for all the tables connected to the report are HARBRINE and not HARBRINE_TEST so this is why i am getting confused.

Any more help would be greatly appreciated.

Rob

---------------------------------------

http://www.thatssolastseason.co.uk

 

[Turkbear]

Hi,
Are you SURE it is not using the new database table?
The field names in the report will not change when the source is changed..CR creates an alias so that the formulas, etc. that use the existing names need not be changed..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

I can't confirm what Turk is stating righ now, I had thought that the SQL showed would be accurate, but Crystal does mask the table names within other areas after a change.

So the set location should be that you point ot each table and then select the new table and update, not just the server level as the same database might exist in each.

-k

 

[Modica82]

Hi there Turkbear,

i thought that aswell, as i never really paid to much attention to the SQL before, but when i analyse the query that is sent to the SQL Server it has the test database names as apposed to the tables so i am getting dummy data back, and in cases no data back as the invoice numbers i use are not in the TEST database.

Rob

---------------------------------------

http://www.thatssolastseason.co.uk

 

[Turkbear]

Hi,
OK, then..Did you try Synapse's method of being sure ( while on the set Datasource screen) to highlight each existing table ( the ones that use the TEST database) and selecting from the lower panel the Tables that should be used and clicking on Update?







To Paraphrase:"The Help you get is proportional to the Help you give.."