Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
andrewcw
Posting Yak Master
133 Posts |
Posted - 2012-02-21 : 15:35:44
|
The main sp(stored procedure) calls a child sp to load a value for the main sp.In SQL management studio I see that single value of the child sp ( the intermediate value) in a table ( No Column Name), then the result set, and finally one with return Value =0The C# application code that executes the sp returns the first table with the single intermediate value. What can I do ?Thanksandrewcw |
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-21 : 15:41:37
|
Put SET NOCOUNT ON; at the top of the parent proc. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-21 : 15:41:46
|
Depending on what you are using in the C# code, you will need to skip over the first result set and get the next result set. If you are using ADO.Net and DataReader, for example, you would need to use the NextResult method on the DataReader. See here: http://msdn.microsoft.com/en-us/library/haa3afyz.aspxAlternatively, you would need to do something in the stored proc, so the result set you want is generated first. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-21 : 15:45:07
|
quote: Originally posted by russell Put SET NOCOUNT ON; at the top of the parent proc.
Yes!! Why didn't I think of that, that is probably what it is!! I thought it was really two result sets. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-21 : 16:45:05
|
You still might be right. But between the two of us, I'm sure we got the OP on the right track! |
|
|
andrewcw
Posting Yak Master
133 Posts |
Posted - 2012-02-21 : 17:13:03
|
I put SET NO COUNT ON at start of main procededure, then added it right after the intermediate procedure & then again at end. But the behavior remains unchanged. I will look at your references at MDSN & thanks for the suggestions.andrewcw |
|
|
andrewcw
Posting Yak Master
133 Posts |
Posted - 2012-02-21 : 17:31:48
|
The orignal code did this : adapter.Fill(this.Tables[table]);I changed it to pick up the whole data set & locate the next tableDataSet ds = new DataSet();adapter.Fill(ds);Sure enough the table was there, but there was NO VALUES ... the columns however were right. I then backed out all the NO COUNT ON but the 2nd table remains empty table when calling the stored procedure & filling a datasetandrewcw |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-22 : 12:41:49
|
How is the child proc passing the values to the parent one? It can't just be a recordset. You need to use output parameters. |
|
|
|
|
|