none
Get data from remote machine on wcf service in clr procedure RRS feed

  • Question

  • //Wcf Metod public int ExcMethodWcf(string query)
            {
             var count = 0;    
               var con = GP.GetLocalConnection();
               con.Open();      
                 var cmd = new SqlCommand(query,con);
                 cmd.ExecuteNonQuery();
                 con.Close();
                 count = 1;
                 return count;
              }

    //Class Metod  public static int ExcMetodClass(string query)
            {
                        var msg = "";
                        WcfWebService clientex = new WcfWebService();
                       clientex.ExcMethod(query));
                       return msg;       
            }

    //Clr Metod  public static void ExcMetodClr()
        {
              var connection = new SqlConnection("context connection=true");
              connection.Open();
            var command = new SqlCommand(Postbox.ExcMetod("SELEC TOP 1 PCNAME FROM TABLE"), connection);
              var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
              if (SqlContext.Pipe != null) SqlContext.Pipe.Send(reader);
        }

    When execute the proc ( EXEC ExcMetodClr ) then it was return below error

    A .NET Framework error occurred during execution of user-defined routine or aggregate "ExcMetodClr":
    System.MissingMethodException: Method not found: 'System.String Class.Postbox.ExcMetodClass(System.String)'.
    System.MissingMethodException:  at StoredProcedures.ExcMetodClr()

    Thursday, March 23, 2017 2:54 PM

Answers

All replies

  • Hi Aly14,

    Thank you for posting here.

    According to your question is more related to WCF, I will move it to Windows Communication Foundation, Serialization, and Networking forum for suitable support.

    The Visual C# discuss and ask the C# programming language, IDE, libraries, samples and tools.

    If you have some grammar or code errors, please feel free to contact us. We will try our best to give you a solution.

    Thanks for your understanding and cooperation.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 24, 2017 2:12 AM
  • >>When execute the proc ( EXEC ExcMetodClr ) then it was return below error

    How did you execute this proc? Is it from WCF method? What is “Postbox.ExcMetod”? It seems to be your custom class. Will it return a query string? SqlCommand will accept a query string and connection string. Will it work if you try below code?

    var connection = new SqlConnection("context connection=true");
     connection.Open();
     var command = new SqlCommand("SELEC TOP 1 PCNAME FROM TABLE", connection);
     var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
     if (SqlContext.Pipe != null) SqlContext.Pipe.Send(reader);

    Based on the error message, it seems there is no StoredProcedures.ExcMetodClr() or it could not be found.

    It would be helpful if you could share us detailed steps to reproduce your issue.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, March 24, 2017 4:49 AM
  • Have an idea ?
    Sunday, March 26, 2017 9:13 PM
  • My above code is used to check whether your issue is in WCF or SQL code.

    >> SqlContext.Pipe.Send(Postbox.Getvalue(value));  

    Based on your description, it seems to be related with SQL. Is CLR method defined in WCF Service? How did you achieve below code?

    SqlContext.Pipe.Send(Postbox.Getvalue(value));  

    A detailed step to reproduce your issue would be helpful.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 27, 2017 2:26 AM
  • I executed the process if method just return string value also described last post , But I want to execute sql query that expilained first post .
    Monday, March 27, 2017 7:09 AM
  • It seems there is some problem to understand each other.

    I know you have a problem when calling “ExcMetodClr”. But, I do not know what is Postbox.ExcMetod, and I do not know how to reproduce your issue. Telling me again and again there is problem when executing sql query would not help to resolve your issue.

    Could you share me a simple project which could reproduce your issue?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 27, 2017 9:18 AM
  • clr procedure does not communication to WCF directly ,CLR proc. is use the special class for communication WCF ,so ExcMetod is a method in POstbox class, postbox class is a class in MyClass.dll

    you can refer the below article , I want to make like below , if I use the method that return string value then everything work fine, if I execute query it was return error that I wrote first post in above .

    https://code.msdn.microsoft.com/Calling-WCF-Service-from-a-8071ceaa

    thanks in advance .

    Monday, March 27, 2017 9:29 AM
  • >>xcMetod is a method in POstbox class, postbox class is a class in MyClass.dll

    What is postbox class and MyClass.dll? I did not find them in provided link.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 28, 2017 2:21 AM
  • I just changed to the name ,the class name is that provided above link is MyClassLibrary

    I am communicating the WCF via CLR if I use simple method so just return string value ,I just not execute sql command ,so the three mechanism is working , just need to change something .

    Tuesday, March 28, 2017 12:22 PM
  • >>so the three mechanism is working , just need to change something .

    Do you mean your issue has been resolved? If so, I would suggest you share us your solution, and mark it as answer, and then others who run into the same issue would find the solution.

    If not, I suggest you share us a simple project which could reproduce your issue.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 29, 2017 5:35 AM
  • Hi Edvard ;

    I mean , its working only if method return a string value , but I dont wanna it , I wanna execute sql command , I am waiting your's solution.

    Thanks again .


    • Edited by Aly14 Wednesday, March 29, 2017 6:22 AM a
    Wednesday, March 29, 2017 6:21 AM
  • >>I wanna execute sql command

    For executing sql command, I suggest you try below code:

            public DataTable ReturnDataTable()
            {
                SqlConnection con = new SqlConnection(@"SQL Connection String");
                DataTable dt = new DataTable();
                string vw = "select * from TodoItems";
                SqlCommand cmd = new SqlCommand(vw, con);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(dt);
                dt.TableName = "TodoItems";
                return dt;
            }


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 30, 2017 3:04 AM
  • Hi Edvard ;

    Where Can I use this method , WCF , CLR or CLASS.DLL

    if you suggest 3 method that included WCF , CLR and CLASS .dll that would be nice .

    Thursday, March 30, 2017 7:28 AM
  • This is a WCF Service method corresponding to WCF OperationContract.

    Service Contract

    namespace WCFDataTable
    {
        // NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService1" in both code and config file together.
        [ServiceContract]
        public interface IWCFDataTable
        {       
            [OperationContract]
            DataTable ReturnDataTable();
            // TODO: Add your service operations here
        }
    }
    

    Service Implementation.

        public class WCFDataTable : IWCFDataTable
        {        
            public DataTable ReturnDataTable()
            {
                SqlConnection con = new SqlConnection(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=DotNetCore;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
                DataTable dt = new DataTable();
                string vw = "select * from TodoItems";
                SqlCommand cmd = new SqlCommand(vw, con);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(dt);
                dt.TableName = "TodoItems";
                return dt;
            }
        }
    

     

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 31, 2017 2:39 AM
  • we are missing something ,

    you should give me 3 method like below

    * Wcf Method

    * Class Method

    * Clr Method

    your last post is WCF method , how should be Class and Clr method .

    Friday, March 31, 2017 7:53 AM
  • What do you mean by Class Method? How did you use Class Method? Where did you define Class Method?

    Do you mean below code did not work in class?

     public class ClassMethod
        {        
            public DataTable ClassReturnDataTable()
            {
                SqlConnection con = new SqlConnection(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=DotNetCore;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
                DataTable dt = new DataTable();
                string vw = "select * from TodoItems";
                SqlCommand cmd = new SqlCommand(vw, con);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(dt);
                dt.TableName = "TodoItems";
                return dt;
            }
        }


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 3, 2017 3:00 AM
  • I need call that in CLR procedure,so if I add reference and call to that in C# project everything is working But I want to call in CLR ,

    So you should give me 3 sample

    * WCF Method

    * Class Method

    * CLR Method .

    Monday, April 3, 2017 7:07 AM
  • >> When execute the proc ( EXEC ExcMetodClr ) then it was return below error

    Did you mean you get error when execute the proc ( EXEC ExcMetodClr ) in SQL Server? If so, I suggest you follow below steps.

    1. check whether ExcMetodClr exist under [your database]->Programmability->Stored Procedures, if exists, delete it

    2.Run below query in SQL Server

    IF EXISTS (
    
        SELECT [name]
    
        FROM sys.assemblies
    
        WHERE [name] = N'SQLCLRProject')
    
     
    
        BEGIN
    
            DROP ASSEMBLY SQLCLRProject
    
            ALTER ASSEMBLY SQLCLRProject
    
            FROM 'Dll Path\SQLCLRProject.dll'
    
            WITH PERMISSION_SET = UNSAFE ;
    
        END
    
    ELSE
    
        BEGIN
    
            CREATE ASSEMBLY SQLCLRProject
    
            FROM 'Dll Path\SQLCLRProject.dll'
    
            WITH PERMISSION_SET = UNSAFE;
    
        END

    3.Run below query in SQL Server

    CREATE PROCEDURE [dbo].[ExcMetodClr]
    
          @parameter1 NVARCHAR(MAX)
    
    WITH EXECUTE AS CALLER
    
    AS
    
    EXTERNAL NAME SQLCLRProject.StoredProcedures.ExcMetodClr

    4. Run below query

    EXEC [ExcMetodClr] "select * from [dbo].[tblUser]"



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 4, 2017 1:47 AM
  • WCF

            public int ExcMethodWcf(string query)
            {
                var count = 0;
                var con = new SqlConnection(@"Data Source=.;Initial Catalog=MSDNTool;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
                con.Open();
                var cmd = new SqlCommand(query, con);
                cmd.ExecuteNonQuery();
                con.Close();
                count = 1;
                return count;
            }
    

    Class

        public class Class1
        {
            public static int ExcMetodClass(string query)
            {
                int msg;
                WCFService.Service1 clientex = new WCFService.Service1();
                bool rs = false;
                clientex.ExcMethodWcf(query, out msg,out rs);            
                return msg;
            }
        }
    

    SQL CLR

    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void ExcMetodClr(string query)
        {
            // Put your code here
            try
            {
                SqlPipe sqlPipe = SqlContext.Pipe;
                int result = MyClasslib.Class1.ExcMetodClass(query);
                SqlContext.Pipe.Send(result.ToString());
            }
            catch (Exception ex)
            {
                SqlContext.Pipe.Send(ex.Message);
            }
        }
    }
    


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 5, 2017 5:01 AM
  • at firslty thank you for your's trace ,

    I applied all of them , When execute proc it was return 1 not return query result

    and I modified the method as return query result , everything is ok ,



              //var localcon= new SqlConnection("context connection=true");
              var remotecon = new SqlConnection("Data Source=remote_adres; Initial Catalog=remote_db; User Id=remote_usr; password=remote_pwd");
              remotecon ();
                SqlPipe sqlPipe = SqlContext.Pipe;
                var cmd = new SqlCommand(CLASS.Postbox.ExcMetodClass(query), remotecon
                var reader = cmd.ExecuteReader();
                SqlContext.Pipe.Send(reader);
                remotecon ();

    if I use the local connection string the proc query local database if I use the remote connection string the proc query for remote database , BUT remote database not respond for sql port therefore I am using wcf.

    Wednesday, April 5, 2017 3:12 PM
  • I am glad it works now.

    I would suggest you mark the helpful reply as answer to close this thread.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 6, 2017 4:53 AM
  • That's not exactly what I want , I want to query remote database .

    The remote database server not open 1433 sql port therefore I can't connect directly using connection string ,I am connecting to it with WCF that locared same side,

    So I have a remote DB and WCF in same location and I have a client in other location

    and I want to query database remote to remote via WCF.

    Thursday, April 6, 2017 8:36 AM
  • >>So I have a remote DB and WCF in same location and I have a client in other location and I want to query database remote to remote via WCF.

    What is your current issue?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 7, 2017 2:54 AM
  • >>Why I can't query to database from remote , I already have a WCF that comminication with Database in same location and I able to connect WCF in CLR using CLASS.

    What do you mean by “I able to connect WCF in CLR using CLASS”?

    How did you query to database from remote computer?

    Do you mean you can query in CLR by local database, but failed under remote database?

    Did you host WCF Service and the remote database in the same computer?

    If you call class from a console application, will it work?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 10, 2017 2:32 AM
  • I suggest you follow below steps.

    1.        Create a WCF Service in remote computer, add two functions, one will query this database, another will return a simple string
    2.        Create a class in local computer, and add service reference
    3.        Create a SQL CLR to call class method
    4.        Test SQL CLR in SQL Server

    Will the two functions work or only one of them work?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Aly14 Tuesday, April 11, 2017 7:59 AM
    • Unmarked as answer by Aly14 Wednesday, April 12, 2017 1:38 AM
    Tuesday, April 11, 2017 5:42 AM
  • >> if sql port not respond to remote call , how is it Works?

    The WCF service is hosted in remote computer, and query the database in the remote computer which means the WCF Service query the local database, why it did not work? Where is your service?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 12, 2017 7:40 AM
  • >>some WCF method that return datatable is working but I Can't execute sqlcommand in CLR

    What do you mean by this? What works, and what did not work?

    ExcMethodWcf and DtMethod are different.

    I suggest you try ExcMethodWcf in winform application.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 13, 2017 2:12 AM
  • Let's assume remote computer is A, and local computer is B.

    1. Is WCF with ExcMethodWcf in A?

    2. Is connection for ExcMethodWcf used to query A? 

    3.Is Custom Class in B?

    4.Is SQL CLR in B?

    5.If you replace the code in ExcMethodWcf by code in DtMethod? Will it work?

    6.If you pass a simple query like "Select * From [Table Name]" to ExcMethodWcf, will it work?

    7.Are ExcMethodWcf and DtMethod in the same service?

    8.How did you call ExcMethodWcf in winformation? Is winform in A or B? I suggest you try to create a winform in B and add custom class to check whether it will work.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 14, 2017 2:48 AM
  • I have shared my complete code in above reply. Please check my code again.

        public int ExcMethodWcf(string query)
            {
                var count = 0;
                var con = new SqlConnection(@"Data Source=.;Initial Catalog=MSDNTool;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
                con.Open();
                var cmd = new SqlCommand(query, con);
                cmd.ExecuteNonQuery();
                con.Close();
                count = 1;
                return count;
            }

    Class

        public class Class1
        {
            public static int ExcMetodClass(string query)
            {
                int msg;
                WCFService.Service1 clientex = new WCFService.Service1();
                bool rs = false;
                clientex.ExcMethodWcf(query, out msg,out rs);            
                return msg;
            }
        }

    SQL CLR

    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void ExcMetodClr(string query)
        {
            // Put your code here
            try
            {
                SqlPipe sqlPipe = SqlContext.Pipe;
                int result = MyClasslib.Class1.ExcMetodClass(query);
                SqlContext.Pipe.Send(result.ToString());
            }
            catch (Exception ex)
            {
                SqlContext.Pipe.Send(ex.Message);
            }
        }
    }

    Could you share us a simple project which could reproduce your issue?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 17, 2017 5:25 AM
  • Did your structure meet below?

    Did winform call library work?

    I suggest you run your WCF in VS, and add breakpoint on the method to check whether it will be hit when you call SQL CLR in local database in B?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 18, 2017 5:48 AM
  • /////// WCF METHOD ///////

    public void ExcMethodWcf(string query)
        {     
            var con = new SqlConnection(RemoteConnection());
            con.Open();
            var cmd = new SqlCommand(query, con);
            cmd.ExecuteNonQuery();
            con.Close();
          }

     public string RemoteConnection()
        {
          var con = new SqlConnection("Server=SERVERNAME;Database=DBNAME;User Id=sa;Password=PWD;");
          return con.ConnectionString;
        }

    /////// CLASS method ///////

    public static string ExcMethodWcf(string query)
        
     {
            WcfWebService srv = new WcfWebService();
            srv.ExcMethodWcf(query);
            return query;      
     }

          public static SqlConnection RemoteConnection()
          {
            WcfWebService srv = new WcfWebService();
            var con = new SqlConnection(srv.RemoteConnection());
            con.Open();
            return  con;
          }

    /////// CLR METHOD ///////

    public static void ExcMethodWcf(string query)
      {
        try
        {   
          var cmd = new SqlCommand(Postbox.ExcMethodWcf(query),Postbox.RemoteConnection());
          var reader = cmd.ExecuteReader();
          if (SqlContext.Pipe != null) SqlContext.Pipe.Send(reader);
         
        }
        catch (Exception ex)
        {
          SqlContext.Pipe.Send(ex.Message);
        }
      }

    When execute the code EXEC dbo.[ExcMethodWcf] 'SELECT @@VERSION' return error login failed for 'sa' ,

    Did you use above code in your current SQL CLR? What is the purpose for below code?

     var cmd = new SqlCommand(Postbox.ExcMethodWcf(query),Postbox.RemoteConnection());
     var reader = cmd.ExecuteReader();
    It seems you query from local computer to remote computer directly.

    Please try to replace above code by below code:

    string result=Postbox.ExcMethodWcf(query)
    Will you get any error?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 19, 2017 9:49 AM
  • * Did you use above code in your current SQL CLR? What is the purpose for below code?

    my purpose is , I want to query remote database from client that away from each other

    * It seems you query from local computer to remote computer directly.   ( YES )

    Please try to replace above code by below code:

    string result=Postbox.ExcMethodWcf(query)

    When I change the like above and calli like this

    EXEC [ExcMethodWcf] 'SELECT * FROM INFORMATION_SCHEMA.TABLES'

    it was return SELECT * FROM INFORMATION_SCHEMA.TABLES

    so passing query string

    Wednesday, April 19, 2017 3:22 PM
  • >>my purpose is , I want to query remote database from client that away from each other

    You could not run query from local database to remote database due to sql port. 

     var cmd = new SqlCommand(Postbox.ExcMethodWcf(query),Postbox.RemoteConnection());
     var reader = cmd.ExecuteReader();

    In your above code, Postbox.ExcMethodWcf(query) will return a query string, Postbox.RemoteConnection() will return connection string to remote database. you execute the query from local computer to remote computer. It would not work. There is no difference from you run this query from local database.

    For above code, what is the purpose of WCF Service? Just return a string which you passed, and a connection string which you already know?

    You need to use WCF Service to run the query and return the result.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 20, 2017 3:02 AM
  • It seems your issue changing from sa login failed to unexpected result, am I right?

    Have your previous issue about sa login failed resolved?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 20, 2017 7:23 AM
  • no still continue ,So I query remote database and I trace the remote db with profiller the query passed But it's return to client side login failed 'sa' , anyway ,if we solve last post about datatable it can be for me.
    Thursday, April 20, 2017 7:28 AM
  • From my above reply, have you known why you got login failed error?

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 20, 2017 7:30 AM
  • I have no idea , everything is correct ,the query already pass to remote side I can see via profiler .

    if I use Windows authantication with connection string

    the error is

    Cannot open database "DBNAME" requested by the login. The login failed.Login failed for user 'NT Service\MSSQLSERVER'.

    if I use sql auth. with connectin string

    the error is , login failed for 'sa'

    But I want to forget :) , the post that we trace so long I want to finish it :)

    if we solve last post ,it's enough for me :)

    Thank you for everything .

    Thursday, April 20, 2017 7:34 AM
  • For sending DataTable, I suggest you refer below links.

    #Send DataSet Sample

    https://technet.microsoft.com/en-us/library/ff878201(v=sql.110).aspx

    #SqlPipe.SendResultsRow Method

    https://msdn.microsoft.com/en-US/library/microsoft.sqlserver.server.sqlpipe.sendresultsrow(VS.80).aspx


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Aly14 Friday, April 21, 2017 7:04 AM
    Friday, April 21, 2017 4:39 AM