• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1164
  • Last Modified:

SQLXMLBULKLOAD Invalid Connection string

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

Simon Cripps
Simon Cripps
  • 2
1 Solution
Meir RivkinFull stack Software EngineerCommented:
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;
Meir RivkinFull stack Software EngineerCommented:
if u connect to an SQL Server instance:
Provider=sqloledb;Data Source=myServerName\theInstanceName;Initial Catalog=myDataBase;Integrated Security=SSPI;
Simon CrippsOwnerAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now