SQLXMLBULKLOAD Invalid Connection string

Posted on 2009-12-29
Last Modified: 2012-05-08
Running SQLXMLBULKLOAD I am getting an issue with the connection string msg "Invalid connection string".
I have tried many variation on what I believe the the connection string should be however continue getting the same msg.
The source attached below show the various options I have used including, any thoughts on what the correct connection string should be.
"<%$ ConnectionStrings:acVitaConnectionString %>" is a working connection I already have set up that can map data to other .net object. This is configured in the web.config file connection string as <add name="acVitaConnectionString" connectionString="Data Source=SIM-PC\sqlexpress;Initial Catalog=acVita;Integrated Security=True" providerName="System.Data.SqlClient"/>.

Of interest is that the code falls over on the objbl.execute line rather than objbl.ConnectionString line could this imply incoreect schema, I have validated the schema and this appears correct.
Dim objbl As Object

        objbl = CreateObject("SQLxmlbulkload.SQLxmlbulkload")

        ' objbl.ConnectionString = "<%$ ConnectionStrings:acVitaConnectionString %>"

        '   objbl.ConnectionString = "provider=SQLOLEDB;data source=localhost;database=acVita;integrated security=SSPI"

        '   objbl.ConnectionString = "provider=System.Data.SqlClient;data Source=SIM-PC\sqlexpress;database=acVita;Integrated Security=True "

        '   objbl.ConnectionString = "Data Source=SIM-PC\sqlexpress;Initial Catalog=acVita;Integrated Security=True providerName=System.Data.SqlClient"

        '   objbl.ConnectionString = "Server=SIM-PC\sqlexpress;Database=acVita;Trusted_Connection=True;"

        ' objbl.ConnectionString = ConfigurationManager.ConnectionStrings("acVitaConnectionString").ConnectionString

        objbl.ConnectionString = "provider=sqlexpress;data source=SIM-PC;database=acVita; Integrated Security=SSPI;"

        objbl.CheckConstraints = True

        objbl.ErrorLogFile = "C:\error.log"

        ' objbl.Execute("~/Data/Schema/PoRSchema.xsd", "~/Data/PaidOnResults-Millets.xml")

        objbl.Execute("C:\Users\Sim\Documents\Visual Studio 2008\WebSites\acVita\Data\Schema\PoRSchema.xsd", "C:\Users\Sim\Documents\Visual Studio 2008\WebSites\acVita\Data\PaidOnResults-Millets.xml")

        objbl = Nothing

Open in new window

Question by:Simon Cripps
    LVL 42

    Expert Comment

    this is the connection string format for trusted connection in sql oledb:
    Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

    for standard security:
    Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
    LVL 42

    Accepted Solution

    if u connect to an SQL Server instance:
    Provider=sqloledb;Data Source=myServerName\theInstanceName;Initial Catalog=myDataBase;Integrated Security=SSPI;

    Author Closing Comment

    by:Simon Cripps
    Thanks that seems to have done the trick, in resolving the connection string issues, but cannot fully test yet as fallig over on other issues as well.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now