Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JohnGault777
Starting Member
31 Posts |
 Posted - 2011-02-28 : 15:10:28
|
| I have a query that will return the results I need when I run it in SSMS. When I try to use Import Export Wizard to get the information to Excel it fails every try. 1. How is it possible for a query to work in SSMS but fail while exporting the exact same query? 2. I'm new to SQL, what is the simplest and easiest way to export a query to excel? Additional information: I use the Export Wizard all the time. I never seem to have an issue. However, the query I'm running now is fairly complex. One of the tables I need to join breaks our all the different attributes of a single entry into different rows, so I had to turn rows into columns and the inner join the table to row entries I need. It works in SQL but will not export. Is there a level of complexity that isn't allowed in the export function?JG777 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
JohnGault777
Starting Member
31 Posts |
Posted - 2011-02-28 : 15:54:48
|
| Here's the error. As far as showing the query, I don't think I can show it. First, it is giant, not in complexity but number of items being pulled. It isn't more than the 256 Columns and the row output only ends up being about 3,000 rows. Second, the database contains sensitive information so I'm hesitant to show the query I'm using. I'll check around internally to see if it's possible. - Validating (Error)MessagesError 0xc02020f6: Data Flow Task: Column "policyNum" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc02020f6: Data Flow Task: Column "otherPriorCarrier" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc02020f6: Data Flow Task: Column "unitNumber" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc02020f6: Data Flow Task: Column "address1" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc02020f6: Data Flow Task: Column "address2" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc02020f6: Data Flow Task: Column "city" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc02020f6: Data Flow Task: Column "county" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc02020f6: Data Flow Task: Column "zip" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc02020f6: Data Flow Task: Column "constructionCode" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc02020f6: Data Flow Task: Column "latitude" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc02020f6: Data Flow Task: Column "longitude" cannot convert between unicode and non-unicode string data types. (SQL Server Import and Export Wizard) Error 0xc004706b: Data Flow Task: "component "Destination - Query" (334)" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard) Error 0xc004700c: Data Flow Task: One or more component failed validation. (SQL Server Import and Export Wizard) Error 0xc0024107: Data Flow Task: There were errors during task validation. (SQL Server Import and Export Wizard) JG777 |
 |
|
|
JohnGault777
Starting Member
31 Posts |
Posted - 2011-02-28 : 17:49:11
|
| Ok, I have kept working in it and cannot figure it out. However, I think I'm getting closer. It appears that it may be a problem with Excel. I think this because the step of the "SQL Server Import and Export Wizard" where it says "Select Source Tables and Views" you're given the option to "Preview...". I can preview the table that is generated by the query but it won't let me export it. In addition, SQL Help says this for the Error number: ----------------------Explanation This message indicates that a data flow component is trying to pass Unicode string data to another component that expects non-Unicode string data in the corresponding column, or vice versa.Possible CausesThis message might appear because of one of the following problems:You are using the Excel source and have not configured a Data Conversion transformation to convert string columns from the Unicode data that was loaded from the Excel source.You are using the Excel destination and have not configured a Data Conversion transformation to convert to Unicode the data that is being saved to the Excel destination.User Action Add Data Conversion transformations to convert the dataAdd Data Conversion transformations to convert string columns from non-Unicode (DT_STR) to Unicode (DT_WSTR) strings, or vice versa.Use the SQL Server Import and Export Wizard to create the packageWhen you use the SQL Server Import and Export Wizard to create a package, it creates and configures any Data Conversion transformations that are required for you. Use the wizard to create a base package, save it, and then reconfigure and enhance it.-----------------------For the User Actions, how do I do either of them? Here's how novice I am, I have no idea what non-Unicode or Unicode really is or how to tell if a column is trying to move from one to the other. Therefore, I don't know how to change the Data Conversion. Could someone help me with that? Next, I think I know how to save the package. I tried it but I cannot find it in the source file. I don't think it is allowing me to create the package file because it is giving me an Error. Can someone please please help?JG777 |
|
|
|
|
|
|
|
|
Valid Query in SSMS will not Export to Excel