filling dataset with Sybase stored procedure

[Guest]

Hi,
How can I fill my dataset with a call to an SP created in sybase 11.5? The
SP queries several tables and dumps the resultset in a #temp_table. What
would I need to do in my SP to transfer that data to ado.net?
This is my code for calling the SP:
da_SP.SelectCommand = cmd_callSP;
cmd_callSP.CommandText = "{call proc_generate_webAds_interface}";
cmd_callSP.CommandType = CommandType.StoredProcedure;
cmd_callSP.Connection = serverConn;
da_SP.Fill(ds_SP); //it executes the SP but the dataset remains empty.

Any help is appreciated.
Thanks.

 

[Jim Hughes]

Hi,
How can I fill my dataset with a call to an SP created in sybase 11.5? The
SP queries several tables and dumps the resultset in a #temp_table. What
would I need to do in my SP to transfer that data to ado.net?
This is my code for calling the SP:
da_SP.SelectCommand = cmd_callSP;
cmd_callSP.CommandText = "{call proc_generate_webAds_interface}";
cmd_callSP.CommandType = CommandType.StoredProcedure;
cmd_callSP.Connection = serverConn;
da_SP.Fill(ds_SP); //it executes the SP but the dataset remains empty.

Please show the stored proc code as well.

Did you include a "select * from #temp_table" at the end of the stored proc?

Check the number of datatables in ds_SP, it is possible that one of the
earlier statements in the proc filled an empty table and the results you are
looking for are in a later table.

for (Int i = 0; i++; i < ds_SP.Tables.Count )
{
System.Diagnostics.Trace.WriteLine(ds_SP.Tables.Count.ToString() );
}

 

[William \(Bill\) Vaughn]

Generally, any #temp tables created by SPs are dropped when the SP ends.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Jim Hughes]

Bill, wouldn't the full resultset be returned if the select * from #temp was
included at the end of the stored proc.

 

[Guest]

Hi,
I'm the one that made the error. I only tested it once (with an error) and I
assumed it was the Select at the end. I tried it yesterday and it worked
great.

Thanks for the help,
VMI