Re: SQLXMLBulkLoad Question (Please help)

Imports SQLXMLBULKLOADLib
Imports SQLDMO

I have reference to Interop.SQLDMO and Interop.SQLXMLBULKLOADLib

"MSSQLServerDeveloper" wrote:

> Lee - What reference do you add to your project and / or imports do you have
> in your code. I cant get the line -
> Dim oBulkLoader As SQLXMLBulkLoad = New SQLXMLBulkLoad3Class()
>
> to work
>
> "LeeH" wrote:
>
> > Here's the vb code...
> >
> > Private Sub GetrDone()
> >
> > Dim dsOld As New DataSet()
> > Dim dsNew As New DataSet()
> >
> > Dim cn As New SqlClient.SqlConnection("user id=sa;password=;initial
> > catalog=bktest;data source=(local);Connect Timeout=30;pooling=true")
> > cn.Open()
> >
> > Dim cmd As New SqlClient.SqlCommand("SELECT * from TestTable ", cn)
> > Dim da As New Data.SqlClient.SqlDataAdapter(cmd)
> >
> > da.Fill(dsOld)
> >
> > dsOld.Tables(0).TableName = "TestTableNew"
> >
> > Dim schemaFileName = "c:\XmlSchemaFile.xsd"
> > Dim xmlFileName = "c:\XmlDataFile.xml"
> > dsOld.WriteXml(xmlFileName)
> > dsOld.WriteXmlSchema(schemaFileName)
> >
> > Try
> > Dim oBulkLoader As SQLXMLBulkLoad = New SQLXMLBulkLoad3Class()
> >
> > oBulkLoader.BulkLoad = True
> > oBulkLoader.CheckConstraints = True
> > oBulkLoader.ErrorLogFile = "c:\LoaderError.log"
> > oBulkLoader.ConnectionString =
> > "PROVIDER=SQLOLEDB.1;SERVER=localhost;DATABASE=testDB;uid=sa;pwd="
> > oBulkLoader.Execute(schemaFileName, xmlFileName)
> >
> > Catch ex As Exception
> > MsgBox(ex.Message)
> > End Try
> >
> > End Sub
> >
> >
> > "MSSQLServerDeveloper" wrote:
> >
> > > Yes - The vb code.
> > >
> > > "LeeH" wrote:
> > >
> > > > I posted the .xml and .xsd file earlier.
> > > > Do you mean the vb code?
> > > >
> > > > I get the same results running from VB and from a stored procedure...
> > > >
> > > > "MSSQLServerDeveloper" wrote:
> > > >
> > > > > Lee - Can I get a snippet of your code?
> > > > >
> > > > > "LeeH" wrote:
> > > > >
> > > > > > I don't think a default will work because I want to preserve both spaces and
> > > > > > empty strings. Both of these result in a Null.
> > > > > >
> > > > > > "Graeme Malcolm" wrote:
> > > > > >
> > > > > > > Can you create a default on the columns in the table? (e.g. DEFAULT= '')
> > > > > > >
> > > > > > > --
> > > > > > > Graeme Malcolm
> > > > > > > Principal Technologist
> > > > > > > Content Master
> > > > > > > - a member of CM Group Ltd.
> > > > > > > www.contentmaster.com
> > > > > > >
> > > > > > >
> > > > > > > "LeeH" <LeeH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > > > > news:20272FBE-1E6F-42D4-AB26-44FB302C2B08@xxxxxxxxxxxxxxxx
> > > > > > > I posted this last week and no one posted a reply. I'll try to re-word the
> > > > > > > question so it's clearer as to what I'm asking.
> > > > > > >
> > > > > > > I'm running SQLXMLBulkLoad from a vb.NET app supplying both and xml file and
> > > > > > > a schema (xsd) file. The data is getting loaded into the table except for
> > > > > > > columns that contain spaces or empty strings. These columns are being
> > > > > > > populated with NULLS.
> > > > > > >
> > > > > > > Is there anyway to instruct SQLXMLBulkLoad to load columns with empty
> > > > > > > strings (or columns that contain only spaces) and retain those values
> > > > > > > instead
> > > > > > > of populating with a Null?
> > > > > > >
> > > > > > > The XML input was created from a dataset (WriteXml) and I'm trying to load
> > > > > > > this data into another database and it needs to retain all values (i.e.
> > > > > > > empty
> > > > > > > strings). I expected that column tags that are missing from the xml file
> > > > > > > would be loaded as nulls but columns such as <tag1></tag2> would load as an
> > > > > > > empty string.
> > > > > > >
> > > > > > > I've searched for this problem extensively but can't find that anyone has
> > > > > > > experienced this problem. However, it seems so basic (Null vs empty string)
> > > > > > > I would think that if SQLXMLBulkLoad did not load data correctly that other
> > > > > > > people would have reported this since Null and empty string handling are so
> > > > > > > basic from a database perspective. Am I missing something here?
> > > > > > >
> > > > > > >
> > > > > > >
.

Relevant Pages

  • Re: VB.Net not building added file
    ... What was happening was that I still had to manually add a reference to ... I would have thought that if the project was building the .dll, ... Imports System.Data.SqlClient ... Dim SqlConnection1 As SqlConnection ...
    (microsoft.public.dotnet.languages.vb)
  • Re: VB.Net not building added file
    ... What was happening was that I still had to manually add a reference to it. ... Imports System.Data.SqlClient ... Dim SqlConnection1 As SqlConnection ... Dim myDbObject As New DbObject ...
    (microsoft.public.dotnet.languages.vb)
  • Re: how to reference feild data -
    ... > Imports System.Data ... > Dim conn As New ADODB.Connection ... reference the proper field by specifing the name or the index. ...
    (microsoft.public.data.ado)
  • Re: how to reference feild data -
    ... >> syntax I'm used to in Access doesn't seem to work - ... >> Imports System.Data ... >> Dim conn As New ADODB.Connection ... > reference the proper field by specifing the name or the index. ...
    (microsoft.public.data.ado)
  • Re: Shared functions vs Non-Shared Functions
    ... > dim context as HttpContext = HttpContext.Current ... Imports Microsoft.VisualBasic ... Public Shared sub sendEmail ... >> dim webMasterEmail As String ...
    (microsoft.public.dotnet.framework.aspnet)