OpenQuery Not Working in C#

Question

I can successfully connect to a remote server using an odbc connection in C#.

public void checkGradedSerials()
        {
            List<string> gradedHides = new List<string>();
            string queryString = "SELECT COUNT(DISTINCT Serial_No) FROM Part_v_Container_Change2 WHERE Change_Date >= '2015-04-01' AND Location = 'H Grading'";
            using (OdbcConnection connection = new OdbcConnection("DSN=Pxxxxx32; UID=odbc.xxxx; PWD=xxxxxxx;"))
            {
                OdbcCommand command = new OdbcCommand(queryString, connection);

                command.Connection = connection;
                connection.Open();
                object test = command.ExecuteScalar();
                Console.WriteLine(test.ToString());

            }
        }

The problem is that this keeps timing out. This, I think, is because I am sending requests from the client to the remote server (I am in New Zealand and the database is in the USA). The table has 50 million records in it and so the conundrum is that it is too big to pull as one table, but as I use more filters in my 'WHERE' the time outs occur as it tries to process the data.

In SQL Management studio I can successfully run the query through an OpenQuery which I believe runs the sql on the remote server.

But if I put that same query into the above code as so:

 string queryString = "SELECT * FROM OPENQUERY (PLEXREPORT, 'SELECT COUNT(DISTINCT Serial_No) FROM Part_v_Container_Change2') WHERE Change_Date >= ''2015-04-01'' AND Location = ''H Grading''";

I get an error:

Does anyone know another way of getting the sql to run on the server, or what I am doing wrong? It's driving me nuts!

Don't share your credentials publically, use `xxxx000` instead — sujith karivelil, Aug 14 '15 at 04:58
Oh wow thanks Im usually careful to check that but it was the last hour of the last day of the week. thank you! — Danrex, Aug 16 '15 at 20:29

score 0 · Answer 1 · answered Aug 14 '15 at 05:26

0

Your SQL is wrong

corrected sql is

SELECT COUNT( Serial_No) FROM Part_v_Container_Change2 WHERE Change_Date >= '2015-04-01' AND Location = 'H Grading'

replace

string queryString = "SELECT COUNT(DISTINCT Serial_No) FROM Part_v_Container_Change2 WHERE Change_Date >= '2015-04-01' AND Location = 'H Grading'";

by

string queryString = "SELECT COUNT(Serial_No) FROM Part_v_Container_Change2 WHERE Change_Date >= '2015-04-01' AND Location = 'H Grading'";

answered Aug 14 '15 at 05:26

Jahirul Islam Bhuiyan

811
7
14

No the SQL is correct. One is counting all the serial numbers, the other is counting only unique ones. Both work. – Danrex Aug 16 '15 at 20:33
then use SQLConnection instead of OdbcConnection – Jahirul Islam Bhuiyan Aug 17 '15 at 01:26

OpenQuery Not Working in C#

1 Answers1