sp_executeSQL and dataadapter

[Techno]

hi there

I was wondering, in C#, is there a way to obtain a datatable or dataset of results when using sp_executeSQL?

I can use normal SQL queries fine, but when using the stored proc sp_executeSQL, i do not know how to obtain the entire collection of results into a datatable or dataset.

any ideas?

[pvb]

using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

public class MyClass
{
    public static void Main()
    {
        DataTable authors = new DataTable();
        string connString="user id=userid;password=password;server=(local);database=pubs;";
        using( SqlConnection cn = new SqlConnection( connString ) )
        using( SqlCommand cmd = new SqlCommand( "sp_executesql", cn ) )
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add( "@stmt", "select * from authors" );
            using( SqlDataAdapter da = new SqlDataAdapter( cmd ) )
            {
                da.Fill( authors );
            }
        }
        Debug.Assert( authors.Rows.Count > 0, "authors datatable contains rows" );
    }
} 

eh eh look familiar?(the place,not me) I'm a BIG fan of the Royal Mile.

[HOASTONGE]

I had the same situations
And in my store proc. I had a PRINT command just before sp_executesql

PRINT @sql

exec sp_executesql @sql

And I just remove my PRINT command in my store procedure
and it's working fine

Weird ! but it's work for me

[Techno]

this is a thread dated 2004

[techgnome]

I had the same situations
And in my store proc. I had a PRINT command just before sp_executesql

PRINT @sql

exec sp_executesql @sql

And I just remove my PRINT command in my store procedure
and it's working fine

Weird ! but it's work for me

Not all that weird actually... ANYthing returned back by SQL Server is considered return output. So the PRINT becomes the first resultset, you have to move to the next resultset in order to get past it.

-tg