Am not to great on my SQL I have to admin so am coming here for some help.
I have a WCF web service that takes an XML call and returns an XML response containing data pulled from a DB for display to a user. This is a XML based event list pretty much.
There are a right few tables with the DB and I connect to them all to get data from each one. I am trying now to create a SQL "View" so when I run it, it will show me the same info as the web services pulls.
I just cant get the ****er to work. Below is the script I am using to select a load of tables
Any thing you guys can see am missing would be great. As it stands the above query returns no results. However If I remove the recording table it works fine.
Cheers in advance
I have a WCF web service that takes an XML call and returns an XML response containing data pulled from a DB for display to a user. This is a XML based event list pretty much.
There are a right few tables with the DB and I connect to them all to get data from each one. I am trying now to create a SQL "View" so when I run it, it will show me the same info as the web services pulls.
I just cant get the ****er to work. Below is the script I am using to select a load of tables
Code:
SELECT dbo.Fixture.FixtureId AS EventId, dbo.Fixture.StartTime, dbo.Fixture.EndTime, dbo.Fixture.Information, dbo.Fixture.Description,
dbo.Fixture.DataProviderId AS DataSourceId, dbo.Fixture.DataProviderItemId AS DataSourceItemId, dbo.Fixture.CompetitionId, dbo.Fixture.SeasonId,
dbo.Fixture.RoundId, dbo.Fixture.GroupId, dbo.Fixture.CreatedTimestamp AS Created, dbo.Fixture.ModifiedBy, dbo.Fixture.Status,
ISNULL(dbo.WTScheduledEvent.WTScheduledEventId, 0) AS WTScheduledEventId, ISNULL(dbo.WTScheduledEvent.CobainChannelId, 235) AS CobainChannelId,
dbo.Competition.CompetitionName AS Competition, dbo.Competition.SportId, dbo.Competition.DisplayOrder, dbo.Round.RoundName AS Round,
dbo.[Group].GroupName AS [Group], dbo.Season.SeasonName AS Season, ISNULL(dbo.Channel.ChannelName, 'Not Set') AS ServerName,
ISNULL(dbo.WTScheduledEvent.DecoderId, 112) AS DecoderId, ISNULL(dbo.BroadcastSource.Source, 'Not Set') AS Source, dbo.Sport.DisplayName AS Sport,
CONVERT(DATETIME, CONVERT(varchar(8), dbo.Fixture.StartTime, 112)) AS EventDate, dbo.EventCompetitors.Competitor2, dbo.EventCompetitors.Competitor1,
dbo.Channel.PackageId, dbo.Package.PackageName AS Package, ISNULL(dbo.WTScheduledEvent.Verified, 0) AS Verified, ISNULL(dbo.WTScheduledEvent.Cancelled,
0) AS Cancelled, ISNULL(dbo.WTScheduledEvent.InternalSchedule, 0) AS InternalSchedule, CASE WHEN SourceId = 427897 THEN CAST(1 AS bit) ELSE CAST(0 AS bit)
END AS StadiumFeed, dbo.WTScheduledEvent.Notes, dbo.Competition.AreaId, dbo.Area.Region, dbo.Area.ISOAlpha2Code, ISNULL(dbo.Fixture.Protected, 0)
AS Protected, ISNULL(dbo.WTScheduledEvent.RedirectId, 235) AS RedirectId, dbo.Fixture.ModifiedBy AS FixtureModifiedBy,
dbo.WTScheduledEvent.ModifiedBy AS WTScheduledEventModifiedBy, Archive.dbo.Recording.FileName, dbo.Round.StartDate, dbo.Round.EndDate
FROM dbo.Fixture LEFT OUTER JOIN
dbo.WTScheduledEvent ON dbo.Fixture.FixtureId = dbo.WTScheduledEvent.FixtureId INNER JOIN
dbo.Competition ON dbo.Fixture.CompetitionId = dbo.Competition.CompetitionId INNER JOIN
dbo.[Group] ON dbo.Fixture.GroupId = dbo.[Group].GroupId INNER JOIN
dbo.Round ON dbo.Fixture.RoundId = dbo.Round.RoundId INNER JOIN
dbo.Season ON dbo.Fixture.SeasonId = dbo.Season.SeasonId LEFT OUTER JOIN
dbo.BroadcastSource ON dbo.WTScheduledEvent.SourceId = dbo.BroadcastSource.BroadcastSourceId LEFT OUTER JOIN
dbo.Channel ON dbo.WTScheduledEvent.CobainChannelId = dbo.Channel.ChannelId INNER JOIN
dbo.Sport ON dbo.Competition.SportId = dbo.Sport.SportId INNER JOIN
dbo.EventCompetitors ON dbo.Fixture.FixtureId = dbo.EventCompetitors.eventId LEFT OUTER JOIN
dbo.Package ON dbo.Channel.PackageId = dbo.Package.PackageId INNER JOIN
dbo.Area ON dbo.Competition.AreaId = dbo.Area.AreaId INNER JOIN
Archive.dbo.Recording ON dbo.Channel.ChannelId = Archive.dbo.Recording.ChannelID
WHERE (dbo.Round.StartDate = CONVERT(DATETIME, '2012-10-12 00:00:00', 102)) AND (dbo.Round.EndDate = CONVERT(DATETIME, '2012-10-12 00:00:00', 102))Any thing you guys can see am missing would be great. As it stands the above query returns no results. However If I remove the recording table it works fine.
Cheers in advance