Can I Insert/Update Large Text Field To Database Without Bulk Insert?

I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.

You can't just use a plain old update statement and set the column = a parameter of the correct datatype?

|||

How do you indicate that a SqlParameter is of type nvarchar(max)? Any numeric length up to 4000 is easy, but beyond that I've come up empty.

|||

When I add a parameter to a command, I use the AddWithValue method instead of the Add method. That way I don't have to type in the datatype and the length, I just pass it text and it works.

It's possible that it will truncate on you using that method, but I've used it with ntext and long text values before

|||

cmd.Parameters.Add("@.Blobby",SqlDbType.Nvarchar)

or

cmd.Parameters.Add("@.Blobby",SqlDbType.Nvarchar,-1)

|||

cmd.Parameters.AddWithValue("@.Blobby",myTextBox.Text)

(or any other object's value instead of myTextBox)

|||

I normally don't recommend AddWithValue because it can cause some problems when it's unclear what the conversions (if any) should be. This comes into play when the result to be passed could possibly be a nvarchar or a more specific data type (integers, dates). Under certain circumstances, .NET decides to send the data to SQL Server as a nvarchar, and when it gets there, it realizes that it needs to be converted to a more specific data type, but the information needed to do the conversion correctly (because of culture formatting) isn't available on the server, or it uses the servers culture rather than the culture of the running page.

Using .Add with a specified datatype insures that the data conversion is done by .NET before sending the parameter on to SQL Server.

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 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 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=405F-B2C5-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 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
>
>|||Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
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 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,
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 awkward. :-)
>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 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
>>
>>
>
>|||Thanks, Steve!
One other option I'm exploring is using a stored procedure with an IMAGE parameter. But,
I have very little experiencing in using VBScript (this is going to be called 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 any
recommendations/links on that route, perchance?
"Steve Kass" <skass@.drew.edu> wrote in message
news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
> 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 awkward. :-)
>>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 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,
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/default.aspx?scid=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=en&lr=&safe=off&threadm=uMwa0SoD%24GA.235%40cppssbbsa02.microsoft.com&rnum=6&prev=/groups%3Fhl%3Den%26lr%3D%26safe%3Doff%26q%3Dsqlserver%2Bstored%2Bprocedure%2Bimage%2Badlongvarbinary
SK
John Peterson wrote:
>Thanks, Steve!
>One other option I'm exploring is using a stored procedure with an IMAGE parameter. But,
>I have very little experiencing in using VBScript (this is going to be called 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 any
>recommendations/links on that route, perchance?
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
>
>>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 awkward. :-)
>>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 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
>>
>>
>>
>>
>
>|||Great stuff! Thanks again, Steve! :-)
"Steve Kass" <skass@.drew.edu> wrote in message
news:eXQUuyjvEHA.2804@.TK2MSFTNGP14.phx.gbl...
> 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/default.aspx?scid=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=en&lr=&safe=off&threadm=uMwa0SoD%24GA.235%40cppssbbsa02.microsoft.com&rnum=6&prev=/groups%3Fhl%3Den%26lr%3D%26safe%3Doff%26q%3Dsqlserver%2Bstored%2Bprocedure%2Bimage%2Badlongvarbinary
> SK
>
> John Peterson wrote:
>>Thanks, Steve!
>>One other option I'm exploring is using a stored procedure with an IMAGE parameter.
>>But, I have very little experiencing in using VBScript (this is going to be called 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 any
>>recommendations/links on that route, perchance?
>>
>>"Steve Kass" <skass@.drew.edu> wrote in message
>>news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
>>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 awkward.
>>:-)
>>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 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
>>
>>
>>
>>

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 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
Yes. 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=40...5-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 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
>
>
|||Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
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 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,
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 awkward. :-)
>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 parameter. But,
I have very little experiencing in using VBScript (this is going to be called 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 any
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-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:
|||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/default...b;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=e...dlongvarbinary
SK
John Peterson wrote:

>Thanks, Steve!
>One other option I'm exploring is using a stored procedure with an IMAGE parameter. But,
>I have very little experiencing in using VBScript (this is going to be called 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 any
>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 the data you're
> storing, according to
> http://support.microsoft.com/default...b;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=e...dlongvarbinary
> SK
>
> John Peterson wrote:

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:
>

Can a Data Flow Task mimic Bulk Insert?

Hi Guys,

The way I understand a Data Flow Task is that it inserts the rows from the source to destination one by one. Is there a way to make it act like a bulk insert task? We have been experiencing performance issues when inserting a lot of rows from one table to another. If there's no way to actually do it, can a bulk insert task functionality be scripted? Coz what I need is a table to table insert, and the bulk insert task only accepts data files as sources.

Thanks!

Kervy

What destination object are you using? The OLEDB Destination and the SQL Server destination both enable bulk insert options for SQL Server.

How many rows are you inserting?

Donald

|||

Oh you mean to say, by default, an OLEDB source inserts to an OLEDB Destination in bulk? We are only inserting a couple of hundred thousand rows, but the source table is in the US. destinations are in Europe and Asia Pacific, so we need to optimize this as much as we can given the proximity of the servers.

We are using OLEDB source and OLEDB Destination

Thanks

|||

If you need to performance tune your data-flow then you should read a whitepaper that I have mentioned here: http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx

There's also more useful information here: http://blogs.conchango.com/jamiethomson/archive/2005/04/20/1319.aspx

Note that SSIS does not insert records one at a time - that would be a performance killer. The SSIS pipeline processes data in buffers and the contents of each buffer is inserted at the same time! A large part of performance tuning your application is finding the optimum buffer size.

Note that in your scenario the bottleneck is likely to be the georgraphic disparity of the source and destination, not how fast SSIS can process it. Try and reduce the amount of data that is coming over the wire if possible - i.e. Filter the data at source and only select columns that you require.

-Jamie

|||

hi Jamie,

Thanks for that response. I will read more on how to performance tune my data flow. One accepted cause for the horrible execution time is the distance between servers (destinations are in 2 different continents!), that is why we are trying to figure out if there are other ways to improve on this. We tried testing with a destination server in the same area, and it took about 9 minutes.. compared to the 16 hours it took when the target was Asia Pacific.. we may have to accept that performance issue for now..

total number of rows to be inserted = 200,000

total number of columns = 167

|||

KervyChoa wrote:

hi Jamie,

Thanks for that response. I will read more on how to performance tune my data flow. One accepted cause for the horrible execution time is the distance between servers (destinations are in 2 different continents!), that is why we are trying to figure out if there are other ways to improve on this. We tried testing with a destination server in the same area, and it took about 9 minutes.. compared to the 16 hours it took when the target was Asia Pacific.. we may have to accept that performance issue for now..

total number of rows to be inserted = 200,000

total number of columns = 167

Exactly, hence my previous comment "Try and reduce the amount of data that is coming over the wire if possible - i.e. Filter the data at source and only select columns that you require."

The point here is to reduce the amount of data coming over the wire because that is quite obviously the bottleneck in your scenario.

-Jamie

|||I tried using perfmon to see what can be done, but I can't find the SQL Server: SSIS Pipeline Performance object in perfmon.. Am I missing something here?|||

KervyChoa wrote:

I tried using perfmon to see what can be done, but I can't find the SQL Server: SSIS Pipeline Performance object in perfmon.. Am I missing something here?

As we have already determined (twice) the bottleneck is not within the SSIS pipeline so looking at this perf counter is not going to help you.

Unless you pick one of the machines up and move it to a different continent, or improve the speed of the link between them, you're not going to see any eprformance improvement.

-Jamie