Скрипт объекта БД посредством SQLDMO.SQLServer

← →
Ольга (2006-05-04 17:41) [0]

Скрипты на создание объектов БД получаю с помощью хранимой процедуры, которая вызывает методы SQLDMO.SQLServer. Все было здорово до тех пор, пока не встретился скрипт размером > 8000 (переменная, в которую пишется скрипт @cScript varchar(8000)) Пыталась подставить другой тип - не работает. Может что подскажете, мастера?
DECLARE @oSrv int, @oDB int, @oObj int, @cScript varchar(8000), @Server varchar(50),@DataBase varchar(50),@TypeObject varchar(30),@NameObject varchar(50) SET @Server = "local" SET @DataBase = "ETALON" SET @TypeObject = "StoredProcedures" SET @NameObject = "DeleteFromShema" EXEC sp_OACreate "SQLDMO.SQLServer", @oSrv output EXEC sp_OASetProperty @oSrv, "LoginSecure", "TRUE" EXEC sp_OAMethod @oSrv, "Connect", NULL, @Server EXEC sp_OAGetProperty @oSrv, "Databases", @oDB output, @DataBase EXEC sp_OAGetProperty @oDB, @TypeObject, @oObj output, @NameObject EXEC sp_OAMethod @oObj, "Script", @cScript output EXEC sp_OAMethod @oSrv, "Disconnect" EXEC sp_OADestroy @oSrv SELECT @cScript

← →
Nikolay M. © (2006-05-04 18:23) [1]

> Скрипты на создание объектов БД получаю с помощью хранимой
> процедуры, которая вызывает методы SQLDMO.SQLServer.

Почему бы не написать простенький СОМ-объект, который будет делать все тоже самое, а скрипт класть в файл или в таблицу с NTEXT-полем?

← →
sniknik © (2006-05-04 21:16) [2]

> varchar(8000)
это максимально возможного размера строка... больше только blob/text

как именно не работает? (ошибка?)

← →
Хозяин (2006-05-05 06:47) [3]

Пыталась подставить другой тип - не работает.

Можно было бы объявить @cScript как ntext, но с локальной переменной так это пройдет.
Но можно сделать такое объявление в параметре этой хранимой процедуры, вот так:

CREATE myProc @cScript ntext=NULL AS

Ну это как вариант...

← →
Хозяин (2006-05-05 06:48) [4]

но с локальной переменной так это не пройдет.

← →
Хозяин (2006-05-05 06:53) [5]

А вообще могу предложить универсальную процедуру "sp_generate_script" из книги Кен Хендерсон "Профессиональное руководство по SQL Server: хранимые процедуры, XML, HTML". Тоже на DMO.

← →
Ольга (2006-05-05 07:13) [6]

Nikolay M. ©

Не хочу формировать скрипт врукопашную - громоздко и по-кустарному. По-моему, лучше, если это делает сам сервер с учетом конкретных особенностей данной БД.

sniknik ©

Когда DECLARE @cScript varchar(8000) ошибка:
ODSOLE Extended Procedure, sp_OAMethod usage:
ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]

Когда DECLARE @cScript text ошибка:
The text, ntext, and image data types are invalid for local variables.

← →
Ольга (2006-05-05 07:17) [7]

Хозяин [5]

Что за процедура sp_generate_script? В Help-е не нашла.

← →
Хозяин (2006-05-05 07:25) [8]

Когда DECLARE @cScript text ошибка:
The text, ntext, and image data types are invalid for local variables.

Попробуйте мне когдато помогло, т.с. выкрутился :)
CREATE PROC myProc @cScript ntext=NULL AS

[7] - это самопальная процедура автора книги.
Секунду...сейчас напечатаю выдержку с книги...

← →
Хозяин (2006-05-05 07:34) [9]

[5] + только Ваш скрипт работает через метод "Script", а у него через "SQLDMO.Transfer".

И вот он пишет почему:
"Для использования sp**** необходимо передать ей имя объекта, сценарий которого хотите создать.
...
Если вам приходилось программировать на DMO, возможно, вы удивились тому, что я использовал объект Transfer, а не вызов метода Script для каждого объекта. Причина по которой я так поступил, проста: наличие зависимостей между объектами. Объект Transfer записывает схему объектов в сценарий в порядке их зависимостей. и т.д."

← →
Ольга (2006-05-05 07:47) [10]

[8]
Не помогло. С параметром @cScript ntext=NULL процедура скомпилировалась. Но при выполнении выдала ошибку:
sp_OAMethod Script: ODSOLE Extended Procedure, Error in srv_paramset.

[9]
Это очень интересно. Иерархию зависимостей я строю сама (задачка неожиданно оказалась очень не тривиальной), но не уверена, что отследила все уровни зависимостей. У вас нет небольшого примерчика с использованием объекта Transfer?

← →
Хозяин (2006-05-05 07:56) [11]

У вас нет небольшого примерчика с использованием объекта Transfer?

http://www.xozyain.nm.ru/sp_generate_script.zip

← →
Хозяин (2006-05-05 08:32) [12]

Ольга, не получилось воспроизвести Вашу ошибку, вечерком еще попробую.

← →
Ольга (2006-05-05 10:38) [13]

Разобралась в процедуре sp_generate_script. Она пишет скрипт в файл по назначенному пути. Может по запросу считать информацию из файла.
Создалась процедура у меня с ошибками - в ней есть вызов двух других процедур, которых у меня не оказалось.
1. sp_displayoaerrorinfo - обработка ошибок, я ее создала сама
2. sp_readtextfile - чтение скрипта из файла
Здесь я задумалась, стоит ли связываться с файлами. Сервер не может создать файл на компьютере пользователя, пользователь может не иметь доступа к файловой системе сервера (мой случай), чтобы считать файл с сервера. Можно, конечно, в самой процедуре счтать из файла во временную таблицу... но уж больно кривой путь получается. Надо найти более простое решение.

← →
Nikolay M. © (2006-05-05 11:17) [14]

> Ольга (05.05.06 07:13) [6]
>
> Не хочу формировать скрипт врукопашную - громоздко и по-
> кустарному. По-моему, лучше, если это делает сам сервер
> с учетом конкретных особенностей данной БД.

А я и не предлагал делать скрипт вручную, читайте мой ответ внимательнее.

← →
Ольга (2006-05-05 11:33) [15]

[14]
Прочитала внимательнее. Ответа на свой вопрос не нашла: как в СОМ-объекте будет реализована функция, которая генерит скрипт объектов? Конкретнее, как сгенерированный скрипт (>8000) передать пользователю?

← →
Nikolay M. © (2006-05-05 11:52) [16]

> ак в СОМ-объекте будет реализована функция, которая генерит
> скрипт объектов?

var
srv : OleVariant;
begin
srv := CreateOleObject ("SQLDMO.SQLServer");
srv.Connect (...);
// Дальше и так знаете
end;

Если это должно работать на клиентском компьютере, придется таскать с собой несколько файликов (БОЛ: Installing SQL-DMO).
Если на сервере, то упаковываете код в СОМ-объект, единственную функцию которого будете потом дергать из T-SQL. Передача данных зависит от Ваших конкретных условий, самое тупое - это класть скрипт в табличку с TEXT-полем, а функция будет возвращать ID вставленной записи.

← →
Ольга (2006-05-05 13:32) [17]

> begin
> srv := CreateOleObject ("SQLDMO.SQLServer");
> srv.Connect (...);
> // Дальше и так знаете
> end;

Вот как раз дальше то и не получается. Облом на строке
EXEC sp_OAMethod @oObj, "Script", @cScript output
т.к. переменной по максимуму @cScript varchar(8000) не хватает для записи в нее скрипта.
Можно воспользоваться методом ScriptTransfer
EXEC sp_OAMethod @object, "ScriptTransfer",NULL, @tfobject, 2, @outputfile
который пишет в файл, но это тоже не устраивает (см.[13])

Хотела уже сдасться и начала делать скрипт врукопашную, но к своему великому удивлению в таблице syscomments в поле text нашла только первые n-символов тела процедуры. Или я не там ищу, или заработалась...

← →
Nikolay M. © (2006-05-05 13:39) [18]

> Вот как раз дальше то и не получается. Облом на строке
> EXEC sp_OAMethod @oObj, "Script", @cScript output
> т.к. переменной по максимуму @cScript varchar(8000) не хватает
> для записи в нее скрипта.

Покажите код, который не получается. В Дельфи нет типа varchar(8000), а конструкция

EXEC sp_OAMethod @oObj, "Script", @cScript output

вообще выдаст ошибку компиляции.

> в таблице syscomments в поле text нашла только первые n-
> символов тела процедуры. Или я не там ищу, или заработалась.

Одно из двух. Если скрипт не помещается в varchar (8000), то он тем более не поместится в nvarchar (4000) (тип поля syscomments.text).

← →
Ольга (2006-05-05 15:46) [19]

> В Дельфи нет типа varchar(8000)

О Дельфи вообще речи не было. Я пока работаю на сервере БД. В каком виде будет клиентская часть пока не решила (дельфовое приложение, или только DLL, возможно СОМ).

> а конструкция ... вообще выдаст ошибку компиляции.

Если в Query записать только эту строку, то конечно. Нужно выполнить весь блок из [0], начиная с создания объекта.

← →
Nikolay M. © (2006-05-05 16:03) [20]

> Ольга (05.05.06 15:46) [19]
> О Дельфи вообще речи не было.

Опять ссылаюсь на [1]. Я не предлагал создавать СОМ-объект именно в Дельфи, делайте на чем нравится.

> > а конструкция ... вообще выдаст ошибку компиляции.
>
> Если в Query записать только эту строку, то конечно.

Если в Query записать "только эту строку", то ошибки компиляции не будет. Но опять же, я не предлагал это делать! Как можно так понять код на Дельфи, в котором нет ни слова о Query и его использование даже не предполагается?

var srv : OleVariant; begin srv := CreateOleObject ("SQLDMO.SQLServer"); srv.Connect (...); // Дальше и так знаете end;

Такое ощущение, что Вы в упор не хотите меня понимать, а зациклились на своем T-SQL-ном скрипте. С SQL-DMO, как и с любым другим СОМ-объектом, можно работать не только из T-SQL.

← →
Ольга (2006-05-05 16:12) [21]

То, что зациклилась - это верно подмечено. Сейчас уже врубилась, попробую из Дельфи.

> Сейчас уже врубилась,

> (05.05.06 16:12) [21]

Лучше поздно, чем никогда :)

← →
Хозяин (2006-05-05 17:07) [23]

Согласен.
:)

← →
Хозяин (2006-05-05 17:38) [24]

Кстати скрипт [0] в QA у меня ошибок не выдавал

с @cScript varchar(8000)
если тело скрипта весит меньше 8000 - то все ОК
если больше - то выдает NULL

[8] - тоже выдает NULL, но это понятно.

← →
Ольга (2006-05-06 10:13) [25]

Пытаюсь перевести свой код с T-SQL на Дельфи, не получается. В help-e есть примеры использования SQLDMO на VB6 и С, никак не могу угадать синтаксис для Дельфи. Торможу уже на соединении с базой. Черкните хотя еще пару строк - дальше соображу сама. Привожу код с закоментариными своими попытками (чтобы дурь была видна):
procedure TForm1.Button1Click(Sender: TObject); var srv, db, sp : OleVariant; scr: string; begin try srv := CreateOleObject ("SQLDMO.SQLServer"); srv.LoginSecure:=True; srv.Connect("BRSQL"); //db:=srv.Databases("ETALON"); //db:= CreateOleObject ("SQLDMO.Database"); //db.Name:="ETALON"; //scr:=srv.Databases("ETALON").StoredProcedures("DeleteFromShema").Script; Memo2.Text:=scr; finally srv.DisConnect; end; end;

procedure TForm1.Button1Click(Sender: TObject); var srv, db : OleVariant; begin srv := CreateOleObject ("SQLDMO.SQLServer"); srv.LoginSecure := False; srv.Connect("srv", "login", "pass"); db := srv.Databases.Item ("Northwind"); ShowMessage (db.StoredProcedures.Item ("Sales by Year", "dbo").Script); end;

И если это планируется делать на клиентских компьютерах, не забудьте, что я говорил о переносе SQL-DMO.

← →
Ольга (2006-05-06 12:15) [28]

Спасибо, заработало!

> И если это планируется делать на клиентских компьютерах,
> не забудьте, что я говорил о переносе SQL-DMO.

Да, клиент будет не такой тонкий, как хотелось бы. Но что поделаешь - за все надо платить. Одно радует - таких клиентов (админов) будет 1-2 на контору. Еще раз спасибо.

> Да, клиент будет не такой тонкий, как хотелось бы. Но что
> поделаешь - за все надо платить. Одно радует - таких клиентов
> (админов) будет 1-2 на контору.

Админам можно и целиком клиентскую часть SQL Server установить.
Если все-таки решите ставить только SQL-DMO, советую изучить чужой опыт

http://www.sql.ru/forum/actualthread.aspx?tid=45114

Скрипт объекта БД посредством SQLDMO.SQLServer Найти похожие ветки