for this using SQL QUERY Analyzer and Sql Server 2000 database

  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

3 answers 3

INSERT INTO Table1 (ImageField) SELECT BulkColumn FROM OPENROWSET (BULK 'c:\picture.jpg', SINGLE_BLOB) AS T

this is for more modern SQL servers, and in your case you should use writetext, something like this:

 CREATE TABLE Table1 ( ID INT PRIMARY KEY IDENTITY (1,1), ImageField IMAGE NULL ) GO -- must insert a dummy value into the image column for TEXTPTR DECLARE @RowId INT INSERT Table1 (ImageField) VALUES (0xFFFFFFFF) SELECT @RowId = SCOPE_IDENTITY() -- get a pointer value DECLARE @Ptr varbinary(16) SELECT @Ptr = TEXTPTR(ImageField) FROM Table1 WHERE Id = @RowId -- write the image WRITETEXT Table1.ImageField @Ptr 'c:\picture.jpg'

You can transfer the contents of the picture through HP

 create procedure writeImage @content image as DECLARE @RowId INT INSERT Table1 (ImageField) VALUES (0xFFFFFFFF) SELECT @RowId = SCOPE_IDENTITY() -- get a pointer value DECLARE @Ptr varbinary(16) SELECT @Ptr = TEXTPTR(ImageField) FROM Table1 WHERE Id = @RowId -- write the image WRITETEXT Table1.ImageField @Ptr @content GO

there is another option using the BCP utility (parameter IN ), but you should have xp_cmdshell permissions

  • almost it, but a little bit wrong: in the image type column there will be such a 'c: \ picture.jpg' ... but not bytes of the picture ... exactly there you need to cram the contents of the picture. if there would be a function ReadFromFile instead of WRITETEXT or something like that - Voza

Here is the link for SQL Server 2000: https://msdn.microsoft.com/en-us/library/aa175795.aspx .

  • one
    Links have the property foul. Include basic information on the link in the answer itself. - andreycha

Through the SQL Query Analyzer wrapper (SQL Server 2000), it was not possible to implement a request to insert images into the table with the image field through the stored procedure because the question remains than to open the file contents (for example, BCP needs the right) ... but I would like it easier. Using the VBS script I decided this:

 ' === Script Information Header === ' Script Name: load.vbs ' Description: Load image in table, database.. ' === Initialization Block === Dim cn,rs Const adTypeBinary = 1 Set rs = CreateObject("ADODB.Recordset") Set fso = CreateObject("Scripting.FileSystemObject") 'Create Stream object Dim BinaryStream Set BinaryStream = CreateObject("ADODB.Stream") Set cn = CreateObject("ADODB.Connection") cn.ConnectionString = "File Name=you_connect.udl" cn.Open rs.Open "you_table", cn, 1, 2 ' where field 'img' type 'image' ' read file jpg(binary data): BinaryStream.Type = adTypeBinary BinaryStream.Open BinaryStream.LoadFromFile "C:\you_img.jpg" bytesBLOB = BinaryStream.Read rs.addnew '... rs.fields("img").AppendChunk bytesBLOB rs.Update BinaryStream.Close MsgBox("Insert image!") ' close connection db cn.Close Set cn = Nothing ' --