Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Table-Valued function call Extended stored proccedure ( dll )  [new]
ART-CODE
Member

Откуда:
Сообщений: 1095
Есть dll, зарегистрированная как Extended stored proccedure
возвращающая таблицу с другого сервера
(на вход процедуры подется строка подключения и sql-запрос, при этом dll делает еще кое-какие хитрые действия)

Очень хотелось обернуть ее в Table-Valued function чтобы можно было результаты выборки Join-ить
со своими таблицами.

Но что-то не выходит...
нельзя внутри функции делать insert exec, хотя просто exec - можно
нельзя использовать табличные переменные

Замкнуть на себя через OpenQuery/OpenRowset пока тоже не получилось... что-то с правами вызова хранимки.
Но даже если с правами разобраться, то всеравно - в эти OpenQuery/OpenRowset я не смогу в динамике передавать строку подключения и запрос.

Со всех сторон обложили...

Пока как рабочий вариант принято - переделать dll так, чтобы она создавала таблицу - результат выборки и в OUTPUT рараметр передавала ее имя, затем наша Table-Valued function делает выборку из этой таблицы, возвращет результат и убивает эту таблицу... (в этом варианте еще можно будет прикрутить механизм кеширования запросов - убивать табицу не сразу, а например при выходе пользователя)


Что скажете ? Нормальное решение, есть еще идеи ?
31 июл 09, 12:07    [7483751]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
А если в функции просто exec сделать, а insert делать уже при вызове функции?
31 июл 09, 12:10    [7483770]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
ART-CODE
Member

Откуда:
Сообщений: 1095
Забыл.
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
31 июл 09, 12:10    [7483771]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
ART-CODE
Member

Откуда:
Сообщений: 1095
Гавриленко Сергей Алексеевич
А если в функции просто exec сделать, а insert делать уже при вызове функции?


Простите, я не совсем понял что вы имели в виду
может лучше на примере
вот - прототип Table-Valued функции Get_remote_table
xp_Get_remote_table - та самая Extended stored proccedure ( которая dll )

Как результат exec попадет на выход функции ?

Create FUNCTION [dbo].[Get_remote_table] ()
RETURNS @tbl TABLE
(
[BEG_DATE] DateTime,
[END_DATE] DateTime,
[Account] varchar(25)

) AS
BEGIN
Declare @ConnStr varchar(500),@QueryStr varchar(500),@Rezult int
set @ConnStr='строка подключения к серверу'
set @QueryStr='Select * from remote_table'

exec master.dbo.xp_Get_remote_table @ConnStr,@QueryStr, @Rezult OUTPUT-- @ConnStr, @ConnStr, @Rezult OUTPUT

Return
end
31 июл 09, 12:23    [7483862]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Никак, да. Пишите хранимку. Причем можно написать так, что она будет вставлять результат в заранее созданную временную таблицу (чтобы на insert into exec не нарываться).

Сообщение было отредактировано: 31 июл 09, 12:26
31 июл 09, 12:26    [7483875]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
Crimean
Member

Откуда:
Сообщений: 13147
получится только через построчное заполнение возвращаемой таблицы в UDF, а в DLL надо будет временно хранить всю коллекцию и через OUT параметры отдавать ее. но не быстро работать будет. ессно, в dll надо будет делать функции типа GetFirst / GetNext :)
31 июл 09, 15:18    [7485221]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
ART-CODE
Member

Откуда:
Сообщений: 1095
Вобщем , хранимкой будем создавать в базе таблицу, а после использования - удалять...
Будем Join-нить уже готовые к использованию - обычные таблицы.
Про функцию прийдется забыть.
31 июл 09, 16:52    [7485816]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
ART-CODE
Member

Откуда:
Сообщений: 1095
Нашел свой старый пост и решил опубликовать.

РЕШЕНИЕ
- заставить свою DLL возвращать строку XML,
а дальше можно внутри функции с этим пакетом делать все, что угодно.
хоть на выход подать, хоть для внутренних вычислений использовать.

ОГРАНИЧЕНИЕ
пакет XML не удастся сделать больше varchar(8000),
Ну т.е. конечно же, можно считать и больший пакет за несколько
выборок по 8000, но все это заметно усложнит и логику самой DLL и способ ее вызова
и сильно ухудшит скорость работы.
Поэтому я ограничился этим размером и использую для небольших и не массовых запросов
типа выяснить состояние на счете в соседней системе, не закрыт ли договор.
15 сен 11, 16:15    [11282369]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
есть же тип xml, зачем вы параметр в процедуре своей объявляете varchar(8000) ?
15 сен 11, 16:18    [11282394]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
ART-CODE
Member

Откуда:
Сообщений: 1095
Нет такого типа: XML в ODS API. Типы данных, определённые в ODS API
Поэтому DLL может вернуть только строку 8000 (при необходимости большего объема - за несколько считываний)

Теоретически SRVTEXT может до 2147483647 символов.
Но я уже не помню, почему я не смог этим воспользоваться (год назад писал)
сейчас даже интересно еще раз открыть исходник и выяснить что там произошло с SRVTEXT.
15 сен 11, 16:43    [11282572]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
зато есть в MS SQL и этот параметр легко переварит на вход строку xml-формата любой длины
15 сен 11, 16:45    [11282590]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
ART-CODE, вот в качестве идеи, сам так не пробовал: генерить временную функцию с нужной строкой подключения и openquery.
exec('create #function() returns table as return(select ... from openquery...);');
15 сен 11, 16:48    [11282602]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
ART-CODE
Member

Откуда:
Сообщений: 1095
Konst_One,
Так я и не спорю о том, что есть в MS SQL. Да, есть :)

Суть вопроса в том, что DLL сможет отдавать только кусками по 8000,
а дальше - собирать все вместе до нужного размера на сервере.
(так, мне надо будет попробовать еще CLR, может там другие ограничения)
15 сен 11, 16:54    [11282626]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Я лопухнулся -- временные функции нельзя создавать.
15 сен 11, 16:56    [11282638]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
ART-CODE
Member

Откуда:
Сообщений: 1095
invm,
Хорошая была бы идея, если бы DLL не должна была делать доп. работу
(внутренние преобразования типов данных, логирование, безопасность)
15 сен 11, 16:57    [11282648]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
ART-CODE
Konst_One,
Так я и не спорю о том, что есть в MS SQL. Да, есть :)

Суть вопроса в том, что DLL сможет отдавать только кусками по 8000,
а дальше - собирать все вместе до нужного размера на сервере.
(так, мне надо будет попробовать еще CLR, может там другие ограничения)


почему dll только кусками по 8000? её нельзя модифицировать?
15 сен 11, 17:09    [11282735]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
ART-CODE
Member

Откуда:
Сообщений: 1095
Так, выяснил/вспомнил - SRVTEXT соответствует типу данных text
при этом локальную переменную типа text нельзя обьявить:
Msg 2739, Level 16, State 1, Line 43
The text, ntext, and image data types are invalid for local variables.

Поэтому при вызове хранимки я в OUTPUT параметр подставляю varchar (8000)
если при этом попробовать подставить varchar (max) то получаем ошибку в этой функции:
if (FAIL == srv_paramsetoutput(this->pSrvProc, params_count, (BYTE*)XML_ANSI_string, nLenOfXML_ANSI_string-1, FALSE))
скорее всего дело в неправильном значении nLenOfXML_ANSI_string

Если кому-то интересно потестировать, доработать, исправить,
то вот исходники проекта и тестовые скрипты: XP_Get_Remote_Rows.dll
ЗЫ
Эта dll работает на боевом сервере уже больше года.
20 сен 11, 13:42    [11305056]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
ART-CODE
то вот исходники проекта и тестовые скрипты: XP_Get_Remote_Rows.dll
striganov.com/cpp/10-xpgetremoterowsdll
и может быть зарегистрирована в MS SQL Server 2005/2008 как extended stored procedure.
Чёрт, я думал старый закрытый проект.
Зачем extended stored procedure? Почему не CLR, хотябы?

Да и вообще можно было отдельной несвязанной прогой. И в настройках (запуска службы) указать имя сервера, базы и очереди SB. Протокол взаиможействия довольно однозначный и простой напрашивается.

ART-CODE
Эта dll работает на боевом сервере уже больше года.
Всего лишь?!

По поводу сабжа. OLE Automation также не может забирать более 8K данных из параметров. MS принципиально садит всех на кол CLR.
20 сен 11, 15:17    [11306197]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mnior
Да и вообще можно было отдельной несвязанной прогой. И в настройках (запуска службы) указать имя сервера, базы и очереди SB. Протокол взаиможействия довольно однозначный и простой напрашивается.
Чёрт забыл - Table-Valued function же.
20 сен 11, 15:18    [11306209]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
ART-CODE
Member

Откуда:
Сообщений: 1095
Почему не CLR - ответ простой
на момент написания dll - у меня был опыт программирования таких вещей на C++,
а опыта создания CLR не было. :)

Тогда я попробовал сделать CLR и напоролся на какие то проблемы с безопасностью.
"Там" работает, а "там" - не работает,
и как правильно настроить права работы CLR во всех местах, где это было нужно - сходу/быстро разобраться не удалось.
Время поджимало, а dll на C++ - она как танк, работает всегда, везде и не капризничает.
20 сен 11, 15:31    [11306333]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
ART-CODE, а теперь самый раз проект сконвертировать с использованием CLR.
20 сен 11, 16:23    [11306981]     Ответить | Цитировать Сообщить модератору
 Re: Table-Valued function call Extended stored proccedure ( dll )  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
ART-CODE
.... работает всегда, везде ...
Как я понял в Denali она уже не работает.
20 сен 11, 16:25    [11307000]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить