multiple select statements in single ODBCdataAdapter

Question

I am trying to use an ODBCdataadapter in C# to run a query which needs to select some data into a temporary table as a preliminary step. However, this initial select statement is causing the query to terminate so that data gets put into the temp table but I can't run the second query to get it out. I have determined that the problem is the presence of two select statements in a single dataadapter query. That is to say the following code only runs the first select:

select 1
select whatever from wherever

When I run my query directly through SQL Server Management Studio it works fine. Has anyone encountered this sort of issue before? I have tried the exact same query previously on similar databases using the same C# code (only the connection string is different) and had no problems.

Before you ask, the temp table is helpful because otherwise I would be running a whole lot of inner select statements which would bog down the database.

Answer 1

Unfortunately I do not have write access to the DB as my organization has been contracted just to extract information to a data warehouse. The program is one generalized for use on multiple systems which is why we went with ODBC. I suppose it would not be terrible to rewrite it using SQL Management Objects.

Answer 2

Assuming you're executing a Command that's command type is CommandText you need a ; to separate the statements.

select 1;

select whatever from wherever;

You might also want to consider using a Stored Procedure if possible. You should also use the SQL client objects instead of the ODBC client. That way you can take advantage of additional methods that aren't available otherwise. You're supposed to get better perf as well.

If you need to support multiple Databases you can just use the DataAdapter class and use a Factory o create the concrete types. This gives you the benefits of using the native drivers without being tied to a specific backend. ORMS that support multiple back ends typically do this. The Enterprise Library Data Access Application Block while not an ORM does this as well.

Answer 3

ODBC Connection requires a single select statement and its retrieval from SQL Server. If any such functionality is required, a Hack can do the purpose

use the query SET NOCOUNT ON

at the top of your select statement.

When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned.

When SET NOCOUNT is OFF, the count is returned. It is used with any SELECT, INSERT, UPDATE, DELETE statement.

The setting of SET NOCOUNT is set at execute or run time and not at parse time.

SET NOCOUNT ON mainly improves stored procedure (SP) performance.

Syntax:

SET NOCOUNT { ON | OFF }