How to change default format from unicode to ANSI for query outputs in SQL 2005?

Question

How to change default format from unicode to ANSI for query outputs in SQL 2005?

mipendorf

Mr or Mrs. 500

Points: 538
More actions
January 8, 2008 at 2:23 pm

#180245

Hey Gang,
I've got a SQL 2005 server which connects to a remote SQL 2000 system and retrieves data about students.
I then query that data, and import it into a Transportation and Routing application that runs in SQL 2005.
I need the data to be updated nightly, so that new students who come in (or current students who move) can be correctly routed.
The Trans/Routing software can import the file *only* if it's ANSI - not if it's unicode.
I can run the query by hand, and save the results to a file. I then open the file (in notepad), and resave it as ANSI. The Trans/Routing software can then import the file, and all is well.
The Trans/Routing software is easily config'd to auto update nightly. Unfortunately, I can't find a way to get SQL2005 to output the results of the query in ANSI automatically (so I can have that query run nightly, and output the file for the Trans/Routing software to import).
Any help would be much appreciated.
Thanks in advance,
M

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

mipendorf Mr or Mrs. 500 Points: 538 More actions · Answer 1

Has anyone else had trouble changing query results from Unicode to ANSI? Should I look into the command line manner of setting up the query?

Thanks for any help - I've been having a hard time with this issue for quite a long time, and only recently have decided to ask about it since I haven't been able to find anything by googling.

Thanks again for your help,

M

KennyClarck Ten Centuries Points: 1239 More actions · Answer 2

KennyClarck

Ten Centuries

Points: 1239

January 10, 2008 at 3:24 am

#766543

Which tools you use to create your file?

mipendorf Mr or Mrs. 500 Points: 538 More actions · Answer 3

KennyClarck (1/10/2008)
Which tools you use to create your file?

It's just a simple query (new query from SQL management interface).

I have created a table on the other server that contains all the info I need (and only the info that I need), so it's a SELECT * FROM tablename

And I have the results go to a file. That file *always* gets the UNICODE format. I can't seem to change that, no matter what I select.

Thanks.

Prakash-205770 Ten Centuries Points: 1079 More actions · Answer 4

We can have scripts to monitor objects inside databases.

But how can we informed if the sqlserver instance is stopped or sqlserver agent is stopped?

This is the biggest problem we face.....

R.Prakash

Kindest Regards,

R

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

Hi,

Saving query results in other formats is tricky! Somebody here on this forum answered to me, so I am not entitled to a credit. Here is how to do it in Management Studio:

Run a query, right-click results, select "Save Results As", in the "Save Grid Results" dialog select the folder to save and specify the file name. As soon as you enter the file name, the Save button becomes enabled. It has a very little arrow on the right. Click this arrow. Select "Save With Encoding" option. The new dialog opens with the "Available Encoding" selection. Select ANSI or Unicode or Chinese.

Let us know if it helped.

I also suggest to create a SSIS job for the original postwer here who would lik the automatic output. You can change the output types in SSIS

Regards,Yelena Varsha

mipendorf Mr or Mrs. 500 Points: 538 More actions · Answer 6

Yelena Varshal (1/10/2008)
Hi,
Saving query results in other formats is tricky! Somebody here on this forum answered to me, so I am not entitled to a credit. Here is how to do it in Management Studio:
Run a query, right-click results, select "Save Results As", in the "Save Grid Results" dialog select the folder to save and specify the file name. As soon as you enter the file name, the Save button becomes enabled. It has a very little arrow on the right. Click this arrow. Select "Save With Encoding" option. The new dialog opens with the "Available Encoding" selection. Select ANSI or Unicode or Chinese.

Thanks. I'll definitely try this.

I also suggest to create a SSIS job for the original postwer here who would lik the automatic output. You can change the output types in SSIS

I'm sorry I'm so new to this. This is exactly what I'd like to do as I need to automate this process so I don't have to do it every night. What is SSIS?

For my job I'll need:

1) Connect to connected server (I'm an admin on one server, but need to get data from another server - and don't have admin rights on that one - but I can read the table I need).

2) Execute Query

3) Cut last 2 lines (showing rows returned, and blank formatting lines)

4) Save results to ANSI file

Let us know if it helped.

Absolutely!

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 7

SSIS is SQL Server Integration Services and it is a 2005 version of 2000 Data Transformation Services. It is a part of SQL Server.

If you don't know how to work with SSIS, tell us here what technologies you know, maybe a VBscript or MS Access programing or web reporting and we will suggest something that you will be comfortable with, because you will need to support this.

Regards,Yelena Varsha

mipendorf Mr or Mrs. 500 Points: 538 More actions · Answer 8

Yelena Varshal (1/10/2008)
SSIS is SQL Server Integration Services and it is a 2005 version of 2000 Data Transformation Services. It is a part of SQL Server.
If you don't know how to work with SSIS, tell us here what technologies you know, maybe a VBscript or MS Access programing or web reporting and we will suggest something that you will be comfortable with, because you will need to support this.

Thanks for your offer. I recently inherited this server, and don't know much about Windows Servers. I have more experience with Linux and OSX.

I'm up for learning SSIS if that's a service I'll need to learn. You're right though, I'll need to support this, so I better get up on it now.

Thanks again for any help you can give me to get me started - I'll start reading up on SSIS today.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 9

Good luck!

I know how you feel: I don't know Oracle, Linux and Unix and one of my apps is Linux/Oracle so I am really trying to learn.

Regards,Yelena Varsha

mipendorf Mr or Mrs. 500 Points: 538 More actions · Answer 10

I ran into another problem, which is that I was trying to automate a job on SQL2005 by querying the SQL2000 server. This failed due to the fact that the SQL2005 is 64-bit, and our SQL2000 isn't (and isn't likely to have the SPs applied).

This support doc seems to have covered it pretty well.

http://support.microsoft.com/kb/906954/en-us

I'm now looking at having the SQL2000 server just run the query on a nightly basis, and write the results of the query to a sharepoint (after SQL2000 server mounts the shared drive from the SQL2005 server).

The transportation program can then grab that file and make the import.

What's the best way to automate a simple query on SQL2000 that writes to the file? Should I use the Enterprise manager and create a job? Can I have that job also mount the sharepoint? I'm pretty sure I can, but am not sure how to do that.

Thanks in advance,

M