June 30, 2016 at 12:55 pm
SQL Server 2012. Trying to get all crazy and use the ^#$%& export wizard to dump a query into an excel file. I get the usual type-unknown 200 and 201 errors because though SQL Server will PREVIEW the results, it somehow has never heard of exotic data types like VarChar(50) and DateTime. So I map the "unknown" data types back and it STILL chokes and won't auto-convert them.
So how in God's green Earth is this wizard supposed to help me? And do I need to explicitly convert a VarChar(50) in the source query to ... VarChar(50)??? My reading seems to say this is a bug. Do they really not have a fix for this?
My apologies for the sarcastic tone but I am legitimately enraged by this. Going from a SQL query to an Excel file should be the EASIEST thing I do all day, not the most time consuming.
June 30, 2016 at 1:53 pm
The Import \ Export wizard only looks at the first 50 rows for the transformations or previews.
If you think about it, attempting to analyze an entire data set to cover all of the possibilities may take forever, so the first 50 kind of makes sense. That's probably why the wizard makes every number field a float when doing an import.
This was never designed to do any real transformations or interpretations. The "hard stuff" is what SSIS is for.
I suspect that there is data someplace in your query that is unicode, and the wizard doesn't really handle that.
You can try a couple things.
1. Create a staging table and make all of the fields nvarchar(1000), or whatever will work, and select all the records into that staging table. Do the export from there.
2. Create an SSIS package, add a transform step between the database connection and the Excell connection that handles the conversion.
3. Add the CONVERT function to all of the fields in your query to change them to nvarchar(1000).
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 30, 2016 at 2:18 pm
Thanks for the reply. So far I have found suggestions to edit the XML data conversion config file (which may get me fired), create a View from the query and reference that in the wizard, and your suggestions. While I can create an SSIS package, I am still flabbergasted that it should be necessary. I am doing a simple join and outputting basic results. And yes, I am using unicode in a few places, but why this should boggle MS is beyond me.
In fact, the simplest solution so far came from me copying and pasting from a SSMS results grid directly into the spreadsheet. Manually typing in the column headers isn't fun, but is less time consuming that everything else.
Thanks.
June 30, 2016 at 2:27 pm
kpwimberger (6/30/2016)
Thanks for the reply. So far I have found suggestions to edit the XML data conversion config file (which may get me fired), create a View from the query and reference that in the wizard, and your suggestions. While I can create an SSIS package, I am still flabbergasted that it should be necessary. I am doing a simple join and outputting basic results. And yes, I am using unicode in a few places, but why this should boggle MS is beyond me.In fact, the simplest solution so far came from me copying and pasting from a SSMS results grid directly into the spreadsheet. Manually typing in the column headers isn't fun, but is less time consuming that everything else.
Thanks.
When copying from the grid in SSMS you can also get the headers. I do this occasionally to send information to others.
June 30, 2016 at 2:36 pm
Well look at that! Thanks so much! 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply