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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris MConsulting - Technology ServicesCommented:
There is an icon in SSMS whenre you can specify to save results to file (.RPT file)
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.
Chris MConsulting - Technology ServicesCommented:
Alternatively before you execute the query, on the File menu, click "Query" > "Results" > "Results to file".

PiedmontHealthAuthor Commented:
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.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Anthony PerkinsCommented:
>> 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" :)
Anthony PerkinsCommented:
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(YourUnicodeColumnNameGoesHere AS varchar(100))  Potentially, you could lose data, but I suspect you already know that.
PiedmontHealthAuthor Commented:
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.
PiedmontHealthAuthor Commented:
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.
Anthony PerkinsCommented:
>>I reviewed the entire script and there are no unicode data types.<<
Than I am afraid I have no idea.
Mark WillsTopic AdvisorCommented:
Well, not too  much good news I am afraid.

It will do unicode formatted files as output if saving a query, or even nominating the query result (output).

You will need to start thinking about bulk exports... maybe even creating a procedure to make it "easier" if you are going to do this often...


If I was to either setup the query result to either a text file or report file, then

select * from AdventureWorks.Person.Contact

Will always create a unicode type file. Even if I put results to grid and save from there... If I was to output as CSV however it is an ANSI file being created.

Your alternative is to start thinking about the BCP utility. For example (repeating the above query)

exec xp_cmdshell 'bcp "select * from AdventureWorks.Person.Contact" queryout "c:\ee\" -T -c -CACP'

will create an ANSI / PC file the "-c" option says use char and the "-CACP" says what character set (ie -C = charset option and ACP = ANSI / ISO 1252)

Now, for fixed length formats, you will probably need to create a format file to set the widths etc, or, if setting up as a stored procedure, then you might do some custom formatting setting CHAR type characteristics with trailing spaces padded out.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim P.Commented:
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's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.