SQL Server + копирование = connection string

← →
Николай2008 (2008-08-05 12:35) [0]

Здравствуйте! Есть один вопрос по OLE DB и SQL Server 2005...

Есть программа, написанная на Delphi. Работает с БД на Access (пока как локальный вариант). Программа в том числе реализует следующую функцию - перепись данных из одной БД Access в другую. Здесь проблем нет - имеется подключенная БД, расположение файла второй известно, в SQL-запросе используем строку подключения (ADO, MS Jet) к другой БД на Access, например:
(все происходит с ADOQuery, уже подключенной к одной из БД)

s:="select a.col1 from TEST a in "+connection_s+" where not exists (select b.col1 from TEST b where a.col1=b.col1)
SQL.Add(s);
Open...

не стал приводить пример со вставкой, этот сравнивает содержимое двух таблиц двух БД. Путь ко второй БД задается connection_s, например:

connection_s:="Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=file.mdb;Mode=Share Deny None;Extended Properties=""..." и т.д.

Все работает надежно.
Теперь корректировка задачи: перепись данных, как сейчас между 2 БД Access может дополниться переписью из БД Access в БД SQL Server 2005 (и обратно). Такой вопрос - не меняя механизм кардинально, а лишь скорректировав его, зная предварительно путь (алиас) второй БД и ее тип (придется переписывать в Access или SQL Server), можно для учета случая с SQL Server подставить в приведенный выше пример свой connection_s (в зависимости от условий)? Ясно, что подключение к сетевой БД будет уже не через MS Jet, а, например, через Ole DB. Пробовал экспериментировать, ознакомившись с msdn, но пока что-то не вышло.

Попытки с
Provider=SQLOLEDB.1;Password=+++;User ID=sa;Initial Catalog=db;Data Source=host\SQLEXPRESS...
успехом не увенчались.

конечно, прямая смена connection_s с Access"овской на Sql Server не пройдет. Это и понятно - MS Jet не коннектиться к SQL, как к Access"у. В строке поменял провайдера и все параметры (строку приводил выше). Все проверено - подключение к SQL выполняется и через ODBC (в программе не использую), и через AdoConnection с указанием провайдера. К тому же SQL у меня висит на отдельном порте, так что в строке подключения еще и порт - все нормально. Это что касается подключения отдельно.

Когда же я, с помощью AdoQuery, подключенной к Access"овской БД пытаюсь (с использованием строки подключения) выбрать данные из второй БД на SQL, то вот здесь-то и проблема.

Наиболее часто встрчающееся сообщение "Не могу найти такой-то файл <дальше весь мой connection string>" (как будто строка подключения к SQL воспринимается просто как указание имени файла)) Здесь можно думать на то, что неправильно указан префикс перед строкой подключения. Например, для MS Jet он в запросе указывается как "in", т.е.
select ... from <table> in "Provider=MS Jet.... DataSource=<файл mdb>..." where....
тогда он ищет access"овскую БД.

С MS SQL такой фокус не проходит. Если подсовывать ему in "<строка подключения>" - такое впечатление, что начинает искать файл, об отсутствии которого и выдает сообщение. На всякие @ т.д. перед строкой подключения и (как рекомендовали на иностранных форумах) реагирует так - ошибка "неверный символ в SQL-запросе после SELECT FROM".

Вот пока провожу эксперименты параллельно с основным делом, успеха не достиг. Хотя дело, может быть в ключевом слове или его расположении (написании)... Строка подключения верная, рабочая, да и в любом случае ошибка выдается именно при распознавании строки. Хотя если эту строку прописать просто в AdoConnection в этой е программе - подключение проходит на "ура". проблема именно при ссылки на вторую БД в тексте запроса с помощью этой строки подключения.

Подскажите, может ли подойти технология, приведенная выше для сравнения и копирования данных не только между двумя БД Access, но и Access и SQL Server или зря пытаюсь? Когда-то делали подобное, ссылаясь на вторую БД (непосредственно в рамках запроса) через алиас ODBC, но пока пытаюсь обойтись без него....

Если доработка со строкой подключения ко второй БД в запросе не удастся, то скорее всего буду пробовать с OPENROWSET(), также указывая подключеение ко второй БД в запросе. Переделки существующего алгоритма будут уже более серьезные.....

← →
Ega23 © (2008-08-05 12:46) [1]

Много букф.
Я бы посоветовал либо в сторону OpenRowSet копнуть, либо в сторону DTS.

← →
Medbe}I{onok XML © (2008-08-05 12:46) [2]

а что так много букв-то? чего надо-то?

← →
Николай2008 (2008-08-05 13:01) [3]

скорее всего OpenRowSet придется...

а много букв потому, что на нескольких уважаемых форумах не приняли краткого объяснения задачи (что удивило) - попросили дополнительные сведения, которые я на всякий случай включил в вопрос здесь.
Хотя все было предельно просто: есть программа, осуществляет своими средствами перепись из одной БД Access в другую (обращение ко второй БД в теле запроса, пример выше). Задача расширяется на SQL Server (опционально) на такую же БД по структуре. Как оптимальнеее сделать, чтобы программа осуществляла перепись между Access"ами и SQL (все направления). Стоит ли модернизировать существующий способ или сразу переделать с "чистого листа".

← →
Medbe}I{onok XML © (2008-08-05 13:03) [4]

прилинкованный к мсскл акцесс не проще?

← →
Ega23 © (2008-08-05 13:05) [5]

> Как оптимальнеее сделать, чтобы программа осуществляла перепись
> между Access"ами и SQL (все направления). Стоит ли модернизировать
> существующий способ или сразу переделать с "чистого листа".
>

Вопрос сложный. Зависит от многих факторов.
Например:
1. Чья БД "первичная" - Access или MSSQL
2. Топология серверов в сети: внутренняя локалка, или какая-то распределённая сеть с доступом к некоторым узлам через какой-нибудь спутниковый канал.
3. Объёмы и интенсивность передаваемых данных

и т.п.

← →
Николай2008 (2008-08-05 13:22) [6]

пока первичные access"овские, но со временем возникнет вопрос центральной. SQL в офисе, Access - для выполнения определенных задач вне его.
Никакой дополнительной связи. Наполнили Access (если вне офиса), подготовили, проверили данные, в определенный момент скинули в центральную (SQL).
Сеть - только локалка в офисе. Удаленных подключений нет.
Число процедур "копирования" из базы в базу - несколько раз в месяц.
Количество автономных Access"овских БД - до 10ти. Контроль всех главных справочников БД (на совместимость данных) уже есть.
Объем копируемой информации за один раз - максимум 10000-15000 строк. Быстродействие некритично. Приоритет - надежность.
Единственно, что можно еще упомянуть - вместо SQL заказчику может захотеться Oracle, , так что сразу приходится предусматривать более гибкий способ. Аппетит приходит во время еды :)

← →
Medbe}I{onok XML © (2008-08-05 13:31) [7]

Наполнили Access (если вне офиса),

Да привези ты его целиком в офис и крути/верти как захочешь

← →
Николай2008 (2008-08-05 13:48) [8]

к сожалению, не мой вопрос. Если все по уму, то можно даже на периоды вне офиса на ПК ставить тот же SQL, без Access"а всякого. Или четко зафиксировать, что перепись будет только из Access в SQL.

Но прототип системы (по многим причинам) был именно с использование Access, Центральная сетевая БД вообще по постановке сначала не предполагалась, все сваливалось бы в одну access (как а архив под контролем ответственного) и вопрос закрыт.

Но требования растут, преимущества сетевой СУБД (для работы в офисе) после определенного момента оспаривать вряд ли кто будет, поэтому приходится готовиться.

Я прекрасно понимаю. что в процессе такой адаптации приходится решать "всплывающие" задачи, которые раньше (в силу постановки, по крайней мере на определенный срок) даже не возникали (хотя я предупреждал:)). Изначально задачей создания программы была отработка разных там решений и методик, локальная БД подходила лучше всего. Вот часть отработали и приходится думать о расширении.

MS SQL напрямую не поддерживает гетерогенные запросы (вот такая она сволоцюга), а только через Data Transport Service (DTS), в акцесе же ненути этого механизма, зато есть гетерогенка с "внешней БД" акцес.
Чтобы делать переливки данных универсально лучше всего написать приложение, которое с помощью двух коннектов и двух датасетов будет поочередно читать исходные таблицы и в цикле все ее записи добавлять в предварительно очищенные результирующие. В результирующей БД для перегоняемой таблицы перед началом вставок отключить счетчики если есть в этом необходимость (межтабличные связи на автоинкременте).

Николай2008 (05.08.08 13:48) [8]
Можно акцесс подключить к MSSQL как Linked server.

← →
Николай2008 (2008-08-23 13:08) [11]

Я понял ответы, спасибо. Давайте подведу итог (по основным принципам) и спрошу еще мнения. Итак, постановка:

1. Программа на Дельфи используется сейчас и работает с БД (Access) через ADO.

2. Программа имеет функцию переписи информации из одной БД в другую (структура совпадает) с предварительной проверкой данных в главных "справочниках". Между 2мя Access"овскими БД программа проверяет идентичность и переписывает с помощью запросов вида
"select a.col1 from TEST a in "+connection_s+" where not exists (select b.col1 from TEST b where a.col1=b.col1), где connection_s путь ко второй БД.

3. За содержанием справочников следит специальный человек, изменения проводятся редко. Будет использовано максимум 6-8 копий программ (с БД) на локальных машинах, причем только одна будет играть роль "БД архива", куда пользователи будут складывать подготовленные и уже сданные результаты. После этого БД на тех ПК, где шла подготовка данных могут вообще обнуляться, т.е. задачи хранения на них нет.

4. Многие нелегкие вопросы, касающиеся функционала, еще далеко не доработаны, заказчик корректирует задачи, иногда приходится менять и структуру БД. В такой ситуации, с нашей т.з. ставить вопрос о развертывании сетевой версии рано (см. еще P.S.).

5. Некоторое время работа на 6-8 ПК запускаться не будет (по нашему совету), т.к. надо доработать и доопределить определенные вещи.

6. Никакой удаленной связи и т.д. баз не планируется.

7. Количество записей в БД небольшое (см. выше).

8. Несмотря на то, что прототип программы надо еще доработать, уже всплывает вопрос о сетевой БД. Причем там нужно сохранить и возможность работы и с Access, и с сетевой. При работе пользователя проблем нет, а вот при переписи данных из БД в БД - вот здесь нужен выработанный подход. Например, копирование Access->SQL Server, Access->Access. Подробно все описано выше.

9. Сетевая БД еще не определена - может Access, SQL, Oracle, DB2 - не наш вопрос. Изначально постановка была - программа с локальной БД, аппетит растет во время еды.

10. Вообще пока мало определено по этому вопросу, я, имея некоторый опыт, в т.ч. научившись на ошибках, просто пытаюсь предусмотреть возможность использования сетевой БД (как централизованной), не исключая при этом, что определенная работа будет проводится и с локальной, а потом данные будут переброшены в центральную. Если при правильном подходе к проектированию БД, использованию типов данных и т.д. программа основные задачи выполнять будет, то вот с копированием между разными базами может быть все не так просто.

Поэтому, если я переделаю (к сожалению, т.к. в рамках существовавшей постановке механизм отлично работает) проверку справочников на идентичность и копирование информации из БД в БД средствами Дельфи, т.е. подключить две ADOQuery к двум БД и просто в цикле сверить, а при совпадении и провести копирование требуемых данных - можно сказать, что это будет не совсем оптимально, но снимет вопрос об использовании различных БД? Или слишком коряво по мнению профессионалов? Хочу узнать Ваше мнение.

P.S. еще следует принять во внимание тот факт, что программа разрабатывается удаленно, никто из нас на месте, где будет развернута (может быть) сетевая версия, присутствовать не будет, так что за нее будет отдельный ответственный из числа имеющегося персонала.

Почему бы вообще не отказаться от акцесс и не использовать MS SQL SERVER для начала можно бесплатную версию Express, которая расчитана на небольшое количество пользователей.
То что я описывал в [10] не требует никакого копирования, но неочень правильно, это можно использовать если по каким-то причинам нельзя отказаться от акцесс.

SQL Server + копирование = connection string Найти похожие ветки