Prev: Stored Procedure - Writeline Where
Next: Many to many table design question
From: Erland Sommarskog on 11 Mar 2010 17:17
m (miroslavsi(a)gmail.com) writes:
> Now I have problem.
>
> I have database A and database B
>
> I made full backup of db A (example function "test" works properly >
> Insensitive collation)
> And restore that backup in db B.
>
> In database B example "test" function doesn't work properly!
>
> I thought if I make full backup of db A and restore it in db B, db B must
> have exact collation like db A?!
>
> I suppose there isn't easy way to covert database collation?

Are the databases on the same server or on different ones? If the
servers are different, they may have different server collation.

When you say "restore that backup in db B", this in accurate. More correctly
is to say "restore that back *as* db B". Because, when you restore a backup
into an existing database, everything in it is flattened.

If you have further questions, please describe more closely what
"doesn't work properly means".

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: m on 12 Mar 2010 02:33
Thanks for your answer.

Databases are on different servers, and they have different server
collation.

"Doesn't work properly" means that given example function
cannot be compiled in B database (has error, see posts before) while in
database A compiles correctly (there are no errors)

So we determined that is becouse different server collation.

Question is what to do to avoid compilation errors in db B, but without
changing sql function code, becouse code is encrypted.

Thanks in advance,

Regards

I restored backup of db A into db
"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D38ECDE4F9F5Yazorman(a)127.0.0.1...
>m (miroslavsi(a)gmail.com) writes:
>> Now I have problem.
>>
>> I have database A and database B
>>
>> I made full backup of db A (example function "test" works properly >
>> Insensitive collation)
>> And restore that backup in db B.
>>
>> In database B example "test" function doesn't work properly!
>>
>> I thought if I make full backup of db A and restore it in db B, db B must
>> have exact collation like db A?!
>>
>> I suppose there isn't easy way to covert database collation?
>
> Are the databases on the same server or on different ones? If the
> servers are different, they may have different server collation.
>
> When you say "restore that backup in db B", this in accurate. More
> correctly
> is to say "restore that back *as* db B". Because, when you restore a
> backup
> into an existing database, everything in it is flattened.
>
> If you have further questions, please describe more closely what
> "doesn't work properly means".
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


From: Erland Sommarskog on 12 Mar 2010 02:59
m (miroslavsi(a)gmail.com) writes:
> Databases are on different servers, and they have different server
> collation.
>
> "Doesn't work properly" means that given example function
> cannot be compiled in B database (has error, see posts before) while in
> database A compiles correctly (there are no errors)
>
> So we determined that is becouse different server collation.
>
> Question is what to do to avoid compilation errors in db B, but without
> changing sql function code, becouse code is encrypted.

I'm afraid that you're stuck. That is, you either need to change the
code, or change the server collation. Or give up the restore entirely.

The recommendation I can give is to use a case-sensitive collation in
development, and also stick to lowercase identifiers consistently to
avoid problems like these.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: m on 12 Mar 2010 03:04
Thank you very much.

Regards

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D395B6F5D3F3Yazorman(a)127.0.0.1...
>m (miroslavsi(a)gmail.com) writes:
>> Databases are on different servers, and they have different server
>> collation.
>>
>> "Doesn't work properly" means that given example function
>> cannot be compiled in B database (has error, see posts before) while in
>> database A compiles correctly (there are no errors)
>>
>> So we determined that is becouse different server collation.
>>
>> Question is what to do to avoid compilation errors in db B, but without
>> changing sql function code, becouse code is encrypted.
>
> I'm afraid that you're stuck. That is, you either need to change the
> code, or change the server collation. Or give up the restore entirely.
>
> The recommendation I can give is to use a case-sensitive collation in
> development, and also stick to lowercase identifiers consistently to
> avoid problems like these.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


From: --CELKO-- on 13 Mar 2010 08:14
>> The recommendation I can give is to use a case-sensitive collation in development, and also stick to lowercase identifiers consistently to avoid problems like these.<<

I will go one stepo further. Get a "Pretty Printer" that will format
the source code for you into a consistent format.I have
recommendations in my book SQL PROGRAMMING STYLE based on the research
we did starting in the 1970's on code readabilty for DoD. Good
formatting makes a 8-12% difference in maintenance time.
First  |  Prev  | 
Pages: 1 2
Prev: Stored Procedure - Writeline Where
Next: Many to many table design question