Таблица для импорта

Входной каталог, завершить символом Л' Входной файл (по умолчанию (atab1e+,ВСР') Имя сервера

Имя учетной записи (по умолчанию sa)

@username varcTiar(128) se. -- Имя учетной записи (по умолчанию sa) password varchar(128)NULL -- Пароль учетной записи

/*

Обьект: sp importtable

Описание: Импортирует таблицу подобно BULK INSERT Использование: sp importtable (Stable varchar(128). lainputpath vanchar(128)-NULL, ginputname varchar(128)-NULL, (server varchar(128)-(local) (Susername varchar(128)-sa, (apassword varChar(128/=NULL -- Пароль учетной записи Возвращает: количество импортированных строк Автор: Кеп Henderson, Email: kh,en(akhen,com Пример: EXEC Importtable authors. C:\TEMP\ Создана: 1999-06-14. Изменена: 1999-07-14, */ AS

IF (!atable=/?) OR ((ainputpath IS NULL) GOTO Help

DECLARE ©object int, -- Переменная для создания COM обьектов

lahr int, -- Хранит HRESULT

(afacobject int. -- Хранит указатель на обьект BulkCopy

PTAB DELIMITED int, -- Константа разделения табуляцией

@logname varchar(12B). -- Имя файла протокола

@errname varchar(12B). -- Имя файла ошибок

ladbname varchar(128). -- Имя базы

(arowsimported int -- Количество импортированных строк

SET (атАВ 0ЕЕ1М1ТЕ0=2 -- Константа SQL-DMO для импорта со разделением табуляцией SET (adbname=ISNULL(PARSENAME((atable.3).DB NAME()) -- Извлечь имя базы SET @table=PARSENAME((atable.l) -- Убрать лишнее из имени таблицы

IF (latable IS NULL) BEGIN

RAISERRORCInvalid table name..16.1)

RETURN -1 END

IF (RIGHT(@inputpath.l)<>\)

SET ?inputpath=(ainputpath+\ -- Добавить \. если требуется

SET (aiogname=@inputpath+(atable+.LOG -- Составить полное имя файла протокола SET iaerrname=(ainputpath+(atable+.ERR -- Составить полное имя файла ошибок

IF (lainputname IS NULL)

SET C1nputname=(ainputpath+(atable+.BCP -- Составить полное имя входного файла ELSE

SET (31 nputname-iainputpath+iai nputname -- Создать обьект SQLServer

EXEC (ahr=sp DACreate SQLDMO.SQLServer. laobject OUT IF mr <> 0) BEGIN

EXEC sp displayoaerrorinfo laobject, lahr

RETURN END

-- Создать обьект BulkCopy

500 г лава 19. OLE-автоматиз

EXEC №г=5р 0АСгеа1е SQLDMO.BuiкСору'. labcobject OUT IF mr <> 0) BEGIN

EXEC sp displayoaerror1nfo labcobject. (Shr

RETURN END

-- Установить свойство DataFilePath обьекта BulkCopy

EXEC (Shr = sp OASetProperty @bcobject. DataFilePath. (Sinputname

IF (lahr <> 0) BEGIN

EXEC sp d1splayoaerror1nfo (Sbcobject. (Shr

RETURN END

-- Указать обьекту BulkCopy на необходимость создать разделенный табуляцией файл EXEC №г = sp OASetProperty №cobject. DataFileType. (aTAB DELIMlTED IF (lahr <> 0) BEGIN

EXEC sp displayoaerror1nfo (Sbcobject. ghr

RETURN END

-- Установить свойство LogFilePath обьекта BulkCopy

EXEC (Shr = sp OASetProperty (Sbcobject. LogFilePath. laiogname

IF mr <> 0) BEGIN

EXEC sp d1sp1ayoaerrorinfo ©bcobject. ?hr

RETURN END

-- Установить свойство ErrorFilePath обьекта BulkCopy

EXEC (ahr = sp OASetProperty (Sbcobject. ErrorFilePath. (aerrname

IF mr <> 0) BEGIN

EXEC sp displayoaerrorinfo ©bcobject, &hr

RETURN END

-- Установить свойство UseServerSideBCP обьекта BulkCopy EXEC (ahr = sp OASetProperty @bcobject. UseServerSideBCP. 1 IF mr <> 0) BEGIN

EXEC sp displayoaerrorinfo @bcobject. ghr

RETURN END

-- Установить соединение с сервером IF (lapassword IS NOT NULL)

EXEC (3hr = sp OAMethod gobject. Connect. NULL. (Sserver. Pusername. ©password ELSE

EXEC ©hr = sp OAMethod ©object. Connect. NULL, ©server, ©username

IF (©hr <> 0) BEGIN

EXEC sp displayoaerrorinfo ©object, ©hr

RETURN END

-- Получить указатель на коллекцию Databases обьекта SQLServer EXEC ©hr = sp OAGetProperty ©object. Databases, ©object OUT IF ©hr <> 0 BEGIN

EXEC sp displayoaerrorinfo ©object, ©hr

RETURN

-- Получигь указатель ча заданную базу из коллекций Databases EXEC Phr = sp OAMethod object, item, (pobject OUT. (adbname IF ghr <> 0 BEGIN

EXEC sojisplayoaerroruito gobjC-ct, gr,r

RETURN END

-- Получить указатель на заданнуй таб.мицу из котлекций Tables

IF (0BJECTPR0PERTY(0BJECT ID(@t3bIe;,Isable)<>!)

BEGIN

RAISERRORC Target object must be a table. С 16.1) RETURN -1 END

EXEC Phr = sp 0AMethod (Pobject. Tables, object OUT. @tabie IF (phr <> 0 BEGIN

EXEC sp displayoaerrorinfc (Pooject. PEr

RETURN END

-- Вызвать метод ImportData обьекта laoie для /.кпорте даннь.х

EXEC @hr - sp OAMethoa object, ImportOala, grows imported OUT, Cabcobject

IF (Эпг <> 0 BEGIN

EXEC sp displayoaerrorinfo goojcct, (?hr

RETURN END

RETURN Prows imported Help:

EXEC sp usage taobjectnamesp importtable, Pdesc=Импортирует таблицу подобно BULK INSERT, (aparameters=

Ptable varchardZB), Таблица для ин,1орта

PinputpatiT varchar(128)=NULL, -- Входной каталог с \

@1nputname varctiar{ 128)=NULl , -- Входной ii?j/.r. (по умолчакио @1аЫе - ,ВСР')

server varchar(128)= (local), -- Икя сервера

Pusername varctiar(128)=sa, -- Имя учетной записи (по умолчанию sa)

password varchar(128)=NULL -- Пароль учетной записи,

iaauthor=Ken Henderson, (aemail = khen(Pkheri.com,

Matecreated= 19990614,ladatel astchanged= 1999071,

PexampleEXEC importtable authors , C:\TEMP\ ,

Preturns Number of rows imported

RETURN -1

Подобно BULK INSERT, spjmporttable :wi-pyH<ae- файлы онерационной системы в таблицы. Как и sp exporttable, эта процедура делает определенные допущения о фор.мате файла, которые при желании .можно из.ме1И1ть. Вот код примера, использующий sp exporttable ti spjmporttable:

SET NOCOUNT ON USE pubs

DECLARE Pre int

-- Сначала экспортировать записи

EXEC ©rcpubs..sp exporttable ©tablе-pubs, .authors, iaoutputpath d:\ temp\bcp\

SELECT ©re AS RowsExported

-- Далее создать таблицу для хранения aannceii

SELECT * INTO authorsimp FROM authors WHERE 1-0

-- Затем импортировать экспортированные ранее записи

EXEC pubs..sp importtable @table=authors1mp.

Pi nputpath=d;\ temp\bcp\.Pi nputname=authors.bcp

SELECT COUNT(*) AS RowsLoaded FROM authorsimp

DROP TABLE authorsimp

Этот сценарий начинается с экспорта таблицы authors учебной базы данных p-ubs. Далее он создает пустую копию таблицы и импортирует экспортированные ранее строки при помощи процедуры spjmporttable. Как и в случае BULK INSERT, файл, содержащий импортируемые данные, должен быть доступен .машине, на которой работает SQL Server.

sp getSQLregistry

в дополнение к операциям массовой загрузки интерфейс SQL-DMO предоставляет доступ к множеству административных служб и серверной икфор.мации посредство.м объектов автоматизации. Одним из таких объектов является объект Registry, предоставляющий доступ к части системного реестра, управляемой SQL Server. Его можно использовать для доступа к таким данны.м, как установленный в данный момент набор си.мволов, имя учетной записи SQL Mail по умолчанию, количество процессоров, объем оперативной памяти на сервере и так далее. Вот храни.мая процедура, дающая доступ к набору данных объекта Registry;

USE master GO

IF OBJECT ID(sp getSQLregistry) IS NOT NULL

DROP PROC sp getSQLregistry

CREATE PROC sp getSQLregistry ©regkey varchar(128), -- Ключ реестра

@regvaluevarchar(8000)=NULL OUTPUT, -- Значение из ветки SQL Server ©server varchar(128)=(local), -- Имя сервера

©username varchar(128)=sa, -- Имя учетнок! записи (no умолчанию sa)

©password varchar(128)=NULL -- Пароль учетно(1 записи

Обьект: sp getSQLregistry

Описание: Получает значение из раздела системного реестра, отведенного для SQL Server Использование: sp getSQLregistry ©regkey varchar(128), -- Ключ реестра

©regvalue varchar(8000) OUTPUT, -- Значение из ветки SQL Server ©server varchar(128)-(local). -- Имя сервера

©username varchar(128)=sa, -- Имя учетной записи (no умолчанию sa) ©password varchar(128)=NULL -- Пароль учетной записи Возвращает: Длину прочитанного из реестра значения

getSQLregistry 503

Автор: Ken Henderson. Email: khenPkhen.com Версия: 6.4

Пример: sp getSQLregistry SQLRootPath. gsqlpath OUTPUT

Создана: 1996-09-03. Изменена: 1999-07-01.

SET NOCOUNT ON

IF (!aregkey=7?) GOTO Help

DECLARE object int. -- Переменная для создания COM оОьектов

ghr Int -- Хранит HRESULT -- Создаем обьект SQLServer

EXEC (ahr=sp OACreate SQLDMO. SQLServer, eobject OUTPUT IF mr <> 0) BEGIN

EXEC sp d1splayoaerror1nfo gobject. @hr

RETURN END

-- Соединяемся с сервером

IF ((apassword IS NOT NULL) AND (gpasswordo)

EXEC Phr = sp OAMethod object. Connect. NULL, server, eusername. (apassword ELSE

EXEC PHr = sp OAMethod (Sobject. Connect. NULL, server. (ausername IF mr <> 0) BEGIN

EXEC sp dlsplayoaerror1nfo (aobject. (ahr

RETURN END

-- Получаем указатель на обьект Registry

EXEC (Shr - sp OAGetProperty (Sobject. Registry, (aobject OUT

IF №r <> 0 BEGIN

EXEC sp displayoaerrorinfo (Sobject. Phr

RETURN END

-- Получаем значение

EXEC Phr sp OAGetProperty (aobject. (Sregkey. (Pregvalue OUT IF №r <> 0 BEGIN

EXEC sp displayoaerrorinfo (aobject. @hr

RETURN END

RETURN datalength(@regvalue) Help:

EXEC sp usage (aobjectname- sp getSQLregl stry.

@desc=Получает значение из раздела системного реестра, отведенного для SQL Server, (aparameters-

(Згедкеу varchar(128). -- Ключ реестра

(aregvalue varchar(8000) OUTPUT. -- Значение из ветки SQL Server (aserver varchar(128)= (local) . -- Имя сервера

(ausername varchar(12B)=sa. -- Имя учетной записи (no умолчанию sa)

(apassword varchar(128)-NULL -- Пароль учетной записи.

(aauthor=Ken Henderson. (aema11=khen(akhen.com.

(adatecreated=19960903. (adatelastchanged=1999070Г .

(avers1on=6. (arev1s1on-4,

@returns=Data length of registry value.

(aexample=sp gelSQLreg1stry SQLRootPath . @sqlpath OUTPUT

Вот скрипт, использующий sp getSQLregistry для доступа к ключевой системной информации: SET NOCOUNT ON

DECLARE (anumprocs varchar(lO). Pinstalledmemory varchar(20). (arootpath varchar(BOOO) EXEC sp getSQLreg1stry PhysicalMemory. (ainstalledmemory OUT

EXEC sp getSQLregistry NumberOfProcessors. ©numprocs OUT EXEC sp getSQLregistry SQLRootPath, ©rootpatii OUT

SELECT (Snumprocs AS NumberOfProcessors. ©installedmemory AS InstalledRAM, ©rootpath AS RootPath

DECLARE ©charset varchar(lOO). @sortorder varchar(lOO) EXEC sp getSQLregistry CharacterSet. @charset OUT SELECT ©charset AS CharacterSet EXEC sp getSQLregistry SortOrder. ©sortorder OUT SELECT gsortorder AS SortOrder

NumberOfProcessors InstalledRAM RootPath

1 79 C;\MSSQL7

CharacterSet

Character Set = 1. iso l ISO 8859-1 (Latin-l) -- Western European 8-bit

character set.

SortOrder

Sort Order = 52. nocase iso Case-insensitive dictionary sort order for use with several We

Заключение

Способность Traiisact-SQL взаимодействовать с объектами автоматизации позволяет ему выполнять задачи, обычно отводимые традициоитнлм средствам разработки, таким как Delphi и Visual Basic. В этой главе вы изучили работу с объектами автоматизации, предоставляемы.ми са.мим сервером - Distributed Management Objects. Но процедуры автоматизации Transact-SQL (sp OAxxxx) не ограничены использование.м только этих объектов. Из Transact-SQL можно автоматизировать Excel, Word, Visio и любое другое приложение, предоставляющее интерфейс автоматизации.

Недокументированный Transact-SQL

Унция техники стоит фупта технологии,

Дэппи Торп

Очень важно воздержаться от выбора недокументированной функциональности, если это не является абсолютной необходимостью. Недокументированные функции не случайно относятся к таковым. Как правило, фупкции, команды DBCC, хранимые процедуры и трассировочные флаги, по описанные в документации SQL Server, являются недокументированными потому, что компания Microsoft не хочет, чтобы их использовали. Они .могут быть опасны и даже привести к катастрофическим последствиям, если их исправильио применять. Неправильное использование недокументированных возможностей СУБД - верный способ потерять данные и линигтъся поддержки со стороны поставщика (Microsoft).

Поэтому будьте осторожны с команда.ми и синтаксисом, приведенными ниже. Используйте их редко и осторожно. Вьипедший из строя сервер испортит вам хорошее настроение, вызватитое использованием новой функции.

Если вы решите задействовать недокументированные функции в своем коде, знайте, что они могут измениться в последующих выпусках SQL Server. Функция PWDENCRYPTO, например, претерпела из.менения между выпусками 6.5 и 7.0, и те, кто полагался на нее, столкнулись с проблемами ирн переходе на версию 7.0.

Не стоит ожидать со стороны поставщика гюддержки недокументированной функциоиа^Еьиости. Когда вы встречаете слово недокумеитироваипое , следует читать неподдерживаемое . Оставляя функциональность недокументированной, поставщик может свободно менять ее, не заботясь о том, что код потребителей перестанет работать. Если вы решите основывать критически важный код на недокументированных аспектах продукта, делайте это иа свой страх и риск.

Что значит недокументированный

Недокументированный Transact-SQL - это команды, функции, флаги трассировки, команды DBCC и хранимые процедуры, не описанные в Books Online. Кое-что из этого можно найти в другой широко доступной доку.ментации Microsoft, кое-что нельзя. В данной главе: если не описано в Books Online, значит, не документировано.

Недокументированные команды DBCC

Изначально команды DBCC представляли собой небольшой набор процедур, не относящихся к Transact-SQL, для обслуживания базы данных. Идея состояла в том, чтобы собрать эти команды в легко запоминае.\ияй и легко используемый набор. Это хорошо работало и было в основном хоронгей идеей. С того времени Sybase и Microsoft расширили назначение команд DBCC, включив много функциональности, о которой не могли предполагать проектировщики SQL Server. Набор команд, не относящихся к проверке базы на содержание ошибок, состоит из десятков позиций, будет неуклюжим и почти нелепы.м. Сегодня команды DBCC выполняют все: от проверки целостности базы до обработки полнотекстовых индексов, от управления кэшем сервера до взаимодействия с Performance Manager. По сути, это самостоятельный язык.

Множество из этих команд недокументированно но веским причииа.м. Почему некоторые из них не сделаны отдельными комаида.ми Transact-SQL, знает только производитель. Прежде че.м погрузиться в изучение ко.манд, дадим несколько предупреждений. Во-первых, добавляйте опцию WITH NOJNFOMSGS для сокращения результатов выполнения команд DBCC до сообщений об оншбках. Это делает некоторые болтливые команды типа DBCC CHECKALLOC более управляемыми, без потерь чего-то важного. Во-вторых, используйте DBCC HELP() для получения встроенной справки по командам DBCC. Большая часть недокументированных команд не отображаются этой командой, но никогда не по.мешает проверить. Наконец, применяйте DBCC TRACEON(3604) для перенаправления результатов выполнения команд DBCC в клиентское соединение, а не в системный журнал ошибок. Помните о то.м, что многие команды по умолчанию направляют свои результаты в журнал ошибок. Если вы выполнили одну из приведенных далее команд и ничего не получили в результате, вероятно, результаты были направлены в журнал ошибок сервера, и следует включить трассировочный флаг 3604, для того чтобы получать результаты на клиенте.

DBCC ADDEXTENDEDPROC(procname,DLL)

Данная ко.манда добавляет расширенную хранимую процедуру в поддерживаемый сервером список. Основная функциональность этой ко.манды такая же, как и у системной хранимой процедуры sp adclextencleclproc, и, по сути, ;эта ко.манда вызывается из указанной процедуры. Параметр procname - это и.мя расширенной хранимой процедуры, параметр DLL - и.мя библиотеки динамической компоновки, где расположен код расшнренгюй хранимой процедуры: овес ADDEXTENDEDPROCC xp computemode. xp stats,d1V)

DBCC ADDINSTANCE(object,instance)

Эта команда добавляет экземпляр объекта для отслеживания в Performance Monitor. Хранимые процедуры, инициализирующие счетчики производительности, используют данную ко.манду для настройки различных аспектов .мониторинга производительности. Параметр object - имя объекта, содержащего экземпляр (например, SQL Replication Agents ), параметр instance - имя экземпляра (например, Logreader ).

DBCC ADDINSTANCECSQL Replication Agents.Snapshot)

DBCC BCPTABLOCK(dbid, tabid, setflag)

Данная команда устанавливает блокировку иа таблицу для операции массовой загрузки, что может увеличить производительность операций массовой вставки данных, так как позволяет избежать установки блокировки на уровне строк для каждой добавляемой записи. Пара.метр dbid - это идентификатор базы, tabid - идентификатор таблицы, setflag указывает на необходи.мость установки или сброса блокировки.

DECLARE тЫй int. (aobjid int

SELECT (adbid=DB lD(pubs). C objid-OBJECT ID(titles) DBCC BCPTABLOCKdadbid.Pobjid.l)

DBCC BUFFER(dbid[,objid][, numberofbuffers][, printopt -CO I 1 I 2}])

Данная ко.манда служит для получения содержимого буферов памяти SQL Server. Можно получить содержимое буферов как заданного объекта, так и всей базы.

DECLARE Pdbid int. Oobjid int

SELECT (adbid=DB ID(pubs). (?objid-OBJECT ID(pubs, .titles) SELECT COUNT(*) FROM pubs..titles - Загрузить буферы DBCC BUFFER((adbid.(aobjid.l.2)

(результаты сокращетил)

BUFFERS (in MRU to LRU order): BUFFER:

BUF (ЭОхПВЗВЗОО

bpage = OxlFC2BOOO bhash - 0x00000000 bdbid = 5 breferences = 8

bstat = 0x9 bspin = 0

PAGE HEADER:

bpageno = (1:122) bkeep = 0

bnext = 0x00000000

Page (aOxlFC2BOOO

m pageld - (1:122) m typeF1agBits = 0x0 m objId = 261575970 m nextPage = (0:0) m freeCnt = 7592 mjsn = (50:302:9) m ghostRecCnt = 0

m headerVersion mjevei - 0 m indexld = 7 pminien = 13 m freeData = 564 m xactReserved = 0 m tornBits = 0

1 m type = 2

m f1agBits - 0x0 m prevPage - (0:0) m s1otCnt = 18 m reservedCnt = 0 m xactld = (0:0)

DATA:

Memory Dump (a0xlFC28060

Ifc28060: 36627573 б96е6573 7320202C 2002GC0e 6ousiress ... Ifc28070; OlOOlaOO 42553130 33323662 7573696e ... .BU10326busir,

lfc28080: 65737320 20202002 00000100 ia004255 ess .......BO

lfc28090: 31313131 36627573 696e6573 73202020 mi6busir,esb

Ifc280a0: 20020000 OlOOlaOO 42553230 37353662 .......BIJ2Q756C

Ifc280b0-. 7573696e 65737320 20202002 00000100 usness .....

DBCC BYTESCstartingaddress.length)

Команда отображает содержимое облас1и па.мяти начиная с адреса starting-address размером length байтов. Указаншяй адрес доллсен принадлежать адресному пространству процесса SQL Server.

DBCC BYTES(0014767000,50)

00el5398: 00000000 00000000 690bl808 COOOOGGO ...........1....

00el53a8: 00000000 00000000 00000000 OOOOOQOC ...............

00el53b8: 00000000 00000008 OCOOOOOO COOOOOOO ...............

00el53c8: 0000 ..

DBCCCALLFULLTEXT(fundd[,catid][,objid])

Команда служит для выполнения множества onepannii, относящихся к полно-текстовому поиску. riapa.vicTp funcid указывает иа функцию, которую необходгьмо выполнить, и на то, какие пара.метры допусти.чил. Параметр catid - идентификатор полнотекстового каталога, objid - идентис|)Икатор объекта, над когоры.м производится операция. Обратите вни.мапие, что команда CALLFULLTEXT допустима только внутри CHcre.vmoft хранимо!! !!роцелуры. Эга !1роцедура доллсна иметь установленный системный 6i!t (см. иедо!суме1!тирова!п!у!о хран!!мую нро!едуру sp MS marksystemobject), и ее имя долж!!о !!ач!1наться с sp fulltext . В табл. 20.1 перечисле!!Ь! поддерживаемые фупктти.

Таблица 20.1. Функции DBCC CALLFULLTEXT()

funcid Функция Параметры

1 Создает полнотекстовый каталог Catalog ID, path

2 Удаляет полнотекстовый каталог Catalog ID

3 Заполняет полнотекстовый каталог Catalog ID, 0=full l=incremental

4 Прекращает заполнение полнотекстового Catalog ID каталога

5 Помечаеттаблицу для индексирования Catalog ID, Object ID

6 Удаляет таблицу из индексирования Catalog ID, Object ID

7 Удаляет все полнотекстовые каталоги Database ID

8 Производит очистку полнотекстовых каталогов

9 Задает доступные для Microsoft Search ре- Значение ресурса (1-5; 1 = фоновый ресурсы процессора жим, 5 = выделенный режим. По умолчанию значение 3)

10 Устанавливает тайм-аут соединения Значение тайм-аута в секундах (1-32767)