Sql Server: Convert VARBINARY column data to raw files using T-SQL
- Posted by Sqltimes
- On May 16, 2015
- 0 Comments
Quick one today:
A few months ago, there was a new requirement to export millions of records in a table column to raw files on a disk. This column contains millions of shopping receipts stored in binary format in database tables. Early on, I stumbled up on a tip that gave me a good idea of how to approach this problem.
This uses T-SQL with help from ‘ OLE Automation Procedures’ to accomplish this task. Towards that, we need to enable ‘Ole Automation Procedures’ advanced options.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
---- Enable 'Ole Automation Procedures'--SELECT * FROM sys.configurationsWHERE name = 'Ole Automation Procedures'GOEXEC sp_configure 'SHOW ADVANCED OPTIONS', 1RECONFIGUREGOEXEC sp_configure 'Ole Automation Procedures', 1RECONFIGUREGOEXEC sp_configure 'SHOW ADVANCED OPTIONS', 0RECONFIGUREGOSELECT * FROM sys.configurationsWHERE name = 'Ole Automation Procedures'GO |
asdas
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
---- Code to export VARBINARY into RAW files using TSQL--DECLARE @SQLIMG VARCHAR(MAX) , @IMG_PATH VARBINARY(MAX) , @TIMESTAMP VARCHAR(MAX) , @ObjectToken INTDECLARE IMGPATH CURSOR FAST_FORWARD FOR SELECT logcontent FROM dbo.TransactionLog WHERE EnterpriseID = '423-DZFDSF-23432-254E57A1-45A7-4DBF'OPEN IMGPATHFETCH NEXT FROM IMGPATH INTO @IMG_PATHWHILE @@FETCH_STATUS = 0BEGIN -- -- Create unique file name for each row of data -- SET @TIMESTAMP = 'M:\MSSQL\Hyvee\Receipts_' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-','_'),':','_'),'.','_'),' ','_') + '.ZIP' -- PRINT @TIMESTAMP -- PRINT @SQLIMG -- -- Use Ole Automation to save the contents to a file on disk -- EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT EXEC sp_OASetProperty @ObjectToken, 'Type' , 1 EXEC sp_OAMethod @ObjectToken, 'Open' EXEC sp_OAMethod @ObjectToken, 'Write' , NULL , @IMG_PATH EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL , @TIMESTAMP , 2 EXEC sp_OAMethod @ObjectToken, 'Close' EXEC sp_OADestroy @ObjectToken FETCH NEXT FROM IMGPATH INTO @IMG_PATHENDCLOSE IMGPATHDEALLOCATE IMGPATHGO |
OleAutomation Procedures RAW Files
At the end of it, reset the ‘Ole Automation Procedures’ settings.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
<pre>---- Enable 'Ole Automation Procedures'--SELECT * FROM sys.configurationsWHERE name = 'Ole Automation Procedures'GOEXEC sp_configure 'SHOW ADVANCED OPTIONS', 1RECONFIGUREGOEXEC sp_configure 'Ole Automation Procedures', 0RECONFIGUREGOEXEC sp_configure 'SHOW ADVANCED OPTIONS', 0RECONFIGUREGOSELECT * FROM sys.configurationsWHERE name = 'Ole Automation Procedures'GO |
Hope this helps,
_Sqltimes
0 Comments