CLR Stored Procedure Unable to connect with SqlConnection Regular Connection

Question

I tried to create a CLR stored procedure in VS2017 but encountering error "NOT Connected." while executing that stored procedure.

I need to connect to other database server to grab some data. Therefore I cannot use context=true in SqlConnection.

Stored procedure will be created in serverA
This stored procedure will query data from serverB
Data will be stored back to serverA.

Is there anything I need to do in order to have regular connection in CLR stored procedure?

Please advise. Thanks!

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void udp_CLR_GetData()
    {
        string ConnStr = "server=MyServer; database=MyDB; user id=accabc; password=abc123";
        string sql = " select top 1 ID from [dbo].Table1 ";
        SqlDataReader dr = null;
        DataTable dt = new DataTable();

        try
        {
            using (SqlConnection fcon = new SqlConnection(ConnStr))
            {
                if (fcon.State == ConnectionState.Open)
                {
                    SqlContext.Pipe.Send("Connected.");

                    using (SqlCommand fcmd = new SqlCommand(sql, fcon))
                    {
                        SqlContext.Pipe.Send("Before executing reader...");
                        dr = fcmd.ExecuteReader();
                        SqlContext.Pipe.Send("After executing reader...");

                        SqlContext.Pipe.Send("Before send...");
                        SqlContext.Pipe.Send(dr);
                        SqlContext.Pipe.Send("After send...");
                    }
                }
                else
                {
                    SqlContext.Pipe.Send("NOT Connected.");
                }
            }
        }
        catch(Exception ex)
        {
            SqlContext.Pipe.Send("Exception error (udp_CLR_GetData): " + ex.Message);
        }
        finally
        {
            if(dr != null && !dr.IsClosed)
            {
                dr.Close();
            }
        }
    }
}

Solomon Rutzky · Accepted Answer · 2019-12-09 06:37:55Z

1

Creating a new instance of a SqlConnection in:

using (SqlConnection fcon = new SqlConnection(ConnStr))

does not create it in an "open" state. You need to actually open it for it to be "open". So, I would remove the if (fcon.State == ConnectionState.Open) and the associated else part of it. I would also remove the SqlContext.Pipe.Send("Connected."); line.

Then, just before the dr = fcmd.ExecuteReader(); line, add a line for:

fcon.Open();

This way you open the connection and immediately execute the command. No need to open the connection only to do other work getting the command ready.

For more info on working with SQLCLR in general, please visit: SQLCLR Info

edited Dec 9, 2019 at 6:37

answered Dec 9, 2019 at 6:30

Solomon Rutzky

49.3k11 gold badges141 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Angie Chuah Over a year ago

OMG!!!!!!!!! I've missed out to open the connection!!!!!! Thanks for pointing out this!!!

bitclicker · Accepted Answer · 2019-12-09 04:05:32Z

0

Try defining the data source in the connection string instead of server

 string ConnStr = "DataSource=MyServer;Initial Catalog=MyDB;User Id=accabc;Password=abc123";

other than that, make sure clr is enabled on the server: https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/clr-integration-enabling?view=sql-server-ver15

answered Dec 9, 2019 at 4:05

bitclicker

114 bronze badges

3 Comments

Solomon Rutzky Over a year ago

The CLR integration option is definitely enabled since this code got far enough to run the SqlContext.Pipe.Send("NOT Connected."); line. If CLR was not enabled, there would be an error stating as much when the O.P. attempted to execute the stored procedure. Also, the connection string format is likely OK. So far, it hasn't been used since the connection was never opened ;-).

bitclicker Over a year ago

Ahh yes good catch. I see your suggested answer and it makes sense

Angie Chuah Over a year ago

ya, as @SolomonRutzky mentioned, CLR is enabled. :)

Collectives™ on Stack Overflow

CLR Stored Procedure Unable to connect with SqlConnection Regular Connection

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related