locked
Use a SQL query to insert row into a external database from a plugin, CRM 2011 RRS feed

  • Question

  • Hello friends,

          I have a plugin registered on the create event of a custom entity.  In the plugin, I need to execute a SQL query to insert a row into an external database.  I tried the below code, but am getting some Generic SQL error or something.

     

      using (SqlConnection connection = new SqlConnection("Data Source=WIN-L4G6G1O2M61;Initial Catalog=ERM;Integrated Security=True"))
                    {
                        SqlCommand command = new SqlCommand("INSERT INTO Tablename(columnname) VALUES ('value1)", connection);
                        command.Connection.Open();
                        
                        if (command.ExecuteNonQuery() == 1)
                        {
                            tracingService.Trace("Successful query execution");
                        }
                        command.Connection.Close();
                    }

     Please let me know what is wrong in this or if there is other way to use SQL queries in Plugin for CRM 2011 on premise.

    Thanks in advance for your help.


    Palani K.B.
    Saturday, December 10, 2011 6:10 AM

Answers

  • Hi

    Can you please post exact Error exception message ?

    if above code is in plugin then you can throw error exception.message on screen by unhandledplugin exception message, so when plugin will execute and if any error accors it will show error message in CRM error popup window.

     

    also, 

    you can try to change security in SQL connection, you can try to set connection string with username and password

     

     


    Many Thanks -Bhautik Desai xRM Technologies
    • Marked as answer by CRM_Intuition Tuesday, December 13, 2011 2:35 PM
    Saturday, December 10, 2011 2:46 PM

All replies

  • Hi

    Can you please post exact Error exception message ?

    if above code is in plugin then you can throw error exception.message on screen by unhandledplugin exception message, so when plugin will execute and if any error accors it will show error message in CRM error popup window.

     

    also, 

    you can try to change security in SQL connection, you can try to set connection string with username and password

     

     


    Many Thanks -Bhautik Desai xRM Technologies
    • Marked as answer by CRM_Intuition Tuesday, December 13, 2011 2:35 PM
    Saturday, December 10, 2011 2:46 PM
  • HI,

    Thanks for your reply.

     

    It was saying "Generic SQL Error" in the errorlog which was downloaded from the error popup. It looked something like below, though this is not the actual message I got, right now I dont have access to the CRM. If you cant find the error from this, i will post the actual message I got.

     

    <OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
    <ErrorCode>-2147204784</ErrorCode> <ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
    <Message>Generic SQL error.</Message>
    <Timestamp>2011-11-29T03:26:31.2765174Z</Timestamp>
    <InnerFault> 
    <ErrorCode>-2147204784</ErrorCode>
    <ErrorDetails xmlns:d3p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
    <Message>Generic SQL error.</Message>
    <Timestamp>2011-11-29T03:26:31.2765174Z</Timestamp>
    <InnerFault>
    <ErrorCode>-2147220970</ErrorCode>
    <ErrorDetails xmlns:d4p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
    <Message>System.Data.SqlClient.SqlException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #C6A5FCCA</Message>
    <Timestamp>2011-11-29T03:26:31.2765174Z</Timestamp>
    <InnerFault i:nil="true" /> <TraceText i:nil="true" />
    </InnerFault> <TraceText i:nil="true" />
    </InnerFault>
    <TraceText i:nil="true" /> 
    
    



    Palani K.B.

    Saturday, December 10, 2011 4:17 PM
  • Hi

    this error looks more like SQL issue, i dont thing this is to do with CRM

     

    • Can you try to place this same code out of CRM, I mean just for testing and finding the problem (use the same server)
    • can you just take one ASP.Net Page (use the same server)
    • Make sQL conenction using same code and try to insert some test data (use the same server)
    •  

    Many Thanks -Bhautik Desai xRM Technologies
    Sunday, December 11, 2011 11:32 AM
  • Hi,

    Plug-ins except Sandbox or asynchronous service execute under the Security Account that is specified under Identity Tab of the CRMAppPool (IIS), and by default CRMAppPool uses the Network Service Account identity unless it is changed with a valid Domain Service Account during the installation. 

    And your Sql Script is to work with Integrated Security (Windows Authentication) (with our username and password) means your Sql Script will run under context of Network Service Account identity of CRMAppPool. Now you need to give access to his CRMAppPool account to the WIN-L4G6G1O2M61 server and the database.

    If it is Network Service, the it is advisable to create a specific sql user with minimum access for the Sql Server to communicate.

    hope this helps.

     

     

     


    Thomas T(MCBMSS) If you find this post helpful then please "Vote as Helpful" and "Mark As Answer".
    Monday, December 12, 2011 1:45 AM
  • Can you successsfully run the SQL code when not in a CRM plugin.

    It could be the SQL privileges the user running the plugin has/doesn't have

     

    I think you need to run the SQL code outside of the CRM plugin and run it as a different user


    Ben Hosking
    Check out my CRM Blog
    Linked-In Profile
    Follow Me on Twitter!
    Tuesday, December 13, 2011 12:51 AM
  •  If you find this post helpful then please "Vote as Helpful" and "Mark As Answer
    Thomas T(MCBMSS) If you find this post helpful then please "Vote as Helpful" and "Mark As Answer".
    Tuesday, December 13, 2011 12:04 PM
  • Thanks Bhautik and others for your replies....The above worked.  I tried the connection string with Username and Password set to my SQL Authentication mode and that solved the error. 

     

     using (SqlConnection connection = new SqlConnection("Data Source=WIN-L4G6G1O2M61;Initial Catalog=ERM;Username=XYZ;Password=lmnop"))

     

    Also, I changed the database connection established in visual studio to reflect the SQL Authentication mode.


    Palani K.B.
    Tuesday, December 13, 2011 2:35 PM
  • Hi all

    I am facing a similar problem. I want to insert data from CRM database to external SQL database by plugin.

    Connection string, I m using:

    ========================================================================================

    SqlConnection

     

    connection = new SqlConnection("Data Source=INFVA04822;Initial Catalog=Test;Username=sa;Password=Newuser123"

    );

    trace.Trace(

    "SQL ConnectionString"

    );

     

    SqlCommand command = new SqlCommand("INSERT INTO GenericAutoNumberentity(ID ,name) VALUES (autonumber,name)"

    , connection);

    trace.Trace(

    "InsertCommand"

    );

    command.Connection.Open();

    trace.Trace(

    "Connection Open"

    );

     

    if

    (command.ExecuteNonQuery() == 1)

    {

    trace.Trace(

    "ExecuteNonQuery"

    );

    trace.Trace(

    "Successful query execution"

    );

    }

     

    command.Connection.Close();

    ========================================================================================

    still i am getting:

    Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Generic SQL error.Detail:
    <OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
      <ErrorCode>-2147204784</ErrorCode>
      <ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
      <Message>Generic SQL error.</Message>
      <Timestamp>2012-01-16T09:57:19.5165618Z</Timestamp>
      <InnerFault i:nil="true" />
    ===================================================================================================

    

    Please Help!!!

    Regards

    Karan Mittal

    Monday, January 16, 2012 10:04 AM
  • Hi,

    It seems there is a mistake in the connection open part of your code.  You have used command.Connection.Open(); It should be just connection.Open(), note the lower case of "c" also.  For the username in the connection string, I have used domainname also, check if that is causing the problem.

    Below is the code I have used for the same purpose.

    ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

     

    using (SqlConnection myConnection = new SqlConnection("Data Source=WIN-SQLSERVNAME;Initial Catalog=DBNAME;User ID=DOMAIN/username; Password = pass_123"))

    {

    tracingService.Trace("Inside Using Statement");

    SqlCommand command = new SqlCommand("INSERT INTO Table1(column1, column2) VALUES ('" + value1_string + "', '" + value2_string + "')", myConnection);

    myConnection.Open();

    tracingService.Trace(myConnection.State.ToString());

    command.ExecuteNonQuery();

    tracingService.Trace(myConnection.ServerVersion.ToString());

    myConnection.Close();

    }

    If the above does not help, try debugging your plugin.  It is much easier to find mistakes in code, if we debug the plugin.

     


    Palani K.B.
    Monday, January 16, 2012 6:15 PM