Collation Conflicts SQL 2005

Hi there,
We have recently upgraded to SQL 2005 standard from SQL 2000.
I have begun a new web project in Visual Studio 2005.
I have added a SQL data source to my project which contains a VIEW.
I am passing a single parameter to the DS to populate a gridview.
I get the following error when I try to:
A. Test the Datasource.
B. Load the page that contains a gridview that uses the DS.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
I also get this error when creating stored procedures with an equal
operation.
Any steer in the right direction to resolve this issue would be greatly
appreciated.
Thanks in advance!
Hi
Have you checked the system collation and the column collations? At a guess
you upgraded/restored a backup with the SQL collation and installed SQL 2005
with the windows collation?
This may be the point where you decided to get rid of the SQL collation? In
which case I think the procedure is that same as SQL 2000, you will need to
alter the collation for the database alter the collation for each column that
uses it. These links should help http://tinyurl.com/429qa
http://tinyurl.com/91sg
John
"Codey" wrote:

> Hi there,
> We have recently upgraded to SQL 2005 standard from SQL 2000.
> I have begun a new web project in Visual Studio 2005.
> I have added a SQL data source to my project which contains a VIEW.
> I am passing a single parameter to the DS to populate a gridview.
> I get the following error when I try to:
> A. Test the Datasource.
> B. Load the page that contains a gridview that uses the DS.
> Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
> "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
>
> I also get this error when creating stored procedures with an equal
> operation.
> Any steer in the right direction to resolve this issue would be greatly
> appreciated.
> Thanks in advance!
>
>
|||Hi John,
Worked an absolute treat! - Thanks a million!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C315F5A7-2EFF-4C59-B5A9-1A8721534E19@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you checked the system collation and the column collations? At a
> guess
> you upgraded/restored a backup with the SQL collation and installed SQL
> 2005
> with the windows collation?
> This may be the point where you decided to get rid of the SQL collation?
> In
> which case I think the procedure is that same as SQL 2000, you will need
> to
> alter the collation for the database alter the collation for each column
> that
> uses it. These links should help http://tinyurl.com/429qa
> http://tinyurl.com/91sg
> John
> "Codey" wrote:

Collation Conflicts SQL 2005

Hi there,
We have recently upgraded to SQL 2005 standard from SQL 2000.
I have begun a new web project in Visual Studio 2005.
I have added a SQL data source to my project which contains a VIEW.
I am passing a single parameter to the DS to populate a gridview.
I get the following error when I try to:
A. Test the Datasource.
B. Load the page that contains a gridview that uses the DS.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
I also get this error when creating stored procedures with an equal
operation.
Any steer in the right direction to resolve this issue would be greatly
appreciated.
Thanks in advance!Hi
Have you checked the system collation and the column collations? At a guess
you upgraded/restored a backup with the SQL collation and installed SQL 2005
with the windows collation?
This may be the point where you decided to get rid of the SQL collation? In
which case I think the procedure is that same as SQL 2000, you will need to
alter the collation for the database alter the collation for each column tha
t
uses it. These links should help http://tinyurl.com/429qa
http://tinyurl.com/91sg
John
"Codey" wrote:

> Hi there,
> We have recently upgraded to SQL 2005 standard from SQL 2000.
> I have begun a new web project in Visual Studio 2005.
> I have added a SQL data source to my project which contains a VIEW.
> I am passing a single parameter to the DS to populate a gridview.
> I get the following error when I try to:
> A. Test the Datasource.
> B. Load the page that contains a gridview that uses the DS.
> Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
> "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
>
> I also get this error when creating stored procedures with an equal
> operation.
> Any steer in the right direction to resolve this issue would be greatly
> appreciated.
> Thanks in advance!
>
>|||Hi John,
Worked an absolute treat! - Thanks a million!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C315F5A7-2EFF-4C59-B5A9-1A8721534E19@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you checked the system collation and the column collations? At a
> guess
> you upgraded/restored a backup with the SQL collation and installed SQL
> 2005
> with the windows collation?
> This may be the point where you decided to get rid of the SQL collation?
> In
> which case I think the procedure is that same as SQL 2000, you will need
> to
> alter the collation for the database alter the collation for each column
> that
> uses it. These links should help http://tinyurl.com/429qa
> http://tinyurl.com/91sg
> John
> "Codey" wrote:
>

Collation Conflicts SQL 2005

Hi there,
We have recently upgraded to SQL 2005 standard from SQL 2000.
I have begun a new web project in Visual Studio 2005.
I have added a SQL data source to my project which contains a VIEW.
I am passing a single parameter to the DS to populate a gridview.
I get the following error when I try to:
A. Test the Datasource.
B. Load the page that contains a gridview that uses the DS.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
I also get this error when creating stored procedures with an equal
operation.
Any steer in the right direction to resolve this issue would be greatly
appreciated.
Thanks in advance!Hi
Have you checked the system collation and the column collations? At a guess
you upgraded/restored a backup with the SQL collation and installed SQL 2005
with the windows collation?
This may be the point where you decided to get rid of the SQL collation? In
which case I think the procedure is that same as SQL 2000, you will need to
alter the collation for the database alter the collation for each column that
uses it. These links should help http://tinyurl.com/429qa
http://tinyurl.com/91sg
John
"Codey" wrote:
> Hi there,
> We have recently upgraded to SQL 2005 standard from SQL 2000.
> I have begun a new web project in Visual Studio 2005.
> I have added a SQL data source to my project which contains a VIEW.
> I am passing a single parameter to the DS to populate a gridview.
> I get the following error when I try to:
> A. Test the Datasource.
> B. Load the page that contains a gridview that uses the DS.
> Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
> "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
>
> I also get this error when creating stored procedures with an equal
> operation.
> Any steer in the right direction to resolve this issue would be greatly
> appreciated.
> Thanks in advance!
>
>|||Hi John,
Worked an absolute treat! - Thanks a million!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C315F5A7-2EFF-4C59-B5A9-1A8721534E19@.microsoft.com...
> Hi
> Have you checked the system collation and the column collations? At a
> guess
> you upgraded/restored a backup with the SQL collation and installed SQL
> 2005
> with the windows collation?
> This may be the point where you decided to get rid of the SQL collation?
> In
> which case I think the procedure is that same as SQL 2000, you will need
> to
> alter the collation for the database alter the collation for each column
> that
> uses it. These links should help http://tinyurl.com/429qa
> http://tinyurl.com/91sg
> John
> "Codey" wrote:
>> Hi there,
>> We have recently upgraded to SQL 2005 standard from SQL 2000.
>> I have begun a new web project in Visual Studio 2005.
>> I have added a SQL data source to my project which contains a VIEW.
>> I am passing a single parameter to the DS to populate a gridview.
>> I get the following error when I try to:
>> A. Test the Datasource.
>> B. Load the page that contains a gridview that uses the DS.
>> Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
>> "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
>>
>> I also get this error when creating stored procedures with an equal
>> operation.
>> Any steer in the right direction to resolve this issue would be greatly
>> appreciated.
>> Thanks in advance!
>>sqlsql

Collates - CP437 - ADO - Visual Basic

Hello, I am workink with a Database that has the Collate SQL_Latin1_General_CP437_CI_AS (Instance and Database-SQL2000), this convert the Database in a OEM Server.
I Can save Characters From 0 to 256 ASCII code CP 437 very well on the database (I import this Data with an DTS) but when i retrive the information with Visual Basic 6.0 with ADO some character > 127 are changed.
Did you know how I can fix It ?
I know that the problem is that Windows is ANSI and my SQL Server OEM, but .. a dontt know how yo fix it.
Thanks I sorry because my poor english. :(

Walter
ArgentinaThe easy answer is to do the transfer using Unicode (16 bit characters). This allows any character that Windows recognizes to pass through without problem.

If you've already got the file that was produced from the table using the 437 collation and want to import it into a server with a different collation, then things get a bit tricky, but they can still be done.

-PatP|||The problem is that I must work with the Hexadecimal code of the character, because I need to send this code (Hexa of ASCII) to a Printer (Special Printer).
All the program is around this printer and I need manipulate ASCII codes.
Thanks you :D

Walter|||Take the data from the table using the 437 collation in a Unicode (16 bit) text file. This format allows the easy transportation of any character that Windows supports, regardless of the character's collation or character set.

The Unicode format will preserve the exact character, although sometimes moving a character from one 8 bit character set to another will cause the hex value of the character to change even though it preserves the glyph (the printed form) of the character exactly. You need to decide if it is more important to preserve the hex value or the glyph for your purposes.

-PatP|||You can replace DTS transformation task with Win32 task and call a batch that contains BCP. In the BCP call you can include -C <code_page>.

And Pat, it's not collation, it's a code page, and with BCP there are no "tricks" ;)|||A code page is definitely one way to implement part of a collation. I see a collation as a lot more complex and flexible than a code page.

I find it interesting that you suggested one of what I consider the tricks for using BCP (specifying a code page) and noted that you feel that BCP has no tricks in the same posting. I have a hard time understanding that apparent contradiction.

-PatP|||How can it be a contradiction if this is part of the syntax?

I see a collation as a lot more complex and flexible than a code page.One has nothing to do with the other, so do tell, - how do you see them? In blue?|||Gracias a Todos, pude resolver el problema.
En el string de conexion con el servidor SQL Server 2000 hay un parametro que se puede indicar para que al momento de recuperar los caracteres, el servidor no los modifique. Este parametro se llama Auto Translate.
Al poner Auto Translate = No los valores Hexadecimales de los caracteres que se guardan en la base permanecen intactos. Probe de Generar una Tabla con 2 columnas, la primera tiene un valor entero de 0 a 255 y la segunda es un caracter en el cual puse un codigo ASCII de 0 a 255, al tomarlo desde Visual Basic muestro el valor entero de 0 a 255, el caracter y luego con una funcion de visual muestro el valor Hexadecimal del caracter y vi muy felizmente que en los 255 casos permanecia igual.

>You can replace DTS transformation task with Win32 task and call a batch that contains BCP. In the BCP call you can include -C <code_page>.

Con respecto al DST, yo no tengo problemas con el DTS ya que al importar un archivo de texto, el mismo se importa exactamente con los cdigos exadecimales que este tiene.

>You need to decide if it is more important to preserve the hex value or the glyph for your purposes.

Para mi es mucho mas importante preservar el codigo Hexadecimal porque este codigo es un caracter de control para la impresora, independientemente de como se vea visualmente en pantalla, la impresora interpreta el codigo Hexadecimal.

Now, I will try to translate all the text but.. its very hard.
Thanks to all of you, I can fix the problem.
There is a parameter In the String of conexion with the SQL Server 2000 that indicate that don`t change the Hexadecimal code when you retrieve the characters from the server. This parameters is call "Auto Translate"

I don`t have problem with the DTS. The DTS imports Ok the characters from the ASCII file.

For me, it`s more important the Hex value that the glymp value because the printer interpretes the Hex value, not the Glyp.

Sorry because my poor english, I cant translate all exactly but i think that the idea is the same.
Thanks !!!

Walter|||Based on how you've described your needs, my first choice would be to use BCP, using the -c trick that rdjabarov suggested earlier for both export and import. The -c parameter allows you to control exactly which Windows code page is used for the file, and as long as the code page/collation mapping operation supports the charcters you need going both ways, you should be fine.

My second choice would be to extract the data using a view, converting the column to VARBINARY to preserve the hex values exactly. This usually means that you need to import the output of that view into a scratch table, then convert the hexadecimal value back to a character value inside of SQL Server.

If you can extract the data directly from one server to another, using a linked server, that would be my next suggestion.

-PatP

Code Snippets

I am looking for a sample code to write a tool or a 3rd party tool that will enable me to get the same functionality as visual studio 2005 code snippets in SSMS.

We do not expose the necessary interfaces to have 3rd parties plug-in their components into SQL Server Management Studio in SQL Server 2005. We are looking at this for a future release. We do have a Template Explorer, which may be of use in the meantime.

Using SQL Server Management Studio Templates
How to: Use Templates in SQL Server Management Studio

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Maybe you are looking for something like this (with sourcecode)

http://www.codeproject.com/useritems/enisey.asp

|||

Paul I was able to use the templates, its just that I was hoping for similar style of code snippets that Visual Studio has, where I could right click and have some of common tsql scripts available while using a query window. But for now the templates look better than nothing. I am not sure where to put in a suggestions, but that would be my first to have code snippets like visual studio 2005 uses in ssms.

Jurgen thanks for sending the like, but I am looking for more custom snippet storage not neccessarily code generation tools. But Thank you for the suggestion.

Thanks Troutbum!

|||

We have a customer portal where you can file suggestions. It's called Microsoft Connect. You can submit your suggestion here: http://connect.microsoft.com/SQLServer/.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Coalesce breaking SQL statement in SSRS?

I've run into what may an odd bug. I'm using SQL Server 2005, and
Visual Studio 2005. I was provided with a SQL statement (that is, I
did not originally write the SQL) for use in creating a simple
manifest report. Names are changed for security, but it is like so:
SELECT DISTINCT
A.NAME [name],
B.NAME [item_type],
CASE B.NAME,
WHEN N'option1' THEN COALESCE( C.name, N'')
WHEN N'option2' THEN COALESCE( D.name, N'')
WHEN N'option3' THEN COALESCE( E.name, N'')
WHEN N'option4' THEN COALESCE( F.name, N'')
WHEN N'option5' THEN COALESCE( G.name, N'')
...
FROM TABLE_A A
INNER JOIN TABLE_B B with (nolock) ON B.id = A.id
INNER JOIN TABLE_C C with (nolock) ON B.id = C.id
LEFT OUTER JOIN TABLE_D with (nolock) ON B.guid = D.id
LEFT OUTER JOIN TABLE_E with (nolock) ON B.guid = E.id
LEFT OUTER JOIN TABLE_F with (nolock) ON B.guid = F.id
LEFT OUTER JOIN TABLE_G with (nolock) ON B.guid = G.id
...
and onward, for 27 tables. There is no WHERE clause.
The quest runs perfectly fine in the SQL Server Management studio,
returning around 4000 rows in under one second. However, when I try to
run the same statement in an .RDL file, the query takes as long as
10-15 minutes, and locks up the Design Studio while it waits. If I
remove the COALESCE statements (i.e. I replace COALESCE( C.name, N'')
with ( C.name) ), the problem disappears entirely.
I have tried using ISNULL as well, and the problem remains. I have
tried creating an entirely new solution, new .RDL, new .RDS, with no
luck. sp_who2 indicates the the process is sleeping, no CPU usage or
DiskIO.
Has anyone seen this before, or have an idea?On Oct 1, 10:05 am, avat...@.gmail.com wrote:
> I've run into what may an odd bug. I'm using SQL Server 2005, and
> Visual Studio 2005. I was provided with a SQL statement (that is, I
> did not originally write the SQL) for use in creating a simple
> manifest report. Names are changed for security, but it is like so:
> SELECT DISTINCT
> A.NAME [name],
> B.NAME [item_type],
> CASE B.NAME,
> WHEN N'option1' THEN COALESCE( C.name, N'')
> WHEN N'option2' THEN COALESCE( D.name, N'')
> WHEN N'option3' THEN COALESCE( E.name, N'')
> WHEN N'option4' THEN COALESCE( F.name, N'')
> WHEN N'option5' THEN COALESCE( G.name, N'')
> ...
> FROM TABLE_A A
> INNER JOIN TABLE_B B with (nolock) ON B.id = A.id
> INNER JOIN TABLE_C C with (nolock) ON B.id = C.id
> LEFT OUTER JOIN TABLE_D with (nolock) ON B.guid = D.id
> LEFT OUTER JOIN TABLE_E with (nolock) ON B.guid = E.id
> LEFT OUTER JOIN TABLE_F with (nolock) ON B.guid = F.id
> LEFT OUTER JOIN TABLE_G with (nolock) ON B.guid = G.id
> ...
> and onward, for 27 tables. There is no WHERE clause.
> The quest runs perfectly fine in the SQL Server Management studio,
> returning around 4000 rows in under one second. However, when I try to
> run the same statement in an .RDL file, the query takes as long as
> 10-15 minutes, and locks up the Design Studio while it waits. If I
> remove the COALESCE statements (i.e. I replace COALESCE( C.name, N'')
> with ( C.name) ), the problem disappears entirely.
> I have tried using ISNULL as well, and the problem remains. I have
> tried creating an entirely new solution, new .RDL, new .RDS, with no
> luck. sp_who2 indicates the the process is sleeping, no CPU usage or
> DiskIO.
> Has anyone seen this before, or have an idea?
A couple of suggestions. Firstly, if you aren't already, you will want
to create a stored procedure out of the query above and access it in
the report. This should improve your performance a little. Secondly,
you might want to run the SQL Profiler while you are running the
report to see what kind of processing is going on and if there is an
exception/error hidden somewhere, etc. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant