Any one using MSDE or SQL2005Express?

[spyork]

I think i have found some more info.

By using profiler have have fetched all querys to the database and then run them in Query analyzer. When i run this query i get the error:

Error converting data type varchar to datetime

The query:

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,@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 röda traktorn',4,40,5,16,''2006-06-21
17:00:00:000'',''2006-06-21 20:00:00:000'',1,1,2



If i remove all time entrys and leave the dates it works.

Have you had any luck with manually updating the EPG from the config app. I noticed you were getting an error then too.

[Urbansk]

Not using the config app. After a scheduled EPG update verything seams to be in place.

[Urbansk]

Thats just the way the SQL Server Profiler is showing it. It has a single quote in my code.

If i rewrite the SQL code to this i get a better result:

exec sp_executesql N'insert into CHANNEL (name, channel_number, favourite_channel) values (@P1,@P2,@P3)',N'@P1 nvarchar(13),@P2 int,@P3 char(1)',N'Animal Planet',102,'Y'

[SniperDude]

not sure if I am reading this correctly .. but in my Access experience date/time fields are updated with # signs around the date..

ie... #2006-06-21 20:00:00:000#

MSDE used single 's
ie '2006-06-21 20:00:00' but I think you may have to use

this format...
'20060621 20:00:00.000 PM'

Just a guess...

[sub]

I dont format these date strings - this is done by the DB Provider component you're using. I treat things date objects, and the DB Provider (SQLite/OleDB/SQLNCLI) takes care of the rest.

It may be worth trying both the OleDB and SQLNCLI providers. They may behave differently for this specific problem.

[spyork]

I dont format these date strings - this is done by the DB Provider component you're using. I treat things date objects, and the DB Provider (SQLite/OleDB/SQLNCLI) takes care of the rest.

It may be worth trying both the OleDB and SQLNCLI providers. They may behave differently for this specific problem.

I've tried both providers on SQL Server 2005 Express and I'm still receiving the errors. I know Spartan is using the SQLNCLI provider and it seems he is having some problems now as well. Any other ideas? I may just go back to using SQLite for now...

[sub]

Any other ideas?

No, afraid not. As I've said before, I've only ever tested the app with Access and SQLite. You're in uncharted territory.

[Urbansk]

No, afraid not. As I've said before, I've only ever tested the app with Access and SQLite. You're in uncharted territory.

There are two problems:
1: EPG update using config app.
Rewriting the SQL query to this get a better result:
New:
exec sp_executesql N'insert into CHANNEL (name, channel_number, favourite_channel) values (@P1,@P2,@P3)',N'@P1 nvarchar(13),@P2 int,@P3 char(1)',N'Animal Planet',102,'Y'

Old:exec sp_executesql N'insert into CHANNEL (name, channel_number, favourite_channel) values (@P1,@P2,'Y')',N'@P1 nvarchar(13),@P2 int,@P3 char(1)',N'Animal Planet',102


2: Schedule recording (all episodes this channel) shows an CAST error.
Somewhere in your code there is an format conversion error where you try to convert between different formats. I am well avare that you only try the code on SQLite and access. Perhaps they are more forgiving. But if you find the error you might avoid future problems in the app.
It only shows whe trying to schedule (all episodes this channel) the other types of reocuring schedules work. What is the difference in how the app handles the varoius types of scheduling.

[spyork]

sub, i know you probably don't want to dedicate any substantial amount of time to a database you don't officially support, but we would greatly appreciate any time you could possibly spare to help resolve these problems. Heck even if it takes a few weeks or months I'm fine with that too. Thanks...

[sub]

There are two problems:
1: EPG update using config app.
Rewriting the SQL query to this get a better result:
New:
exec sp_executesql N'insert into CHANNEL (name, channel_number, favourite_channel) values (@P1,@P2,@P3)',N'@P1 nvarchar(13),@P2 int,@P3 char(1)',N'Animal Planet',102,'Y'

Old:exec sp_executesql N'insert into CHANNEL (name, channel_number, favourite_channel) values (@P1,@P2,'Y')',N'@P1 nvarchar(13),@P2 int,@P3 char(1)',N'Animal Planet',102

OK, I've fixed this one - not quite in the way you're suggesting, but it should do the job.

2: Schedule recording (all episodes this channel) shows an CAST error.
Somewhere in your code there is an format conversion error where you try to convert between different formats. I am well avare that you only try the code on SQLite and access. Perhaps they are more forgiving. But if you find the error you might avoid future problems in the app.
It only shows whe trying to schedule (all episodes this channel) the other types of reocuring schedules work. What is the difference in how the app handles the varoius types of scheduling.

If you can tell me exactly what is wrong, or as much detail about this specific problem as you can, then I'll also try to correct this one. I wont be installing MSDE or SQL2005 Express to test this, so you'll need to give me all the information.