Monday, March 19, 2012
Combination of English and Chinese characters in Microsoft SQLServer 2000
We have Microsoft SQL Server 2000 with Service Pack 3a installed. In
one of our databases, we have a table REQ with one of the fields
COMMENT being defined as TEXT. Now, when we try to view the COMMENT
field, it shows us only English characters whereas the Chinese
characters are displayed as ?.
Is it possible to store both English and Chinese characters in the same
column of a table? If yes, then how?
Thanks in advance.
New user.
It still does not work. Is there any other alternative?
On Nov 19, 1:48 am, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sa...@.avtenta.si > wrote:
> --
> Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
|||newsqlserveruser (trupti.pujara@.gmail.com) writes:
> We have Microsoft SQL Server 2000 with Service Pack 3a installed. In
> one of our databases, we have a table REQ with one of the fields
> COMMENT being defined as TEXT. Now, when we try to view the COMMENT
> field, it shows us only English characters whereas the Chinese
> characters are displayed as ?.
> Is it possible to store both English and Chinese characters in the same
> column of a table? If yes, then how?
Yes, it's possible, but with the information you have given, it's difficult
to give exact advice.
First of all, which character set do you use for Chinese? Unicode? Big-5?
If you use Unicode, you should use ntext, if you use Big-5 or any other
non-Unicode character set, you should use text.
Next question is find out where things go wrong. Do something like:
SELECT substring(col, 1, 20), convert(binary(20), substring(col, 1, 20))
If there are question marks in the selected substring which should have
been Chinese characters, what do you see in their place in the binary
string? If you see 3F, the Chinese characters have been stored as ?, and
there is a problem on input. If you see the codes for the Chinese
characters, it's a display problem.
If it is an input problem, it would help to know who the characters enters
the database.
Disclaimer: I have no experience of working with Chinese on my own.
Erland Sommarskog, SQL Server MVP, esquel@.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
|||I ran the query as u suggested and the output for the binary string
shows as 3F for the Chinese characters. If that means there is a
problem in input, how do I go ahead from here?
On Nov 21, 9:17 pm, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> newsqlserveruser (trupti.puj...@.gmail.com) writes:
> to give exact advice.
> First of all, which character set do you use for Chinese? Unicode? Big-5?
> If you use Unicode, you should use ntext, if you use Big-5 or any other
> non-Unicode character set, you should use text.
> Next question is find out where things go wrong. Do something like:
> SELECT substring(col, 1, 20), convert(binary(20), substring(col, 1, 20))
> If there are question marks in the selected substring which should have
> been Chinese characters, what do you see in their place in the binary
> string? If you see 3F, the Chinese characters have been stored as ?, and
> there is a problem on input. If you see the codes for the Chinese
> characters, it's a display problem.
> If it is an input problem, it would help to know who the characters enters
> the database.
> Disclaimer: I have no experience of working with Chinese on my own.
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||newsqlserveruser (trupti.pujara@.gmail.com) writes:
> I ran the query as u suggested and the output for the binary string
> shows as 3F for the Chinese characters. If that means there is a
> problem in input, how do I go ahead from here?
To start with, please answer a few questions:
1) How does the data enter the database? From a Windows client? From a
web client? Which API do you use?
2) Which character set does the client use for the data?
3) What is the collation of the text column? (You can view this with
sp_help.)
Erland Sommarskog, SQL Server MVP, esquel@.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
|||Thank you very much for all your help. The problem has been solved.
After changing the field from text to ntext in the database, the client
still had some problems. Even though East Asian languages were
supported, the Language for non-Unicode programs was English. When it
was changed to Chinese and the PC was rebooted, everything is all fine
now.
On Nov 26, 1:15 am, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> newsqlserveruser (trupti.puj...@.gmail.com) writes:
> 1) How does the data enter the database? From a Windows client? From a
> web client? Which API do you use?
> 2) Which character set does the client use for the data?
> 3) What is the collation of the text column? (You can view this with
> sp_help.)
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Sunday, March 11, 2012
columns_updated compatibility between sql2000 and sql 2005
I am working on a trigger that could be installed on both sql2000 and
sql2005, so the code has to work on both systems.
The trigger uses COLUMNS_UPDATED() function to determine which fields were
updated, as BOL for sql 2005 indicate there is a slight difference
in this function parameters: if you work with sql2000 you can use
ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
calculations and then use the value with columns_updated, in case of sql2005
you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter version
does not work properly in sql 2000.
My question: Is it possible to write a single trigger that uses
columns_updated and works on both versions, if not how to distinguish
between 2 versions in a trigger,
e.g. if ver2000 set @.var = ....
else if ver2005 set @.var=...
Please let me know if the question is not clear I'll try to add more info.
Thank you
VadimHi
The ColumnId property is new in SQL 2005, therefore earlier versions would
return NULL so you could try something like:
ISNULL(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID'),ORDINAL_POSITION)
You could use the columnid and other information directly from syscolumns if
you aren't concerned about using system catalogues.
If you want to check SQL Server version look at
SELECT SERVERPROPERTY('ProductVersion')
other ways are listed at
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
John
"Vadim" wrote:
> Hi,
> I am working on a trigger that could be installed on both sql2000 and
> sql2005, so the code has to work on both systems.
> The trigger uses COLUMNS_UPDATED() function to determine which fields were
> updated, as BOL for sql 2005 indicate there is a slight difference
> in this function parameters: if you work with sql2000 you can use
> ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
> calculations and then use the value with columns_updated, in case of sql2005
> you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
> COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter version
> does not work properly in sql 2000.
> My question: Is it possible to write a single trigger that uses
> columns_updated and works on both versions, if not how to distinguish
> between 2 versions in a trigger,
> e.g. if ver2000 set @.var = ....
> else if ver2005 set @.var=...
> Please let me know if the question is not clear I'll try to add more info.
> Thank you
> Vadim
>
>|||John,
Thank you very much, that's exactly what I needed, it worked.
Vadim
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7F8A7747-8E97-432B-A722-A50801BDB805@.microsoft.com...
> Hi
> The ColumnId property is new in SQL 2005, therefore earlier versions would
> return NULL so you could try something like:
> ISNULL(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
> COLUMN_NAME, 'ColumnID'),ORDINAL_POSITION)
> You could use the columnid and other information directly from syscolumns
> if
> you aren't concerned about using system catalogues.
> If you want to check SQL Server version look at
> SELECT SERVERPROPERTY('ProductVersion')
> other ways are listed at
> http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html
>
> John
> "Vadim" wrote:
>> Hi,
>> I am working on a trigger that could be installed on both sql2000 and
>> sql2005, so the code has to work on both systems.
>> The trigger uses COLUMNS_UPDATED() function to determine which fields
>> were
>> updated, as BOL for sql 2005 indicate there is a slight difference
>> in this function parameters: if you work with sql2000 you can use
>> ORDINAL_POSITION of the field from INFORMATION_SCHEMA.COLUMNS, apply some
>> calculations and then use the value with columns_updated, in case of
>> sql2005
>> you have to use COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' +
>> TABLE_NAME),
>> COLUMN_NAME, 'ColumnID') from INFORMATION_SCHEMA.COLUMNS, the latter
>> version
>> does not work properly in sql 2000.
>> My question: Is it possible to write a single trigger that uses
>> columns_updated and works on both versions, if not how to distinguish
>> between 2 versions in a trigger,
>> e.g. if ver2000 set @.var = ....
>> else if ver2005 set @.var=...
>> Please let me know if the question is not clear I'll try to add more
>> info.
>> Thank you
>> Vadim
>>
Friday, February 24, 2012
Column Layout
on my development machine (Win XP Pro SP2).
I created a simple report and set the layout to be 2 columns across.
It print previews as two columns in VS designer, but when viewing through
the report web interface, it only displays as a single column.
However, if I export it to PDF, it then renders the 2 columns correctly.
Is there a way to fix this?
Also, does anyone know of a news server that has this group on it, so it can
be accessed via Outlook Express?
It is not available on news.microsoft.com
TIAThe new group is located at msnews.microsoft.com .
Have you tried the sample reports to see if they work correctly?
"AVG" <AVG@.discussions.microsoft.com> wrote in message
news:72ED551C-4D61-4A5D-88F0-3402BFFA969E@.microsoft.com...
>I am just getting started with reportingservices and have it installed
>(SP2)
> on my development machine (Win XP Pro SP2).
> I created a simple report and set the layout to be 2 columns across.
> It print previews as two columns in VS designer, but when viewing through
> the report web interface, it only displays as a single column.
> However, if I export it to PDF, it then renders the 2 columns correctly.
> Is there a way to fix this?
> Also, does anyone know of a news server that has this group on it, so it
> can
> be accessed via Outlook Express?
> It is not available on news.microsoft.com
> TIA
>|||Thanks for the quick reply Mark. Outlook Express is much quicker than the
web interface.
The only sample report that does not work is the Foodmart. Apparently MS
forgot to include the datasource for it.
Anyway, none of the samples use the multiple column layout. They are all
single column.
I am not referring to simply two columns of data like productid and name. I
am referring to the columns property of the layout tab of the report
properties.
--
Alphonse Giambrone
Email: a-giam at customdatasolutions dot us
"Mark" <mis@.ifcfabricsnospam.com> wrote in message
news:ONsEgGNTFHA.2828@.TK2MSFTNGP10.phx.gbl...
> The new group is located at msnews.microsoft.com .
> Have you tried the sample reports to see if they work correctly?
> "AVG" <AVG@.discussions.microsoft.com> wrote in message
> news:72ED551C-4D61-4A5D-88F0-3402BFFA969E@.microsoft.com...
>>I am just getting started with reportingservices and have it installed
>>(SP2)
>> on my development machine (Win XP Pro SP2).
>> I created a simple report and set the layout to be 2 columns across.
>> It print previews as two columns in VS designer, but when viewing through
>> the report web interface, it only displays as a single column.
>> However, if I export it to PDF, it then renders the 2 columns correctly.
>> Is there a way to fix this?
>> Also, does anyone know of a news server that has this group on it, so it
>> can
>> be accessed via Outlook Express?
>> It is not available on news.microsoft.com
>> TIA
>|||I think this must be by design. Mine does the same, previews in 1 column but
exports & prints in multiple columns.
Maybe MSFT can comment.
"AVG" <NOSPAMa-giam@.example.invalid> wrote in message
news:emfUAQNTFHA.3696@.TK2MSFTNGP15.phx.gbl...
> Thanks for the quick reply Mark. Outlook Express is much quicker than the
> web interface.
> The only sample report that does not work is the Foodmart. Apparently MS
> forgot to include the datasource for it.
> Anyway, none of the samples use the multiple column layout. They are all
> single column.
> I am not referring to simply two columns of data like productid and name.
> I am referring to the columns property of the layout tab of the report
> properties.
> --
> Alphonse Giambrone
> Email: a-giam at customdatasolutions dot us
>
> "Mark" <mis@.ifcfabricsnospam.com> wrote in message
> news:ONsEgGNTFHA.2828@.TK2MSFTNGP10.phx.gbl...
>> The new group is located at msnews.microsoft.com .
>> Have you tried the sample reports to see if they work correctly?
>> "AVG" <AVG@.discussions.microsoft.com> wrote in message
>> news:72ED551C-4D61-4A5D-88F0-3402BFFA969E@.microsoft.com...
>>I am just getting started with reportingservices and have it installed
>>(SP2)
>> on my development machine (Win XP Pro SP2).
>> I created a simple report and set the layout to be 2 columns across.
>> It print previews as two columns in VS designer, but when viewing
>> through
>> the report web interface, it only displays as a single column.
>> However, if I export it to PDF, it then renders the 2 columns correctly.
>> Is there a way to fix this?
>> Also, does anyone know of a news server that has this group on it, so it
>> can
>> be accessed via Outlook Express?
>> It is not available on news.microsoft.com
>> TIA
>>
>|||I wouldn't mind so much if it did print correctly, but for me it does not
(except through the VS designer).
The only way I get the two columns (via reportserver) is to export to pdf.
Ok, Microsoft, any answers?
--
Alphonse Giambrone
Email: a-giam at customdatasolutions dot us
"Mark" <mis@.ifcfabricsnospam.com> wrote in message
news:%23hLRKkNTFHA.3308@.TK2MSFTNGP14.phx.gbl...
>I think this must be by design. Mine does the same, previews in 1 column
>but exports & prints in multiple columns.
> Maybe MSFT can comment.
> "AVG" <NOSPAMa-giam@.example.invalid> wrote in message
> news:emfUAQNTFHA.3696@.TK2MSFTNGP15.phx.gbl...
>> Thanks for the quick reply Mark. Outlook Express is much quicker than the
>> web interface.
>> The only sample report that does not work is the Foodmart. Apparently MS
>> forgot to include the datasource for it.
>> Anyway, none of the samples use the multiple column layout. They are all
>> single column.
>> I am not referring to simply two columns of data like productid and name.
>> I am referring to the columns property of the layout tab of the report
>> properties.
>> --
>> Alphonse Giambrone
>> Email: a-giam at customdatasolutions dot us
>>
>> "Mark" <mis@.ifcfabricsnospam.com> wrote in message
>> news:ONsEgGNTFHA.2828@.TK2MSFTNGP10.phx.gbl...
>> The new group is located at msnews.microsoft.com .
>> Have you tried the sample reports to see if they work correctly?
>> "AVG" <AVG@.discussions.microsoft.com> wrote in message
>> news:72ED551C-4D61-4A5D-88F0-3402BFFA969E@.microsoft.com...
>>I am just getting started with reportingservices and have it installed
>>(SP2)
>> on my development machine (Win XP Pro SP2).
>> I created a simple report and set the layout to be 2 columns across.
>> It print previews as two columns in VS designer, but when viewing
>> through
>> the report web interface, it only displays as a single column.
>> However, if I export it to PDF, it then renders the 2 columns
>> correctly.
>> Is there a way to fix this?
>> Also, does anyone know of a news server that has this group on it, so
>> it can
>> be accessed via Outlook Express?
>> It is not available on news.microsoft.com
>> TIA
>>
>>
>|||Multi-column layout is not supported in the HTML rendering extension.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AVG" <AVG@.discussions.microsoft.com> wrote in message
news:72ED551C-4D61-4A5D-88F0-3402BFFA969E@.microsoft.com...
>I am just getting started with reportingservices and have it installed
>(SP2)
> on my development machine (Win XP Pro SP2).
> I created a simple report and set the layout to be 2 columns across.
> It print previews as two columns in VS designer, but when viewing through
> the report web interface, it only displays as a single column.
> However, if I export it to PDF, it then renders the 2 columns correctly.
> Is there a way to fix this?
> Also, does anyone know of a news server that has this group on it, so it
> can
> be accessed via Outlook Express?
> It is not available on news.microsoft.com
> TIA
>|||Thanks Brian,
I am sure MS has a reason for it.
Is there any documentation available on features that are not supported in
the various rendering extensions?
One could waste a lot of time trying to figure things like this out.
--
Alphonse Giambrone
Email: a-giam at customdatasolutions dot us
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:%23c4zeEUTFHA.1040@.TK2MSFTNGP10.phx.gbl...
> Multi-column layout is not supported in the HTML rendering extension.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "AVG" <AVG@.discussions.microsoft.com> wrote in message
> news:72ED551C-4D61-4A5D-88F0-3402BFFA969E@.microsoft.com...
>>I am just getting started with reportingservices and have it installed
>>(SP2)
>> on my development machine (Win XP Pro SP2).
>> I created a simple report and set the layout to be 2 columns across.
>> It print previews as two columns in VS designer, but when viewing through
>> the report web interface, it only displays as a single column.
>> However, if I export it to PDF, it then renders the 2 columns correctly.
>> Is there a way to fix this?
>> Also, does anyone know of a news server that has this group on it, so it
>> can
>> be accessed via Outlook Express?
>> It is not available on news.microsoft.com
>> TIA
>|||There is a section in the docs but it is unfortunately not very complete.
I'll pass this suggestion to our doc team.
The reason it is not supported is that there is no easy way to calculate the
page size like there is on physical pages. I suppose we could take the
definition page breaks and cut it in half but this would result in different
sizes for each page.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AVG" <NOSPAMa-giam@.example.invalid> wrote in message
news:%23OYpquXTFHA.3840@.tk2msftngp13.phx.gbl...
> Thanks Brian,
> I am sure MS has a reason for it.
> Is there any documentation available on features that are not supported in
> the various rendering extensions?
> One could waste a lot of time trying to figure things like this out.
> --
> Alphonse Giambrone
> Email: a-giam at customdatasolutions dot us
>
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:%23c4zeEUTFHA.1040@.TK2MSFTNGP10.phx.gbl...
>> Multi-column layout is not supported in the HTML rendering extension.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "AVG" <AVG@.discussions.microsoft.com> wrote in message
>> news:72ED551C-4D61-4A5D-88F0-3402BFFA969E@.microsoft.com...
>>I am just getting started with reportingservices and have it installed
>>(SP2)
>> on my development machine (Win XP Pro SP2).
>> I created a simple report and set the layout to be 2 columns across.
>> It print previews as two columns in VS designer, but when viewing
>> through
>> the report web interface, it only displays as a single column.
>> However, if I export it to PDF, it then renders the 2 columns correctly.
>> Is there a way to fix this?
>> Also, does anyone know of a news server that has this group on it, so it
>> can
>> be accessed via Outlook Express?
>> It is not available on news.microsoft.com
>> TIA
>>
>
Sunday, February 12, 2012
Collation+Greek
I am using an application which loads data from an SQL Database 2000 (no
service pack installed).
Some fields of the application use drop-down lists in order to enter a
value. The problem is that when I try to create the drop-down list and fill
it with values (in my case, words written in Greek and words written with
latin characters). The latin written words are sorted alphabetically but the
words written in Greek are not sorted.
Is this a problem of collation? I have installed SQL server with the
default settings (typical installation)
Thank you
It sounds like a collation problem. I've had the same problem but that
was few years ago on 6.5, and it was caused because of the use of wrong
page code. I didn't see this behavior in later versions, so I might be
wrong here, but my bet would be that the cause of the problem is using
wrong collation.
Adi
Collation+Greek
I am using an application which loads data from an SQL Database 2000 (no
service pack installed).
Some fields of the application use drop-down lists in order to enter a
value. The problem is that when I try to create the drop-down list and fill
it with values (in my case, words written in Greek and words written with
latin characters). The latin written words are sorted alphabetically but the
words written in Greek are not sorted.
Is this a problem of collation? I have installed SQL server with the
default settings (typical installation)
Thank youIt sounds like a collation problem. I've had the same problem but that
was few years ago on 6.5, and it was caused because of the use of wrong
page code. I didn't see this behavior in later versions, so I might be
wrong here, but my bet would be that the cause of the problem is using
wrong collation.
Adi
Collation troubles with SQLServer Express 2005
I installed SQL Server Express 2005 in my system, and created a simple database to store mailng addresses. The database has an email field and a name field. The data to populate the database came from an Excel file saved as a Unicode Text file containing the data as:
xxx@.somemail.com; To?o Peres yyy@.someothermail.com; Iván Cárdenas Note that this data has names with accented characters.
I then tried to import the data into the SQLServer Express with the following command:
bulk insert lista
from 'c:\temp\todas.csv'
with (
DATAFILETYPE ='widechar',
FIELDTERMINATOR =';'
)
I got the following error message:
Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.
And the data was imported as:
xxx@.somemail.com To±o Peres yyy@.someothermail.com Ivfn C?rdenas
I tried using the DATAFILETYPE ='native' and DATAFILETYPE ='widenative' options but these caused the following error:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
I want to avoid re-installing the server and the Express 2005 edition does not come with the tools needed to rebuild the master database anyway (see this link).
When I type the data with the Server Management Studio Express program, I can insert the accented characters correctly.
Does anyone out there know how to deal with this problem? How do I tell SQL Server Express 2005 to insert the text from the text file and keep the accented characters as they were initially?
I will welcome any help.
Ivan.
Hi Ivan,
Are you sure you saved the spreadsheet as Unicode Text? The .csv filetype suggests you didn't - unless you manually changed it.
I tried using the data above in Excel, saving as Unicode Text and then bulk inserting and it worked fine.
Thanks
Friday, February 10, 2012
Collation problem - urgent help
My server is installed as - Latin1_General_CS_AS, but one of the important
50GB databases migrated from another server has -
SQL_Latin1_General_CP1_CS_AS, and we are getting errors:
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CS_AS" and
"SQL_Latin1_General_CP1_CS_AS" in the equal to operation
WHat sthe best way to resolve these problems...would bcp in/out fix it?
Tnanks in advance.Nasir (nmajeed@.prosrm.com) writes:
Quote:
Originally Posted by
What is the best way to resolve these collation errors:
>
My server is installed as - Latin1_General_CS_AS, but one of the
important 50GB databases migrated from another server has -
SQL_Latin1_General_CP1_CS_AS, and we are getting errors:
>
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CS_AS" and
"SQL_Latin1_General_CP1_CS_AS" in the equal to operation
>
WHat sthe best way to resolve these problems...would bcp in/out fix it?
You would have to rebuild the database from scripts in such case.
The quickest and easiest fix may be to simply install a second instance
with SQL_Latin1_General_CP1_CS_AS as the server collation.
You can also run:
SELECT 'ALTER TABLE ' + o.name + ' ALTER COLUMN ' + c.name etc
and cut and paste the result into a query window. Unforunately, it is not
that easy, if columns are indexed, or referenced by foreign keys, so
indexes have to be dropped and reapplied, same goes for constraints.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Is there any easy way to export out all objects and data, so it can be
exported into any collation of SQL server.
Also, if I create multiple users in a databases, that they can have
independent schemas; can they be backed up separately and restores
separately to the same database.
Where did DTS go. It was nice to load or export different formats of data?
TIA
"Nasir" <nmajeed@.prosrm.comwrote in message
news:44e9f86a$0$1005$39cecf19@.news.twtelecom.net.. .
Quote:
Originally Posted by
What is the best way to resolve these collation errors:
>
My server is installed as - Latin1_General_CS_AS, but one of the
important 50GB databases migrated from another server has -
SQL_Latin1_General_CP1_CS_AS, and we are getting errors:
>
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CS_AS" and
"SQL_Latin1_General_CP1_CS_AS" in the equal to operation
>
>
>
WHat sthe best way to resolve these problems...would bcp in/out fix it?
>
>
>
Tnanks in advance.
>
>
Quote:
Originally Posted by
Is there any easy way to export out all objects and data, so it can be
exported into any collation of SQL server.
SELECT 'bcp yourdb.' + quotename(schema_name(schema_id)) + '.' +
quotename(name) + ' out "' + name + '.bcp" ' +
'-T -n -C RAW'
FROM sys.objects
WHERE type = 'U'
Copy and paste into BAT file. To copy back, you need to add the -E option
for tables with identity columns. That is left as an exercise for the
reader. :-)
I believe the -C RAW option should prevent character conversions, but
you have to play around with that.
Quote:
Originally Posted by
Also, if I create multiple users in a databases, that they can have
independent schemas; can they be backed up separately and restores
separately to the same database.
Particularly, in SQL 2005, users don't have to have a schema at all.
Or they can own several.
No, you cannot backup a schema on its own. But you can backup on filegroup
level, I believe.
Quote:
Originally Posted by
Where did DTS go. It was nice to load or export different formats of data?
DTS was succeeded by SQL Server Integration Services in SQL 2005. I
know nothing about neither.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx