[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQLXMLBULKLOAD Invalid Connection string

Posted on 2009-12-29
Medium Priority
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
  • 2
LVL 42

Expert Comment

by:Meir Rivkin
ID: 26143585
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

Meir Rivkin earned 2000 total points
ID: 26143589
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
ID: 31670862
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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

873 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