bulk load from a stream

The docs for sqlxml bulk loader indicate that the execute method
supports a stream for the document to load.
This code works:
xslTransform.Load("map.xsl")
textWriter = New XmlTextWriter("out.xml", Nothing)
xslTransform.Transform(xmlRsp, Nothing, textWriter)
textWriter.Flush()
textWriter.Close()
With xmlBulkLoader
.ConnectionString = "provider=SQLOLEDB;..."
.ErrorLogFile = "bulkLoaderErrors.txt"
.KeepIdentity = False
.XMLFragment = True
.Execute("schema.xml", "out.xml")
End With
When the transform is sent into a memory stream, the stream fills as
expected
dim memStream as New System.IO.MemoryStream
xslTransform.Transform(xmlRsp, Nothing, memStream)
but I can't figure out how to use memStream in
xmlBulkLoader.Execute("schema.xml", memStream)
I've tried using various properties of the memStream, and tried using a
stream reader, but the execute always returns "Error opening data file."
Check out the article here:
http://msdn.microsoft.com/library/de...exchsqlxml.asp
It shows how to do this in .NET
Irwin
<jmeerdink@.synergy.gs> wrote in message
news:1110571753.372547.264070@.f14g2000cwb.googlegr oups.com...
> The docs for sqlxml bulk loader indicate that the execute method
> supports a stream for the document to load.
> This code works:
> xslTransform.Load("map.xsl")
> textWriter = New XmlTextWriter("out.xml", Nothing)
> xslTransform.Transform(xmlRsp, Nothing, textWriter)
> textWriter.Flush()
> textWriter.Close()
> With xmlBulkLoader
> .ConnectionString = "provider=SQLOLEDB;..."
> .ErrorLogFile = "bulkLoaderErrors.txt"
> .KeepIdentity = False
> .XMLFragment = True
> .Execute("schema.xml", "out.xml")
> End With
> When the transform is sent into a memory stream, the stream fills as
> expected
> dim memStream as New System.IO.MemoryStream
> xslTransform.Transform(xmlRsp, Nothing, memStream)
> but I can't figure out how to use memStream in
> xmlBulkLoader.Execute("schema.xml", memStream)
> I've tried using various properties of the memStream, and tried using a
> stream reader, but the execute always returns "Error opening data file."
>
|||Thanks Irwin - wow that's a lot of code. The article mentioned that
ADODB.Stream is directly supported. Would that easier to work with?
|||That's in native code, looked like you were doing managed. If you were
doing native code it might be.
"jayMeer" <jmeerdink@.synergy.gs> wrote in message
news:1112792519.498993.310540@.z14g2000cwz.googlegr oups.com...
> Thanks Irwin - wow that's a lot of code. The article mentioned that
> ADODB.Stream is directly supported. Would that easier to work with?
>

bulk load from a stream

The docs for sqlxml bulk loader indicate that the execute method
supports a stream for the document to load.
This code works:
xslTransform.Load("map.xsl")
textWriter = New XmlTextWriter("out.xml", Nothing)
xslTransform.Transform(xmlRsp, Nothing, textWriter)
textWriter.Flush()
textWriter.Close()
With xmlBulkLoader
.ConnectionString = "provider=SQLOLEDB;..."
.ErrorLogFile = "bulkLoaderErrors.txt"
.KeepIdentity = False
.XMLFragment = True
.Execute("schema.xml", "out.xml")
End With
When the transform is sent into a memory stream, the stream fills as
expected
dim memStream as New System.IO.MemoryStream
xslTransform.Transform(xmlRsp, Nothing, memStream)
but I can't figure out how to use memStream in
xmlBulkLoader.Execute("schema.xml", memStream)
I've tried using various properties of the memStream, and tried using a
stream reader, but the execute always returns "Error opening data file."Check out the article here:
http://msdn.microsoft.com/library/d... />
sqlxml.asp
It shows how to do this in .NET
Irwin
<jmeerdink@.synergy.gs> wrote in message
news:1110571753.372547.264070@.f14g2000cwb.googlegroups.com...
> The docs for sqlxml bulk loader indicate that the execute method
> supports a stream for the document to load.
> This code works:
> xslTransform.Load("map.xsl")
> textWriter = New XmlTextWriter("out.xml", Nothing)
> xslTransform.Transform(xmlRsp, Nothing, textWriter)
> textWriter.Flush()
> textWriter.Close()
> With xmlBulkLoader
> .ConnectionString = "provider=SQLOLEDB;..."
> .ErrorLogFile = "bulkLoaderErrors.txt"
> .KeepIdentity = False
> .XMLFragment = True
> .Execute("schema.xml", "out.xml")
> End With
> When the transform is sent into a memory stream, the stream fills as
> expected
> dim memStream as New System.IO.MemoryStream
> xslTransform.Transform(xmlRsp, Nothing, memStream)
> but I can't figure out how to use memStream in
> xmlBulkLoader.Execute("schema.xml", memStream)
> I've tried using various properties of the memStream, and tried using a
> stream reader, but the execute always returns "Error opening data file."
>|||Thanks Irwin - wow that's a lot of code. The article mentioned that
ADODB.Stream is directly supported. Would that easier to work with?|||That's in native code, looked like you were doing managed. If you were
doing native code it might be.
"jayMeer" <jmeerdink@.synergy.gs> wrote in message
news:1112792519.498993.310540@.z14g2000cwz.googlegroups.com...
> Thanks Irwin - wow that's a lot of code. The article mentioned that
> ADODB.Stream is directly supported. Would that easier to work with?
>

BULK COPY - in memory data

Hi,

I have a set of records in application memory seperated by a record terminator '\n'. I can write the memory stream to a local disk file and call bcp api functions to load the file in to SQL server. But how do I transfer the in memory data directly to the SQL server, without writing to a data file, using ODBC. I am not using any .Net Framework classes in my code. The SQL server and application server(generating the data records) are on two different physical servers connected through network. I am trying to figureout the fastest and efficient way to load the data to SQL server from a remote application server. Thanks for your help.

Srini

Hi Srini,

You need to use the In-Memory BCP APIs for this purpose. You can look up the MSDN documentation for bcp_bind function which also has a small sample code about how to do it. Here is a link:

http://msdn2.microsoft.com/ru-ru/library/ms131401.aspx

Thanks

Waseem