18

I have a NAnt-based script which that I run on my local PC that connects to SQL Server 2008 Express also running on my local PC to drop and recreate databases using .sql files - this works fine, no problem here.

The problem comes when I have recreated the same set-up on another PC, I get the error in my NAnt script saying that:

System.InvalidOperationException: The 'SQLNCLI' provider is not registered on the local machine.
  at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
  at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
  at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
  at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
  at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
  at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
  at System.Data.OleDb.OleDbConnection.Open()
  at NAnt.Contrib.Util.SqlHelper..ctor(String connectionString, Boolean useTransaction)
  at NAnt.Contrib.Tasks.SqlTask.ExecuteTask()
  at NAnt.Core.Task.Execute()
  at NAnt.Core.Target.Execute()
  at NAnt.Core.Project.Execute(String targetName, Boolean forceDependencies)
  at NAnt.Core.Project.Execute()
  at NAnt.Core.Project.Run()

I have searched online and have found that in a forum it was suggested that I needed to install the Microsoft SQL Server Native Client, and got it from this URL. (Admittedly, I have only installed the Native Client part of this)

Despite this being installed on my second PC, the script still gives me the same error. Any suggestions?

KyleMit
  • 30,350
  • 66
  • 462
  • 664
Brett Rigby
  • 6,101
  • 10
  • 46
  • 76
  • Should have worked...Did you try restarting the machine? – Robert Harvey Sep 26 '09 at 20:28
  • 1
    Any particular reason why you use the OleDB provider from .Net ? Use the SqlClient provider, it does not require any driver to be installed, everything ships with .net. – Remus Rusanu Sep 26 '09 at 22:44
  • He may just want the flexibility of writing to something other than SQL Server if needed. – SqlRyan Sep 30 '09 at 20:46
  • No, no reason really - it's part of the NAnt script I found online somewhere and didn't question it at the time. I'll look into that part further. – Brett Rigby Oct 01 '09 at 06:51
  • There's no need to change the DB access method, so I wouldn't waste time there if you don't have a need. It just seemed like an odd choice for what you were doing, but since it's in a script you found, that explains it. – SqlRyan Oct 01 '09 at 19:22

2 Answers2

22

The correct provider for SQL Server 2008 is SQLNCLI10.1.
For SQL Server 2012, that would be SQLNCLI11. (source)

Sadly, the migration to 2008 did not pick up the change...

Kobi
  • 135,331
  • 41
  • 252
  • 292
user181938
  • 236
  • 2
  • 2
  • You were right! The connection string needed to make reference to SQLNCL10, and not the SQLNCLI that I had in the script. Nice one. – Brett Rigby Oct 01 '09 at 20:28
  • 1
    Brett, you said SQLNCL10 rather than the correct SQLNCLI10. Probably a typo, but I cut and pasted it :-( – Ben McIntyre Jul 11 '12 at 03:03
  • Edited to clean it up a little. I couldn't find an authoritative list of providers. Some people on the internet suggest **`SQLNCLI11.1`** also works for 2012. – Kobi Feb 11 '13 at 14:12
4

Could it be a problem with x86/x64? If the workstation in question is x64, and you've installed the x64 version of the client, but the application you're trying to run is in the x86 space, you may need the x86 version of the client on that workstation as well. An x86 app will only check for an x86 version of the provider, and seeing none, will give an error like this one. The x64 version is probably smart enough to check for either version, though maybe you'd have the same situation.

This same thing happens with ODBC - x86 and x64 data sources aren't available to the other type of application - if you want something to be visible from both builds of applications, you'll need to create an ODBC source in each version's Admin tool.

SqlRyan
  • 33,116
  • 33
  • 114
  • 199