4

I run a query in SQL management studio (results to grid), then select all results and copy with headers. Then I paste into Excel.

The result: two specific records are put together on one row in Excel.

With two specific I mean that I first got 26000+ records, then after discovering this bug, changed the query to get just these two records, and still had the same phenomenon occurring, this did not happen in any of the other 26000 records.

I'm running management studio 2008 on a windows 7 machine connected to SQL Server 2000 running on a Windows 2003 server. I tried copying to both Excel 2003 and Excel 2007 and it happens in both cases.

It seems that it has something to do with double quotes. The fourth field (of type varchar(50))has double quotes for both lines (and many more), but it's not the first or last character. The sixth field (of type varchar(1000)) in the first of the two records has double quotes as first character. The fourth field of the first record appears normally. All the fields from the sixth field of the first record upto (inclusive) the fourth field of the second record appear in the same cell in excel. But the double quotes at the end of the sixth field in the first record and in the fourth field of the second record are removed. The characters after the double quotes in the fourth field of the second record still remain in that same cell. The rest of the fields of the second record appear in consecutive columns in Excel.

When pasting in notepad the data does appear normally.

I can't give the exact data, but it's something of the following form:

    float   varchar(50) varchar(50) varchar(50) datetime    varchar(50) varchar(1000)   varchar(50)
    1.1 text    text    text"text   2012-01-01 00:00:00.000 text    "text   text
    1.1 text    text    text"text   2012-01-01 00:00:00.000 text    text    text

Has anyone encountered this bug before? What is the offending application? SQL Server management studio 2008, Windows 7, both versions of Excel, sql server 2000, Windows 2003? If it is SQL server, does it also happen internally in SQL server?

Kenny Rasschaert
  • 8,925
  • 3
  • 41
  • 58
Ernst
  • 41
  • 1
  • 2
  • 2
    26K records seems like a lot to cut and paste. I suggest you use DTS, SSIS or put the query into a data connection in Excel and pull the records in that way. – Rory Jan 18 '12 at 22:09

2 Answers2

1

I know this is a very old question but i happened to run into the same issue just now and it was because of the " character. I just removed that in my query with a replace syntax and it worked. PS i also removed all CR LR data as well.

(REPLACE(REPLACE(REPLACE(REPLACE(, CHAR(9), ''), CHAR(10), ''), CHAR(13), ''), CHAR(34), ''))

user2181585
  • 21
  • 2
-1

No need to worry about anything. Its solution is simple, Just dont select all columns using *, you have to write column names ...be it be all columns but you have to mention column names in select, Then it will surely work .. I have tested and it works just fine..Cheers:)

Owais Shah
  • 11