SHARE
TWEET

Get binary files from SQL

priore Apr 28th, 2012 145 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --
  2. --  Created by Danilo Priore.
  3. --  Copyright (c) 2012 Prioregroup.com. All rights reserved.
  4. --
  5. --  Permission is hereby granted, free of charge, to any person obtaining a copy
  6. --  of this software and associated documentation files (the "Software"), to deal
  7. --  in the Software without restriction, including without limitation the rights
  8. --  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  9. --  copies of the Software, and to permit persons to whom the Software is
  10. --  furnished to do so, subject to the following conditions:
  11. --
  12. --  The above copyright notice and this permission notice shall be included in
  13. --  all copies or substantial portions of the Software.
  14. --
  15. --  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  16. --  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  17. --  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  18. --  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  19. --  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  20. --  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  21. --  THE SOFTWARE.
  22. --
  23.  
  24. CREATE PROCEDURE spGetBinaryFile(@filename sysname) AS
  25. BEGIN
  26. SET NOCOUNT ON
  27.  
  28. -- get file size
  29. DECLARE @filesize int
  30. CREATE TABLE #fileinfo(
  31.         fname varchar(255) NULL,
  32.         [size] int NULL,
  33.         unused1 int NULL,
  34.         unused2 int NULL,
  35.         unused3 int NULL,
  36.         unused4 int NULL,
  37.         unused5 int NULL,
  38.         unused6 int NULL,
  39.         unused7 int NULL
  40. )
  41. INSERT #fileinfo exec master..xp_getfiledetails @filename
  42. SELECT @filesize = [size] FROM #fileinfo
  43. DROP TABLE #fileinfo
  44.  
  45. -- file not found (0 bytes)
  46. IF ISNULL(@filesize,0) = 0 BEGIN
  47.         RETURN
  48. END
  49.  
  50. -- create a unique file name
  51. DECLARE @dt varchar(23)
  52. SET @dt = CONVERT(varchar(23),GETDATE(),121)
  53. SET @dt = REPLACE(@dt,' ','')
  54. SET @dt = REPLACE(@dt,'-','')
  55. SET @dt = REPLACE(@dt,'/','')
  56. SET @dt = REPLACE(@dt,':','')
  57. SET @dt = REPLACE(@dt,'.','')
  58. SET @dt = REPLACE(@dt,',','')
  59. SET @dt = @dt + '.fmt'
  60.  
  61. SET @dt = "test.fmt"
  62.  
  63. -- create temp FTM file
  64. DECLARE @cmd varchar(200)
  65. SET @cmd = "master..xp_cmdshell '@echo 1 SQLIMAGE 0 " + CONVERT(varchar(10),@filesize)
  66. SET @cmd = @cmd + ' "" 1 img "" >> ' + @dt + "', NO_OUTPUT"
  67. EXEC ("master..xp_cmdshell '@echo 8.0 > " + @dt + "', NO_OUTPUT")
  68. EXEC ("master..xp_cmdshell '@echo 1 >> " + @dt + "', NO_OUTPUT")
  69. EXEC  (@cmd)
  70.  
  71. -- import and return data
  72. CREATE TABLE #tmpimg (img image)
  73. EXEC("bulk INSERT #tmpimg FROM '" + @filename + "' WITH (CODEPAGE='RAW',DATAFILETYPE='widenative',FORMATFILE='" + @dt + "')")
  74. IF @@ERROR = 0 BEGIN
  75.         SELECT img FROM #tmpimg
  76. END
  77. ELSE BEGIN
  78.         RETURN
  79. END
  80. DROP TABLE #tmpimg
  81.  
  82. -- remove FMT file
  83. EXEC("master..xp_cmdshell 'del " + @dt + "', NO_OUTPUT")
  84. END
  85. GO
RAW Paste Data
Top