Can BULK INSERT be used like TEXTCOPY?

(SQL Server 2000, SP3a)
Hello all!
Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files
(on disk),
and I'd like to squirt them in to a table that has an IMAGE column. I could
"shell" out
to use TEXTCOPY, but was wondering if I could leverage some built-in SQL con
struct. BULK
INSERT looked close, but it also looks like the built-in analogue to BCP. T
hat is, it
processes the contents of the file, and I really just want to squirt the fil
e in to the
column verbatim.
Thanks for any help you can provide!
John PetersonYes. You can use bulk insert but you need a format file.
e.g.
1 SQLIMAGE 0 999999 "" 2 coln ""
1= entire file
SQLIMAGE= datatype
0= prefix length
999999= file length/size
""= no terminator
2= column ordinal
coln= column name
""= no collation
Thus, the bulk insert looks like this:
bulk insert tb
from 'file.img'
with(formatfile='fmt.fmt')
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to use BULK INSERT like TEXTCOPY? I have a series of
files (on disk),
> and I'd like to squirt them in to a table that has an IMAGE column. I
could "shell" out
> to use TEXTCOPY, but was wondering if I could leverage some built-in SQL
construct. BULK
> INSERT looked close, but it also looks like the built-in analogue to BCP.
That is, it
> processes the contents of the file, and I really just want to squirt the
file in to the
> column verbatim.
> Thanks for any help you can provide!
> John Peterson
>|||John,
See this thread for an example:
http://groups.google.com/groups?q=4...C5-7256A4B9870A
Steve Kass
Drew University
John Peterson wrote:

>(SQL Server 2000, SP3a)
>Hello all!
>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of file
s (on disk),
>and I'd like to squirt them in to a table that has an IMAGE column. I coul
d "shell" out
>to use TEXTCOPY, but was wondering if I could leverage some built-in SQL co
nstruct. BULK
>INSERT looked close, but it also looks like the built-in analogue to BCP.
That is, it
>processes the contents of the file, and I really just want to squirt the fi
le in to the
>column verbatim.
>Thanks for any help you can provide!
>John Peterson
>
>|||Thanks oj and Steve! I think that'll do the trick, even if it's a little aw
kward. :-)
BTW: Do you need an *exact* binary file size in the format file, as Steve's
link
suggests? Or will "oversizing" it suffice?
Thanks again!
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to use BULK INSERT like TEXTCOPY? I have a series of fil
es (on disk),
> and I'd like to squirt them in to a table that has an IMAGE column. I cou
ld "shell" out
> to use TEXTCOPY, but was wondering if I could leverage some built-in SQL c
onstruct.
> BULK INSERT looked close, but it also looks like the built-in analogue to
BCP. That is,
> it processes the contents of the file, and I really just want to squirt th
e file in to
> the column verbatim.
> Thanks for any help you can provide!
> John Peterson
>|||John,
It's got to be exact. If you oversize it, you get an unexpected
end-of-file:
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.
SK
John Peterson wrote:

>Thanks oj and Steve! I think that'll do the trick, even if it's a little a
wkward. :-)
>BTW: Do you need an *exact* binary file size in the format file, as Steve'
s link
>suggests? Or will "oversizing" it suffice?
>Thanks again!
>
>"John Peterson" <j0hnp@.comcast.net> wrote in message
>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>
>
>|||Thanks, Steve!
One other option I'm exploring is using a stored procedure with an IMAGE par
ameter. But,
I have very little experiencing in using VBScript (this is going to be calle
d from a Web
page) and ADO Parameter objects for an IMAGE data. I've been searching on t
he Web, and
I've seen a few snippets -- but not a comprehensive example. Do you have an
y
recommendations/links on that route, perchance?
"Steve Kass" <skass@.drew.edu> wrote in message
news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> John,
> It's got to be exact. If you oversize it, you get an unexpected end-of-f
ile:
> Server: Msg 4832, Level 16, State 1, Line 1
> Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
information
> about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows retu
rned 0x80004005:
> The provider did not give any information about the error.].
> The statement has been terminated.
> SK
> John Peterson wrote:
>|||John,
I haven't done this myself, but you should be able to pass the image
data with a adLongVarBinary parameter.
You might need to create the parameter 1 byte longer than the length of
the data you're storing, according to
http://support.microsoft.com/defaul...kb;en-us;190450
I also saw one suggestion that this meant you have to read back all but
the last byte of the stored image
value when retrieving the data, so you should be watchful:
http://groups.google.com/groups?hl=...
dlongvarbinary
SK
John Peterson wrote:

>Thanks, Steve!
>One other option I'm exploring is using a stored procedure with an IMAGE pa
rameter. But,
>I have very little experiencing in using VBScript (this is going to be call
ed from a Web
>page) and ADO Parameter objects for an IMAGE data. I've been searching on
the Web, and
>I've seen a few snippets -- but not a comprehensive example. Do you have a
ny
>recommendations/links on that route, perchance?
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
>
>
>|||Great stuff! Thanks again, Steve! :-)
"Steve Kass" <skass@.drew.edu> wrote in message
news:eXQUuyjvEHA.2804@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> John,
> I haven't done this myself, but you should be able to pass the image data
with a
> adLongVarBinary parameter.
> You might need to create the parameter 1 byte longer than the length of th
e data you're
> storing, according to
> http://support.microsoft.com/defaul...kb;en-us;190450
> I also saw one suggestion that this meant you have to read back all but th
e last byte of
> the stored image
> value when retrieving the data, so you should be watchful:
> http://groups.google.com/groups?hl=...adlongvarbinary
> SK
>
> John Peterson wrote:
>