Need to change a database collation.

Hi, i have a server running sql2000 sp3 on win2000 server.
The server has configured the sql_latin1_general_cp1_ci_as collation,
but on this server a user create a database that has the
Modern_spanish_ci_as
collation.
Every time we make a query on that database from a database that has the
server collation it reports problems with the collation.
We want to change the db collation, but we dont know what effect does
it will cause on the data and the application.
What do you recommend to us to change the collation?
thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
MariaGuzman wrote:
> Hi, i have a server running sql2000 sp3 on win2000 server.
> The server has configured the sql_latin1_general_cp1_ci_as collation,
> but on this server a user create a database that has the
> Modern_spanish_ci_as
> collation.
> Every time we make a query on that database from a database that has
> the server collation it reports problems with the collation.
> We want to change the db collation, but we dont know what effect does
> it will cause on the data and the application.
> What do you recommend to us to change the collation?
> thanks a lot for your help.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
You can use the COLLATE clause on an ALTER DATABASE to change the
default collation of the database:
ALTER DATABASE MyDatabase COLLATE French_CI_AS
However, altering the default collation of a database does not change
the collations of the columns in any existing user-defined tables.
You'll need to change each table with ALTER TABLE.
There are restrictions:
You cannot alter the collation of a column that is currently referenced
by:
a.. A computed column.
b.. An index.
c.. Distribution statistics, either generated automatically or by the
CREATE STATISTICS statement.
d.. A CHECK constraint.
e.. A FOREIGN KEY constraint.
If the database is small , you might have better luck creating a new
database, migrating the objects over, renaming the old database and then
renaming the new one (keep the old one around until you are sure the
migration was done correctly).
David Gugick
Imceda Software
www.imceda.com

Need to change a database collation.

Hi, i have a server running sql2000 sp3 on win2000 server.
The server has configured the sql_latin1_general_cp1_ci_as collation,
but on this server a user create a database that has the
Modern_spanish_ci_as
collation.
Every time we make a query on that database from a database that has the
server collation it reports problems with the collation.
We want to change the db collation, but we don´t know what effect does
it will cause on the data and the application.
What do you recommend to us to change the collation'
thanks a lot for your help.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!MariaGuzman wrote:
> Hi, i have a server running sql2000 sp3 on win2000 server.
> The server has configured the sql_latin1_general_cp1_ci_as collation,
> but on this server a user create a database that has the
> Modern_spanish_ci_as
> collation.
> Every time we make a query on that database from a database that has
> the server collation it reports problems with the collation.
> We want to change the db collation, but we don´t know what effect does
> it will cause on the data and the application.
> What do you recommend to us to change the collation'
> thanks a lot for your help.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
You can use the COLLATE clause on an ALTER DATABASE to change the
default collation of the database:
ALTER DATABASE MyDatabase COLLATE French_CI_AS
However, altering the default collation of a database does not change
the collations of the columns in any existing user-defined tables.
You'll need to change each table with ALTER TABLE.
There are restrictions:
You cannot alter the collation of a column that is currently referenced
by:
a.. A computed column.
b.. An index.
c.. Distribution statistics, either generated automatically or by the
CREATE STATISTICS statement.
d.. A CHECK constraint.
e.. A FOREIGN KEY constraint.
If the database is small , you might have better luck creating a new
database, migrating the objects over, renaming the old database and then
renaming the new one (keep the old one around until you are sure the
migration was done correctly).
--
David Gugick
Imceda Software
www.imceda.comsql

Need to change a database collation.

Hi, i have a server running sql2000 sp3 on win2000 server.
The server has configured the sql_latin1_general_cp1_ci_as collation,
but on this server a user create a database that has the
Modern_spanish_ci_as
collation.
Every time we make a query on that database from a database that has the
server collation it reports problems with the collation.
We want to change the db collation, but we dont know what effect does
it will cause on the data and the application.
What do you recommend to us to change the collation'
thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!MariaGuzman wrote:
> Hi, i have a server running sql2000 sp3 on win2000 server.
> The server has configured the sql_latin1_general_cp1_ci_as collation,
> but on this server a user create a database that has the
> Modern_spanish_ci_as
> collation.
> Every time we make a query on that database from a database that has
> the server collation it reports problems with the collation.
> We want to change the db collation, but we dont know what effect does
> it will cause on the data and the application.
> What do you recommend to us to change the collation'
> thanks a lot for your help.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
You can use the COLLATE clause on an ALTER DATABASE to change the
default collation of the database:
ALTER DATABASE MyDatabase COLLATE French_CI_AS
However, altering the default collation of a database does not change
the collations of the columns in any existing user-defined tables.
You'll need to change each table with ALTER TABLE.
There are restrictions:
You cannot alter the collation of a column that is currently referenced
by:
a.. A computed column.
b.. An index.
c.. Distribution statistics, either generated automatically or by the
CREATE STATISTICS statement.
d.. A CHECK constraint.
e.. A FOREIGN KEY constraint.
If the database is small , you might have better luck creating a new
database, migrating the objects over, renaming the old database and then
renaming the new one (keep the old one around until you are sure the
migration was done correctly).
David Gugick
Imceda Software
www.imceda.com

Need Help, DTS is Running extremely Slow!

Hi guys.

I have a DTS package ON SQL 2000 which transfer data from AS400 to SQL 2000 using an ODBC Client Access 5.1 (The DSN was configured by a sysdmin on the AS400 so it is configured properly).
When i execute the package manualy (by right click and "execute package") the package runns fine and ruterns data in no time (Eproximatly 30000 rows in 15 sec).

The problem starts when a job executes the same packagee!!!
When i start the job, the DTS package is running Very Very Slow!!!!
sometime it takes Hours to return a few rows! and it seems that it is stuck.

The SQLAgent is running as a NT Account with Administrator rights, and has full access to the AS400!! so the problem is not the Agent.

by monitoring the AS400, i have noticed that the job/DTS is retreaving the first fetch quickly , and then it is in a waiting status

i have tried everything and cant seem to get this problem fixed

Does anyone know what could be the problem?
I Need Help Quick!!!
Thank You

GilTo maximize CPU resources, you can define a CPU idle condition for SQL Server Agent. SQL Server Agent uses the CPU idle condition setting to determine the most advantageous time to execute jobs.

For example, you can schedule a daily backup job to occur during CPU idle time and slow production periods.

Before you define jobs to execute during CPU idle time, determine how much CPU the job requires. You can use SQL Server Profiler or Windows NT Performance Monitor to monitor server traffic and collect statistics. You can use the information you gather to set the CPU idle time percentage.

Define the CPU idle condition as a percentage below which the average CPU usage must remain for a specified time. Next, set the amount of time. When this time has been exceeded, SQL Server Agent starts all jobs that have a CPU idle time schedule.

But test it before deploying on production.|||Thnx for replying Satya,

I am afraid this ahs nothing to do with CPU resources. Even when i start the job manualy when there is no activity at all an the server the DTS runs Very Very Slow.

I am prety sure that there is somthing else to be done , allthow i will try your advise.

Any suggestions?|||The are a number of thing you can do. It's specific to your job. First of all who has control. AS400 or the database(SQL). I don't know of a DTS package in AS400. So start there, next, you might want to look at the parameters manually vs job. Are there any environmental issues to look at. Such as: numbers of records to commit, indexes, maintenance and backups, buffers size, Look it up on books online SQL Server.|||AS400?

You mean DB2, no?

Have the DB2 dba set up an unload then copy the data to your box...

Then bcp the data in

I know that doesn't address your problem...

What's the execution step in the job look like?

One difference in your methods is the context of which ID is being used.

When you execute it from EM, it's under the context of your id, AND your location.

From the job, it's under the context of the agent AND the server drive mappings..

I'm guessing it's a network issue...

Did the job ever finish?|||Thnx alll 4 replying,

But i am afriad that that is not the problem...

I think i have solved it though...

The Client Access has an option to translate data by a DLL.
it was using one...dont know its name.

removing this dll solved the problem and the job is executing fine now.

thank you all for helping

Gil