How to use MSDE(SQL Server) as GBPVR Database

[cginzel]

Okay KingArgyle,

I finally figured out what I was doing wrong in the MSDE install... I was including the quotes around the password as shown in the MSDE setup instructions!

Note to anyone doing install, used SAPSW=password without quotes around the password!

Here are the grants I used for all of the tables (I'm using gbpvr for the user id as suggested by KingArgyle):

GRANT SELECT, INSERT, UPDATE, DELETE ON CAPTURE_SOURCE TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON CHANNEL TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON CUSTOM_TASK_SETTINGS TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON DIRECT_RECORDING_SOURCE TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON EXE_CHANNEL_CHANGER TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON GIRDER_CHANNEL_CHANGER TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON GO7007SB_RECORDING_SOURCE TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON IR200L_CHANNEL_CHANGER TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON LIVETV_FILE TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON PLAYBACK_POSITION TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON PROGRAMME TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON RECORDING_SCHEDULE TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON RECORD_SEASON TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON REDRAT_CHANNEL_CHANGER TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON SOURCE_CHANNEL_MAPPING TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON USBUIRT_CHANNEL_CHANGER TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON WINTV_CAP_SOURCE TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON XMLTV_EPGSOURCE TO gbpvr
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON ZAP2IT_EPGSOURCE TO gbpvr
GO

However...  

I use Zap2It data and at the very end of the EPG Update, I get the following error...

"Failure in ChanneFactory:toreChannels(): Specified cast is not valid."

And off course there are no channels displayed after this...  

So KingArgyle, there must be an error in one of the Access type conversions...

I'm running GBPVR v0.23.13.

I ran your last set of scripts; I'm guessing one or the other of these tables are at fault...

CREATE TABLE [ZAP2IT_EPGSOURCE] (
[capture_source_oid] [int] NOT NULL ,
[username] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[add_offset] [int] NULL
) ON [PRIMARY]
GO


CREATE TABLE [CHANNEL] (
[oid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[channelID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[channel_number] [int] NULL ,
[favourite] [bit] NOT NULL
) ON [PRIMARY]
GO

Note: there is only one row in the CHANNEL table and no rows in the ZAP2IT_EPGSOURCE

Any suggestions are welcome...

-Charles

[cginzel]

Here is a snippet from cofig.exe.log:

12/20/2004 10:36:38 PM.031 INFO [241] Auto-Updating EPG
12/20/2004 10:36:38 PM.031 VERBOSE [241] FindLastListingDate() oid: 1
12/20/2004 10:36:38 PM.031 INFO [241] Requesting listings for period: 2004-12-20T00:00:00Z - 2004-12-28T00:00:00Z
12/20/2004 10:39:54 PM.078 VERBOSE [241] EPGUpdate storeChannels()
12/20/2004 10:39:54 PM.078 VERBOSE [241] getValue cached value: /settings/EPGProcessingMode : NORMAL
12/20/2004 10:39:54 PM.078 VERBOSE [241] getValue cached value: /settings/EPGProcessingMode : NORMAL
12/20/2004 10:39:54 PM.125 WARNING [241] Unexpected error removing old shows from EPG: Line 1: Incorrect syntax near 'p'.
12/20/2004 10:39:54 PM.218 ERROR [241] Failure in ChannelFactory:toreChannels(): Specified cast is not valid.
12/20/2004 10:39:54 PM.218 ERROR [241] Failure in ChannelFactory:toreChannels(): at a2.a(ICollection A_0, Int32 A_1)
12/20/2004 10:39:56 PM.500 VERBOSE [241] RecordingFactory.loadSchedule()

It almost looks like another Zap2It parsing problem, but when I switch back to using the access db connection, I can update the EPG with no problems...

[KingArgyle]

Check your SOURCE_CHANNEL_MAPPING table, I believe that is where the error is. I was getting the same error with .2313, and I had to change the nvarchar datatypes to varchar datatypes in this table and the Channels table.

Here is my table:
CREATE TABLE [dbo].[SOURCE_CHANNEL_MAPPING] (
[oid] [int] IDENTITY (1, 1) NOT NULL ,
[channel_oid] [int] NULL ,
[capture_source_oid] [int] NULL ,
[source_channel_number] [int] NULL ,
[enabled] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

The is is the Enabled field, is initally set to nvarchar, when it needs to be at least a varchar, probably should just be a char field as it is pointless to have a 1 character varchar field.

Once that is done, you should be able to run the Episode Guide update from the Config application or when the recording service runs it at 3am. The error message is kinda of misleading as to what the source of the problem is. It appears that after loading the program information, the source channel mappings are updated to make sure no new channels were added to the lineup since the guide was last downloaded.

[cginzel]

My table matches your table def...

Based on what you were saying I changed the nvarchar in ZAP2IT_EPGSOURCE like so...

CREATE TABLE [ZAP2IT_EPGSOURCE] (
[capture_source_oid] [int] NOT NULL ,
[username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[add_offset] [int] NULL
) ON [PRIMARY]
GO

but that didn't fix it either...

Are using Zap2It for your TV listing?

-Charles

[KingArgyle]

Yeah, on my Development machine, I'm using MSDE and the Zap2It episode guide. I tested the import last night, and it worked fine.

Send me an email, and I'll get you a stripped down version of the database tonight. Mount it and see if you can get my version working. You might have to change the Source_Channel_Mappings.

You could also delete all your data from the MSDE gbpvr, and create a DTS package, to migrate the data from your Access database, to the MSDE database. Once it is repopulated, rerun the Zap2It episode guide import.

The only tables I touched to get it to work were Channel, and Source_Channel_Mapping.

[KingArgyle]

Here are also a couple of links in regards to datatype differences between Access and SQL Server:

http://www.schemamania.org/jkl/booksonli...005_64.htm

http://www.databasejournal.com/features/...hp/1490561

[cginzel]

Thanks KingArgyle, I'll give the DTS a try when I get home and see how that goes...

-Charles

[KingArgyle]

If you use DTS, use the Bulk Data Copy method to copy the table data over. If you use the Transform methods, you'll have to get the order correct when loading the data because of the constraints. Easiest way to do this, is to just run the Import Data wizard, and it'll set it up for you.

I suspect a Identity column is not matching up, and causing the storechannel to fail.

[KingArgyle]

One more interesting twist I discovered tonight. Apparently sub is using the Access 2000 Now() function when scheduling season recordings. The equivalnt to this is getdate(). However, since the sql he is using is hard coded there isn't an easy way to change this. The result is that it doesn't appear that any Season recordings are getting rescheduled. I haven't totally verified this yet, but the logs show an error occuring when trying to schedule season recordings.

I'm working on an external scheduler that can be run alongside the GBPVR scheduler. This should make sure that season recordings happen regardless.

This is the one thing I don't like about Databases and SQL. Core language the databases are syntax compatible, but start adding functions, and you get a incompatiblities moving sql from one database to another.

[cginzel]

My first thought was to wrap getdate() inside of a function called now(), but getdate() is non-deterministic and therefore cannot be used inside of a function...

i.e. this doesn't compile:

CREATE FUNCTION dbo.Now() RETURNS datetime
AS
BEGIN
DECLARE @current_dt datetime
SELECT @current_dt = getdate()
RETURN @current_dt
END