Error converting data type varchar to datetime

An application i am using creates an error.

Using SQL-profiler i catch this statement:

exec sp_executesql N'insert into RECORDING_SCHEDULE (programme_oid, capture_source_oid, filename, status, recording_group, recording_type, manual_channel_oid,
manual_start_time, manual_end_time, quality_level, pre_pad_minutes, post_pad_minutes) values (@.P1,@.P2,@.P3,@.P4,@.P5,@.P6,@.P7,@.P8,@.P9,@.P10,@.P11,@.P1 2)',N'@.P1 int,@.P2 int,@.P3
nvarchar(19),@.P4 int,@.P5 int,@.P6 int,@.P7 int,@.P8 datetime,@.P9 datetime,@.P10 int,@.P11 int,@.P12 int',177748,2,N'Lilla rda traktorn',4,40,5,16,''2006-06-21
17:00:00:000'',''2006-06-21 20:00:00:000'',1,1,2

After removing the double ' around the date and time parts i get the following error when running in QA:
Msg 8114, Level 16, State 5, Line 0
Error converting data type varchar to datetime.

If a remove the time portion it works.

The DDL for the table is:

CREATE TABLE [dbo].[RECORDING_SCHEDULE](
[oid] [int] IDENTITY(1,1) NOT NULL,
[programme_oid] [int] NULL,
[capture_source_oid] [int] NULL,
[status] [smallint] NOT NULL,
[filename] [varchar](255) NULL,
[recording_type] [int] NULL,
[recording_group] [int] NULL,
[manual_start_time] [datetime] NULL,
[manual_end_time] [datetime] NULL,
[manual_channel_oid] [int] NULL,
[quality_level] [int] NULL CONSTRAINT [DF__RECORDING__quali__38996AB5] DEFAULT ((0)),
[pre_pad_minutes] [int] NULL CONSTRAINT [DF__RECORDING__pre_p__398D8EEE] DEFAULT ((0)),
[post_pad_minutes] [int] NULL CONSTRAINT [DF__RECORDING__post___3A81B327] DEFAULT ((0)),
CONSTRAINT [aaaaaRECORDING_SCHEDULE_PK] PRIMARY KEY NONCLUSTERED

What am I missing?Well, I corrected a type where you listed [@.P1 2] rather than the obviously intended [@.P12], but after that this code worked fine for me:CREATE TABLE [dbo].[RECORDING_SCHEDULETMP](
[oid] [int] IDENTITY(1,1) NOT NULL,
[programme_oid] [int] NULL,
[capture_source_oid] [int] NULL,
[status] [smallint] NOT NULL,
[filename] [varchar](255) NULL,
[recording_type] [int] NULL,
[recording_group] [int] NULL,
[manual_start_time] [datetime] NULL,
[manual_end_time] [datetime] NULL,
[manual_channel_oid] [int] NULL,
[quality_level] [int] NULL,
[pre_pad_minutes] [int] NULL,
[post_pad_minutes] [int] NULL,
)

exec sp_executesql N'
insert into RECORDING_SCHEDULETMP
(programme_oid,
capture_source_oid,
filename,
status,
recording_group,
recording_type,
manual_channel_oid,
manual_start_time,
manual_end_time,
quality_level,
pre_pad_minutes,
post_pad_minutes)
values (@.P1,
@.P2,
@.P3,
@.P4,
@.P5,
@.P6,
@.P7,
@.P8,
@.P9,
@.P10,
@.P11,
@.P12)',
N'@.P1 int,
@.P2 int,
@.P3 nvarchar(19),
@.P4 int,
@.P5 int,
@.P6 int,
@.P7 int,
@.P8 datetime,
@.P9 datetime,
@.P10 int,
@.P11 int,
@.P12 int',
177748,
2,
N'Lilla rda traktorn',
4,
40,
5,
16,
'2006-06-21 17:00:00:000',
'2006-06-21 20:00:00:000',
1,
1,
2

drop table RECORDING_SCHEDULETMPTry cutting and pasting this to QA and see if it runs for you.|||Oh, I think I see the issue. You "removed" the double quotes, when you should have just changed them to single quotes, as in my sample code...

Error catch in SQL

Hi everyone, I am using an SQL extended stored procedure to send emails in a
DTS package using a cursor that goes through each row in a table.

Email sending code below
======================
exec master.dbo.xp_smtp_sendmail
@.FROM = @.sFrom,
@.FROM_NAME = @.sFrom,
@.TO = @.sRecepients,
@.subject = @.sSubject,
@.message = @.sBody,
@.type = N'text/html',
@.codepage = 0,
@.server =N'MYMAILSERVER'
======================
Fetch Next From EmailCursor ...

Now the problem I have is that if an individual email address in invalid
then an error occurs and the whole DTS package falls over. What I would like
to be able to do is "catch the error", something like this (C# code used as
example)

try
{
exec master.dbo.xp_smtp_sendmail
@.FROM = @.sFrom,
@.FROM_NAME = @.sFrom,
@.TO = @.sRecepients,
@.subject = @.sSubject,
@.message = @.sBody,
@.type = N'text/html',
@.codepage = 0,
@.server =N'MYMAILSERVER'
} catch {
exec master.dbo.xp_smtp_sendmail
@.FROM = "arealaddress@.mybusiness.com",
@.FROM_NAME = @.sFrom,
@.TO = @.sRecepients,
@.subject = @.sSubject,
@.message = @.sBody,
@.type = N'text/html',
@.codepage = 0,
@.server =N'MYMAILSERVER'
}

Is this possible? Normally I would do all the email validation before the
email is entered into the database but unfortunately, I do not have access
to the application code so I am stuck doing it this way.

Thanks in advance
MarkMark (markjones@.n0Sp8mTAIRAWHITIdotAC.NZ) writes:
> Now the problem I have is that if an individual email address in invalid
> then an error occurs and the whole DTS package falls over. What I would
> like to be able to do is "catch the error", something like this (C# code
> used as example)

In T-SQL there is no way to suppress the error. (In SQL2000, that is. The
coming version SQL2005 has the TRY CATCH you are looking for.

Presumably you should be able to have the DTS package to swallow the
error, but I don't know DTS so I can't give any details. The nice and
friendly people in microsoft.public.sqlserver.dts may have some ideas.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland
Regards
Mark
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9554F1DAF21E5Yazorman@.127.0.0.1...
> Mark (markjones@.n0Sp8mTAIRAWHITIdotAC.NZ) writes:
> > Now the problem I have is that if an individual email address in invalid
> > then an error occurs and the whole DTS package falls over. What I would
> > like to be able to do is "catch the error", something like this (C# code
> > used as example)
> In T-SQL there is no way to suppress the error. (In SQL2000, that is. The
> coming version SQL2005 has the TRY CATCH you are looking for.
> Presumably you should be able to have the DTS package to swallow the
> error, but I don't know DTS so I can't give any details. The nice and
> friendly people in microsoft.public.sqlserver.dts may have some ideas.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp