StrataFrame Forum » StrataFrame Database Deployment Toolkit » Database Deployment Toolkit (How do I?) » Deployed App - SMO Error

Deployed App - SMO Error

William Fields — Mon, 08 Dec 2014 10:33:24 GMT

Hello,

From a non-development machine, I'm running a customized/compiled version of the SF Database Installer sample app and am getting the following error when the DDT metadata is deployed:

The SQLSMOMergeModulel.msm was installed on the workstation along with the SF DLL's.

Any ideas?

• 12/03/2014 2:10:53 PM -> Deploying meta-data for database 'DDTdeploymentTest'
X 12/03/2014 2:10:53 PM -> An error occurred while deploying the meta-data to the server.
X 12/03/2014 2:10:53 PM -> IndexOutOfRangeException
X 12/03/2014 2:10:53 PM ->   Index was outside the bounds of the array.
X 12/03/2014 2:10:53 PM ->
X 12/03/2014 2:10:53 PM -> Source     : Microsoft.SqlServer.Smo
X 12/03/2014 2:10:53 PM ->
X 12/03/2014 2:10:53 PM -> Stack Trace:
X 12/03/2014 2:10:53 PM ->
X 12/03/2014 2:10:53 PM ->    at Microsoft.SqlServer.Management.Smo.SqlPropertyMetadataProvider.PropertyNameToIDLookupWithException(String propertyName, PropertyAccessPurpose pap)
X 12/03/2014 2:10:53 PM ->    at Microsoft.SqlServer.Management.Smo.PropertyCollection.GetProperty(String name)
X 12/03/2014 2:10:53 PM ->    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetDbComparer(Boolean inServer)
X 12/03/2014 2:10:53 PM ->    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitializeStringComparer()
X 12/03/2014 2:10:53 PM ->    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.get_StringComparer()
X 12/03/2014 2:10:53 PM ->    at Microsoft.SqlServer.Management.Smo.SimpleObjectCollectionBase.InitInnerCollection()
X 12/03/2014 2:10:53 PM ->    at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetObjectByKey(ObjectKeyBase key)
X 12/03/2014 2:10:53 PM ->    at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.Contains(ObjectKeyBase key)
X 12/03/2014 2:10:53 PM ->    at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.FindDatabase(Int32 DatabasePK)
X 12/03/2014 2:10:53 PM ->    at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.EnsureDatabases()
X 12/03/2014 2:10:53 PM ->    at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.DeployMetaDataThread()
X 12/03/2014 2:10:53 PM ->

RE: Deployed App - SMO Error

William Fields — Mon, 08 Dec 2014 10:33:24 GMT

Thanks for the information Edhy!

I did run into problems installing the 2012 versions of the CLR/SMO packages on Windows v8.1 and posted it to the SQL Server forums.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c55d8104-79aa-44d1-9df1-05416a4ad7ae/sharedmanagementobjectsmsi-wont-install?forum=sqldatabaseengine

The response was the 2012 packages are not Win8.1 compatible. I ran into the same error when attempting to install the 2014 versions too.

Anyhow, the SSMS install is a good work around until the StrataFrame SQLSMOMergeModulel.msm is updated to work w/ SQL 2012.

Bill

RE: Deployed App - SMO Error

Edhy Rijo — Fri, 05 Dec 2014 14:15:44 GMT

Hi William,
[quote][b]William Fields (12/4/2014)[/b][hr]I can confirm that after installing the SQL Server 2012 Express Management Studio (and associated bundled tools), the update tool works..[/quote]

Be aware that for an end user, you don't need to install the SQL Management Studio, that is overkill for any installation, what you need is the SharedManagementObjects.msi as posted in my previous message.
in fact here are the link for SQL 2014 which includes the files you need:

Microsoft® SQL Server® 2014 Feature Pack

http://www.microsoft.com/en-in/download/details.aspx?id=42295

Remember you will need these 2 files for either x64 or x86

SharedManagementObjects.msi
SQLSysClrTypes.msi

Enjoy!!!!

RE: Deployed App - SMO Error

StrataFrame Team — Thu, 04 Dec 2014 16:29:05 GMT

William,

I'm glad you found a workaround. I thought that installing just the management tools and associated connectors would fix it, but I had never confirmed that. We've always installed full SQL Server 2012 (Standard or Development depending on the machine), so I had never tried it with only installing the management tools.

Edhy,

Thanks for that post. I remember us running into that problem when trying to get over the x64 stuff, but I didn't remember whether it worked with SQL Server 2012. I do have a folder on my computer that has the 2008 R2 SMO objects saved off that I have to copy back to the GAC on a computer after installing SQL Server 2012 in order to be able to build the DBEngine from source.

We used SMO hoping it would simplify things, but every time SQL Server has a new version we run into deployment/compilation problems. I'm guessing we're going to have the same issues with SQL Server 2014 when it becomes more widespread.

Hrm... Maybe that's why we've avoided SMO for V2...

RE: Deployed App - SMO Error

William Fields — Thu, 04 Dec 2014 16:11:08 GMT

Thanks Ben.

I can confirm that after installing the SQL Server 2012 Express Management Studio (and associated bundled tools), the update tool works. Here's the link to the download page:

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

There are a number of files that can be downloaded from that page, but I only grabbed the management studio ones:

SQLManagementStudio_x86_ENU.exe

SQLManagementStudio_x64_ENU.exe

So, I have a work around for now.

Thanks!

Might be good to post that the current SMO on the SF download page is not SQL 2012 compatible and point to this forum link for more info.

RE: Deployed App - SMO Error

Edhy Rijo — Thu, 04 Dec 2014 15:51:59 GMT

Hi All,

Ben, now that you mentioned this, I remember, yes there is a version I use for SMO which works fine for me with MS-SQL 2012 - 2014, you can find the link here in this post http://forum.strataframe.net/FindPost32382.aspx

William, download that version and see if that work for you.
Be aware that the files you need from above link are:

1033\x64\SharedManagementObjects.msi
1033\x86\SharedManagementObjects.msi

RE: Deployed App - SMO Error

StrataFrame Team — Thu, 04 Dec 2014 15:10:08 GMT

You are correct in that it is an issue with SQL Server 2012. MS changed the engine enough that it will not work with the SMO that is distributed. That error occurs when the SMO tries to enumerate the databases. Funny thing is, SQL Server Management Studio also uses SMO, so for a long while, I was not able to connect to any of the SQL Server 2012 databases in our office with my development workstation because I only had SQL Server 2008 R2 Developer and the accompanying Management Studio installed. I would get the same exact IndexOutOfRange error whenever I tried to connect with the Object Explorer and open the Databases node in the tree.

We have not had the issue with deployment because our updates are almost always done local to the SQL Server. Our updater is run on the same machine, and if SQL Server 2012 is installed on the same machine where you try to run the DDT, it works properly. I am not sure if this is because the SMO will use the v11 (2012) SMO objects from the GAC (I doubt it) or if the SQL Native Client needs to be updated on the machine (more likely). I have not researched the issue beyond knowing that it doesn't work. I would start by installing the newest SQL Server Native Client or SQL Server Connectivity SDK on the machine you're trying to deploy from. If you're still stuck after that, let me know, and I will do some more digging.

RE: Deployed App - SMO Error

Trent L. Taylor — Thu, 04 Dec 2014 15:01:52 GMT

William:

It doesn't look like an SMO issue, but rather something that is going on within your package. The portion that mentioned "PropertyNameToIDLookupWithException" is what is making me think that something else is going...that exception CAME from SMO. That being said, here is what I would do. First, create a simple database and table and package it up. Attempt to deploy to an environment other than development to prove this point. Then, start looking over the structures to see if there is something not quite right with one of your primary key fields on your table structures.

I think that it is going to be somewhere in this neighborhood versus the SMO not working. Did it create anything when you deployed the structures? Did it at least create the database or did it fail right off of the bat?

RE: Deployed App - SMO Error

William Fields — Thu, 04 Dec 2014 12:28:14 GMT

Tried the same install on Win7x64 Pro and am seeing the same SMO error.

I installed the 2012 CLR/SMO packages on the same workstation and am seeing the same error.

I'm kind of dead in the water and unable to deploy a functioning database update utility to my customers at this point.

Does anyone have a basic install package and app that uses MDDeployMain() and works to update SQL Server 2012 SP2 databases?

Mind if I take a copy?

RE: Deployed App - SMO Error

William Fields — Thu, 04 Dec 2014 09:35:04 GMT

Is the current SQLSMOMergeModulel.msm on StrataFrame's download area compatible with SQL Server 2012?

RE: Deployed App - SMO Error

William Fields — Thu, 04 Dec 2014 09:26:19 GMT

FYI - I do not get the error when running the same app on my development workstation.