#sql-server #sp-msforeachdb
#sql-server #sp-msforeachdb
Вопрос:
Мне нужно создать представление или табличную функцию, которая возвращает один набор результатов из запроса, выполняемого с динамическим списком баз данных (хранящимся в таблице). Все базы данных имеют одинаковую структуру, и view / tvf должен содержать эквивалент объединения всех запросов для каждой базы данных в databases_table . Общая картина всего этого заключается в том, что мне нужно, чтобы результаты этого запроса были доступны через ODBC.
После долгих поисков в Интернете я придумал несколько потенциальных решений, но ни одно из них не идеально подходит для того, чего я пытаюсь достичь.
Первое, на что я обратил внимание, это наличие представления с выбором для каждой отдельной базы данных, все объединены вместе. Это сработало отлично, за исключением того факта, что оно настолько далеко от того, чтобы его можно было поддерживать, насколько я могу себе представить. У меня будет 25-100 таких запросов с несколькими базами данных, все из которых должны выполняться с постоянно меняющимся набором баз данных.
Я все еще рассматриваю возможность использования этого метода и создания хранимых процедур для динамического создания представлений для меня, но это все еще не очень полезно, так как меня попросили сделать весь этот процесс без помощи рук. Выполнение этого таким образом потребовало бы, чтобы кто-то запустил обновление представления, прежде чем мы попытаемся подключиться к представлениям через ODBC. Я хотел бы избежать этого, если это вообще возможно.
Второе, что я изучил, помогло мне намного приблизиться к тому, что мне было нужно. Я использовал хранимую процедуру sp_msforeachdb, проверил, есть ли база данных в списке, и если да, то добавил результаты запроса в табличную переменную. Это работает просто отлично, но проблема заключалась в том, что динамический sql не позволяет мне обернуть его в табличную функцию или представление…
Declare @RETURNTABLE Table(variable1 varchar(20), variable 2 varchar(30))
INSERT INTO @RETURNTABLE
exec sp_msforeachdb 'IF "?" IN (SELECT DatabaseName FROM DatabaseList) BEGIN SELECT "?" [DatabaseName], variable1 , variable2 from [?].dbo.myTable END'
SELECT * FROM @DBINFO
Это отлично работает при запуске из запроса из ms ssms, но, как я уже сказал, поместить его в veiw или tvf оказалось выше моих возможностей. Насколько мне известно, динамический SQL исключает использование tvf, а представления не могут работать с переменными.
Есть ли какой-нибудь способ, которым я могу сделать результаты приведенного выше кода доступными через ODBC? Есть ли альтернативное решение, которого мне не хватает?
Комментарии:
1. Я бы сказал, что это явный показатель того, что ваш дизайн базы данных нуждается в рефакторинге.
2. Я не думаю, что вы можете сделать это с помощью view или tvf, только sp будет делать то, что вы хотите.
3. @HLGEM Я не могу провести рефакторинг, базы данных такие, какие они есть, и я должен работать с ними как есть.
Ответ №1:
Насколько я могу судить, сохраненный процесс с использованием динамического SQL — ваш единственный выбор.
Комментарии:
1. Я уже поместил его в хранимую процедуру, и это работает просто отлично. Моя проблема в том, что мне нужно получить возвращаемые данные, доступные через ODBC. Я мог бы изменить SQL для выполнения вставки в таблицу, предназначенную для хранения информации, но тогда возникает вопрос, как обеспечить, чтобы информация всегда обновлялась на случай, если кто-то обращается к ней через ODBC. Есть ли какой-нибудь способ «обернуть» результаты sproc в представление?
2. Нет, это не так. почему они не могут просто вызвать сохраненный процесс через ODBC?
3. Причина этого заключается в том, что стороннее приложение может импортировать данные в свою собственную базу данных. Стороннее приложение настроено только для подключения к таблицам или представлениям, а не к sprocs. Я думаю, что мне, возможно, придется создать пустую версию базы данных, а затем разработать службу для управления информацией, которая хранится внутри. Это все еще не идеально, но не похоже, что есть лучший способ, доступный для меня. Тем не менее, все еще открыт для предложений.
4. Прошло много времени, но я решил принять этот ответ. Если бы не ряд довольно специфических обстоятельств, это сработало бы для меня. В конце концов, я перестал пытаться заставить импорт в стороннем программном обеспечении работать, и я разработал сервис, который отслеживал исходные базы данных на предмет изменений, а затем обновлял стороннюю базу данных при обнаружении изменений. Для реализации потребовалось гораздо больше работы, но это было самое чистое и наиболее доступное для меня решение.