SQL Server SMO complains of missing DLL

Question

Ok, I've scoured the web, BOL, various forums and I'm no closer to an answer...hopefully you fine folks can lend a hand...

We've got a dozen or so SQL Servers (some 2k, some 2005) on a network. I'm using SMO objects in a .NET application to get some standard information. My problem appears to boil down to a missing DLL - Microsoft.SqlServer.BatchParser.dll. However, this DLL did not come with the other SQL DLLs (Microsoft.SqlServer.ConnectionInfo.dll, Microsoft.SqlServer.Smo.dll, Microsoft.SqlServer.SmoEnum.dll, Microsoft.SqlServer.SqlEnum.dll, etc...). I also downloaded the SS2005 feature pack from Microsoft's site that includes the SMO objects, but still no luck.

The following code works, unless I uncomment the line that is currently commented, in which case I get the error below:

protected void btnArchive_Click(object sender, EventArgs e)
{
    ServerConnection conn = new ServerConnection("my_server");

    conn.LoginSecure = false;
    conn.Login = "my_login";
    conn.Password = "my_password";

    Server s = new Server(conn);
    Database d = s.Databases["my_database"];
    //Table tbl = d.Tables["my_table"];

    Response.Write(s.Name + " " + s.Information.RootDirectory + " " + d.CreateDate.ToShortDateString());
    conn.Disconnect();
}

Error: Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

Note, I've also tried this from SSIS using VB.NET, same behavior.

Any thoughts would be appreciated.

Thanks.

Malcom

Answer 1

I was able to successfully run your code using the 10.* versions of the assemblies "Microsoft.SqlServer.ConnectionInfo", "Microsoft.SqlServer.Management.Sdk.Sfc" and "Microsoft.SqlServer.Smo". Try downloading the 2008 version of the SMO components, maybe it was a bug that they've now fixed.

Perdurable

Following this worked for me. I will add, however, that in my case I had to use the 32 bit version because my Application Pool was running with 32 bit mode enabled. Took forever to figure out the problem, so I hope this helps someone else. – Biotic

@Biotic you need to have this as it's own answer! hours later this finally saved me (for v 12.0.0.0) – Gerge

Answer 2

Are you running a x64 OS on your box? There appear to be problems with BatchParser.dll in 64-bit environments - usually it is recommended to download the SMO x64 Package (SQLServer2005_XMO_x64.msi) from Microsoft.

See information about this here.

Met

Answer 3

I know I'm very late to the party here, but this is still the first result when you google "smo batchparser".

When installing SMO 2014 (12.0.x) some DLLs are only installed to the GAC. To make your app work without having to manually install SMO in the target server, you must copy these DLLs with your app. Here are the paths where I got them from.

C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll

C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll

C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.SmoExtended.dll

C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll

C:\Windows\assembly\GAC_64\Microsoft.SqlServer.BatchParser\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.BatchParser.dll

C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.BatchParserClient\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.BatchParserClient.dll

C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlClrProvider\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlClrProvider.dll

To access the GAC files you will need to disable the windows built-in viewer using the instructions provided by this article.

An excerpt of the article for convenience:

Open the registry editor and add/set the HKLM\Software\Microsoft\Fusion\DisableCacheViewer DWORD value: 1

Piffle

The BatchParser dll ultimately causes this error: "Attempt to load an unverifiable executable with fixups (IAT with more than 2 sections or a TLS section.) HRESULT : 0x80131019". – Iridic

Hours later: could be caused by application built with any cpu, but with "Prefer 32 bits" activated. – Iridic

Answer 4

You need to install following package to solve the problem , I have solved this problem twice with this package hope it may work for everyone too...

Microsoft SQL Server 2005 Management Objects Collection The Management Objects Collection package includes several key elements of the SQL Server 2005 management API, including Analysis Management Objects (AMO), Replication Management Objects (RMO), and SQL Server Management Objects (SMO). Developers and DBAs can use these components to programmatically manage SQL Server 2005.

You can get from Feature Pack for Microsoft SQL Server 2005 - December 2008 from Microsoft site

http://www.microsoft.com/en-us/download/details.aspx?id=11988

But the link location of download page changes every-time if you don't find resource you can download it from my blog too.

I have also provided some tips and resources about this in my blog if you want http://rndp-android.blogspot.com/p/missing-microsoftsqlservermanagementsdk.html

Maihem

Answer 5

Just so we understand the issue properly, the Microsoft.SqlServer.BatchParser.dll is not installed in the Global Assembly Cache on your development machine? If so, you might want to start with re-installing the .NET Framework (the version of the Framework that you are targeting) to see if that resolves the issue.

You can also try using the .NET Framework Configuration tool to see if the assembly is indeed present in the Global Assembly Cache (GAC). This MSDN Article describes how to use this tool.

(A search of my hard drive did not return the Microsoft.SqlServer.BatchParser.dll assembly even though I can see it using the .NET Framework Configuration tool).

Irenics

Thanks for the reply. Though, I don't specifically know where the Global Assembly Cache is located, I have searched the entire drive and that dll does not exist. I have also re-installed the latest framework (3.5 SP1). – Malcom

The GAC is usually in %windir%\assembly – Pumpkin

Answer 6

it comes as part of the 2005 or 2008 upgrade advisor.

Genteelism