PiedmontHealth
asked on
How to change SQL 2005 query results from unicode to ansi
In SQL 2000, if you ran a query and saved the results, the file saved in ansi format. In SQL 2005, the file saves with unicode instead. This is causing problems with the external system that a particular file goes to. I do not see an option to change this. I googled the issue and all the responses referred to the screen where you specify what file to save the results to and they mention the encoding drop down box. When I save the results to file, I do not have an encoding drop down option - I only see this if I save the file first, open it in Notepad, and then I can resave in ansi, but this is too much of a hassle. Can't this be saved in ansi directly from SQL 2005?
Alternatively before you execute the query, on the File menu, click "Query" > "Results" > "Results to file".
ASKER
I tried your solution and it did not work. First of all, the format is still unicode and secondly, it added a header and line of dashes at the top, which we cannot have.
>> it added a header and line of dashes at the top, which we cannot have.<<
It would probably help if you had not enabled "Include column headers in the result set" :)
It would probably help if you had not enabled "Include column headers in the result set" :)
But to answer your question, since you have chosen to save your data as unicode, you will have to convert to non-unicode, by using CAST as in CAST(YourUnicodeColumnName
ASKER
You stated that "since you have chosen to save your data as unicode" - how did I do that? That is the whole point, I don't want it to be unicode. Is this some choice in the database? I did not get this in SQL2000 but am experiencing it once the database was upgraded to SQL2005.
ASKER
I reviewed the entire script and there are no unicode data types. All output is from tremporary tables created in the script and all data type are non-unicode.
>>I reviewed the entire script and there are no unicode data types.<<
Than I am afraid I have no idea.
Than I am afraid I have no idea.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are you doing this from the SQL Server Mnangement Studio or via command line?
It the SSMS, go into Tools --> Options --> Qery(?) --> Results to file and see what is checked on and off.
It the SSMS, go into Tools --> Options --> Qery(?) --> Results to file and see what is checked on and off.
Choose it and run the query, SSMS will prompt for a file path to save your file.
Specify the path and save the RPT file.
This will simplify things.