t-sql ado recordset / SQL Server

I want to execute a T-SQL statement with ADO-layer. The sql-statement is like:

DECLARE @var TABLE (id int)
INSERT INTO @var SELECT id FROM tblFoo WHERE name='myName' AND idx=2
SELECT * FROM tblFoo WHERE id IN (SELECT * FROM @var)

In the SQL Server management console all works well. But if I use that for opening a ADO_Recordset via Open(sql) th开发者_如何学Cat recordset will not be filled/opened. eof() said: recordset is not opened.

The question ist: How to exectue T-SQL stataments that will result a recordset on the ADO layer?

In the Options parameter of the Open() method you need to pass in CommandTypeEnum.adCmdText.

see this link for more info http://msdn.microsoft.com/en-us/library/ms675544(v=vs.85).aspx

Also change your query to this:

    SELECT * FROM tblFoo WHERE name='myName' AND idx=2

You need to treat this SQL as plain test something like:

cmd.CommandType = adCmdText