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?
Can anyone please help me.
Thanks
If it is happening with Views as well then follow these steps to avoid this Error:
right click on Crystal Reports link in Programe Files
and Select Run as Administrator
i hope this will work
Thanks,
Z
Thank you for your reply.
I tried it but it didn’t work.
It is not the same with the views, I am able to work with the views.
Thanks
Veni,
when you create a parameter within Stored Procedure, there is no need to create a parameter in Crystal, Crystal is gona carete the parameter automatically when you select the stored procedure as a data source the way you define in the stored procedure
the problem you are having look like a SQL Server user permission issue, the user you are using to connect from Crystal must be sysadmin role
and also i suggest to gave it a try using SQL Native Client Driver rather than ODBC
Thanks,
Z
Why do you need to use an output parameter? Couldn’t you just add that data to the results of query in the stored procedure?
Perhaps you could post your stored procedure (or a similar sample) here for review.
Noel
Below is the query
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
— =============================================
ALTER
PROCEDURE [dbo].[get_measures_with_output_para]
— Add the parameters for the stored procedure here
(@meas_system_seqno Numeric(8),
@inlet_rate VARCHAR(10) OUTPUT,
@inlet_size VARCHAR(10) OUTPUT,
@inlet_temp VARCHAR(10) OUTPUT)
AS
BEGIN
SET NOCOUNT ON;
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 inletrate, @inlet_size inletsize, @inlet_temp inlettemp;
END;
The reason I am having three select statment was to get vlaues for different column_names like ‘inlet_rate’, ‘inlet_size’ and ‘inlet_temp’.
I want all these three values in columns and not in rows. So I have the last select statement to get the values into columns.
I want to use these in my report to display the measures, I want to drag the columns where ever need in the main report or subreport and as many times as needed..
Thanks. I really appreciate that.
I think if you make your output parameters local variables instead you should be able to achieve your goal. I think this should work.
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
— =============================================
ALTER
PROCEDURE [dbo].[get_measures_with_output_para]
— Add the parameters for the stored procedure here
(@meas_system_seqno Numeric(8))
AS
BEGIN
SET NOCOUNT ON;
DECLARE @inlet_rate VARCHAR(10)
DECLARE @inlet_size VARCHAR(10)
DECLARE @inlet_temp VARCHAR(10)
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 inletrate, @inlet_size inletsize, @inlet_temp inlettemp;
END;
Veni,
Did what I suggest make sense? Did it work for you?
Thanks,
Noel
Noel,
Thank you for your reply.
I tried your idea. I am able to link it to the data sources and use it in a new report without connecting it to any other tables.
But If I use it in my exisitng report and link it to other table and run the report I get the following error.
failed to retrieve data from the database 08S01 [Microsoft] [SQL Native Client] Communication link
I am tring to find out why this error is comming. Any idea?
Thanks a lot.
I am not sure. Sorry.
Noel
No, problem.
I will post the solution once I resolve this, it might help someone.
Thanks
Veni,
Your query is appearing as a ‘Blog’. Can you please check if thats the case from your end andclose/delete/end this one.
Please re-post as a ‘Discussion’.
I liked Noel’s suggestion.
Since the rows-to-columns has been done in the SP, that could be the probable reason of the failure of linking with other tables.
However, you can workaround this one by using a main report-sub report approach.
More…..onceyou re-post this !!
Thanks,
Prathamesh
Sorry. I am new to this.
Thanks