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

http://www.microsoft.com/downloads/details.aspx?FamilyID=228de03f-3b5a-428a-923f-58a033d316e1&DisplayLang=en

 Error message:

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.

Hi John,

Based on your description and the error message, I think the issue is more related to .NET Framework. Please refer to the following threads:

http://social.msdn.microsoft.com/Forums/en/vstsdb/thread/00bb76a9-a52f-46f8-ae58-56729cea5783

http://social.msdn.microsoft.com/Forums/en-US/clr/thread/1f60a379-e2e3-46b9-b343-0235486fc746

Jian,

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.

Hi

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.

Thanks

 

 

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" ?>

<configuration>

  <startup useLegacyV2RuntimeActivationPolicy="true">

    <supportedRuntime version="v4.0"/>

  </startup>

</configuration>

 

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" ?>

<configuration>

  <startup useLegacyV2RuntimeActivationPolicy="true">

    <supportedRuntime version="v4.0"/>

  </startup>

</configuration>

 

• Proposed as answer by bkejser_ Saturday, August 21, 2010 3:28 PM
• Marked as answer by jjhii Wednesday, September 08, 2010 2:27 PM

I 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.

Hi Vindy,

 

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.)

Thank you!

 

Hello bk1234,

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

 

Trying 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?

 <startup useLegacyV2RuntimeActivationPolicy="true">  ExecuteNonQuery worked for me.

 

 

Any updates regarding official SMO support for .NET 4.0?

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.

http://www.microsoft.com/download/en/details.aspx?id=27069

Hi

I created an open source version of SMO called SMOLite. This may help you.

Thanks

Hi bkejser_,

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?

Thanks!

I'm thinking the same. Thanks!

Yes

It handles go statements. I'll update the sample to indicate this.

Thanks

bkejser

Neat, will look at it, thanks...

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?

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

Hi

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.

Thanks

Thanks! I'll download the new version, try it out, and let you know.

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.

Unfortunately this did not fix the problem for me.  I tried both 10.5 (100\SDK) and 11.0 (110\SDK) without success.

Unfortunately this did not fix the problem for me.  I tried both 10.5 (100\SDK) and 11.0 (110\SDK) without success.

• Proposed as answer by Rengeek Tuesday, January 28, 2014 8:59 PM
• Unproposed as answer by Rengeek Tuesday, January 28, 2014 8:59 PM

Hello,

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

Thank you for your suggestion Papy, but I tried that as well.  I have the same problem as before - it fails when I do ExecuteNonQuery().

It is now 2013, I have Visual Studio 2012 and SQL Server 2012 installed and I have the same problem. The config file workaround doesn't work for test projects (without hack). If there was a an alternative to approach to run an sql script from C# I would gladly go with that, but I can't see one. 
EDIT: to answer my own question - I ended up using EF and code. Not ideal for scripting database create/fill type operations.

It is now 2013, I have Visual Studio 2012 and SQL Server 2012 installed and I have the same problem. The config file workaround doesn't work for test projects (without hack). If there was a an alternative to approach to run an sql script from C# I would gladly go with that, but I can't see one. 
EDIT: to answer my own question - I ended up using EF and code. Not ideal for scripting database create/fill type operations.

• Edited by acarlonvsn1 Monday, July 01, 2013 5:14 AM update

You do not need SMO to execute a SQL Script in .NET.  Can't you just use a SqlConnection and SqlCommand in System.Data.SqlClient namespace?

The SqlCommand can take a string that has all the stuff from a file .sql in it.  Then you just call the method ExecuteNonQuery(string) on the SqlCommand object. Am I missing something?

You can run a single ddl statement with execute non query, but if you are trying to create 200 tables in a single script separated by go, you can not use SQLCommand.  It will not process a batch of statements.

You can run a single ddl statement with execute non query, but if you are trying to create 200 tables in a single script separated by go, you can not use SQLCommand.  It will not process a batch of statements.

• Proposed as answer by Papy NormandModerator Friday, August 02, 2013 9:00 PM
• Marked as answer by Papy NormandModerator Sunday, September 01, 2013 11:11 PM

Hi,

I have already changed my app.config file

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <startup useLegacyV2RuntimeActivationPolicy="true">

    <supportedRuntime version="v4.0"/>

  </startup>

</configuration>

and target framework 4.0 and prerequisites 4.0 but still i have same problem.Any one have ideas?