I have created a stored procedure in MS SQL Server 2005 with one input parameter and three output parameters. I have executed the query successfully in SQL Server. Now I am trying to built a report using this stored procedure. When I select the stored procedure in Crystal Reports database expert it prompts for the input and output parameters. When I enter them and press OK then I get the following error.
Database Connector Error:
‘Cannot obtain error message from server.’
My first question is how should I pass the output parameter, I have declared the output parameters as VARCHAR(10) in stored procedure in SQL server.
So, do I have to pass it as for eg. @rate OR {?rate} OR rate.
I don’t think I have any problems with ODBC drivers or Grants.
If I create a stored procedure with on output paratmeters, I have no issues. I am able to add it to the datasource and get the data in the report.
I feel I am having issue only with the output parameters. Did I miss anything?
Have you compiled and executed stored procedure successfully at the backend?
If not, then please do it first then Log off and then log on your specified data source connection from Database(menu)>Log On or Off server… option.
Yes, I compiled the stored procedure successfully and able to get the expected output in MS SQL Server 2005.
What I observed was once I am trying to get the output value I am getting the Database error. If I don’t have any output parameters I am not getting the error.
I have pasted the query below.
Thank you
ALTER PROCEDURE [dbo].[get_measures]
-- Add the parameters for the stored procedure here
(@meas_system_seqno Numeric(8),
@inlet_rate CHAR(10) OUTPUT,
@inlet_size CHAR(10) OUTPUT,
@inlet_temp CHAR(10) OUTPUT)
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select @inlet_rate = syum.unit_of_measure_label
from sys_column_dict c inner join sys_table_dict t on t.sys_table_dict_seqno = c.sys_table_dict_seqno
inner join measure_format mf on c.measure_domain_seqno = mf.measure_domain_seqno and
sys_meas_system_seqno = @meas_system_seqno
inner join sys_unit_of_measure syum on syum.sys_unit_of_measure_seqno = mf.sys_unit_of_measure_seqno
where column_name = ‘inlet_rate’
and table_name =
(
select table_name
from sys_table_dict t1
where t1.sys_table_dict_seqno = t.sys_table_dict_seqno
);
select @inlet_size = syum.unit_of_measure_label
from sys_column_dict c inner join sys_table_dict t on t.sys_table_dict_seqno = c.sys_table_dict_seqno
inner join measure_format mf on c.measure_domain_seqno = mf.measure_domain_seqno and
sys_meas_system_seqno = @meas_system_seqno
inner join sys_unit_of_measure syum on syum.sys_unit_of_measure_seqno = mf.sys_unit_of_measure_seqno
where column_name = ‘inlet_size’
and table_name =
(
select table_name
from sys_table_dict t1
where t1.sys_table_dict_seqno = t.sys_table_dict_seqno
);
select @inlet_temp = syum.unit_of_measure_label
from sys_column_dict c inner join sys_table_dict t on t.sys_table_dict_seqno = c.sys_table_dict_seqno
inner join measure_format mf on c.measure_domain_seqno = mf.measure_domain_seqno and
sys_meas_system_seqno = @meas_system_seqno
inner join sys_unit_of_measure syum on syum.sys_unit_of_measure_seqno = mf.sys_unit_of_measure_seqno
where column_name = ‘inlet_temp’
and table_name =
(
select table_name
from sys_table_dict t1
where t1.sys_table_dict_seqno = t.sys_table_dict_seqno
);
select @inlet_rate = syum.unit_of_measure_label
from
sys_column_dict c inner join sys_table_dict t on t.sys_table_dict_seqno = c.sys_table_dict_seqno
inner join measure_format mf on c.measure_domain_seqno = mf.measure_domain_seqno and
sys_meas_system_seqno = @meas_system_seqno
inner join sys_unit_of_measure syum on syum.sys_unit_of_measure_seqno = mf.sys_unit_of_measure_seqno
where column_name = ‘inlet_rate’
and table_name =
(
select table_name
from sys_table_dict t1
where t1.sys_table_dict_seqno = t.sys_table_dict_seqno
);
select @inlet_size = syum.unit_of_measure_label
from sys_column_dict c inner join sys_table_dict t on t.sys_table_dict_seqno = c.sys_table_dict_seqno
inner join measure_format mf on c.measure_domain_seqno = mf.measure_domain_seqno and
sys_meas_system_seqno = @meas_system_seqno
inner join sys_unit_of_measure syum on syum.sys_unit_of_measure_seqno = mf.sys_unit_of_measure_seqno
where column_name = ‘inlet_size’
and table_name =
(
select table_name
from sys_table_dict t1
where t1.sys_table_dict_seqno = t.sys_table_dict_seqno
);
select @inlet_temp = syum.unit_of_measure_label
from sys_column_dict c inner join sys_table_dict t on t.sys_table_dict_seqno = c.sys_table_dict_seqno
inner join measure_format mf on c.measure_domain_seqno = mf.measure_domain_seqno and
sys_meas_system_seqno = @meas_system_seqno
inner join sys_unit_of_measure syum on syum.sys_unit_of_measure_seqno = mf.sys_unit_of_measure_seqno
where column_name = ‘inlet_temp’
and table_name =
(
select table_name
from sys_table_dict t1
where t1.sys_table_dict_seqno = t.sys_table_dict_seqno
);
(BOB member since 2012-08-13)