TV-Server on WHS - Collection of fixes and workarounds (9 Viewers)

[larry_S]

AW: TV-Server on WHS - Collection of fixes and workarounds

The think something with the SQL-Statement does not work.
You can send me the Entries with "Copy & Paste" from the management Studio.

At the moment I reinstall my Home Server. So i can't test the SQL Statement.
I will test it this evening.

 

[vvulture]

Re: AW: TV-Server on WHS - Collection of fixes and workarounds

Ok.
I have put my schedules into a txt file ( attached ) by copying and pasting from DB.
Let me know if its no good...

Again, thank you very much for trying to sort this out for me... You are very kind.

Cheers

 

[vvulture]

Re: AW: TV-Server on WHS - Collection of fixes and workarounds

Can you test the following:
Delete the schedule tasks. Create only one Single Scheduled Task.

Larry, just for the hell of it i tried this again... Did not work..

 

[yhoogi]

Just one though wich crossed my mind - even if this is very unlikely:

What date ordering settings do you have day/month/year (European) or month/day/year (as used in the US)?
Altough I doubt it: how is this local date handling processed?

Cheers,

 

[larry_S]

AW: TV-Server on WHS - Collection of fixes and workarounds

The Problem is the SQL-Statement.
I work on it. If the Correction is done, i will insert the Update (new SQL) here.

 

[larry_S]

AW: TV-Server on WHS - Collection of fixes and workarounds

vvulture
Try to replace the part with the SQL-Statement.

Here is the new code:

set rs1=db1.execute("select channel, programName, startTime, endTime, preRecordInterval, postRecordInterval , dateadd(mi, (preRecordInterval + "&Zeitdiff&") * -1, startTime) as Start " & _
                          "from " & _
                          "(/* 0 = einmalig */ " & _
                          "  select c.name as channel, s.programName, s.startTime, s.endTime, s.preRecordInterval, s.postRecordInterval " & _
                          "  from schedule s " & _
                          "  inner join channel c " & _
                          "        on c.idChannel = s.idChannel " & _
                          "  where s.scheduleType = 0 " & _
                          "Union " & _
                          "/* 1 = täglich zu dieser Zeit */ " & _
                          "  Select c.name as channel, s.programName, dateadd(hh,datepart(hh,s.starttime),dateadd(n,datepart(n,s.starttime),start)) as startTime, dateadd(hh,datepart(hh,s.Endtime),dateadd(n,datepart(n,s.Endtime),start)) as EndTime, s.preRecordInterval, s.postRecordInterval " & _
                          "  from schedule s " & _
                          "  inner join channel c " & _
                          "        on s.idChannel = c.idChannel " & _
                          "  inner join (Select CONVERT(varchar(10), startTime, 104) as start " & _
                          "              from program " & _
                          "              group by CONVERT(varchar(10), startTime, 104)) d " & _
                          "        on 1=1 " & _
                          "  where s.scheduleType = 1 " & _
                          "union " & _
                          "/* 2 = wöchentlich zu dieser Zeit */ " & _
                          "  Select c.name as channel, s.programName, dateadd(hh,datepart(hh,s.starttime),dateadd(n,datepart(n,s.starttime),start)) as startTime, dateadd(hh,datepart(hh,s.Endtime),dateadd(n,datepart(n,s.Endtime),start)) as EndTime, s.preRecordInterval, s.postRecordInterval " & _
                          "  from schedule s " & _
                          "  inner join channel c " & _
                          "        on s.idChannel = c.idChannel " & _
                          "  inner join (Select CONVERT(varchar(10), startTime, 104) as start " & _
                          "              from program " & _
                          "              group by CONVERT(varchar(10), startTime, 104)) d " & _
                          "        on datepart(dw,d.start)= datepart(dw,s.starttime) " & _
                          "  where s.scheduleType = 2 " & _
                          "union " & _
                          "/* 3 = immer auf diesem Kanal */ " & _
                          "  Select c.name as channel, s.programName, p.startTime, p.EndTime, s.preRecordInterval, s.postRecordInterval " & _
                          "  from schedule s " & _
                          "  inner join program p " & _
                          "        on p.title = s.programName " & _
                          "        and s.idChannel = p.idChannel " & _
                          "  inner join channel c " & _
                          "        on  c.idChannel = s.idChannel " & _
                          "  where s.scheduleType = '3' " & _
                          "union " & _
                          "/* 4 = immer auf allen Kanälen */ " & _
                          "  Select c.name as channel, s.programName, p.startTime, p.EndTime, s.preRecordInterval, s.postRecordInterval " & _
                          "  from schedule s " & _
                          "  inner join program p " & _
                          "        on p.title = s.programName " & _
                          "  inner join channel c " & _
                          "        on  c.idChannel = s.idChannel " & _
                          "  where s.scheduleType = '4' " & _
                          "union " & _
                          "/* 5 = Samstags und Sonntags */ " & _
                          "  Select c.name as channel, s.programName, p.startTime, p.EndTime, s.preRecordInterval, s.postRecordInterval " & _
                          "  from schedule s " & _
                          "  inner join program p " & _
                          "        on p.title = s.programName " & _
                          "        and s.idChannel = p.idChannel " & _
                          "		and CONVERT(varchar(8), s.startTime, 108) = CONVERT(varchar(8), p.startTime, 108) " & _
                          "  inner join channel c " & _
                          "        on  c.idChannel = s.idChannel " & _
                          "  where s.scheduleType = '5' and datepart(dw,p.starttime) between 6 and 7 " & _
                          "union " & _
                          "/* 6 = Montags bis Freitags */ " & _
                          "  Select c.name as channel, s.programName, p.startTime, p.EndTime, s.preRecordInterval, s.postRecordInterval " & _
                          "  from schedule s " & _
                          "  inner join program p " & _
                          "        on p.title = s.programName " & _
                          "        and s.idChannel = p.idChannel " & _
                          "		and CONVERT(varchar(8), s.startTime, 108) = CONVERT(varchar(8), p.startTime, 108) " & _
                          "  inner join channel c " & _
                          "        on  c.idChannel = s.idChannel " & _
                          "  where s.scheduleType = '6' and datepart(dw,p.starttime) between 1 and 5 " & _
                          ") as ua " & _
                          "where dateadd(mi, (postRecordInterval + 3), EndTime)> current_Timestamp " & _
                          "order by 3 ")

 

[vvulture]

Re: AW: TV-Server on WHS - Collection of fixes and workarounds

Just one though wich crossed my mind - even if this is very unlikely:

What date ordering settings do you have day/month/year (European) or month/day/year (as used in the US)?
Altough I doubt it: how is this local date handling processed?

Cheers,

Hi yhoogi,
I am using DD/MM/YYYY as in european.

@vvulture
Try to replace the part with the SQL-Statement.

Thank you very much Larry... I will have to wait until this afternoon.. Have to go to work now.

Cheers

 

[vvulture]

Hi Larry, i have tried your SQL changes.
We have some improvement.
The script can now 'see' my 'type 3' schedules in the database which are dated in the past. This was not the case before. Good news !
However, the MP_Aufnahmen task is still not updated.

I have attached a copy of my edited script for you to look at, as well as a copy of my log and a screenshot of my scheduled tasks.

cheers

 

[larry_S]

AW: TV-Server on WHS - Collection of fixes and workarounds

Ok, Step by Step

I think there is a Problem with the Command Line Parameters.
In the Script you find following commandline:

intReturn = WshShell.Run("SCHTASKS /change /ru "&lcase(net.computerName)&"\"&PLUser&" /RP "&chr(34)&PLPW&chr(34)&" /TN MP_Aufnahmen /ST "&right(rs1("Start"),8)&" /SD "&replace(left(rs1("Start"),10),".","/") , 1, true)

Add (before or after) this code:

Logdatei "SCHTASKS /change /ru "&lcase(net.computerName)&"\"&PLUser&" /RP "&chr(34)&PLPW&chr(34)&" /TN MP_Aufnahmen /ST "&right(rs1("Start"),8)&" /SD "&replace(left(rs1("Start"),10),".","/")

Then you can see the complete commanline at the logfile. Then you can enter (copy&paste) this command in an CMD-Windows. After execute this line, you will get an Error Message. From this you can make a Screenshot.
Also you can make a Screenshot from the command "SCHTASKS /?". This will show the correct commandline Parameters.

 

[vvulture]

Re: AW: TV-Server on WHS - Collection of fixes and workarounds

Ok, Step by Step

I think there is a Problem with the Command Line Parameters.
In the Script you find following commandline:

intReturn = WshShell.Run("SCHTASKS /change /ru "&lcase(net.computerName)&"\"&PLUser&" /RP "&chr(34)&PLPW&chr(34)&" /TN MP_Aufnahmen /ST "&right(rs1("Start"),8)&" /SD "&replace(left(rs1("Start"),10),".","/") , 1, true)

Add (before or after) this code:

Logdatei "SCHTASKS /change /ru "&lcase(net.computerName)&"\"&PLUser&" /RP "&chr(34)&PLPW&chr(34)&" /TN MP_Aufnahmen /ST "&right(rs1("Start"),8)&" /SD "&replace(left(rs1("Start"),10),".","/")

Then you can see the complete commanline at the logfile. Then you can enter (copy&paste) this command in an CMD-Windows. After execute this line, you will get an Error Message. From this you can make a Screenshot.
Also you can make a Screenshot from the command "SCHTASKS /?". This will show the correct commandline Parameters.

Hi Larry..

I will try this as soon as i get home from work, hopefully in the next 2 hrs or so...

cheers