Modify your query so that it converts your joins to
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J
>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=
[TB2].[ProductCode])
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])
|||Your [TB1].[Product] and [TB2].[ProductCode] columns have different
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_default
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])
Showing posts with label wrong. Show all posts
Showing posts with label wrong. Show all posts
Thursday, March 22, 2012
Collation Conflict
Modify your query so that it converts your joins to
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J
>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[/vbc
ol]
[TB2].[ProductCode])[vbcol=seagreen]
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx
.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])|||Your [TB1].[Product] and [TB2].[ProductCode] columns have di
fferent
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_def
ault
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx
.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J
>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[/vbc
ol]
[TB2].[ProductCode])[vbcol=seagreen]
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx
.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])|||Your [TB1].[Product] and [TB2].[ProductCode] columns have di
fferent
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_def
ault
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx
.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])
Saturday, February 25, 2012
CMDEXEC Can't Find File, Yet Runs In a "Run" Dialog
Good morning, I hope someone here has had this problem and can tell me what
I'm doing wrong.
I have created a CMDEXEC job to run a program. I know my path is correct
because it works in a Run dialog. Yet SQL Server Agent reports "Process
could not be created... the system cannot find the file specified" in the
error log.
The job step is currently set to the path and name of the exe. I.e.:
\\server\folder\app.exe
Agent reports "Success" for this step after just a second, but the
application runs for an hour or so when it's started manually. Even if the
application was failing and crashing out, which it does not do when run
manually, it should take it a few seconds. Is Agent starting the app in the
background, asynchronously?
Any suggestions appreciated!JonOfAllTrades wrote:
> Good morning, I hope someone here has had this problem and can tell me wha
t
> I'm doing wrong.
> I have created a CMDEXEC job to run a program. I know my path is correct
> because it works in a Run dialog. Yet SQL Server Agent reports "Process
> could not be created... the system cannot find the file specified" in the
> error log.
> The job step is currently set to the path and name of the exe. I.e.:
> \\server\folder\app.exe
> Agent reports "Success" for this step after just a second, but the
> application runs for an hour or so when it's started manually. Even if th
e
> application was failing and crashing out, which it does not do when run
> manually, it should take it a few seconds. Is Agent starting the app in t
he
> background, asynchronously?
> Any suggestions appreciated!
It's a permissions problem. SQL Agent is running as a user (or maybe
Local System) that doesn't have permission to access
\\server\folder\app.exe. Fix that, and all should be well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
I'm doing wrong.
I have created a CMDEXEC job to run a program. I know my path is correct
because it works in a Run dialog. Yet SQL Server Agent reports "Process
could not be created... the system cannot find the file specified" in the
error log.
The job step is currently set to the path and name of the exe. I.e.:
\\server\folder\app.exe
Agent reports "Success" for this step after just a second, but the
application runs for an hour or so when it's started manually. Even if the
application was failing and crashing out, which it does not do when run
manually, it should take it a few seconds. Is Agent starting the app in the
background, asynchronously?
Any suggestions appreciated!JonOfAllTrades wrote:
> Good morning, I hope someone here has had this problem and can tell me wha
t
> I'm doing wrong.
> I have created a CMDEXEC job to run a program. I know my path is correct
> because it works in a Run dialog. Yet SQL Server Agent reports "Process
> could not be created... the system cannot find the file specified" in the
> error log.
> The job step is currently set to the path and name of the exe. I.e.:
> \\server\folder\app.exe
> Agent reports "Success" for this step after just a second, but the
> application runs for an hour or so when it's started manually. Even if th
e
> application was failing and crashing out, which it does not do when run
> manually, it should take it a few seconds. Is Agent starting the app in t
he
> background, asynchronously?
> Any suggestions appreciated!
It's a permissions problem. SQL Agent is running as a user (or maybe
Local System) that doesn't have permission to access
\\server\folder\app.exe. Fix that, and all should be well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
CMDEXEC Can't Find File, Yet Runs In a "Run" Dialog
Good morning, I hope someone here has had this problem and can tell me what
I'm doing wrong.
I have created a CMDEXEC job to run a program. I know my path is correct
because it works in a Run dialog. Yet SQL Server Agent reports "Process
could not be created... the system cannot find the file specified" in the
error log.
The job step is currently set to the path and name of the exe. I.e.:
\\server\folder\app.exe
Agent reports "Success" for this step after just a second, but the
application runs for an hour or so when it's started manually. Even if the
application was failing and crashing out, which it does not do when run
manually, it should take it a few seconds. Is Agent starting the app in the
background, asynchronously?
Any suggestions appreciated!JonOfAllTrades wrote:
> Good morning, I hope someone here has had this problem and can tell me what
> I'm doing wrong.
> I have created a CMDEXEC job to run a program. I know my path is correct
> because it works in a Run dialog. Yet SQL Server Agent reports "Process
> could not be created... the system cannot find the file specified" in the
> error log.
> The job step is currently set to the path and name of the exe. I.e.:
> \\server\folder\app.exe
> Agent reports "Success" for this step after just a second, but the
> application runs for an hour or so when it's started manually. Even if the
> application was failing and crashing out, which it does not do when run
> manually, it should take it a few seconds. Is Agent starting the app in the
> background, asynchronously?
> Any suggestions appreciated!
It's a permissions problem. SQL Agent is running as a user (or maybe
Local System) that doesn't have permission to access
\\server\folder\app.exe. Fix that, and all should be well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
I'm doing wrong.
I have created a CMDEXEC job to run a program. I know my path is correct
because it works in a Run dialog. Yet SQL Server Agent reports "Process
could not be created... the system cannot find the file specified" in the
error log.
The job step is currently set to the path and name of the exe. I.e.:
\\server\folder\app.exe
Agent reports "Success" for this step after just a second, but the
application runs for an hour or so when it's started manually. Even if the
application was failing and crashing out, which it does not do when run
manually, it should take it a few seconds. Is Agent starting the app in the
background, asynchronously?
Any suggestions appreciated!JonOfAllTrades wrote:
> Good morning, I hope someone here has had this problem and can tell me what
> I'm doing wrong.
> I have created a CMDEXEC job to run a program. I know my path is correct
> because it works in a Run dialog. Yet SQL Server Agent reports "Process
> could not be created... the system cannot find the file specified" in the
> error log.
> The job step is currently set to the path and name of the exe. I.e.:
> \\server\folder\app.exe
> Agent reports "Success" for this step after just a second, but the
> application runs for an hour or so when it's started manually. Even if the
> application was failing and crashing out, which it does not do when run
> manually, it should take it a few seconds. Is Agent starting the app in the
> background, asynchronously?
> Any suggestions appreciated!
It's a permissions problem. SQL Agent is running as a user (or maybe
Local System) that doesn't have permission to access
\\server\folder\app.exe. Fix that, and all should be well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Subscribe to:
Posts (Atom)