2

I'm writing a business plan (due Monday) that depends on data in SQL Server 2012 getting exported to Excel 2007

I have a few years' experience of coding in SQL and C# but that's not my main job!

Having spend most of the day following various threads and trying workarounds, I'm at a loss:

Right click DB > tasks > export > SQL Native Client 11.0 > Excel 2003 OR 2007 > the column mapping works ok for int, DateTime, bit, but all the text (varchar(255)) columns map by default to longtext. Even changing that to varchar doesn't help.

I've also tried mapping the Source tables to Destination Excel sheets with a different name (from the drop-down). Then I can only map and export the primary keys ( is a dropdown that allows choice of "F1" only on the PK column.

System Details My PC: 64-bit OS, x64-based processor running Windows 10 Pro CPU: AMD quad-core 3.8GHz + 8GB RAM

SQL SERVER: v12 - (11.0.2100)

Microsoft SQL Server Management Studio 11.0.2100.60 Microsoft Data Access Components (MDAC) 10.0.10586.0 Microsoft MSXML 3.0 4.0 6.0 Microsoft Internet Explorer 9.11.10586.0 Microsoft .NET Framework 4.0.30319.42000 Operating System 6.3.10586

Error message/report (one of many): - Validating (Warning) Messages * Warning 0x80049304: Data Flow Task 1: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console. (SQL Server Import and Export Wizard)

I've tried: 1. Default: Sql native client > .xls --> "potential lost conversion from varchar to longtext"

  1. Using OLE DB provider for Sql Server > .xls --> same error
  2. SQL native client > .xlsx --> same error (some people said send to an earlier version of Excel. I've gone back as far as 4.0)

Running out of ideas! (and time).

Is this some sort of conflict between versions of OS, SQL, Excel, drivers/providers, 32 vs 64 bit??

Even tried reversing the roles: importing into Excel - which caused even more trouble.

Just now I found that I can actually IMPORT via Excel Data menu (which hadn't worked earlier because I chose to import pivot table rather than table).

So...Problem solved...but if I still don't understand WHY it doesn't work the other way round, I'm no wiser...

Have a good weekend if you read this far...and sorry for the long post

Dan

Daniel Otto Kellett
  • 21
  • 1
  • 3
  • Not as sure about version 2012, but I have previously used command line utility _bcp_ to export data from SQL Server – Rumbleweed Jan 23 '16 at 22:07
  • SQL Server has no issues with exporting data to Excel. I suspect the issue has more to do with confusion between exporting data or creating a report in Excel format. BTW the proper extension is `xlsx`, xls is deprecated. There's no need to go looking for drivers, xlsx is a compressed xml format. As for formats - Excel values are all text. It's the cell format that makes them appear one way or another. In anyt case, the Export wizard actually creates an SSIS package which you can save and edit using Visual Studio to change settings, formats etc., and then reuse – Panagiotis Kanavos Feb 09 '16 at 08:56

1 Answers1

3

Am experiencing the same problem. am hoping i will also find a solution like you did cheers

the solution worked

go to Excel

  1. click >on Data
  2. click >from other sources
  3. select>from sql server

use the wizard as you wish enjoy

dannjoroge
  • 608
  • 1
  • 8
  • 19