none
SMO - .NET 4.0

    Question

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


    John J. Hughes
    www.functioninternational.com/
    Wednesday, August 04, 2010 6:23 PM

Answers

  • 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
    Saturday, August 21, 2010 3:24 PM
  • 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.
    Friday, August 02, 2013 8:24 PM

All replies

  • 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


    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 9:50 AM
  • 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.


    John J. Hughes
    www.functioninternational.com/
    Monday, August 09, 2010 12:26 PM
  • 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

     

     

    Sunday, August 15, 2010 11:09 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" ?>

    <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
    Saturday, August 21, 2010 3:24 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.
    John J. Hughes
    www.functioninternational.com/
    Monday, August 23, 2010 12:24 PM
  • 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!

     

    Thursday, January 06, 2011 2:02 PM
  • 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

     

    Thursday, January 06, 2011 5:42 PM
  • 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?
    Monday, January 17, 2011 2:06 PM
  •  <startup useLegacyV2RuntimeActivationPolicy="true">  ExecuteNonQuery worked for me.

     

     



    Sac Net Guy
    Thursday, December 01, 2011 11:39 PM
  • Any updates regarding official SMO support for .NET 4.0?
    Thursday, February 09, 2012 3:18 AM
  • 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


    John J. Hughes II
    www.functioninternational.com

    Thursday, February 09, 2012 12:59 PM
  • Hi

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

    Thanks


    http://www.contentrendering.com
    http://www.smolite.com
    http://www.objecttranspose.com

    Thursday, February 09, 2012 2:32 PM
  • 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!

    Thursday, February 09, 2012 2:51 PM
  • I'm thinking the same. Thanks!
    Thursday, February 09, 2012 2:56 PM
  • Yes

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

    Thanks


    http://www.contentrendering.com
    http://www.smolite.com
    http://www.objecttranspose.com

    Thursday, February 09, 2012 3:07 PM
  • bkejser

    Neat, will look at it, thanks...


    John J. Hughes II
    www.functioninternational.com

    Thursday, February 09, 2012 6:10 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?


    Thursday, February 09, 2012 6:26 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


    http://www.contentrendering.com
    http://www.smolite.com
    http://www.objecttranspose.com

    Friday, February 10, 2012 1:30 PM
  • Thanks! I'll download the new version, try it out, and let you know.

    Friday, February 10, 2012 5:42 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.


    coder

    • Proposed as answer by Rengeek Tuesday, January 28, 2014 8:59 PM
    Monday, July 23, 2012 6:51 PM
  • 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
    Tuesday, October 02, 2012 10:09 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


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Wednesday, October 03, 2012 3:32 PM
  • 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().


    David Adams

    Thursday, October 04, 2012 2:54 AM
  • 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
    Monday, July 01, 2013 2:25 AM
  • 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?


    Ben Miller - SQL Server MVP - @DBADuck

    Thursday, July 25, 2013 10:56 PM
  • 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.
    Friday, August 02, 2013 8:24 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?

    Monday, March 10, 2014 7:50 AM