Error - Failed to open rowset. Details:42000:

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error - Failed to open rowset. Details:42000:

Author

Topic

RJ
Starting Member

11 Posts

Posted - 2011-06-28 : 03:43:17

Full Error when running the Crystal report:

Failed to open rowset. Details:42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or Function'A001_SVC_Dates' expeccts parameter '@StartDate', which was not supplied.

I get this message when running a Crystal report (using Crystal XI) that uses a stored procedure with two parameters - see SP below.

The script and report were created on my local PC (I have a copy of the database running local) and it all works fine, the report prompts for dates and then continues OK. However, now I have now loaded the SP onto the main database server, placed the report in the reports folder on the server it fails when I run it on a network PC. The report does not request the parameters it only displays this error message. I know the issue relates to the variable parameters as if they are removed the report runs fine. Any ideas welcome.

SP:

ALTER PROCEDURE [dbo].[A001_SVC_DATES]

(
@StartDate Datetime,
@EndDate Datetime
)
AS

DROP TABLE svccrm.dbo.svcrem

select cust.custno, cust.fname, cust.lname, cust.addr1, cust.addr2, cust.addr2e, cust.addr3, cust.po, cust.postcd,
cust.email, cust.htel, cust.wtel, cust.tel3, cust.consent, cust.nocontact,
vehi.licno, vehi.serialno, vehi.make, vehi.model, vehi.submodel,
convert(varchar,vehi.fregd, 103) as RegDate,
convert(varchar,vehi.nmd, 103) as NextSvcDate,
convert(varchar,vehi.last_service, 103) as LastSvcDate,
convert(varchar,vehi.ninspecday, 103) as NxtInspDate,
convert(varchar,vehi.inspecd, 103) as LastInspDate,
convert(varchar,vehi.next_emission_inspect, 103) as NxtEmiDate,
convert(varchar,vehi.last_emission_inspect, 103) as LastEmiDate

INTO [svccrm].dbo.SVCREM
from vehi
join cust on cust.custno = vehi.custno
where nmd between @StartDate and @EndDate

waitfor delay '00:00:05';
select * from [svccrm].dbo.svcrem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-28 : 03:46:24

You are missing a date parameter. The error message says so.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-28 : 03:48:09

ALTER PROCEDURE [dbo].[A001_SVC_DATES]
(
@StartDate Datetime = NULL,
@EndDate Datetime = NULL
)
AS

IF @StartDate IS NULL SET @StartDate = '19000101'
IF @EndDate IS NULL SET @EndDate = '99991231'

DROP TABLE svccrm.dbo.svcrem

select cust.custno, cust.fname, cust.lname, cust.addr1, cust.addr2, cust.addr2e, cust.addr3, cust.po, cust.postcd,
cust.email, cust.htel, cust.wtel, cust.tel3, cust.consent, cust.nocontact,
vehi.licno, vehi.serialno, vehi.make, vehi.model, vehi.submodel,
convert(varchar,vehi.fregd, 103) as RegDate,
convert(varchar,vehi.nmd, 103) as NextSvcDate,
convert(varchar,vehi.last_service, 103) as LastSvcDate,
convert(varchar,vehi.ninspecday, 103) as NxtInspDate,
convert(varchar,vehi.inspecd, 103) as LastInspDate,
convert(varchar,vehi.next_emission_inspect, 103) as NxtEmiDate,
convert(varchar,vehi.last_emission_inspect, 103) as LastEmiDate
INTO [svccrm].dbo.SVCREM
from vehi
join cust on cust.custno = vehi.custno
where nmd between @StartDate and @EndDate

--waitfor delay '00:00:05';
select * from [svccrm].dbo.svcrem

N 56°04'39.26"
E 12°55'05.63"

RJ
Starting Member

11 Posts

Posted - 2011-06-28 : 12:14:48

I know that the error related to no date parameter. When you open the Crystal report it should prompt for the date parameter to be entered, but all you get is the error message. Applying your solution did not rectify the cause (no date requested) it by passes and uses the dates specified in the set statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-28 : 15:26:24

Did you also notice the default date values in the parameter header?

N 56°04'39.26"
E 12°55'05.63"

RJ
Starting Member

11 Posts

Posted - 2011-06-28 : 15:34:49

Yes I entered these as shown, but as I said the SP then uses these parameters as it fails to prompt for parameter dates when running the report.

I agree this solution stops the error, but it does not solve the problem.

Thanks for trying to assist.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources