Wednesday, August 04, 2010 6:23 PM
I created a new application with VS2010 which by default uses the .NET 4.0. I added some canned function from an existing application which uses .NET 3.5. The canned functions use SMO so I added the 10.0.0.0 SMO from the SDK that I have on my system. When I run the application it throws and can't use 2.0 with 4.0 error.
Is there a new SDK for SQL that uses the NET 4.0? Or another work around?
The SQL SDK I am using is August 2008
Microsoft.SqlServer.Management.Smo.FailedOperationException: ExecuteNonQuery failed for Database 'TM-Data'. ---> System.IO.FileLoadException: Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.
John J. Hughes
Monday, August 09, 2010 9:50 AM
Based on your description and the error message, I think the issue is more related to .NET Framework. Please refer to the following threads:
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
Monday, August 09, 2010 12:26 PM
Thanks for the response. I disagree that it is a .NET framework issue per say. If the SQL group updates there SDK files to use .NET 4.0 I won't have a problem so the question stands is there or is there going to be an update to the SMO SDK?
Now as you have pointed out there are many reference to the "useLegacyV2RuntimeActivationPolicy" and I agree it might work if placed in the machine.config but it does not work in my case in the app.config. I will not have access to my client's machines to update the machine.config file. If there is another way of telling the system to use both I could try that but I have not found it yet. Most of the solutions I have found are for WEB applications and my is WPF based.
I am also concerned if I require .NET 4.0 to be installed which means the .NET 2.0/3.0/3.5 don't have to exist (if I understand 4.0) setting the legacy value will cause a failure which will require I tell them to install 3.5 with 2.0. Sort of defeats the 4.0 not requiring 2.0 concept.
Thanks but still looking for an answer.
John J. Hughes
Sunday, August 15, 2010 11:09 PM
I have the same problem.
The ExecuteNonQuery method of the Database class failed, however, I was able to create tables and stored procedures using the Table and Procedure clases.
I was only able to resolve the problem by downgrading to the 3.5 framework.
I'm curious why Microsoft has not provided new SMO libraries for the 3.5 and 4.0 frameworks. There isn't much point upgrading to the new framework if the libraries we are dependent on are not being upgraded.
Saturday, August 21, 2010 3:24 PM
This is a known issue. SMO's usage against .Net 4.0 hasnt been signed off or announced by microsoft.
There is an unsupported option to get this working.
<?xml version="1.0" encoding="utf-8" ?>
Monday, August 23, 2010 12:24 PMI tried that and it did not work but it is possible I put it in the wrong place? I was using a WPF application in .NET 4. Since then I have moved all my application back to 3.5sp1 since all my application use SMO to some extent.
John J. Hughes
Thursday, January 06, 2011 2:02 PM
I have the same problem with trying to run CREATE PROCEDURE statements from a file with GO statements between each (in a .NET 4.0 project). I "discovered" SMO and thought I had found the solution, but it does not work, and creating an app.config with the attributes that you specified do not work. (The attribute useLegacyV2RuntimeActivationPolicy is not found in the schema.) Also, when I run my application with such a config file, no exception is thrown, it simply does not work.
Can you please provide an update as to whether Microsoft has yet signed off on SMO usage with .NET 4.0. I want to close the door on this solution once and for all, if indeed, it is not to be used.
Also, can you inform on alternatives? As an administrator, I need to be able to install a database and its objects via a script file. I would like to avoid rewriting everything to use the "exec sp_executesql". (I have huge scripts to run.)
Thursday, January 06, 2011 5:42 PM
I was wondering if you would be so kind as to share the code of your solution. I cannot find a way to ExecuteNonQuery for something called a procedure class, that you've indicated is in the SMO collection of namespaces.
(I have changed my target framework to 3.5 from 4.0 already, in preparation of trying out your solution.)
Thank you in advance, Peg
Monday, January 17, 2011 2:06 PMTrying to deploy the latest version of our product with .NET 4 Just ran into this mixed mode problem and see all the postings from last summer (including yours) regarding the use of the "uselegacy" parameter. Seems like an ugly workaround with potential problems if you are not careful. Also seems it was made available mostly to support those shops who are dragging their feet moving their assemblies to .NET 4. I find it hard to believe (or maybe not) that the offender in my case is Microsoft themselves due to my need for SMO. To make it worse this is January 2011 now. I am getting suspicious that this is a bad sign that maybe SMO is about to be shelved. Hope I'm wrong but I find it hard to believe Microsoft can't get around to rebuilding their SMO assemblies with .NET 4. Can anyone at Microsoft calm our concerns that SMO is dying? If so, any idea when we can expect a .NET 4 compatible set of assemblies?
Thursday, December 01, 2011 11:39 PM
<startup useLegacyV2RuntimeActivationPolicy="true"> ExecuteNonQuery worked for me.
Sac Net Guy
Thursday, February 09, 2012 3:18 AMAny updates regarding official SMO support for .NET 4.0?
Thursday, February 09, 2012 12:59 PM
Since SMO is part of SQL my guess would be when they release SQL 2012 unless it has 4.5. Most likely they will relase SQL 2012 with 4.0 and sortly thereafter release .NET 4.5 and we will have the same problem with 4.5 as we now have with 4.0. you could try downloading the preview 3, maybe it has it. I have not had time to deal with it.
John J. Hughes II
Thursday, February 09, 2012 2:32 PM
I created an open source version of SMO called SMOLite. This may help you.
Thursday, February 09, 2012 2:51 PM
I was just looking at the examples on the smolite.com site, but couldn't determine whether it will also work for runing "*.sql" script files without the annoying "GO" errors, etc. Does SMOLite support running "*.sql" script files without modification?
Thursday, February 09, 2012 2:56 PMI'm thinking the same. Thanks!
Thursday, February 09, 2012 3:07 PM
It handles go statements. I'll update the sample to indicate this.
Thursday, February 09, 2012 6:10 PM
Neat, will look at it, thanks...
John J. Hughes II
Thursday, February 09, 2012 6:26 PM
I just downloaded and added SMOLite in my project's references, however, the only way that I found to create a server object is by using the Server.ConnectionContext.ServerInstance property. Unfortunately, I too need to specify other connection parameters like: timeout, username, password or Trusted Connection, etc.
Is there any other way that I can instantiate the Server class? It would be perfect if I could use a SqlConnection object instead of just the ServerInstance.
Thanks for putting this library together. The documentation seems really good too :) Did you use SandCastle by any chance?
- Edited by --- Miguel Guzman --- Thursday, February 09, 2012 7:08 PM
Friday, February 10, 2012 1:30 PM
Yes. Sandcastle produces the documentation.
A second constructor on the Server class has been aded to accept a SqlConnection instance. This is on the 1.1.4 release.
Friday, February 10, 2012 5:42 PM
Thanks! I'll download the new version, try it out, and let you know.
Monday, July 23, 2012 6:51 PM
If your project uses .NET Framework 4.0, you need to add the version of 10.0 Microsoft.SqlServer.Smo as a reference which you can find from your installation directory such as c:\Program Files (x86)\Microsoft SQL Server\100\SDK\Asseblies.
Tuesday, October 02, 2012 10:09 PMUnfortunately this did not fix the problem for me. I tried both 10.5 (100\SDK) and 11.0 (110\SDK) without success.
Wednesday, October 03, 2012 3:32 PMModerator
Have you tried to copy as locale the only version 11.0 ( you click on your reference , right-click on your reference, select Properties and you chane the value of Copy as local from false to true ).Usually, it is working. With a little annoyement , your executable will be bigger.
Advantage : it is working for 10.5 and 11.0. I used it to do applications versus 90 (2005) and 2008.
Have a nice day
Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
Thursday, October 04, 2012 2:54 AMThank you for your suggestion Papy, but I tried that as well. I have the same problem as before - it fails when I do ExecuteNonQuery().