Collation and views

Suppose you have your databases's collation as X but the value of collation
on the varchar fields of some of your tables as Y.
Suppose you create a view like this:
SELECT 'CostantString1', 'CostantString2', Field1, Field2 FROM
Table_A
UNIONA ALL
SELECT VarcharField1, VarcharField2, Field3, Field4 FROM
Table_B
You've got an error of incompatble collation on the first two columns of the
view. I think because on the constant string values the db assign the
collation X while the corresponding varchar fields of Table_B have
collation Y.
Is there any solution to this problem?
Thank you all
Andreayes, there is: use COLLATE clause in the select statement.
dean
"Andrea Temporin" <NOSPAM_temporin@.encopro.it> wrote in message
news:%232K2hKyGFHA.3108@.tk2msftngp13.phx.gbl...
> Suppose you have your databases's collation as X but the value of
collation
> on the varchar fields of some of your tables as Y.
> Suppose you create a view like this:
> SELECT 'CostantString1', 'CostantString2', Field1, Field2 FROM
> Table_A
> UNIONA ALL
> SELECT VarcharField1, VarcharField2, Field3, Field4 FROM
> Table_B
> You've got an error of incompatble collation on the first two columns of
the
> view. I think because on the constant string values the db assign the
> collation X while the corresponding varchar fields of Table_B have
> collation Y.
> Is there any solution to this problem?
> Thank you all
> Andrea
>

cold backup

Hi
And lots of thanks to Vishal P for the quick answer. The
sp_attach_db system stored procedure seems to be the
fastest way to go I suppose, but which is the best way to
get a cold backup?
Subject: Re: Disaster recovery
From: "Vishal Parkar" <a@.hotmail.com> Sent: 9/29/2003
6:58:43 AM
>do it. Is a new installation of SQL2000 Server needed on
>the test server, or can we do it by only restoring from
>the backup tapes of the existing SQL2000 Server
>environment?
If you want to restore the existing copy of database on
another server then you have to have SQL Server instance
installed. If one already exists no need to do
reinstallation of SQL instance.
If you possess copies of the data and log files. (ie cold
backup) you can copy these files to the destination server
and simply run sp_attach_db system stored procedure to
recreate the database.
See more help on sp_attach_db in BOL(it contains examples
as well.)
If you have taken backup of the database using "backup
database" command then you will have to restore it
using "restore database" command.
See more help on this in BOL.
- Vishal
>--Original Message--
>Hi
>We are about to install a test invironment identical to
>the existing SQL2000 Server environmnet, running on
>Win2000. The question is however, which is the best way
to
>do it. Is a new installation of SQL2000 Server needed on
>the test server, or can we do it by only restoring from
>the backup tapes of the existing SQL2000 Server
>environment? We backup everything on the existing server
+
>the database files and transaction logs.
>Your prompt answer will be highly appreciated!
>With many thanks,
>Suzy
>
>.
>
.You can detach a database using sp_detach_db. The detached
files remain and can be moved to another server or copied
to another backup device using os command.
If you are using sp_detach_db then other databases will
remain untouched which is not the case when you shutdown
the server completely just for the sake of taking backup
of single database.
- Vishal.