February 1, 2010 at 8:55 am
I have the following stored procedure. It works fine running it in SQL Server but when I run it in Reporting Services I get the following message regarding my dates.
-"Failed to convert parameter value from a String to a Date Time. (System.Data)
Can someone help? Thanks
USE [Database_NM]
GO
/****** Object: StoredProcedure [dbo].[pr_STORED_PROCEDURE_NM] Script Date: 01/27/2010 13:53:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--DROP PROCEDURE dbo.pr_STORED_PROCEDURE_NM
--DROP TABLE #RATE_SUBTYPE_ID
--DROP TABLE #DETAIL_COS_ID
--ALTER PROCEDURE [dbo].[pr_STORED_PROCEDURE_NM]
--(@RATE_SUBTYPE_ID INT
--, @DETAIL_COS_ID INT
--, @ACCRUAL_RISK_CD VARCHAR(10)
--, @ACCRUAL_SUB_PRODUCT_CD VARCHAR(10)
--, @PAID_AMT NUMERIC (18,8)
--, @SVC_BEGIN_DT DATETIME
--, @SVC_END_DT DATETIME
--, @ALLOCATION_TYPE_CD VARCHAR(3)
--, @DETAIL_DESC VARCHAR (100)
--, @FUNCTION_ID INT)
--
--AS
--GRANT EXECUTE ON pr_STORED_PROCEDURE_NM TO reportuser
DECLARE @RATE_SUBTYPE_ID INT
DECLARE @DETAIL_COS_ID INT
DECLARE @ACCRUAL_RISK_CD VARCHAR(10)
DECLARE @ACCRUAL_SUB_PRODUCT_CD VARCHAR(10)
DECLARE @PAID_AMT NUMERIC (18,8)
DECLARE @SVC_BEGIN_DT DATETIME
DECLARE @SVC_END_DT DATETIME
DECLARE @ALLOCATION_TYPE_CD VARCHAR(3)
DECLARE @DETAIL_DESC VARCHAR (100)
DECLARE @FUNCTION_ID INT
DECLARE @MESSAGE_DESC VARCHAR(8000)
SET @MESSAGE_DESC = ''
SET @RATE_SUBTYPE_ID = 135
SET @DETAIL_COS_ID = 20
SET @ACCRUAL_RISK_CD ='FL'
SET @ACCRUAL_SUB_PRODUCT_CD = 'CAID'
SET @PAID_AMT = '62356.00'
SET @SVC_BEGIN_DT = '01/01/2009'
SET @SVC_END_DT = '12/31/2009'
SET @ALLOCATION_TYPE_CD = 'FFS'
SET @DETAIL_DESC = 'Test'
SET @FUNCTION_ID = 1
----+++++++++++++++++++++++++++++++++++++++++++++++
--------CREATING TEMP TABLE TO HOUSE MULTI SELECT PARMS
SELECT
* INTO #RATE_SUBTYPE_ID
FROM ACCRUAL_SRP.dbo.fnDStringToTable(@RATE_SUBTYPE_ID,',')
SELECT
* INTO #DETAIL_COS_ID
FROM ACCRUAL_SRP.dbo.fnDStringToTable(@DETAIL_COS_ID,',')
----+++++++++++++++++++++++++++++++++++++++++++++++++
IF @MESSAGE_DESC = '' AND
(@RATE_SUBTYPE_ID = '' OR @DETAIL_COS_ID = ''
OR @ACCRUAL_RISK_CD = '' OR @ACCRUAL_SUB_PRODUCT_CD = ''
--OR @PAID_AMT = ''
OR @SVC_BEGIN_DT = '' OR @SVC_END_DT = ''
OR @ALLOCATION_TYPE_CD = '')
BEGIN
SET @MESSAGE_DESC = 'RATE_SUBTYPE_ID,DETAIL_COS_ID
,ACCRUAL_RISK_CD,ACCRUAL_SUB_PRODUCT_CD
,SVC_BEGIN_DT, SVC_END_DT, and ALLOCATION_TYPE_CD must be populated'
END
--
--IF @MESSAGE_DESC = '' AND (SELECT LEN(@PAID_AMT)) > 18
--BEGIN
--SET @MESSAGE_DESC = 'Paid Amount exceeds field length'
--END
IF @MESSAGE_DESC = '' AND (SELECT LEN(@DETAIL_DESC)) > 100
BEGIN
SET @MESSAGE_DESC = 'Detail Description exceeds field length'
END
--IF @MESSAGE_DESC = '' AND @PAID_AMT NOT LIKE '%_._%'
--BEGIN
--SET @MESSAGE_DESC = 'Paid Amount not in proper format'
--END
IF @FUNCTION_ID = 1 AND @MESSAGE_DESC = ''
BEGIN
IF @MESSAGE_DESC = '' AND
(SELECT COUNT(*) FROM #RATE_SUBTYPE_ID)
<> (SELECT COUNT(*)FROM #DETAIL_COS_ID)
BEGIN
SET @MESSAGE_DESC = 'Number of Rate SubType IDs and Category of Service chosen must be equal'
END
IF @MESSAGE_DESC = ''
BEGIN
INSERT INTO TABLE_NM
SELECT @RATE_SUBTYPE_ID, @DETAIL_COS_ID,@ACCRUAL_RISK_CD,@ACCRUAL_SUB_PRODUCT_CD,0, @SVC_BEGIN_DT,
@SVC_END_DT,'FFS', 'TEST', GETDATE(), 'USER_ID'
END
END
SELECT @MESSAGE_DESC AS MESSAGE_DESC
February 1, 2010 at 3:27 pm
Within a stored procedure, you don't DECLARE the parameter variables - those will exist because the procedure exists. Testing the code outside of the procedure will require the declares, but you need to take those declares out of the proc that are actual parameters to the procedure. I can see you have the procedure piece commented out, but I can't assume you've done that with the actual procedure.
Steve
(aka sgmunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 1, 2010 at 5:53 pm
It looks like Reporting Services is passing the parameter formatted in such a way SQL Server cannot implicitly convert it. This may be caused by your regional settings or locale. Try to provide date in one of standard formats, like yyyymmdd for example.
HTH
Piotr
...and your only reply is slàinte mhath
April 6, 2010 at 3:20 pm
I just had the same problem today.
The short answer is SSRS cannot get a good hold on the results of the procedure (my interpretation of SSRS-speak). You're using temp table, I'm using EXEC (@SQL)
What I did to get around it is temporarily change my proc to return something solid:
create proc MyProcThatWillRunACustomQuery (@Parameters) as
select FieldListTheCustomQueryWillReturn
from sometable
From that, SSRS will move ahead happily. After the report is ready to test, I plug the real guts back into the proc.
Another option is to add this as the first couple lines in the proc:
SET FMTONLY OFF
SET NOCOUNT ON
The above didn't work in my latest proc, so I used the first work-around.
Hope that helps (or, HTH as they say)
Kurt
May 13, 2014 at 6:22 pm
this is an older post (2011) but still very relevant today (2014)........... here's how how I resolved the issue.........
-- the below is the last remaining lines of the SP - the @select_clause is built in the SP..............
if OBJECT_ID('temp..#tblCodeCompliance') is not null drop table #tblCodeCompliane
CREATE TABLE #tblCodeCompliance
(
TRS varchar(25),
TAXID varchar(50),
COMPLAINT_NUMBER varchar(15),
MSB_CODE varchar(20),
REQUEST_DATE datetime,
CLOSED_DATE datetime,
COMPLAINT_STATUS varchar(25),
INSPECTOR varchar(80)
)
insert into #tblCodeCompliance
exec (@select_clause )
select * from #tblCodeCompliance
--------------------
have fun.
Ed.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply