locked
Has exceeded the maximum number RRS feed

  • Question

  • Hi,

    I'm to execute one cursor by a SP that will one by one call each record in the header table. But I've got this

    The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

    and the process was stopped there. How can I resolve this?


    Many Thanks & Best Regards, HuaMin Chen
    Wednesday, May 11, 2011 6:50 AM

Answers

  • The simplest resolution may be to run with Results to Text. I'm not sure why run this in SSMS at all, except for testing. Even if the limit of 100 result sets is a little stupid, it's not very practical with 100 small grids.

    Or insert data into a temp table, and return a single result set when you're done.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Wednesday, May 11, 2011 1:13 PM
    • Marked as answer by Jackson_1990 Thursday, May 12, 2011 7:37 AM
    Wednesday, May 11, 2011 7:41 AM
  • Menu: Query, Results To, ...
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by Jackson_1990 Thursday, May 12, 2011 7:37 AM
    Thursday, May 12, 2011 6:57 AM

All replies

  • Hi,

    I think you are using SQL SERVER 2005.

    This issue was resolved in the higher version.

    Here is the connect URL for this

    http://connect.microsoft.com/SQLServer/feedback/details/267537/sql-server-management-studio-results-grid-limitation#details


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Wednesday, May 11, 2011 7:00 AM
  • Many thanks Rishabh. Right, I'm using Sql server 2005. Is there any way to avoid this? I think it's not good to use higher version due to this problem.
    Many Thanks & Best Regards, HuaMin Chen
    Wednesday, May 11, 2011 7:13 AM
  • For instance, there's around 470 records in the header table. There have already been 250 records in the header table finished without any problem. Do you think I can separate the whole process into 2 or 3 sub processes, of which each is with around 200 records?
    Many Thanks & Best Regards, HuaMin Chen
    Wednesday, May 11, 2011 7:16 AM
  • Hi,

    Yes Separating the records into subgroups may accomplish this.

     


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Wednesday, May 11, 2011 7:19 AM
  • I think you should sub divide your data in small chunks and then process them in batches. This will help you to overcome the issue.

    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Wednesday, May 11, 2011 7:20 AM
  • Take a look at this technique

    http://dfarber.com/computer-consulting-blog.aspx?filterby=Batch%20processing%20records%20in%20MS%20SQL


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, May 11, 2011 7:30 AM
    Answerer
  • The simplest resolution may be to run with Results to Text. I'm not sure why run this in SSMS at all, except for testing. Even if the limit of 100 result sets is a little stupid, it's not very practical with 100 small grids.

    Or insert data into a temp table, and return a single result set when you're done.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Wednesday, May 11, 2011 1:13 PM
    • Marked as answer by Jackson_1990 Thursday, May 12, 2011 7:37 AM
    Wednesday, May 11, 2011 7:41 AM
  • Thanks to all. Uri, I did not have much idea after having read your link.

    Erland, is there any way to avoid this in Sql server 2005. I reduce the size to each time only select the top 90 records from the header table but I've still got the same error.


    Many Thanks & Best Regards, HuaMin Chen
    Wednesday, May 11, 2011 8:27 AM
  • I have a feeling that you inside your cursor loop do something like FETCH colname without INTO @varname, which causes the FETCH to return a resultset. This is typically a mistake, and something you would never do in production code. I suggest you work with this, instead of trying to get the tool allow for more then 100 resultsets (or just execute into text, as Erland mentioned).


    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, May 11, 2011 9:15 AM
  • Many thanks Tibor. For every fetch, I have put an into phase like "into @par1, @par2 ...". And I can successfully finish it and have got more than 90 header records completed. I just have checked that I did not put any "fetch" without any list of proper parameters.
    Many Thanks & Best Regards, HuaMin Chen
    Wednesday, May 11, 2011 9:40 AM
  • > Erland, is there any way to avoid this in Sql server 2005. I reduce the size to each time only select the top 90 records from the header table but I've still got the same error.

    Did you try text mode?

    It is still not clear to me why you want to produce all these result sets. Why can't you save the result sets to a temp table and send out all data at the end?

    Or is this some debug SELECT that you have?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 11, 2011 10:40 AM
  • Why you are selecting these much of amount of result set?

    I dont think that there is any requirement to do. For Testing purpose, insert resultset into some dummy table and fetch all data at once.

    This will give you the exact scenario without any limitation of sql server query editor.


    Yogesh Bhadauriya - Sr Software Engineer in Microsoft Technologies
    Wednesday, May 11, 2011 12:30 PM
  • Do you have any SELECT statement inside the loop (or call to an SP returning result set)? Instead of SELECT statement, you need to use

    INSERT INTO #...

    SELECT ...

    so you will not produce 100+ result sets.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, May 11, 2011 1:15 PM
  • Many thanks to all. I did not intentionally produce any recordsets in the SPs that were called by other SPs, but I have used the cursors and then fetched them into sets of parameters inside the SPs .
    Many Thanks & Best Regards, HuaMin Chen
    Wednesday, May 11, 2011 3:09 PM
  • > Many thanks to all. I did not intentionally produce any recordsets in the SPs that were called by other SPs, but I have used the cursors and then fetched them into sets of parameters inside the SPs .

    Not sure if this means that the issue is resolved.

    But if you are saying that you are running a cursor over procedures that produces result sets, I guess one question is what your ultimate goal is. I don't think you plan to run this from SSMS in production, do you?

    Rather than asking about this particular problem, it may be better if you give us the full story. For instance, you have been talking about some header tables, but we don't about what headers you are working with.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 11, 2011 10:01 PM
  • Hi,
    Many thanks to all and good day.
    Erlang,
    When I run the process I see that there're outputs every time (see below)
    http://www.4shared.com/photo/YILr5xS1/fig96.html

    Is every row above treated as one record set (counted by Sql server)?

    Since in the process, one SP will call another SP in the following way. If "EXEC @ret_val=..." each time is treated as one recordset, and further Sql server have a restriction for the total record sets, how can we avoid this?

      EXEC @ret_val=dbo.p_prc1 @hdr_reference, @success OUT, @message OUT

     EXEC @ret_val=p_prc2 @hdr_id, @err_ind, @message2 output, @wrong_dtl OUTPUT


    Many Thanks & Best Regards, HuaMin Chen
    Thursday, May 12, 2011 2:12 AM
  • Hi Erland,

    How can I achieve this?

    "to run with Results to Text"


    Many Thanks & Best Regards, HuaMin Chen
    Thursday, May 12, 2011 6:52 AM
  • Menu: Query, Results To, ...
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by Jackson_1990 Thursday, May 12, 2011 7:37 AM
    Thursday, May 12, 2011 6:57 AM
  • is all your SPs are returing  same result set (I mean same data types and number of columns) if yes then you can create a temp table and insert all your results in that table and at the end of your script just select * from temptable

     


    If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. DVR
    Thursday, May 12, 2011 6:58 AM
  • > Since in the process, one SP will call another SP in the following way. If "EXEC @ret_val=..." each time is treated as one recordset, and further Sql server have a restriction for the total record sets, how can we avoid this?

    "EXEC @ret_val=..."  is not treated as a result set as such. Apparently the procedure you are calling is producing this result set. I have no idea that this procedure is doing, but you should investigate whether they actually should produce this result set or not.

    You are definitely focusing on the wrong thing. This procedure you are working with, is it going to be run in SSMS once it is in production? Probably not. If this is going to be run from an application, all these result sets are likely to cause problems on their own for the application as well.

    So look into these procedures instaead of worrying about SSMS. That is probably the least of your problems.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 12, 2011 7:21 AM
  • Many thanks all.
    Tibor,
    Having changed it to be in Text, it's better now. But I see that it completed with errors. How can I see the error there?
    http://www.4shared.com/photo/rnRvcj9h/fig97.html

    Many Thanks & Best Regards, HuaMin Chen
    Thursday, May 12, 2011 7:36 AM
  • Good day Erland.
    The called SP inside this is fine when being called by other parts of the system. Here I got the problem when making a process to scan the header table. It really improved by Query to text! Many many thanks to all
    Many Thanks & Best Regards, HuaMin Chen
    Thursday, May 12, 2011 7:40 AM
  • Tibor,

    I need your advice above.


    Many Thanks & Best Regards, HuaMin Chen
    Thursday, May 12, 2011 8:04 AM
  • You have to scroll through the result and to find the error. But I strongly suggest you read Erland's replies again and communicate what the logic behind what you want to achieve. HAving a cursor and then view a result set for each iteration is not a best practice, but unless we know what in the end you want to achieve, we can't be of much help in the broader sense.
    Tibor Karaszi, SQL Server MVP | web | blog
    Thursday, May 12, 2011 8:27 AM
  • Many thanks Tibor.
    Before this, I also scrolled to the end of that. But I did not see the error!
    http://www.4shared.com/photo/_GtjYa6d/fig98.html

    Anyway, I don't have that problem of exceeding maximum recordset now.


    Many Thanks & Best Regards, HuaMin Chen
    Thursday, May 12, 2011 8:55 AM
  • Before this, I also scrolled to the end of that. But I did not see the
    error!
    http://www.4shared.com/photo/_GtjYa6d/fig98.html

    Anyway, I don't have that problem of exceeding maximum recordset now.


    You need to examine the entire output window. The error may be in the middle. You should search for the string Msg to spot it. Beware that there may be multiple errors.

    But again, stop this procedure from producing this output! It may be working well in the rest of the system, but that may be pure luck. Or maybe it should be that way, in which case you may need to find a way to prevent it from happening when you run the procedure, because I don't think this will work well when you call your cursor procedure from the application.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 12, 2011 12:15 PM
  • Good day Erland, Even if I've scrolled through the whole Results subwindow, I still could not see any other specific errors (see this below)

    http://www.4shared.com/photo/CqvV92IM/fig99.html


    Many Thanks & Best Regards, HuaMin Chen
    Friday, May 13, 2011 2:43 AM
  • > Good day Erland, Even if I've scrolled through the whole Results subwindow, I still could not see any other specific errors (see this below)

    Did you try searching for "Msg"?

    In any case, your prime objective should be to get rid of these result sets.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 13, 2011 7:53 AM
  • From the Results output subwindow, I could not find out any "Msg" there. Thanks
    Many Thanks & Best Regards, HuaMin Chen
    Friday, May 13, 2011 9:22 AM
  • Good day Erland and all. Any advice for being able to capture all of the output messages there(if any)?
    Many Thanks & Best Regards, HuaMin Chen
    Tuesday, May 17, 2011 6:31 AM
  • > Good day Erland and all. Any advice for being able to capture all of the output messages there(if any)?

    As I've said, you need to investigate why this output appears at all. I don't think it should. I think it is a stray result set which should not be there.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 17, 2011 7:32 AM
  • I expect to see if there is any error message shown but I can't see that.
    Many Thanks & Best Regards, HuaMin Chen
    Tuesday, May 17, 2011 7:37 AM
  • > I expect to see if there is any error message shown but I can't see that.

    Yeah, the fact that you get "Query completed with error", but there is no error message to be found is spooky. But all my experience from program development is that you need to get ride of what occludes your sight and that is the stray result set. Once that is resovled, other problems will be easier to see - or they will go away automatically.

    You could create a temp table and run the proceudre as

    INSERT #temp(a) EXEC that_sp

    But you would sweep the real problem under the carpet.

    As I have been saying over and over again, you need to find out why that result set occurs. Maybe it should be there, but my gut feeling is that it's junk.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 17, 2011 8:03 AM
  • Hi Erland,
    My existing syntax that I use to call my process is like

    DECLARE @return_value int
    EXEC @return_value = dbo.process2
    SELECT @return_value AS N'@Return Value'
    GO

    could you pls tell me how to insert the records into one temp table? Many thanks.

    By the way, what's your time now? Please go to sleep if it's very late now!


    Many Thanks & Best Regards, HuaMin Chen
    Tuesday, May 17, 2011 8:23 AM
  • CREATE #temp (a varchar(100))
    INSERT #temp (a)
       EXEC @return_value = dbo.process2

    But again, you should investigate why this result is there. Most likely, it shouldn't, and you should get rid of it.

    By the way, what's your time now? Please go to sleep if it's very late now!


    Yeah, given the pizza I just had for lunch, I could benefit from a nap. :-)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 17, 2011 10:44 AM
  • Good day Erland,
    I have run this

    DECLARE @return_value int
    CREATE #temp (a varchar(300))
    INSERT #temp (a)
       EXEC @return_value = dbo.process2
    go

    But I've got
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near '#temp'.

    I think you're in Middle-east now.


    Many Thanks & Best Regards, HuaMin Chen
    Wednesday, May 18, 2011 3:03 AM
  • Good day Erland,
    I have run this

    DECLARE @return_value int
    CREATE #temp (a varchar(300))
    INSERT #temp (a)
       EXEC @return_value = dbo.process2
    go

    But I've got
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near '#temp'.


    It should of course be "CREATE TABLE #temp".


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 18, 2011 7:33 AM
  • Thanks a lot Erland.
    How can I see the data in "#temp"
    When I run this
    USE [mySch]
    GO

    select * from #temp
    go

    after I've run these
    DECLARE @return_value int
    CREATE table #temp (a varchar(300))
    INSERT into #temp (a)
       EXEC @return_value = dbo.process2
    go

    it says
    Msg 208, Level 16, State 0, Line 2
    Invalid object name '#temp'.

    But I know that #temp is existing in that schema.


    Many Thanks & Best Regards, HuaMin Chen
    Wednesday, May 18, 2011 8:12 AM
  • Sorry, I don't know what is going on. If you run

    DECLARE @return_value int
    CREATE table #temp (a varchar(300))
    INSERT into #temp (a)
       EXEC @return_value = dbo.process2
    go
    SELECT * FROM #temp

    You should see the data in the table. The one exception is there is a severe error that terminates the connection. Then again, the temp table only has all thus NULL values that you previously got in the SQL window.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 18, 2011 8:22 AM
  • Erland,
    Many thanks.
    I've run this

    DECLARE @return_value int
    drop table #temp
    CREATE table #temp (a varchar(300))
    INSERT into #temp (a)
       EXEC @return_value = dbo.process2
    go
    select * from #temp
    go

    But after that, I can still see this only
    http://www.4shared.com/photo/nSFofOL4/fig104.html


    Many Thanks & Best Regards, HuaMin Chen
    Wednesday, May 18, 2011 9:02 AM
  • I see a number of "Mail queued" messages.

    When you ran the batch, did you have results set to grid or text?

    I'm afraid that it is very difficult to help, because I know so very little of what you are doing. This procedure process2, is this the procedure you called that generate a one-result set every time?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 18, 2011 10:44 AM
  • Dear Erland,
    I sent you one mail for this. Thanks a lot
    Many Thanks & Best Regards, HuaMin Chen
    Thursday, May 19, 2011 6:28 AM
  • Good day Erland and all others,

    Erland,

    I have mentioned the following to you already. I don't know the process would then have "null" returned until some "breaking point" when running it. It seems the problem happens when the process was running till something was overloaded (I am not very sure that the OS was out of memory at that moment). But when testing such process some days ago, I had ever got the "out of memory" problem from the OS.

    How to capture the case that "null" was returned? Any advice from others?

    In the folliwng, when everything is normally going on, it would say "Mail queued.". I think it was malfunctioning for that "null" was returned there.

    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       
    NULL                                                                                                                                                                                                                                                                                                       


    Many Thanks & Best Regards, HuaMin Chen
    Monday, May 30, 2011 2:10 AM
  • Good day Erland,
    After I've run this below together with the profiler,

    USE [FD_DARptDB_globalDA_qa0]
    GO

    DECLARE @return_value int
    drop table #temp
    CREATE table #temp (a varchar(300))
    INSERT into #temp (a)
       EXEC @return_value = dbo.p_da_rebuild_co_other
    go
    select * from #temp
    go

    I've got these and do not see that "null" is returned like last time.

    Msg 3701, Level 11, State 5, Line 3
    Cannot drop the table '#temp', because it does not exist or you do not have permission.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.
    Mail queued.

    (14554 row(s) affected)

    (14554 row(s) affected)

    Finally it completes and still says "Query completed with errors" but I'm not very sure that the error is only for "#temp" or not.

    I did not change any codes involved for the process. I also don't know why after having made use of the profiler, I now do not have the "null" returned case above. 


    Many Thanks & Best Regards, HuaMin Chen


    Wednesday, June 1, 2011 2:24 AM
  • I've got these and do not see that "null" is returned like last time.

    But there is still:

    (14554 row(s) affected)

    (14554 row(s) affected)


    So it seems that somewhere 14544 rows are produced twice, although I don't
    know where.

    Anyway, since you seem to be victim to a problem in SSMS, the connection is
    severed before the final SELECT is run. And the more interesting question is
    where all these result sets come from. That you should be able to find in
    the Profiler trace.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 1, 2011 10:19 PM
  • Good day Erland,
    If I now do not see any "null" returned there, can I say that we should not have more other suspicion, or even say that no problem for the execution of the process? The returned messages above now do not mention that any record set has been returned but "Mail queued". Do you think that I should still pay attention to any record set processed for this? Appreciated a lot!
    Many Thanks & Best Regards, HuaMin Chen
    Thursday, June 2, 2011 1:57 AM
  • The main question is of course whether your procedure works as you intended to. If it produces the correct result when you test it in your application, there is no need to worry.

    But as I understand it, this whole thread started because you felt it didn't.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 2, 2011 8:56 AM
  • Many thanks Erland. From the output result, there should be nothing abnormal returned that is what we can see, right?
    Many Thanks & Best Regards, HuaMin Chen
    Thursday, June 2, 2011 9:04 AM
  • > Many thanks Erland. From the output result, there should be nothing abnormal returned that is what we can see, right?

    Sorry, I don't know your requirements, so I don't want to vouch for anything.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 2, 2011 9:25 AM
  • The process is really doing what I expect to have. I only want to see that, nothing is abnormal for what we can see in the output, isn't it?

    Many Thanks & Best Regards, HuaMin Chen
    Thursday, June 2, 2011 9:31 AM
  • Hi,

    Can I conclude that the message like "Mail queued" is nothing special and it should be a normal message as the output? 


    Many Thanks & Best Regards, HuaMin Chen
    Friday, June 3, 2011 1:46 AM
  • > Can I conclude that the message like "Mail queued" is nothing special and it should be a normal message as the output? 

    If you ask me, it is not normal that a procedure outputs "Mail queued" 14000 times when you run it, even less that 14000 mails are queued. But I don't know your application, so I can't really tell. That is up to you to decide.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 3, 2011 8:35 AM
  • Thanks. Still appreciated!
    Many Thanks & Best Regards, HuaMin Chen
    Friday, June 3, 2011 8:47 AM