==Edited to include outputs from each server==
==Edited to include additional table definition information==
I am attempting to integrate an application running on a SQL 2008R2 database, with a new application that runs on a SQL Server 2017 database.
This is undertaken by SQL scripts that are run as stored procedures on the 2017 database to copy information across from the 2008 database.
The SQL script below works perfectly fine on the 2008R2 database (in management studio 2014) and uses the for XML command to produce a string list of 1's and 0's that correspond to a week that an activity occurs. 1= occurs, 0 = does not occur, with this script being part of a larger SQL script.
When I run this script within SQL management Studio 17 on a 2017 server with the 2008R2 database setup as a linked server, the script runs but the FOR XML export just returns a sting of 0's and is not working as expected.
I've looked into the For XML command and I am not aware of it acting any differently on different versions on SQL server.
I also have another 10-15 integration scripts (though none of the others use the for xml command), that work perfectly well between the 2008 and 2017 database where the 2008 database is a linked server.
I can individually return the information from the tables via the linked server, but when I attempt to run the query the activity id returns successfully but the code string does not.
I am having to use the for XML script as the old database records each occurrence of an activity as an individual line, while the new system records one record for the activity and then records a string of 0's and 1's that work as a week pattern to say if an activity occurs or not.
I don't know if it is the use of the for xml command itself or the fact that its being run via a linked server.
In the script below I have removed the references for the linked server and the database name for security reasons, but as mentioned the script works perfectly fine in my 2008R2 environment.
When run in 2008 I receive the below output
+------------+-------------------------------------------------+
| activityid | code |
+------------+-------------------------------------------------+
| 59936 | 11111110111111100000000000000000000000000000000 |
+------------+-------------------------------------------------+
When Run in 2017 I receive the following output
+------------+-------------------------------------------------+
| activityid | code |
+------------+-------------------------------------------------+
| 59936 | 00000000000000000000000000000000000000000000000 |
+------------+-------------------------------------------------+
The vw_AcademicWeeks element is a view which picks up the following information
+----------------+-------------+
| Field | Type |
+----------------+-------------+
| ay_code | varchar(4) |
| week_number | int |
| ay_start | date |
| ay_end | date |
+----------------+-------------+
This returns for each week within an academic year the start and end date of the week (example shown below)
+---------+---------+------------+------------+
| ay_code | week_no | ay_start | ay_end |
+---------+---------+------------+------------+
| 1718 | 1 | 01/08/2017 | 06/08/2017 |
| 1718 | 2 | 07/08/2017 | 13/08/2017 |
| 1718 | 3 | 14/08/2017 | 20/08/2017 |
| 1718 | 4 | 21/08/2017 | 27/08/2017 |
+---------+---------+------------+------------+
The TT_Activity table is setup as below
+----------------------+-----------+
| Colum Name | Data Type |
+----------------------+-----------+
| ActivityOccurrenceID | int |
| ActivityID | int |
| StartTime | datetime |
| EndTime | datetime |
+----------------------+-----------+
This table contains multiple rows for an activity, with different start and end times i.e. if an activity occurs every day at 9am, there would be five entries for a week
+----------------------+------------+---------------------+---------------------+
| ActivityOccurrenceID | ActivityID | StartTime | EndTime |
+----------------------+------------+---------------------+---------------------+
| 2214753 | 65577 | 12/07/2019 13:30:00 | 12/07/2019 14:30:00 |
| 2214752 | 65577 | 05/07/2019 13:30:00 | 05/07/2019 14:30:00 |
| 2214906 | 65583 | 02/07/2019 14:30:00 | 02/07/2019 16:00:00 |
| 2215967 | 65613 | 02/07/2019 14:30:00 | 02/07/2019 16:00:00 |
| 2226569 | 65949 | 02/07/2019 14:30:00 | 02/07/2019 16:00:00 |
| 2226754 | 65963 | 02/07/2019 14:30:00 | 02/07/2019 16:00:00 |
+----------------------+------------+---------------------+---------------------+
The TT_Activity field contains the basic information for an activity and contains a single record for each activity
+-------------+--------------+
| Colum Name | Data Type |
+-------------+--------------+
| ActivityID | int |
| Code | varchar(40) |
| Description | varchar(255) |
| PeriodID | int |
+-------------+--------------+
Which contains the following information
+------------+---------+-------------+----------+
| ActivityID | Code | Description | PeriodID |
+------------+---------+-------------+----------+
| 20668 | Maths | Maths | 2017 |
| 20669 | English | English | 2017 |
| 20670 | Science | Science | 2017 |
+------------+---------+-------------+----------+
==SQL Query Below==
select
tta2.activityid,
(
select
case when ttao.endtime is null then '0' else '1' end
from
vw_AcademicWeeks aw
left join
TT_ActivityOccurrence ttao
on
(dateadd(dd,datediff(dd,0,DATEADD(dd, -(DATEPART(dw, ttao.StartTime)-1), ttao.StartTime)),0)) = aw.ay_start
and ay_code='1718'
and ttao.ActivityID=tta2.ActivityID
where
aw.week_no>=6
group by
ttao.ActivityID,
aw.week_no,
case when ttao.endtime is null then '0' else '1' end
having
count(aw.week_no)<>9
order by
week_no asc
FOR XML PATH(''))as code
from
TT_Activity tta2
where tta2.PeriodID='2017'
Having looked at the code again and pulling it apart I've found the cause of the issue.
The language of the 2008 R2 server was set as British, while the language of the 2017 server was set as US-English.
This was causing the vw_AcademicWeeks view to create start and end dates of a week that were wrong, as such the formula string below was returning the incorrect date which was then not matching up.
TT_ActivityOccurrence TTAO ON (dateadd(dd, datediff(dd, 0, DATEADD(dd, - (DATEPART(dw, ttao.StartTime) - 1), ttao.StartTime)), 0)) = aw.ay_start