How to use openquery in c#

Question

I have an openquery SQL script:

Select * from openquery([oak],'
SELECT LicenseKey, SUM(PaymentAmount)as Payments
FROM vw_ODBC_actv_Payments pt 
WHERE MONTH(pt.EntryDate) = 2 and
YEAR(pt.EntryDate) = 2015 
GROUP BY LicenseKey
')

When I run this from SSMS I can see that it returns expected n rows.

However when I'm firing this with the same connection properties to get the data in a DataSet for a C# console application:

    SqlDataAdapter da = new SqlDataAdapter();
    SqlCommand pcmd= new SqlCommand();
    DataSet ds= new DataSet();
    OpenConnection();
    pcmd.Connection = new SqlConnection("Data source=IP adress of the server;Initial Catalog=master; user ID=***; password=***");
    cmd.CommandText = "Select * from openquery([oak],'" +
    "SELECT LicenseKey, SUM(PaymentAmount)as Payments" +
    "FROM vw_ODBC_actv_Payments pt " +
    "WHERE MONTH(pt.EntryDate) = 2 and" +
    "YEAR(pt.EntryDate) = 2015" +
    "GROUP BY LicenseKey')";
try
{
    da.SelectCommand = pcmd;
    da.Fill(ds); //here comes the error
}
catch (Exception ex)
{
    throw new Exception("DBUtils.ExecuteReader():" + ex.Message);
}

I'm getting an error like this:

The provider indicates that the user did not have the permission to perform the operation. Now I need to do something with this issue

I'm just learning about openquery. Can anybody guide?

You're not opening the connection anywhere from the above code.. also you haven't declared `ds` — Izzy, Apr 22 '15 at 11:20
@Izzy - it was an code extract from my project. Connection and DataSets are provided by custom functions. My problem is coming when I'm opening the connection. — Zameer Ansari, Apr 22 '15 at 11:30

score 3 · Accepted Answer · answered Apr 22 '15 at 11:31

3

Firstly you're not opening the connection anywhere in your code hence the error. Second clean up your code with the using block. So assuming the query works as required you can do something like.

using(SqlConnection con = new SqlConnection("Connection String Here"))
    {
        string myQuery = "Your Query";
        using(SqlCommand cmd = new SqlCommand(myQuery, con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                con.Open();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds);
            }
        }
    }

Note: It would be a better if you stored the connectionString in your config file and read it in your code.

answered Apr 22 '15 at 11:31

Izzy

6,740
7
40
84

still there is same exception. – Zameer Ansari Apr 22 '15 at 11:34
1

Try `providerName="System.Data.SqlClient"` in your `connectionString` – Izzy Apr 22 '15 at 11:36
Keyword not supported: `providername` – Zameer Ansari Apr 22 '15 at 11:41
1

Try this.. Create an empty text file on desktop, name it with a `.udl` extension. Once created, double-click on it and you will see a GUI open up to guide you through to build the connection string. Step through it and click on test connection if successful then open the `udl` file up in a text editor and copy the `connectionstring` and paste it in your code – Izzy Apr 22 '15 at 11:50
1

Izzy - your solution helped me connect to the linked server. I believe there is some restriction - `The provider indicates that the user did not have the permission to perform the operation. Now I need to do something with this issue` – Zameer Ansari Apr 22 '15 at 12:13
1

The error is self explanatory really. How your permissions are set etc you'll need to check and then take it from there. My provided solution answered you orginal question. – Izzy Apr 22 '15 at 13:27
I wanted to keep this question open, many +1s for your help!! – Zameer Ansari Apr 22 '15 at 13:33

How to use openquery in c#

1 Answers1

Linked