Comparing columns contents between 2 table...

any idea on the syntax for such a querie? all my attempts to compare an item # column in 2 seperate tables keep coming up an incorrect syntax...(I want to compare one column in one table with a column in another table).
Thanks in advance:rolleyes:I'm not sure on what you are asking?

SELECT a.*
FROM TBL a, TBL b
WHERE a.col = b.col

or

SELECT a.*
FROM TBL a
WHERE NOT EXISTS
(
SELECT *
FROM TBL b
WHERE a.col = b.col
)

??|||sorry I did not respond to your reply:

Thanks for the info - also the first one works for me...

SELECT *
FROM tableA, tableB
WHERE tableA.a_fieldname = tableB.b_fieldname

simple really :)

Again thanks... Now if I can only get it to ignore NULL and empty cells...|||SELECT *
FROM tableA, tableB
WHERE tableA.a_fieldname = tableB.b_fieldname
AND tableB.b_fieldname is not null
and LEN (tableB.b_fieldname) <> 0

--you can also put in tableA.a_fieldname as well if you so desire
-- tell me if that sorts out your problem|||thanks works fine :)

got to get it onto my production server and run now... Thanks,

compare tables accross databases?

So I've got two identically-structured tables in two databases. They each contain member details for two seperate online forums. I want to select all the members whose email address occurs in both tables:

select * from icna.dbo.forum_users
where exists
(select 1 from his.dbo.forum_users
where email = icna.dbo.forum_users.email)

I get an error: "Cannot resolve collation conflict for equal to operation."

I have made sure that neither table contains duplicate email addresses - the following returns no rows when run against either table:

select email from forum_users
group by email
having count(*)>1

So what is causing the collation conflict? What IS a collation conflict? Can I even compare stuff between databases?

Thanks :)Try specifying the collation name in the query. I beleive it is COLLATE followed by the name of the collation. For example :

select t.a, t2.b from server1.db.tbl t
inner join server2.db.tbl t2 on
(t.lastname = t2.lastname COLLATE LATIN_BIN_GENERAL)

I'm not sure that this is the exact syntax but it should be in BOL.

Might help...............|||Sorry, I'm a little lost. I read about collate in BOL but that left me even more mystified :) I don't want to select with an inner join. Do I? All I want is all the rows from icna.forum_users where the email crops up anywhere in his.forum_users?|||Generally, you can cast collation in a query with COLLATE <collation name>. Example:

select table1.columnA , table2.columnB
from some_table table1 another_table table2
where table1.columnA = table2.columnB
COLLATE Finnish_Swedish_CI_AS

This will cast columnB to the specified collation.|||Crikey.

I've no idea what I just did but I ran this:

select table1.email AS em1 , table2.email AS em2
from icna.dbo.forum_users table1, his.dbo.forum_users table2
where table1.email = table2.email
COLLATE Finnish_Swedish_CI_AS

and got back about 80 rows of data :) I assume that these are all the people whose email occurs in both tables - could anyone explain what, exactly, "Finnish_Swedish_CI_AS" is?|||THe collation Finnish_Swedish_CI_AS was just an example of a collation.

This very collation is the Swedish/Finnish alphabet without case sensitivity but WITH accent sensitivity. Why AS instead of AI? Because our alphabet includes letters A-Z plus , and but not W. W is just regarded as a variant of V. So, I need AS to get V and W to be regarded as different characters.

Compare feilds in seperate databases on seperate servers

Hi,
I want to run an update script where a field in a table in a database on a
server is equal to another field in a table in a database on a seperate
server. Here are the details:
Server 1:
Server name - Server1\Logi
Database - Ascent
Table - _customers
Field - email
Server 2:
Server name - Server2\Web
Database - ProductCart
Table - customers
Field - email
I want to update a field called 'flag' to equal 1 where the email addresses
are equal.
Can anyone help?To do this right we'd have to see more DDL - particulary interested in keys.
ML
http://milambda.blogspot.com/|||Hi
assuming you are on Server1\Logi, you can do something like
sp_addlinkedserver Web, @.srvproduct='', @.provider='SQLNCLI',
@.datasrc='Server2\WEB'
update _customers set flag = 1
from _customers a inner join Web.ProductCart.dbo.customers b on a.email =
b.email
exec sp_dropserver Web
Note that you should take care about proper credentials when connecting to
the linked server - read BOL about sp_addlinkedserver sproc. It is possible
also that it might be better to join tables on other column[s], but you did
not give any info on this.
HTH
Peter|||Hi Peter,
Thanks for the reply. I ran your script but got the following error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
Any ideas what this means?
Darren
"Rogas69" <rogas69@.no_spamers.o2.ie> wrote in message
news:OAH9afjAGHA.1460@.TK2MSFTNGP14.phx.gbl...
> Hi
> assuming you are on Server1\Logi, you can do something like
> sp_addlinkedserver Web, @.srvproduct='', @.provider='SQLNCLI',
> @.datasrc='Server2\WEB'
> update _customers set flag = 1
> from _customers a inner join Web.ProductCart.dbo.customers b on a.email =
> b.email
> exec sp_dropserver Web
> Note that you should take care about proper credentials when connecting to
> the linked server - read BOL about sp_addlinkedserver sproc. It is
> possible also that it might be better to join tables on other column[s],
> but you did not give any info on this.
> HTH
> Peter
>|||You have to make sure that they are using the same collation to join
them, sample below:
Select * FROM
sometable localtable
Inner join
SomeotherServer.Database.Owner.SomeTable linkedtable
WHERE linkedserv.Somecolumn COLLATE SQL_Latin1_General_CP1_CI_AI =3D
localtable.somecolumn COLLATE SQL_Latin1_General_CP1_CI_AI
Normally you don=B4t need to specify that on both sides if you just
specify the collation on the side that is different to this on your
local server and vice cersa.
HTH, jens Suessmeyer.|||This means that the two databases use different collations. Look up
collations in Books Online, there you'll also find the COLLATE keyword, whic
h
you can use to solve the problem.
Something like that:
update _customers set flag = 1
from _customers a
inner join Web.ProductCart.dbo.customers b
on a.email = b.email collate <collation
name>
The collation name is displayed in database properties in Enterprise manager
.
ML
http://milambda.blogspot.com/|||Thanks guys, i'll go check it out.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:70933017-66F0-4438-B8EE-E5824B77E3C9@.microsoft.com...
> This means that the two databases use different collations. Look up
> collations in Books Online, there you'll also find the COLLATE keyword,
> which
> you can use to solve the problem.
> Something like that:
> update _customers set flag = 1
> from _customers a
> inner join Web.ProductCart.dbo.customers b
> on a.email = b.email collate <collation
> name>
> The collation name is displayed in database properties in Enterprise
> manager.
>
> ML
> --
> http://milambda.blogspot.com/

Command line tools missing

I have read about the dtexec utility, but I can not find this on my installation. Is it a seperate install or do I need to reinstall?

Bob

How I found it was by clicking on Start-->Run then entering in dtexecui.exe|||

Bob Everland wrote:

I have read about the dtexec utility, but I can not find this on my installation. Is it a seperate install or do I need to reinstall?

Bob

You'll need to install SQL Server Integration Services. Installing Workstation components is not enough.

-Jamie