15.07.2005 18:51 |
|
Admin
Регистрация: 05.10.2005
Проживание: Москва
Сообщения: 284
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
Есть полезная функция - OPENQUERY (LinkedServerName, Query), позвляет выполнять запросы из любого источника данных, используя Linked Server.
Рассмотрим пример с dbf-ными таблицами от FoxPro.
1. Добавляем Linked Server.
DECLARE @pr nvarchar(4000)
SELECT @pr= N"DRIVER=MICROSOFT VISUAL FOXPRO DRIVER;SOURCETYPE=DBF;SOURCEDB=e:\serg\work\dbf\;BACKGROUNDFETCH=NO;Exclusive=NO;NULL=NO"
EXEC sp_addlinkedserver
@server = "DBF",
@srvproduct = "",
@provider = "MSDASQL",
@provstr = @pr
2. В этом источнике допустим есть таблица sc84.dbf. Пример SELECT"а:
SELECT
t1.code,
t1.descr
FROM
OPENQUERY(DBF, "SELECT code, descr FROM sc84") t1
3. Пример INSERT"а:
INSERT
OPENQUERY(DBF, "SELECT code, descr FROM sc84")
SELECT
"00001", "Test"
4. Пример UPDATE
UPDATE
OPENQUERY(DBF, "SELECT * FROM sc84")
SET
descr = "Test"
WHERE
code="10000105"
5. Пример DELETE
DELETE
FROM
OPENQUERY(DBF, "SELECT * FROM sc84")
WHERE
code = "00001"
|
21.08.2005 23:31 |
|
Активный участник
Регистрация: 17.10.2005
Проживание:
Сообщения: 29
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
Классная вещь этот LinkServer. Особенно мне нравится корректировать dbf–ки через SQL в среде FoxPro. :))
Жаль только путь к таблицам нельзя менять динамически.
|
21.08.2005 23:31 |
|
Активный участник
Регистрация: 17.10.2005
Проживание:
Сообщения: 29
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
Можно еще воспользоваться функцией:
OPENROWSET("provider_name" , "provider_string" , "query")
В этом случае LinkServer остается в стороне, а все параметры соединения передаются через "provider_string"
Запрос для dbf-таблицы :
SELECT b.*
FROM
OPENROWSET( "MSDASQL",
"Driver={Microsoft Visual FoxPro Driver};
UID=;
SourceDB=e:\serg\work\dbf\;
SourceType=DBF;
xclusive=No;
BackgroundFetch=Yes;
Collate=Russian;
Null=No;
Deleted=Yes";,
SELECT code, descr FROM sc84 ") AS b
|
21.08.2005 23:47 |
|
Активный участник
Регистрация: 17.10.2005
Проживание:
Сообщения: 29
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
А есть у SQL что-нить типа макро-подстановки? Никак не могу найти.
|
22.08.2005 23:46 |
|
Admin
Регистрация: 05.10.2005
Проживание: Москва
Сообщения: 284
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
>Можно еще воспользоваться функцией:
OPENROWSET
Эт да, но только неохота каждый раз такую конструкцию таскать! :)
Еще одну штуку хочу заставить работать.. вот по идее должен катить запрос типа
SELECT *
FROM DBF...sc84
ан не работает.. Говорит
Invalid schema or catalog specified for provider "MSDASQL".
Для mdb-файлов, dbf (через Microsoft.Jet) работает, надо для фокса поковырять..
Вообще такие запросы должны быть типа
SELECT *
FROM [server].[db].[schema].[table]
|
22.08.2005 23:47 |
|
Admin
Регистрация: 05.10.2005
Проживание: Москва
Сообщения: 284
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
> А есть у SQL что-нить типа макро-подстановки? Никак не могу найти.
Смотря что под этим понимать, пример в студию!
> Жаль только путь к таблицам нельзя менять динамически
Кстати, смотри пункт номер 1, можно просто пересозадвать сервер с другим путем и все!
|
22.08.2005 23:55 |
|
Admin
Регистрация: 05.10.2005
Проживание: Москва
Сообщения: 284
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
Кстати, на такой запрос
SELECT *
FROM DBF..[e:\serg\work\dbf].sc84
мне сказали:
Invalid use of schema and/or catalog for OLE DB provider "MSDASQL". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
то есть наверно через MSDASQL нельзя, надо через VFP OLE DB, но я в мсдн читал, что для фокса нет OLE DB провайдера...
|
23.08.2005 00:34 |
|
Активный участник
Регистрация: 17.10.2005
Проживание:
Сообщения: 29
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
Есть у Фокса OLE DB.
Опять же через запуск LinkedServer.
EXEC sp_addlinkedserver
@server = "DBF",
@provider = "VFPOLEDB",
@srvproduct = "",
@datasrc = "e:\serg\work\dbf"
SELECT *
FROM DBF...sc84
А сам драйвер можно забрать отсюда , или у меня :)
|
23.08.2005 00:59 |
|
Активный участник
Регистрация: 17.10.2005
Проживание:
Сообщения: 29
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
> Смотря что под этим понимать, пример в студию! :)
Пожалуйста. Например в фоксе:
cFieldsName = "code, descr"
cFileName="sc84"
SELECT &cFieldsName FROM (cFileName)
или
SELECT &cFieldsName FROM &cFileName
равносильно
SELECT code, descr FROM sc84
> Кстати, смотри пункт номер 1, можно просто пересозадвать сервер с другим путем и все
А если пересоздавать сервер придется очень часто? Тогда я не вижу особой разницы между addlinkedserver и OPENROWSET.
|
23.08.2005 12:27 |
|
Admin
Регистрация: 05.10.2005
Проживание: Москва
Сообщения: 284
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
А вот так сойдет?
DECLARE
@cFieldsName varchar(500),
@cFileName varchar(500)
SET @cFieldsName = "code, descr"
SET @cFileName = "sc84"
EXEC ("SELECT " + @cFieldsName + " FROM " + @cFileName)
Одно только неудобно... Если кусок с твоими подстановками является частью большого запроса, то весь этот большой запрос надо передавать как строку функции EXEC...
|
23.08.2005 12:31 |
|
Admin
Регистрация: 05.10.2005
Проживание: Москва
Сообщения: 284
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
> А если пересоздавать сервер придется очень часто? Тогда я не вижу особой разницы между addlinkedserver и OPENROWSET.
Ну, конечно, я просто имел в виду, если по какой-то нужде НАДО использовать Linked Server, а так - конечно OPENROWSET :))
|
24.08.2005 01:24 |
|
Активный участник
Регистрация: 17.10.2005
Проживание:
Сообщения: 29
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
>А вот так сойдет?
Классно. :) Спасибо! То что надо.
|
13.09.2005 01:22 |
|
Admin
Регистрация: 05.10.2005
Проживание: Москва
Сообщения: 284
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
> Можно еще воспользоваться функцией:
>OPENROWSET("provider_name" , "provider_string" , "query")
Я вот попробовал пользовать ее на VFPOLEDB..
Вот так работает:
OPENROWSET("VFPOLEDB","E:\Serg\Work\Dbf\";;, sc84)
А вот так нет:
OPENROWSET("VFPOLEDB","Data Source=E:\Serg\Work\Dbf\", sc84)
Хотя второй параметр вроде бы и есть "provider_string", однако... Что не так? :)
|
14.09.2005 00:45 |
|
Активный участник
Регистрация: 17.10.2005
Проживание:
Сообщения: 29
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
Кажется такой синтаксис "provider_string" используется только для MSDASQL. А для остальных OLE DB параметры передаются неименованные.
|
27.09.2005 14:53 |
|
Admin
Регистрация: 05.10.2005
Проживание: Москва
Сообщения: 284
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
Я тут случайно натолкнулся вот на что... Выполнял один и тот же запрос к одним и тем же дбф-кам, только в первый раз через Linked Server через VFPOLEDB, а второй - через MSDASQL. В первом случае запрос выполнялся 63 сек, во втором - 6 сек!!!
Запросы привожу ниже:
Первый
SELECT
n.code,
n.descr AS name,
n.sp3570 AS art,
ROUND(SUM(r.sp102),0) AS stock
FROM
DBF...sc33 n
INNER JOIN DBF...rg99 r
ON n.ID = r.sp101
WHERE
n.ParentID IN (SELECT ID FROM DBF...sc33 WHERE LTrim(RTrim(ParentID)) IN ("A","Z","10","11"))
AND Year(r.Period)*100 + Month(r.Period) = Year(GetDate())*100 + Month(GetDate())
GROUP BY
n.code,
n.descr,
n.sp3570,
n.ParentID
ORDER BY
n.ParentID,
n.descr
И второй
SELECT
n.code,
n.descr AS name,
n.sp3570 AS art,
ROUND(SUM(r.sp102),0) AS stock
FROM
OPENQUERY(DBF2, "SELECT * FROM sc33") n
INNER JOIN OPENQUERY(DBF2, "SELECT * FROM rg99") r
ON n.ID = r.sp101
WHERE
n.ParentID IN (SELECT ID FROM OPENQUERY(DBF2, "SELECT * FROM sc33") WHERE LTrim(RTrim(ParentID)) IN ("A","Z","10","11"))
AND Year(r.Period)*100 + Month(r.Period) = Year(GetDate())*100 + Month(GetDate())
GROUP BY
n.code,
n.descr,
n.sp3570,
n.ParentID
ORDER BY
n.ParentID,
n.descr
Маленькое уточнение - индексных файлов типа cdx в папке не было, просто дбф-ки. Размер sc33 - 12Мб, размер rg99 - 6Мб.
Может, это из-за индексов? Но почему тогда разные провайдеры так по-разному работают?
|
28.09.2005 03:14 |
|
Активный участник
Регистрация: 17.10.2005
Проживание:
Сообщения: 29
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
Нет, индексы тут не причем. Во всяком случае, на моих таблицах с включенными индексами и без них результаты получились одинаковыми.
Я так думаю, что дело тут в этапах обработки данных у разных провайдеров.
Очень похоже, что через MSDASQL сначала идет преобразование выборки (select) к формату SQL и дальше уже работает один SQL. А через VFPOLEDB данные преобразовываются при каждом обращении к dbf-таблице.
|
29.09.2005 00:13 |
|
Admin
Регистрация: 05.10.2005
Проживание: Москва
Сообщения: 284
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
Ты имеешь в виду, что в случае MSDASQL сначала возвращается курсор от функции OPENQUERY и далее все операции с ним уже (кстати как в этом случае используются индексы интересно)? А в случае VFPOLEDB строится как бы "мост" к дбф-ке и она выступает как полноценная таблица SQL ?
Если я тебя правильно понял, то тогда я согласен с твоим мнением :)
|
29.09.2005 00:54 |
|
Активный участник
Регистрация: 17.10.2005
Проживание:
Сообщения: 29
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
Именно так. У тебя точнее получилось. :)
|
16.08.2007 08:26 |
|
Новичок
Регистрация: 16.08.2007
Проживание: Самара
Сообщения: 1
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
> DECLARE @pr nvarchar(4000)
SELECT @pr= N"DRIVER=MICROSOFT VISUAL FOXPRO DRIVER;SOURCETYPE=DBF;SOURCEDB=e:\serg\work\dbf\;BACKGROUNDFETCH=NO;Exclusive=NO;NULL=NO"
EXEC sp_addlinkedserver
@server = "DBF",
@srvproduct = "",
@provider = "MSDASQL",
@provstr = @pr
SELECT
t1.code,
t1.descr
FROM
OPENQUERY(DBF, "SELECT code, descr FROM sc84") t1
Есть FoxPro'шная БД ,нужно к ней обращаться из MS SQL Studio средствами SQL . Вот взял за основу Ваш пример (естесственно, со своими путями и именами), линкед сервер создался, а при выполнении SELECT'а происходит ошибка
"Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DBF"" Подскажите, плиз, в чём дело?
|
20.08.2007 16:17 |
|
Admin
Регистрация: 05.10.2005
Проживание: Москва
Сообщения: 284
|
MSSQL - Распределенные запросы. Пример SELECT/INSERT/UPDATE/DELETE dbf-ных таблиц.
Скорее всего, MSSQL не видит путь, по которому лежат дбф-ки (проверь SOURCEDB=)
|