October 8, 2008 at 7:48 am
Good day,
I've created a view witch contains the folowing line:
ISNULL([SomeDB].[SomeOwner].[SomeTable].[SomeColumn], '30-12-2099') AS uitdienst
When I query the view there is no problem. The query gives me back the right results.
But when I call the query (view) within EXEC msdb.dbo.sp_send_dbmail
I receive a message with the folowing error:
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478
Query execution failed: Msg 242, Level 16, State 3, Server APPL21, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
What goes wrong here? And how can I work around or solve the problem?
Many Thanks.
Pim Verkley
October 8, 2008 at 8:55 am
If you could post your query, or failing that, the line that you reference your view in, it'd help.
October 9, 2008 at 1:44 am
Garadin,
Thanks for your reply. Underneath is the code that I use in the view that I call from SP_SEND_DBMAIL.
I learned from trial and error that the line:
, ISNULL([Beaufort].[dbo].[dpic300].[uitdnst_dt], '30-12-2099') AS uitdienst
Is the one that the error is complaining about. When I REM the line there is no error message.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[controle_adreslijst] as
SELECT
[RegioCat].[dbo].[M_adreslijst].persnummer AS pers_nr
, isnull(Beaufort.dbo.DPIB010.NAAM_SAMEN, 'Geen Naam') + ','+ ' '+ isnull(Beaufort.dbo.DPIB010.E_VRLT, '') + ' ' + isnull (Beaufort.dbo.DPIB010.vrvg_samen, '') as naam_samen
, ISNULL([Beaufort].[dbo].[dpic300].[uitdnst_dt], '30-12-2099') AS uitdienst
FROM [RegioCat].[dbo].[M_adreslijst]
INNER JOIN [Beaufort].dbo.[dpic300]
ON [RegioCat].[dbo].[M_adreslijst].[persnummer] = [Beaufort].dbo.[dpic300].[pers_nr]
INNER JOIN Beaufort.dbo.DPIB010
ON Beaufort.dbo.DPIB010.[pers_nr] = [RegioCat].[dbo].[M_adreslijst].[persnummer]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
October 9, 2008 at 6:08 am
ISNULL([Beaufort].[dbo].[dpic300].[uitdnst_dt], '30-12-2099')
When I select this:
SELECT CAST('30-12-2099' as datetime)
I receive this:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Look familiar?
To fix this, either change the format of your date to something that can be implicitly converted like (12/30/2099), or replace it in your ISNULL with this: convert(datetime,'30-12-2099',105)
October 9, 2008 at 6:50 am
Look familiar??
Yeah it did.....
:blush:
Thanks for you're reply. I was looking in the wrong directions....
It now works fine. Never thought it would.
Pim Verkley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply