0

I have no issues using Data Connections in Excel 2010 most of the time, but occasionally when I get external data via Microsoft Query (via SSMS), the query results will display fine in the GUI, but when I click export to Excel, The "Query from [database]:Getting Data..." will drop off the Getting Data part and return no results.

It simply says, "Query from [database name]". Has anyone had this issue before and how do I resolve it? Often times, I have to simply restart my PC or try again later, and it will work. Any ideas?

halfer
  • 19,824
  • 17
  • 99
  • 186
TWaller
  • 81
  • 1
  • 7
  • Additionally, often if I make the query into a stored procedure and simply have "exec [stored procedure name]", it will work and return results. I am trying to avoid that route as the end user does not have access to execute sp's, but can run queries. Maybe there is a maximum length for a query in Excel? FYI, the query I'm running is 280 lines. – TWaller May 10 '16 at 13:23
  • The issue was the fact that part of the code was creating a temp table which Excel's Data Connections did not like. I put Set Nocount On; at that top of my query and it removes the messages regarding the number of rows the temp table created, and it worked. Thanks! – TWaller Mar 28 '17 at 16:39

2 Answers2

1

Check your SSMS options (Tools|Options) for maximum characters retrieved values - and - or -

(a previous posting by SSChampion) SSMS has an option to save a query results directly to file, without displaying it(Control + Shift + F, then execute the query); that's an option you might want to try instead, and just save the file to .csv format.

BCP.EXE out or sqlcmd is a better option to manipulating large amounts of data to disk.

Excel's not the right tool to be using for massive numbers of rows of data. large files like that should be handled with text editors, like NotePad++ or EditPlus instead of Excel.

JimR
  • 41
  • 2
  • It was because part of the script created messages (1 row inserted, etc.). I put "set nocount on;" at the top of the script and I haven't had a problem since. Thanks for the help, though! – TWaller Apr 11 '17 at 17:51
0

It was because part of the script created messages (1 row inserted, etc.). I put "set nocount on;" at the top of the script which removes these messages, and I haven't had a problem since.

TWaller
  • 81
  • 1
  • 7