Как выгрузить бинарные данные из таблицы на диск?
Об этом уже много написано н разных ресурсах.Если кратко, то есть два способа выгрузки бинарных данных из таблицы на диск:
1. с помощью утилиты bcp
2. с помощью .NET
Мы поговорим о первом способе.
И вот сам скрипт, выгружающий данные из нужного поля и сохраняющий их на диск:
declare @cmd varchar(500)
declare @id uniqueidentifier
declare @reportname nvarchar(250)
declare report cursor
for select [ID] from Report where [SecurityLevel] = 0
open report
fetch next from report into @id
while @@fetch_status = 0
begin
set @cmd = 'bcp "select cast([Data] as image) from IMReports.dbo.Report where [ID] = ''' + cast(@id as nvarchar(50)) + '''" queryout C:\' + cast(@id as nvarchar(50)) + '.rpt -T -f C:\report.fmt'
exec xp_cmdshell @cmd
fetch next from report into @id
end
close report
deallocate report
Мы объявляем переменные для команды, ID текущей записи и названия. Далее, создается курсор, в котором мы получаем данные, добавляем их в команду и выполняем xp_cmdshell.
И так для каждой записи. После мы закрываем курсор и освобождаем его ресурсы. Вот так все просто, однако есть пара моментов, без которых данный скрипт не выполнится. Вот о них мы и поговорим ниже.
Прежде всего нам нужно выполнить несколько действий:
1. создать файл форматирования. Это текстовой или xml файл (по префиксу -x) в котором описана структура поля или полей таблицы из которой мы будем копировать.
создается он также с помощью утилиты bcp, но немного другой набор параметров. примерно так:
bcp IMReports.dbo.Report format nul -T -f c:\format.fmt
выделены важные особенности:
с помощью "format nul" вы указываете, что формата нет и вы собираетесь его создавать.
Ключ "-T" указывает на использование доверенного соединения. полезно, когда нет необходимости вводить логин/пароль.
после этого вам предложат указать верные данные по полям таблицы.
выделено целевое поле. обратите внимание на указание префикса длины равного 4 байтам.
Если вы оставите все как есть, то в начале каждого файла будет добавлено 4 байта, указывающих длину самих данных.
на выходе получится такой файл:
------report.fmt------
10.0
9
1 SQLUNIQUEID 1 16 "" 1 ID ""
2 SQLCHAR 2 250 "" 2 Name Cyrillic_General_CI_AS
3 SQLCHAR 2 4000 "" 3 Note Cyrillic_General_CI_AS
4 SQLTINYINT 0 1 "" 4 SecurityLevel ""
5 SQLDATETIME 0 8 "" 5 DateCreated ""
6 SQLDATETIME 0 8 "" 6 DateModified ""
7 SQLIMAGE 4 0 "" 7 Data ""
8 SQLUNIQUEID 1 16 "" 8 ReportFolderID ""
9 SQLBINARY 2 8 "" 9 RowVersion ""
--------------------------
Тут:
10.0 - версия SQL сервера.
9 - количество полей.
Далее поля и в них:
1 - порядковый номер поля.
2 - тип данных.
3 - длина.
4 - максимальный размер поля.
5 - префикс окончания строки (для разделения данных, например, как в CSV)
7 - название поля.
8 - кодировка.
Нам нужны только данные из одного поля - Data, соответственно, изменяем этот файл. Убираем лишние поля, меняем их количество, порядковый номер и префикс длины (см. выше).
Получится такой файл:
------report.fmt------
10.0
1
1 SQLIMAGE 0 0 "" 1 Data ""
------------------------
2. включить использование xp_cmdshell
begin
EXEC sp_configure 'xp_cmdshell',1
reconfigure with override;
end
Вот и все :)
Если при выполнении у вас вывалится подобная ошибка:
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
это означает, что что-то препятствует записи файла, который вы выгружаете в указанном месте.
Убедитесь, что у Вас есть права на запись в данное место.
Об этом уже много написано н разных ресурсах.Если кратко, то есть два способа выгрузки бинарных данных из таблицы на диск:
1. с помощью утилиты bcp
2. с помощью .NET
Мы поговорим о первом способе.
И вот сам скрипт, выгружающий данные из нужного поля и сохраняющий их на диск:
declare @cmd varchar(500)
declare @id uniqueidentifier
declare @reportname nvarchar(250)
declare report cursor
for select [ID] from Report where [SecurityLevel] = 0
open report
fetch next from report into @id
while @@fetch_status = 0
begin
set @cmd = 'bcp "select cast([Data] as image) from IMReports.dbo.Report where [ID] = ''' + cast(@id as nvarchar(50)) + '''" queryout C:\' + cast(@id as nvarchar(50)) + '.rpt -T -f C:\report.fmt'
exec xp_cmdshell @cmd
fetch next from report into @id
end
close report
deallocate report
Мы объявляем переменные для команды, ID текущей записи и названия. Далее, создается курсор, в котором мы получаем данные, добавляем их в команду и выполняем xp_cmdshell.
И так для каждой записи. После мы закрываем курсор и освобождаем его ресурсы. Вот так все просто, однако есть пара моментов, без которых данный скрипт не выполнится. Вот о них мы и поговорим ниже.
Прежде всего нам нужно выполнить несколько действий:
1. создать файл форматирования. Это текстовой или xml файл (по префиксу -x) в котором описана структура поля или полей таблицы из которой мы будем копировать.
создается он также с помощью утилиты bcp, но немного другой набор параметров. примерно так:
bcp IMReports.dbo.Report format nul -T -f c:\format.fmt
выделены важные особенности:
с помощью "format nul" вы указываете, что формата нет и вы собираетесь его создавать.
Ключ "-T" указывает на использование доверенного соединения. полезно, когда нет необходимости вводить логин/пароль.
после этого вам предложат указать верные данные по полям таблицы.
выделено целевое поле. обратите внимание на указание префикса длины равного 4 байтам.
Если вы оставите все как есть, то в начале каждого файла будет добавлено 4 байта, указывающих длину самих данных.
на выходе получится такой файл:
------report.fmt------
10.0
9
1 SQLUNIQUEID 1 16 "" 1 ID ""
2 SQLCHAR 2 250 "" 2 Name Cyrillic_General_CI_AS
3 SQLCHAR 2 4000 "" 3 Note Cyrillic_General_CI_AS
4 SQLTINYINT 0 1 "" 4 SecurityLevel ""
5 SQLDATETIME 0 8 "" 5 DateCreated ""
6 SQLDATETIME 0 8 "" 6 DateModified ""
7 SQLIMAGE 4 0 "" 7 Data ""
8 SQLUNIQUEID 1 16 "" 8 ReportFolderID ""
9 SQLBINARY 2 8 "" 9 RowVersion ""
--------------------------
Тут:
10.0 - версия SQL сервера.
9 - количество полей.
Далее поля и в них:
1 - порядковый номер поля.
2 - тип данных.
3 - длина.
4 - максимальный размер поля.
5 - префикс окончания строки (для разделения данных, например, как в CSV)
7 - название поля.
8 - кодировка.
Нам нужны только данные из одного поля - Data, соответственно, изменяем этот файл. Убираем лишние поля, меняем их количество, порядковый номер и префикс длины (см. выше).
Получится такой файл:
------report.fmt------
10.0
1
1 SQLIMAGE 0 0 "" 1 Data ""
------------------------
2. включить использование xp_cmdshell
begin
EXEC sp_configure 'xp_cmdshell',1
reconfigure with override;
end
Вот и все :)
Если при выполнении у вас вывалится подобная ошибка:
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
это означает, что что-то препятствует записи файла, который вы выгружаете в указанном месте.
Убедитесь, что у Вас есть права на запись в данное место.