Database Connector Error Message CR XI 1

[baycolor]

Hi,

I'm using CR XI developer on my PC (latest updates have been applied). I'm connecting to a Teradata database via ODBC.

I've created an Add Command SQL statement. The statement is below and when executed it returns the message: Database Connector Error: 'Cannot obtain error message from server.'
Kicker though is the SQL statement does exeute and inserts 3 rows.

I know it should insert 3 rows because I can use another database query tool I have and run the exact same SQL and it inserts 3 rows. This other query tool connects to the exact same database with the exact same ODBC connection. No error encountered in this query tool though. And yes when I ran the SQL in Add Command I made sure the target table had been emptied out - no duplicate errors.

I know that CR is supposed to pass an Add Command statement directly to the database without changing it. So I'm not sure why its getting an error back. It's almost like its choking on the success or fail return code.

Any ideas why CR is barking or what it isn't understanding (as far as I know there isn't any error message coming back from the server)?

Thanks in advance.

INS INTO MS130628.HOLD
SEL
	program_no
	,STR1.str1_web_id_cnt
	,touch_desc
	,touch_date
	,SUBSTRING(comment_text,1,7) AS web_id 
	,SUBSTRING(comment_text FROM POSITION('|' IN comment_text) + 1) AS comment_text2
	,POSITION(';' IN comment_text) + 1 AS start_pos_string_1
	,SUBSTRING(comment_text FROM start_pos_string_1) AS rest_of_comment_text_1
	,POSITION(';' IN rest_of_comment_text_1) + 1 + start_pos_string_1 - 1 AS start_pos_string_2
	,SUBSTRING(comment_text FROM start_pos_string_2) AS rest_of_comment_text_2
	,POSITION(';' IN rest_of_comment_text_2) + 1 + start_pos_string_2 - 1 AS start_pos_string_3
	,SUBSTRING(comment_text FROM start_pos_string_3) AS rest_of_comment_text_3
	,POSITION(';' IN rest_of_comment_text_3) + 1 + start_pos_string_3 - 1 AS start_pos_string_4
	,SUBSTRING(comment_text FROM start_pos_string_4) AS rest_of_comment_text_4
	,POSITION(';' IN rest_of_comment_text_4) + 1 + start_pos_string_4 - 1 AS start_pos_string_5
	,start_pos_string_2 - start_pos_string_1 - 1 AS len_string_1
	,start_pos_string_3 - start_pos_string_2 - 1 AS len_string_2
	,start_pos_string_4 - start_pos_string_3 - 1 AS len_string_3
	,start_pos_string_5 - start_pos_string_4 - 1 AS len_string_4
	,LENGTH(comment_text) AS string_length
	,(string_length - start_pos_string_5) + 1 AS len_string_5
	,CASE WHEN start_pos_string_1 = 1 THEN 'N' ELSE 'Y' 
	 END AS string_1_flag
	,CASE WHEN string_1_flag = 'N' THEN 'N' 
			WHEN string_1_flag = 'Y' AND start_pos_string_2 = start_pos_string_1 THEN 'N'
			WHEN string_1_flag = 'Y' AND start_pos_string_2 <> start_pos_string_1 THEN 'Y'
	 END AS string_2_flag
	,CASE WHEN string_1_flag = 'N' THEN 'N' 
			WHEN string_1_flag = 'Y' AND start_pos_string_3 = start_pos_string_2 THEN 'N'
			WHEN string_1_flag = 'Y' AND start_pos_string_3 <> start_pos_string_2 THEN 'Y'
	 END AS string_3_flag
	,CASE WHEN string_1_flag = 'N' THEN 'N' 
			WHEN string_1_flag = 'Y' AND start_pos_string_4 = start_pos_string_3 THEN 'N'
			WHEN string_1_flag = 'Y' AND start_pos_string_4 <> start_pos_string_3 THEN 'Y'
	 END AS string_4_flag
	,CASE WHEN string_1_flag = 'N' THEN 'N' 
			WHEN string_1_flag = 'Y' AND start_pos_string_5 = start_pos_string_4 THEN 'N'
			WHEN string_1_flag = 'Y' AND start_pos_string_5 <> start_pos_string_4 THEN 'Y'
		 END AS string_5_flag
	,CASE WHEN string_1_flag = 'Y' and string_2_flag = 'N' 
			THEN SUBSTRING(comment_text FROM start_pos_string_1) 
			WHEN string_1_flag = 'Y' and string_2_flag = 'Y' 
			THEN SUBSTRING(comment_text FROM start_pos_string_1 FOR len_string_1) 
			ELSE 'N/A' 
	END AS string_1
	,CASE WHEN string_2_flag = 'Y' and string_3_flag = 'N' 
			THEN SUBSTRING(comment_text FROM start_pos_string_2) 
			WHEN string_2_flag = 'Y' and string_3_flag = 'Y' 
			THEN SUBSTRING(comment_text FROM start_pos_string_2 FOR len_string_2) 
			ELSE 'N/A' 
	END AS string_2
	,CASE WHEN string_3_flag = 'Y' and string_4_flag = 'N' 
			THEN SUBSTRING(comment_text FROM start_pos_string_3) 
			WHEN string_3_flag = 'Y' and string_4_flag = 'Y' 
			THEN SUBSTRING(comment_text FROM start_pos_string_3 FOR len_string_3) 
			ELSE 'N/A' 
	END AS string_3
	,CASE WHEN string_4_flag = 'Y' and string_5_flag = 'N' 
			THEN SUBSTRING(comment_text FROM start_pos_string_4) 
			WHEN string_4_flag = 'Y' and string_5_flag = 'Y' 
			THEN SUBSTRING(comment_text FROM start_pos_string_4 FOR len_string_4) 
			ELSE 'N/A' 
	END AS string_4
	,CASE WHEN string_5_flag = 'Y' 
			THEN SUBSTRING(comment_text FROM start_pos_string_5 FOR len_string_5) 
			ELSE 'N/A' 
	END AS string_5
FROM
	RMDB.rmal_v_programtouch PGMTOUCH
	JOIN 
	(
		SEL 
			SUBSTRING(comment_text FROM (POSITION('(' IN comment_text) + 1) FOR 7)
			,COUNT(*) 
		FROM 
			RMDB.rmal_v_actualtouch ACTTOUCH
			JOIN RMDB.rmal_v_actualtouch_touchresult TOUCHRES
			ON ACTTOUCH.touch_no = TOUCHRES.touch_no
		WHERE 
			touchresult_no = 3065
			AND touchtype_no = 16
			AND program_no = 3083			
		GROUP BY 1
	) STR1 (str1_web_id, str1_web_id_cnt)
	ON web_id = STR1.str1_web_id
WHERE
	program_no = 3083;

 

[elsenorjose]

Crystal Reports is a tool to EXTRACT data from databases for reporting purposes, NOT for database administration. It's probably choking on that.

 

[baycolor]

Thanks. I know that but I have seen this type of code put in an Add Command. Last year I was at a company and they were doing thing like the following in an Add Command:

Create table statement to create table A.
Insert statement that inserts into table A.
Create table statement to create table B.
Insert statement that inserts into table B.
Select statement that joined table A, table B and table C - this output was pulled into the Crystal Report (last step in the Add Command).

They were doing this to get around some things they couldn't do with straight SQL (same reason I'm doing it).

So... I'm not sure why my installation of CR is giving me trouble. Any help would be appreciated. NOTE: Nobody has to go through the SQL I just included it for completeness purposes (I know it works).

Thanks

 

[rmadamba]

I also got the same error when I was choosing the stored procedure to use in the report. Did you ever resolve your problem? I would be interested to know.

Thanks.

 

[synapsevampire]

I've done advanced command objects with SQL Server, but not with Teradata.

You've seen this sort of thing from Crystal against Teradata?

Try doing a select against any small table at the very end so that a rowset is returned.

You might also need to add some settings lines up front so that the database doesn't return verbose info.

-k

 

[baycolor]

I gave up on the Add Command that was originally posted in this thread (had to complete my work some other way). I just recently did have some success with an add command that did some similar stuff. It leverages synapsevampire's idea although I was a little blown away that the SEL that doesn't return any rows worked as the third step in the Add Command. I guess Crystal Reports getting a valid SEL response anywhere in the Add Command keeps it happy - nice to know.

DEL FROM CRYSTALREPORTS.crfl_t_2007_masters_contacts;
DEL FROM CRYSTALREPORTS.crfl_t_2007_masters_hotels;
SEL * FROM CRYSTALREPORTS.crfl_t_2007_masters_orgs WHERE 1=0;

INSERT INTO CRYSTALREPORTS.crfl_t_2007_masters_contacts
SEL 
	CONT.contact_no
	,FLXDDD1.data_value AS wave
	,CONT.first_name
	,CONT.last_name
	,ACCTCONT.contact_co_title
	,ACCT.account_name
	,ACCTCONT.phone_no
	,ACCTCONT.email_addr
	,ACCTCONT.admin_name
	,ACCTCONT.admin_email
	,FLXTEXT1.data_value AS arriv_airport
	,FLXDATE1.date_value AS arriv_date
	,COALESCE(FLXTEXT2.data_value, ' ') AS arriv_time_flight
	,COALESCE(FLXTEXT6.data_value, ' ') AS arriv_from
	,FLXTEXT3.data_value AS dep_airport
	,FLXDATE2.date_value AS dep_date
	,COALESCE(FLXTEXT4.data_value, ' ') AS dep_time_flight
	,FLXTEXT5.data_value AS one_on_one_mtg
	,FLXDATE3.date_value AS confirm_email_date
	,FLXAREA1.data_value AS special_notes
	,FLXAREA2.data_value AS follow_up_notes
	,FLXDDD2.data_value AS org
FROM
	/*** OBTAIN CONTACT_NO, NAME, TITLE OTHER CONTACT INFO OF CONTACTS HAVING MASTERS CODE ASSIGNED ***/
	RMDB.rmal_v_contact CONT
	JOIN RMDB.rmal_v_contact_contactcode CONTCODE
	ON CONT.contact_no = CONTCODE.contact_no
		AND CONTCODE.contactcode_no = 825
	JOIN RMDB.rmal_v_account_contact ACCTCONT
	ON CONT.contact_no = ACCTCONT.contact_no
	/*** OBTAIN ACCOUNT RELATED INFO FOR THESE CONTACTS ***/
	JOIN RMDB.rmal_v_account ACCT
	ON ACCTCONT.account_no = ACCT.account_no
	/*** OBTAIN THE FLEX DROPDOWN WAVE VALUE ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_sel_dropdown FLXSELDD1
	ON CONTCODE.contact_no = FLXSELDD1.flx_data_no
		AND FLXSELDD1.flx_field_no = 239
	LEFT JOIN RMDB.rmal_v_flx_dropdown FLXDDD1
	ON FLXSELDD1.flx_dropdown_no = FLXDDD1.flx_dropdown_no
	/*** OBTAIN THE FLEX TEXT ARRIVAL AIRPORT VALUE ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_text FLXTEXT1
	ON CONTCODE.contact_no = FLXTEXT1.flx_data_no
		AND FLXTEXT1.flx_field_no = 248
	/*** OBTAIN THE FLEX ARRIVAL DATE VALUE ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_date FLXDATE1
	ON CONTCODE.contact_no = FLXDATE1.flx_data_no
		AND FLXDATE1.flx_field_no = 246
	/*** OBTAIN THE FLEX ARRIVAL TIME AND FLIGHT VALUE ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_text FLXTEXT2
	ON CONTCODE.contact_no = FLXTEXT2.flx_data_no
		AND FLXTEXT2.flx_field_no = 247
	/*** OBTAIN THE FLEX TEXT DEPARTURE AIRPORT VALUE ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_text FLXTEXT3
	ON CONTCODE.contact_no = FLXTEXT3.flx_data_no
		AND FLXTEXT3.flx_field_no = 251
	/*** OBTAIN THE FLEX DEPARTURE DATE VALUE ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_date FLXDATE2
	ON CONTCODE.contact_no = FLXDATE2.flx_data_no
		AND FLXDATE2.flx_field_no = 249
	/*** OBTAIN THE FLEX DEPARTURE TIME AND FLIGHT VALUE ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_text FLXTEXT4
	ON CONTCODE.contact_no = FLXTEXT4.flx_data_no
		AND FLXTEXT4.flx_field_no = 250
	/*** OBTAIN THE FLEX ONE ON ONE TIME AND FLIGHT VALUE ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_text FLXTEXT5
	ON CONTCODE.contact_no = FLXTEXT5.flx_data_no
		AND FLXTEXT5.flx_field_no = 243
	/*** OBTAIN THE FLEX CONFIRMATION EMAIL SENT DATE VALUE ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_date FLXDATE3
	ON CONTCODE.contact_no = FLXDATE3.flx_data_no
		AND FLXDATE3.flx_field_no = 241
	/*** OBTAIN THE FLEX SPECIAL NOTES VALUE ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_textarea FLXAREA1
	ON CONTCODE.contact_no = FLXAREA1.flx_data_no
		AND FLXAREA1.flx_field_no = 244
	/*** OBTAIN THE FLEX FOLLOW UP NOTES VALUE ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_textarea FLXAREA2
	ON CONTCODE.contact_no = FLXAREA2.flx_data_no
		AND FLXAREA2.flx_field_no = 245
	/*** OBTAIN THE FLEX DROPDOWN ORG VALUE ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_sel_dropdown FLXSELDD2
	ON CONTCODE.contact_no = FLXSELDD2.flx_data_no
		AND FLXSELDD2.flx_field_no = 242
	LEFT JOIN RMDB.rmal_v_flx_dropdown FLXDDD2
	ON FLXSELDD2.flx_dropdown_no = FLXDDD2.flx_dropdown_no
	/*** OBTAIN THE FLEX ARRIVE FROM VALUE ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_text FLXTEXT6
	ON CONTCODE.contact_no = FLXTEXT6.flx_data_no
		AND FLXTEXT6.flx_field_no = 264;


INSERT INTO CRYSTALREPORTS.crfl_t_2007_masters_hotels
SEL 
	CONT.contact_no
	,FLXCHECK1.data_value AS hotel_flag_mon_apr_2
	,FLXCHECK2.data_value AS hotel_flag_tue_apr_3
	,FLXCHECK3.data_value AS hotel_flag_wed_apr_4
	,FLXCHECK4.data_value AS hotel_flag_thu_apr_5
	,FLXCHECK5.data_value AS hotel_flag_fri_apr_6
	,FLXCHECK6.data_value AS hotel_flag_sat_apr_7
	,FLXCHECK7.data_value AS hotel_flag_sun_apr_8
	,FLEXTEXT1.data_value AS hotel_pickup
FROM
	/*** OBTAIN CONTACT_NO, NAME, TITLE OTHER CONTACT INFO OF CONTACTS HAVING MASTERS CODE ASSIGNED ***/
	RMDB.rmal_v_contact CONT
	JOIN RMDB.rmal_v_contact_contactcode CONTCODE
	ON CONT.contact_no = CONTCODE.contact_no
		AND CONTCODE.contactcode_no = 825
	/*** OBTAIN THE FLEX HOTEL INDICATOR VALUE FOR MON APR 2ND ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_check FLXCHECK1
	ON CONTCODE.contact_no = FLXCHECK1.flx_data_no
		AND FLXCHECK1.flx_field_no = 253
	/*** OBTAIN THE FLEX HOTEL INDICATOR VALUE FOR TUE APR 3RD ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_check FLXCHECK2
	ON CONTCODE.contact_no = FLXCHECK2.flx_data_no
		AND FLXCHECK2.flx_field_no = 254
	/*** OBTAIN THE FLEX HOTEL INDICATOR VALUE FOR WED APR 4TH ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_check FLXCHECK3
	ON CONTCODE.contact_no = FLXCHECK3.flx_data_no
		AND FLXCHECK3.flx_field_no = 255
	/*** OBTAIN THE FLEX HOTEL INDICATOR VALUE FOR THU APR 5TH ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_check FLXCHECK4
	ON CONTCODE.contact_no = FLXCHECK4.flx_data_no
		AND FLXCHECK4.flx_field_no = 256
	/*** OBTAIN THE FLEX HOTEL INDICATOR VALUE FOR FRI APR 6TH ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_check FLXCHECK5
	ON CONTCODE.contact_no = FLXCHECK5.flx_data_no
		AND FLXCHECK5.flx_field_no = 257
	/*** OBTAIN THE FLEX HOTEL INDICATOR VALUE FOR SAT APR 7TH ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_check FLXCHECK6
	ON CONTCODE.contact_no = FLXCHECK6.flx_data_no
		AND FLXCHECK6.flx_field_no = 258
	/*** OBTAIN THE FLEX HOTEL INDICATOR VALUE FOR SUN APR 8TH ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_check FLXCHECK7
	ON CONTCODE.contact_no = FLXCHECK7.flx_data_no
		AND FLXCHECK7.flx_field_no = 259
	/*** OBTAIN THE FLEX HOTEL PICKUP VALUE ASSIGNED TO THE CONTACT ***/
	LEFT JOIN RMDB.rmal_v_flx_text FLEXTEXT1
	ON CONTCODE.contact_no = FLEXTEXT1.flx_data_no
		AND FLEXTEXT1.flx_field_no = 252