==Edited to include outputs from each server==
==Edited to include additional table definition information==
Я пытаюсь интегрировать приложение, работающее в базе данных SQL 2008R2, с новым приложением, работающим в базе данных SQL Server 2017.
Это выполняется сценариями SQL, которые запускаются как хранимые процедуры в базе данных 2017 года для копирования информации из базы данных 2008 года.
Приведенный ниже SQL-скрипт прекрасно работает с базой данных 2008R2 (в management studio 2014) и использует команду for XML для создания строкового списка из 1 и 0, соответствующих неделе, когда происходит действие. 1= происходит, 0 = не происходит, при этом этот сценарий является частью более крупного сценария SQL.
Когда я запускаю этот сценарий в SQL Management Studio 17 на сервере 2017 года с настройкой базы данных 2008R2 в качестве связанного сервера, сценарий запускается, но экспорт FOR XML просто возвращает нулевое значение и не работает должным образом.
Я изучил команду For XML и не знаю, действует ли она по-разному в разных версиях SQL-сервера.
У меня также есть еще 10-15 сценариев интеграции (хотя ни один из них не использует команду for xml), которые отлично работают между базами данных 2008 и 2017 годов, где база данных 2008 года является связанным сервером.
Я могу индивидуально вернуть информацию из таблиц через связанный сервер, но когда я пытаюсь выполнить запрос, идентификатор активности возвращается успешно, а строка кода - нет.
Мне приходится использовать сценарий for XML, так как старая база данных записывает каждое появление действия в виде отдельной строки, в то время как новая система записывает одну запись для действия, а затем записывает строку 0 и 1, которые работают как недельный шаблон для сказать, если действие происходит или нет.
Я не знаю, связано ли это с использованием самой команды for xml или с тем, что она запускается через связанный сервер.
В приведенном ниже сценарии я удалил ссылки на связанный сервер и имя базы данных по соображениям безопасности, но, как уже упоминалось, сценарий отлично работает в моей среде 2008R2.
При запуске в 2008 году я получаю вывод ниже
+------------+-------------------------------------------------+
| activityid | code |
+------------+-------------------------------------------------+
| 59936 | 11111110111111100000000000000000000000000000000 |
+------------+-------------------------------------------------+
При запуске в 2017 году я получаю следующий вывод
+------------+-------------------------------------------------+
| activityid | code |
+------------+-------------------------------------------------+
| 59936 | 00000000000000000000000000000000000000000000000 |
+------------+-------------------------------------------------+
Элемент vw_AcademicWeeks — это представление, которое собирает следующую информацию.
+----------------+-------------+
| Field | Type |
+----------------+-------------+
| ay_code | varchar(4) |
| week_number | int |
| ay_start | date |
| ay_end | date |
+----------------+-------------+
Это возвращает для каждой недели в течение учебного года дату начала и окончания недели (пример показан ниже).
+---------+---------+------------+------------+
| 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 |
+---------+---------+------------+------------+
Таблица TT_Activity настроена, как показано ниже.
+----------------------+-----------+
| Colum Name | Data Type |
+----------------------+-----------+
| ActivityOccurrenceID | int |
| ActivityID | int |
| StartTime | datetime |
| EndTime | datetime |
+----------------------+-----------+
Эта таблица содержит несколько строк для действия с разным временем начала и окончания, т. е. если действие происходит каждый день в 9 утра, для недели будет пять записей.
+----------------------+------------+---------------------+---------------------+
| 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 |
+----------------------+------------+---------------------+---------------------+
Поле TT_Activity содержит основную информацию о действии и содержит одну запись для каждого действия.
+-------------+--------------+
| Colum Name | Data Type |
+-------------+--------------+
| ActivityID | int |
| Code | varchar(40) |
| Description | varchar(255) |
| PeriodID | int |
+-------------+--------------+
Который содержит следующую информацию
+------------+---------+-------------+----------+
| ActivityID | Code | Description | PeriodID |
+------------+---------+-------------+----------+
| 20668 | Maths | Maths | 2017 |
| 20669 | English | English | 2017 |
| 20670 | Science | Science | 2017 |
+------------+---------+-------------+----------+
==SQL-запрос ниже==
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'
