collation issues

hello,
i have a sql script/batch that i run against a db every day on my
workstation w/o a problem. recently i tried running it on my laptop
(replication of usual db)
i get an error on one select statement. the statement is a join b/t two
tables, on a field of type varchar(256) . i've tried both like and = as the
operator. one of the fields may actually be of type char(16) -- regardless,
this join always works on my desktop.
on my laptop, the text of the error message is:
Cannot resolve collation conflict for equal to operation
(or when i use the like operator)
Cannot resolve collation conflict for like operation
guessing this has to do w/ some collation setting for my sql server instance
on my laptop, but don't know. also, the sql server on my laptop is
development edition, while on my desktop it's enterprise edition -- don't
know if that matters
thanks for any help
matthewIt sounds as if the collation on the two columns is different. Use QA to scr
ipt
the Create Table statement to the clipboard and paste it into a message. The
re
is a way to coerce one collation into another if you know the collations on
the
columns.
Thomas
"matthew c. harad" <matthewcharad@.discussions.microsoft.com> wrote in messag
e
news:51C9A1FA-0DD3-44C8-AD49-309B5637CC24@.microsoft.com...
> hello,
> i have a sql script/batch that i run against a db every day on my
> workstation w/o a problem. recently i tried running it on my laptop
> (replication of usual db)
> i get an error on one select statement. the statement is a join b/t two
> tables, on a field of type varchar(256) . i've tried both like and = as t
he
> operator. one of the fields may actually be of type char(16) -- regardles
s,
> this join always works on my desktop.
> on my laptop, the text of the error message is:
> Cannot resolve collation conflict for equal to operation
> (or when i use the like operator)
> Cannot resolve collation conflict for like operation
> guessing this has to do w/ some collation setting for my sql server instan
ce
> on my laptop, but don't know. also, the sql server on my laptop is
> development edition, while on my desktop it's enterprise edition -- don't
> know if that matters
> thanks for any help
> matthew|||Check the collation_name of both columns from information_schema.columns and
use COLLATE to force the collations to be the same.
Example:
use northwind
go
create table t1 (
c1 char(10) collate SQL_Latin1_General_CP1_CI_AS
)
go
create table t2 (
c1 char(10) collate SQL_Latin1_General_CP1_CS_AS
)
go
insert into t1 values('microsoft')
insert into t2 values('Microsoft')
go
-- will give an error
select
*
from
t1 inner join t2
on t1.c1 = t2.c1
go
select
*
from
t1 inner join t2
on t1.c1 = t2.c1 collate SQL_Latin1_General_CP1_CI_AS
go
drop table t1, t2
go
AMB
"matthew c. harad" wrote:

> hello,
> i have a sql script/batch that i run against a db every day on my
> workstation w/o a problem. recently i tried running it on my laptop
> (replication of usual db)
> i get an error on one select statement. the statement is a join b/t two
> tables, on a field of type varchar(256) . i've tried both like and = as t
he
> operator. one of the fields may actually be of type char(16) -- regardles
s,
> this join always works on my desktop.
> on my laptop, the text of the error message is:
> Cannot resolve collation conflict for equal to operation
> (or when i use the like operator)
> Cannot resolve collation conflict for like operation
> guessing this has to do w/ some collation setting for my sql server instan
ce
> on my laptop, but don't know. also, the sql server on my laptop is
> development edition, while on my desktop it's enterprise edition -- don't
> know if that matters
> thanks for any help
> matthew

Collation issue

Dear fellows,
Sometimes when I obtain a script for tables and then I copy them to another
Sql Server, collation at field level is lost. Why?
Thank you very much,If no specifiy coallation is named on the column, it wont be scripted.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Enric" <Enric@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6322A9EB-4603-43C6-BC2C-09DBB81F45D3@.microsoft.com...
> Dear fellows,
> Sometimes when I obtain a script for tables and then I copy them to
> another
> Sql Server, collation at field level is lost. Why?
> Thank you very much,

collation conflict

I have a strange problem. When I run a script which drops and rebuilds an entire
database on one machine, it runs fine. When I run it on another machine,
four procedures fail to create with this error message
Cannot resolve collation conflich for equal to operation.
Further investigation reveals that these 4 procedures (out of 30 odd) call a function
in the SQL inside the procedure. That function also is in the same script and
it gets created successfully.
The script is self containing and does not have any collate statement. So, all collates
is the database default which is Latin1_General_CI_AS.
what can be the cause?
Check if the two servers has different collations in the master database. If they do, watch out for
temp tables (search BOL for database_default). And of course other references outside your database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||The SQL Servers have a different collation, which means that tempdb uses a
different collation. Character columns in temporary tables and table
variables are created with the collation of tempdb, not with the collation
of the database that the user is connected to when the temporary tables and
table variables are created. When you then compare or join these columns in
the temporary tables or table variables to permanent tables, you get a
collation conflict. You can work around this by either specifying all the
character columns in temporary tables and table variables with either an
explicit collation or with COLLATE DATABASE_DEFAULT.
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an
>entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
> a function
> in the SQL inside the procedure. That function also is in the same script
> and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
> all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||Check default collation for servers and databases.
AMB
"Data Cruncher" wrote:

> I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||Thanks all. It was indeed the collate on tempdbs.

collation conflict

I have a strange problem. When I run a script which drops and rebuilds an entire
database on one machine, it runs fine. When I run it on another machine,
four procedures fail to create with this error message
Cannot resolve collation conflich for equal to operation.
Further investigation reveals that these 4 procedures (out of 30 odd) call a function
in the SQL inside the procedure. That function also is in the same script and
it gets created successfully.
The script is self containing and does not have any collate statement. So, all collates
is the database default which is Latin1_General_CI_AS.
what can be the cause?Check if the two servers has different collations in the master database. If they do, watch out for
temp tables (search BOL for database_default). And of course other references outside your database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||The SQL Servers have a different collation, which means that tempdb uses a
different collation. Character columns in temporary tables and table
variables are created with the collation of tempdb, not with the collation
of the database that the user is connected to when the temporary tables and
table variables are created. When you then compare or join these columns in
the temporary tables or table variables to permanent tables, you get a
collation conflict. You can work around this by either specifying all the
character columns in temporary tables and table variables with either an
explicit collation or with COLLATE DATABASE_DEFAULT.
--
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an
>entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
> a function
> in the SQL inside the procedure. That function also is in the same script
> and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
> all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||Check default collation for servers and databases.
AMB
"Data Cruncher" wrote:
> I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||Thanks all. It was indeed the collate on tempdbs.

collation conflict

I have a strange problem. When I run a script which drops and rebuilds an en
tire
database on one machine, it runs fine. When I run it on another machine,
four procedures fail to create with this error message
Cannot resolve collation conflich for equal to operation.
Further investigation reveals that these 4 procedures (out of 30 odd) call a
function
in the SQL inside the procedure. That function also is in the same script an
d
it gets created successfully.
The script is self containing and does not have any collate statement. So, a
ll collates
is the database default which is Latin1_General_CI_AS.
what can be the cause?Check if the two servers has different collations in the master database. If
they do, watch out for
temp tables (search BOL for database_default). And of course other reference
s outside your database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message news:3es0utF4lp0bU1@.individual.ne
t...
>I have a strange problem. When I run a script which drops and rebuilds an e
ntire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
a function
> in the SQL inside the procedure. That function also is in the same script
and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||The SQL Servers have a different collation, which means that tempdb uses a
different collation. Character columns in temporary tables and table
variables are created with the collation of tempdb, not with the collation
of the database that the user is connected to when the temporary tables and
table variables are created. When you then compare or join these columns in
the temporary tables or table variables to permanent tables, you get a
collation conflict. You can work around this by either specifying all the
character columns in temporary tables and table variables with either an
explicit collation or with COLLATE DATABASE_DEFAULT.
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an
>entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
> a function
> in the SQL inside the procedure. That function also is in the same script
> and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
> all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||Check default collation for servers and databases.
AMB
"Data Cruncher" wrote:

> I have a strange problem. When I run a script which drops and rebuilds an
entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
a function
> in the SQL inside the procedure. That function also is in the same script
and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>|||Thanks all. It was indeed the collate on tempdbs.

Code Sample for SSIS Script Component

Greetings,

I have been developing VBA apps in Access and Excel for sometime and am fairly proficient in VBA. Now we are moving all of our data to SQL Server 2005. I am in need of learning how to write code for the Script Component of a data flow task. And so I have a couple of questions.

First, are there any books you recommend for learning ? (I'm not even sure what I need to learn: .NET? ADO.NET?)

And as a follow-up, any good websites that provide good reference documentation?

And my second question is more specific to my current problem. If I had a bit of code to get me started, I'm sure I could scream all the way to the bottom of the hill.

Data source is coming from a sort task where the data is sorted by STATUS and then MOD_DATE and the AUDIT_ID. I need to read each row and compare it to the next row. If STATUS is the same, discard the second row. When STATUS is different, send the first row to the output (to be used by the next task in the data flow). Using the "different" row from step 3, go to step 2.

I know how to write if statements, case statements, for/next statements. I'm just not understanding how to read the rows in and then send them back out. I've been searching for some sample code but everything I find tends to be solving much bigger issues.

Any help you can provide would be much appreciated.

Rob

1.
Which kind of book do you need ? T-SQL or SQL-CLR integration ?
For T-SQL I suggest you to buy a good basic book from amazon (that ha a good rating) just to learn the basics and then the buy the following two monuments by Itzik Ben-gan (MS Press)
INSIDE SQL SERVER 2005: T-SQL QUERYING
INSIDE SQL SERVER 2005: T-SQL PROGRAMMING
They are really hard... I don't know anything better.
For the second I suggest PRO SQL SERVER 2005 ASSEMBLY by Dewson (Apress)

2.
http://www.sql-server-performance.com
http://www.sqlservercentral.com
Google...

3.
can you explain better ? are you trying to code it in T-SQL or are you trying with a CLR stored procedure ? In the second case to send back a row you may use Pipe.Send or Pipe.ExecuteAndSend methods.

__

? www.carlop.com × carlop-dev.blogspot.com

|||

carlop wrote:

3.
can you explain better ? are you trying to code it in T-SQL or are you trying with a CLR stored procedure ? In the second case to send back a row you may use Pipe.Send or Pipe.ExecuteAndSend methods.

carlop - thank you for the book suggestions. I'll check them out.

Here is a sample of data. I am after capturing each row when the STATUS changes (highlighted in yellow). A total of six rows: one each of Not Ready, Hold and Complete and three of Active. All of the other rows will be discarded.

I'm thinking this is best done with a CLR procedure, however, I'm open to suggestions.

Rob

AUDIT_ID OBJECTID MODIFIED_DATE STATUS 2753029 124248079 4/26/2007 11:30 Active 2753035 124248079 4/26/2007 11:30 Active 2753076 124248079 4/26/2007 11:33 Active 2754823 124248079 4/26/2007 14:31 Not Ready 2794102 124248079 4/27/2007 7:29 Not Ready 2817836 124248079 4/27/2007 17:06 Active 2817859 124248079 4/27/2007 17:09 Active 2818286 124248079 4/27/2007 18:00 Hold 2818473 124248079 4/27/2007 18:38 Active 2818483 124248079 4/27/2007 18:44 Active 2989403 124248079 5/1/2007 7:14 Active 3022521 124248079 5/1/2007 7:19 Active 3042121 124248079 5/1/2007 10:45 Complete 3082880 124248079 5/2/2007 7:07 Complete

Code for Scripts

I'm looking for code that will create a script of a stored procedure vs.
going through the gui. Can anyone help?
See the below link:
http://www.karaszi.com/sqlserver/inf...ate_script.asp
Thanks
Hari
SQL Server MVP
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:69FCF02E-E868-4A1C-AB28-36562C8B5571@.microsoft.com...
> I'm looking for code that will create a script of a stored procedure vs.
> going through the gui. Can anyone help?