Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to change default format from unicode to ANSI for query outputs in SQL 2005? Expand / Collapse
Author
Message
Posted Tuesday, January 8, 2008 2:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2008 2:00 PM
Points: 6, Visits: 21
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
Post #440297
Posted Wednesday, January 9, 2008 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2008 2:00 PM
Points: 6, Visits: 21
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
Post #440775
Posted Thursday, January 10, 2008 3:24 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 2:42 AM
Points: 739, Visits: 75
Which tools you use to create your file?
Post #441075
Posted Thursday, January 10, 2008 9:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2008 2:00 PM
Points: 6, Visits: 21
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.
Post #441280
Posted Thursday, January 10, 2008 9:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:18 PM
Points: 191, Visits: 312
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

Post #441284
Posted Thursday, January 10, 2008 9:52 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:53 AM
Points: 3,475, Visits: 584
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 Varshal

Post #441285
Posted Thursday, January 10, 2008 10:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2008 2:00 PM
Points: 6, Visits: 21
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!
Post #441305
Posted Thursday, January 10, 2008 2:30 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:53 AM
Points: 3,475, Visits: 584
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 Varshal

Post #441482
Posted Friday, January 11, 2008 10:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2008 2:00 PM
Points: 6, Visits: 21
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.
Post #441897
Posted Friday, January 11, 2008 1:26 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:53 AM
Points: 3,475, Visits: 584
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 Varshal

Post #441990
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse