You receive the following error:
Could not load file or assembly 'Microsoft.SqlServer.ConnectionInfo[...]
Could not load file or assembly 'Microsoft.SqlServer.BatchParser.dll' or one of its dependencies. The specified module could not be found.
The error text may also include the following details:
An error occurred during executing this statement \r\n IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[<TableName>]') AND OBJECTPROPERTY(id, N'IsTable') = 1) DROP TABLE [<TableName>];: ExecuteNonQuery failed for Database '<DatabaseName>'.
You have not installed the SQL Shared Management Objects (SMO). They are needed to create tables and other SQL objects from TX, or alternatively you get this error when connecting to the Tabular model because the Analysis Services Management Objects (AMO) are missing.
For the BatchParser message the server is missing or unable to call a necessary dynamic-link library (dll) file. On a typical machine, you may find multiple copies of Microsoft.SqlServer.BatchParser.dll . However, in some configurations, the dlls installed with TimeXtender and SQL Server Management Studio (SSMS) do not seem to load, while the ones installed with SMO 2016 are.
Download the SQL Shared Management Objects from the SQL Server Feature Packages and install it. The download sites are linked below - the files you need are called SharedManagementObjects.msi and SQLSysClrTypes.msi.
If your current SQL server version is newer than 2016, installing the newest version management studio on the server should be enough.
When you are also connecting to Analysis services, you will need to download the AMO, ADOMD, MSOLAP programs. The client libraries for Azure will also work for sql versions newer than 2016.
- Microsoft Analysis Server Feature Pack 2016
- Client libraries for connecting to Azure Analysis Services
Please be aware that if you are using another version of SQL server, you must find the right version of the feature packages.
If you are deploying to multiple versions of SQL Server, you must install all the relevant versions of SMO/XMO. For example, if staging is SQL 2008 and Data Warehouse is SQL 2012, you must install both of these files.
SQL Native Client is a pre-requisite and will in most cases need to be installed first.
In some cases, you will also need to install more than one version of the Analysis Management Objects package, to resolve this error.
An option for Analysis Services Tabular is to choose the Universal option as the deployment target.
It will help with this error as well.
AMO needs to be installed for tabular when you used to have analysis service version before 2016 installed.
The link above for SQL Server 2017 states:
"Beginning with SQL Server 2017 SMO is distributed as the Microsoft.SqlServer.SqlManagementObjects NuGet package to allow users to develop applications with SMO.
This is a replacement for SharedManagementObjects.msi, which was previously released as part of the SQL Feature Pack for each release of SQL Server. Applications that use SMO should be updated to use the NuGet package instead and will be responsible for ensuring the binaries are installed with the application being developed."
Can I interpret this to mean that TimeXtender are, or will soon, be bundling SMO with the Discovery Hub software, so we won't need to install it ourselves to use Discovery Hub when deploying to SS2017 instances?
(I am still on a slightly older version of Discovery Hub so can't test with your latest release yet.)
It is correct that that starting with SQL Server 2017 the SMO DLLs are bundled with Discovery Hub, so you no longer need to install additional DLLs. The article only applies to SQL Server 2005, 2008, 2012, 2014, and 2016.