Copy Paste fails with large result sets.

Last Post 06 Apr 2011 06:08 AM by ASAPDBA. 13 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
ASAPDBA
New Member
New Member

--
30 Mar 2011 12:30 PM

In SQL 2008 Management Studio. The copy fails on with large result set. I can select and copy small portions and paste in excel, notepad, word ect.... Once I have the entire result set over I can easily copy paste the entire result set between those other applications. So this appears to be only an issue in SSMS. When doing before and after testing on a stored proc, it is very helpful to copy the results into Araxis Merge to quickly identify differences. There is a work around, we can just select results to file then compare the two files, but this is a little more cumbersome. I seen a few articles on the web stating this problem with no resolutions posted. Just wondering if anyone here knows of a setting or fix?  


Thanks

gunneyk
New Member
New Member

--
30 Mar 2011 02:38 PM
Do you get an error? Are you missing rows or parts of columns? I copy and paste large result sets all the time with 2008 SSMS and never had a problem. How large are you talking about?
ASAPDBA
New Member
New Member

--
31 Mar 2011 05:43 AM
No error, Nothing happens when it gets to a certain size. We have some hefty reports.

Here is some simple code that illustrates the problem. Set results to text, run the script and just copy and paste the results into note pad. In note pad I can moe then quadruple the amount and copy paste the entire amount into excel and vise vesa.

DECLARE @Table TABLE (TestStr VarChar(8000))
SET NOCOUNT ON

/*Copy and paste works.*/
INSERT INTO @Table SELECT REPLICATE('a', 8000)
DECLARE @Cnt Int = 120

/*Copy and paste DOES NOT work.*/
--INSERT INTO @Table SELECT REPLICATE('b', 8000)
--DECLARE @Cnt Int = 130

WHILE @Cnt > 0
BEGIN
SET NOCOUNT ON
SELECT TestStr FROM @Table
SELECT @Cnt = @Cnt - 1
END
gunneyk
New Member
New Member

--
31 Mar 2011 10:47 AM
When you use text results it is just like a single column and there is a limit of 32760 chars I believe with a single column. Try results to Grid and it should work. SSMS was never designed to do something like that. Use results to file instead. It's the wrong tool for the job.
ASAPDBA
New Member
New Member

--
01 Apr 2011 05:34 AM
It does not work with results to grid either. The results are in the result pane, it just won't copy them to the clipboard. I appreciate you trying to help but, to say SSMS was never designed to do something that every other Microsoft product easily handles is an indicator that it probably can be fixed and should be fixed. I have been using the copy paste functions for over ten years and it is the perfect use of that tool. Thanks for your opinion, and I'm not trying to offend, but you really don't need to post if you don't have an answer just because you don't use the product the same way as someone else does.






russellb
New Member
New Member

--
01 Apr 2011 08:15 PM
Are you saying that the results are truncated, or that the copy actually fails?

If the former, then gunney is right, wrong tool. Even if you don't like that fact.

If it's the latter, works fine for me. What are you settings for tools/options/query results/results to text?

Also, copy/pasting multiple recordsets opens a new can of worms. I'm not telling you to abandon copy/paste (after all, we all do that and probably often) but there are better ways to do it -- especially if your intent is to compare two results. In fact if you're doing it often, you can automate the output to say, Excel, with a comparison formula embedded.

In any event, I don't have any problems executing your samples and copy/pasting 'em
gunneyk
New Member
New Member

--
02 Apr 2011 11:00 AM
There are enough problems with SSMS that effect more people on a daily basis than not being able to copy and paste a monster result set I am 100% sure of this. Can this be fixed? Probably but hard to say for sure without knowing exactly what is causing it. But I do know I would much rather have the team fix more real world issues with SSMS before something like this especially when there are other ways to handle it. I use copy and paste every day as well but I fully expect there to be limits to this ability. It's a bummer that the tool doesn't do what you think it should or want it to but there are many other issues that are much higher on the list to be fixed and that is the reality of it. I suggected two things that I thought might be the cause. Obviously they are not so it must be a deeper issue and I can't do anything about it other than to suggest you use the method that you already know works.
ASAPDBA
New Member
New Member

--
04 Apr 2011 06:09 AM
Russelb,

Copy fails, It doesn't copy anything, Tab delimited, include column headers, Max num Char set to 8000. What settings did you use? I already have a workaround. just wondering if anyone else ran into this and found what setting or set of circumstances fixed it for them.


Gunneyk,
I don't disagree that there are much more pressing items for the team to be working on reguarding SSMS, but this is the "General Discussion" forum, not the "Only Ask Priority one items that should be worked on" forum. I appreciate the time you took and the suggestions you offered. What I took offense too was the statement that SSMS wasn't designed to do this and it was the wrong tool because you didn't know of an answer. I appologize that the little issue I have with SSMS doesn't make your top 25 items to fix list. But this was the main reason we have a subscription to SQLMAG, so we can find solutions to annoying little problems.

Again, I appreciate the time anyone takes reading this question and if you have some suggestions to a solution great. If you don't think the issue is important and don't know of a solution then posting a reply really isn't helpful.
ASAPDBA
New Member
New Member

--
04 Apr 2011 06:54 AM
Russelb.

Since this worked for you, I went in and was going to try other settings, and found that it worked for the 130 cnt that didn't work last week, but does fail when I change that to 300. I did get to reboot my computer over the weekend. I can still copy paste larger amounts of data between other MS apps when this fails, so this may just be tied up with SSMS memory allotted for the clipboard function. Again this isn't a big issue, but if someone has some insight to a solution it would be greatly appreciated.

DECLARE @Table TABLE (TestStr VarChar(8000))
SET NOCOUNT ON

INSERT INTO @Table SELECT REPLICATE('a', 8000)
DECLARE @Cnt Int = 300

WHILE @Cnt > 0
BEGIN
SET NOCOUNT ON
SELECT TestStr FROM @Table
SELECT @Cnt = @Cnt - 1
END
rm
New Member
New Member

--
04 Apr 2011 09:55 AM
ASAPDBA, sqlmag doesn't fix anything for MS. You can submit what you like to see to MS Connect site https://connect.microsoft.com/directory/?keywords=sql.
gunneyk
New Member
New Member

--
04 Apr 2011 04:25 PM
ASAPDBA, I didn't say that because I didn't have an answer I was 100% serious when I said it was not designed to do that. I have been working with SQL Server for many years and have been a SQL MVP for over 10. I know the perople who wrote and or designed this tool personally and we have discussed this issue before. So I am sorry you don't like my answer but it comes with inside knowlege. SSMS does have limitations and it is not the best when it comes to memory usage. It's better now than it used to be in some areas but we have seen people have issues with very large copies before and the answer has always been if it doesn't work directly use the to file option. From what I remember it did behaive a little differently depending on how much memory was available etc. but there are definate limits and they are not just like other MS tools. SSMS currently is it's own tool and thus has it's own issues. And it is a 32 bit tool regardless of the OS or version of SQL Server so that comes into play as well. So the bottom line is that you may be able to tweak some things to get it to behave a little better but I know there is a limit (and you are apparently hitting it) that is known and there will be nothing done about it. The next version of SQL will use a completely different tool so most things will not get touched.
ASAPDBA
New Member
New Member

--
05 Apr 2011 08:59 AM
RM, I'm not asking SQLMAG to fix anything, I posted a simple question in the forum of an issue I ran into to see if someone else reading the forumns had run into the same issue and had a soulution. nothing more, nothing less. I thought this was the purpose of the forums? Please let me know if this has changed, I would be happy to not renew our subscription.


GunnyK,
Hidden amongst the text is an answer I can buy, "We've seen this before and it seems to be a known issue in SSMS, You already have the preferred workaround."

Fantastic! Now I know it's not a setting on my machine nor in SSMS, nor a compatiblility issue with other software. But when you said SSMS was purposly designed not to use copy/paste like other MS software, well that just doesn't make sense, and sounds like a copout answer.

I appreciate the help, as frustrating as it was.





russellb
New Member
New Member

--
06 Apr 2011 04:24 AM
Weird, no matter how high I set @cnt, it works for me. Although, I only get 256 characters width.

I have had issues where my clipboard stops working from time to time (in any application) and a reboot always fixes it. I do a lot of copy/paste from RDP windows which seems to be the culprit when it happens.

I think this thread to a wrong turn somewhere. No one wants to chase you away. Always tough to read tone and intent in a forum post. Surely no harm was meant. Do feel welcome to continue to post when you have questions.
ASAPDBA
New Member
New Member

--
06 Apr 2011 06:08 AM
in Tools/options/queryresults/sql server/results to text. You would need to set your "Maximum number of characters displayed in each column" to 8000. Default is 256

This was like someone telling me a hammer is the wrong tool to pound a nail......just because the hammer in the toolbox has a broken handle.



You are not authorized to post a reply.

Acceptable Use Policy