Help on Oracle RDB Migration to SQL Server.

Hi,
We need help on following things,

1. Inputs on creating comments on the columns & Tables of a SQL
Database & generating the sql script of that.

2. Is it possible to call a .exe file in SQL server like following
code in ORACLE

create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
language SQL;
external
name "CERT_VERIFY"
location 'HOST_IMG:TEST_CALCS.EXE'
with ALL logical_name translation
language C
GENERAL parameter style

3. We are using Rules for restricting data(now), We need inputs
whether to use Check constraints or Rules.

Thanks & Regards,
Chandra MohanOn Thu, 07 Aug 2003 04:45:54 -0700, Chandra Mohan wrote:

> Hi,
> We need help on following things,
> 1. Inputs on creating comments on the columns & Tables of a SQL
> Database & generating the sql script of that.

There is no direct support for Rdb-style comments in SQL Server. There is
an Extended Property facility, and SQL Enterprise Manager uses this to
allow you to annotate objects with comments. You could do the same thing
yourself with the system stored procedures for extended properties. Or
(and I've never looked at this) you could see if DMO has a way to
programmatically manipulate the same comments as SQL Enterprise Manager
uses. Then you could script calls to DMO.

> 2. Is it possible to call a .exe file in SQL server like following
> code in ORACLE
> create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
> language SQL;
> external
> name "CERT_VERIFY"
> location 'HOST_IMG:TEST_CALCS.EXE'
> with ALL logical_name translation
> language C
> GENERAL parameter style

Unfortunately not something similar to Rdb. Keep in mind that Rdb (on
VMS) runs as a run-time library in the user process. Thus, subject to a
little bit of security work to make sure you drop into user mode, its
pretty easy to run external logic. SQL Server runs as a central server
process, so it is far more difficult to safely run external logic.

Currently SQL Server has three mechanism for running external logic.
XP_CMDSHELL allows you to directly send a command or script to a command
shell. You could wrap a call to XP_CMDSHELL in a stored procedure to
simulate something like what you can code in Rdb, but its quite different.
Anyway, take a look and pay attention to the security requirements.
Second, you can write extended stored procedures to call code written in
C. Third, you can use the OLE Automation stored procedures to call OLE
Automation objects. This is probably the closest thing to the Rdb
capability since much software on Windows exposes its functionality via
OLE Automation.

> 3. We are using Rules for restricting data(now), We need inputs
> whether to use Check constraints or Rules.

Use Check constraints.

Hal

Help needed with OpenQuery

Hello
I am trying to do the following on a linked server (Oracle RDB), not
(Oracle, Oracle).
1. drop a constraint
2. truncate a table
3. load new records into the lined server from ms sql server.
I am struggling with the syntax of the commands to make the medata changes
on the linked server. The following command results in the error:
Server: Msg 156, Level 15, Sate 1
Incorrect syntax near the keyword ;OPENQUERY'
The query is:
OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
Any help on the drop constraint and truncate table commands would be
greatly appreciate.
Thanks
Jim Wile
Jim,
OPENQUERY is a row-set function. You have to use it with SELECT, INSERT,
UPDATE or DELETE.
select *
from openquery(my_linked_Server, 'select top 1 * from my_table')
if you are sending DML statements, try to return something, if not OPENQUERY
will give you an error like:
OLE DB provider unable to process object, since the object has no columns
Example:
select *
from openquery(my_linked_server, '
set nocount on;
declare @.i int
drop table northwind.dbo.t;
set @.i = @.@.error
if @.i = 0
begin
commit transaction
select 0
end
else
begin
rollback transaction
select @.i
end')
go
You can also, create a stored procedure in your linked server and execute it
using OPENQUERY.
AMB
"Jim Wile" wrote:

> Hello
> I am trying to do the following on a linked server (Oracle RDB), not
> (Oracle, Oracle).
> 1. drop a constraint
> 2. truncate a table
> 3. load new records into the lined server from ms sql server.
> I am struggling with the syntax of the commands to make the medata changes
> on the linked server. The following command results in the error:
> Server: Msg 156, Level 15, Sate 1
> Incorrect syntax near the keyword ;OPENQUERY'
> The query is:
> OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
>
> Any help on the drop constraint and truncate table commands would be
> greatly appreciate.
> Thanks
> Jim Wile
>
>

Help needed with OpenQuery

Hello
I am trying to do the following on a linked server (Oracle RDB), not
(Oracle, Oracle).
1. drop a constraint
2. truncate a table
3. load new records into the lined server from ms sql server.
I am struggling with the syntax of the commands to make the medata changes
on the linked server. The following command results in the error:
Server: Msg 156, Level 15, Sate 1
Incorrect syntax near the keyword ;OPENQUERY'
The query is:
OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
Any help on the drop constraint and truncate table commands would be
greatly appreciate.
Thanks
Jim WileJim,
OPENQUERY is a row-set function. You have to use it with SELECT, INSERT,
UPDATE or DELETE.
select *
from openquery(my_linked_Server, 'select top 1 * from my_table')
if you are sending DML statements, try to return something, if not OPENQUERY
will give you an error like:
OLE DB provider unable to process object, since the object has no columns
Example:
select *
from openquery(my_linked_server, '
set nocount on;
declare @.i int
drop table northwind.dbo.t;
set @.i = @.@.error
if @.i = 0
begin
commit transaction
select 0
end
else
begin
rollback transaction
select @.i
end')
go
You can also, create a stored procedure in your linked server and execute it
using OPENQUERY.
AMB
"Jim Wile" wrote:

> Hello
> I am trying to do the following on a linked server (Oracle RDB), not
> (Oracle, Oracle).
> 1. drop a constraint
> 2. truncate a table
> 3. load new records into the lined server from ms sql server.
> I am struggling with the syntax of the commands to make the medata changes
> on the linked server. The following command results in the error:
> Server: Msg 156, Level 15, Sate 1
> Incorrect syntax near the keyword ;OPENQUERY'
> The query is:
> OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
>
> Any help on the drop constraint and truncate table commands would be
> greatly appreciate.
> Thanks
> Jim Wile
>
>

Help needed with OpenQuery

Hello
I am trying to do the following on a linked server (Oracle RDB), not
(Oracle, Oracle).
1. drop a constraint
2. truncate a table
3. load new records into the lined server from ms sql server.
I am struggling with the syntax of the commands to make the medata changes
on the linked server. The following command results in the error:
Server: Msg 156, Level 15, Sate 1
Incorrect syntax near the keyword ;OPENQUERY'
The query is:
OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
Any help on the drop constraint and truncate table commands would be
greatly appreciate.
Thanks
Jim WileJim,
OPENQUERY is a row-set function. You have to use it with SELECT, INSERT,
UPDATE or DELETE.
select *
from openquery(my_linked_Server, 'select top 1 * from my_table')
if you are sending DML statements, try to return something, if not OPENQUERY
will give you an error like:
OLE DB provider unable to process object, since the object has no columns
Example:
select *
from openquery(my_linked_server, '
set nocount on;
declare @.i int
drop table northwind.dbo.t;
set @.i = @.@.error
if @.i = 0
begin
commit transaction
select 0
end
else
begin
rollback transaction
select @.i
end')
go
You can also, create a stored procedure in your linked server and execute it
using OPENQUERY.
AMB
"Jim Wile" wrote:
> Hello
> I am trying to do the following on a linked server (Oracle RDB), not
> (Oracle, Oracle).
> 1. drop a constraint
> 2. truncate a table
> 3. load new records into the lined server from ms sql server.
> I am struggling with the syntax of the commands to make the medata changes
> on the linked server. The following command results in the error:
> Server: Msg 156, Level 15, Sate 1
> Incorrect syntax near the keyword ;OPENQUERY'
> The query is:
> OPENQUERY (CASPR_VENDORS,'DROP CONSTRAINT UNK_DELIV_HAULER')
>
> Any help on the drop constraint and truncate table commands would be
> greatly appreciate.
> Thanks
> Jim Wile
>
>

Help needed

Hello
I am trying to insert records into a lined server (Oracle RDB database) from
a sql database using Tsql. I cannot get past the error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
The Tsql script follows, any help will be greatly appreciated!
Thanks
Jim Wile
--SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
INSERT INTO CASPR_VENDORS...VENDORS
(VENDOR_NUMBER,
VENDOR_TYPE,
VENDOR_NAME,
ADDRESS_1,
ADDRESS_2,
CITY,
STATE,
ZIP_CODE,
PHONE_NUMBER,
TAGS_ON_BILLS,
ADDRESS_3)
SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
'XX',
M.VENDOR_NAME,
M.SYS_ADDRESS_1,
M.SYS_ADDRESS_2,
M.SYS_CITY,
M.SYS_STATE,
M.SYS_POSTAL_CODE,
M.PHONE,
'N',
SYS_ADDRESS_3
FROM Magpi.dbo.VENDORS AS M
WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
C.VENDOR_NUMBER)
Is there no one who has had a simular situation or has indepth knowledge of
linked servers, I have been struggling with this for days.
Thanks
Jim Wile
"Jim Wile" <jimwile@.mopac.com> wrote in message
news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am trying to insert records into a lined server (Oracle RDB database)
from
> a sql database using Tsql. I cannot get past the error:
> Server: Msg 446, Level 16, State 9, Line 4
> Cannot resolve collation conflict for equal to operation.
> The Tsql script follows, any help will be greatly appreciated!
> Thanks
> Jim Wile
> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> INSERT INTO CASPR_VENDORS...VENDORS
> (VENDOR_NUMBER,
> VENDOR_TYPE,
> VENDOR_NAME,
> ADDRESS_1,
> ADDRESS_2,
> CITY,
> STATE,
> ZIP_CODE,
> PHONE_NUMBER,
> TAGS_ON_BILLS,
> ADDRESS_3)
> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> 'XX',
> M.VENDOR_NAME,
> M.SYS_ADDRESS_1,
> M.SYS_ADDRESS_2,
> M.SYS_CITY,
> M.SYS_STATE,
> M.SYS_POSTAL_CODE,
> M.PHONE,
> 'N',
> SYS_ADDRESS_3
> FROM Magpi.dbo.VENDORS AS M
> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
> C.VENDOR_NUMBER)
>
>
|||The easy answer would to that you need to change the collation for the
fields you are using in your comparison. I don't know if it's possible to
find out which collation the ORacle server is using - and also I don't know
if that will solve the problem.
If not, then you might be able to select the values you want to compare from
your Oracle server into a temp table on your SQL server and then use that
for mathing the values in your other SQL server database. It's maybe not a
very nice solution and I don't know if it's gonna work, but it might be
worth giving a thought.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Is there no one who has had a simular situation or has indepth
> knowledge of linked servers, I have been struggling with this for
> days.
> Thanks
> Jim Wile
>
> "Jim Wile" <jimwile@.mopac.com> wrote in message
> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
|||Hi Steen
Thanks for replying. What I don't understand is I can select (read) the
vendor_number field from the linked server, but as soon as I try to compare
it to the SQL vendor_number field (both are defined as character data)the
error occurs.
Thanks
Jim Wile
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
> The easy answer would to that you need to change the collation for the
> fields you are using in your comparison. I don't know if it's possible to
> find out which collation the ORacle server is using - and also I don't
know
> if that will solve the problem.
> If not, then you might be able to select the values you want to compare
from
> your Oracle server into a temp table on your SQL server and then use that
> for mathing the values in your other SQL server database. It's maybe not a
> very nice solution and I don't know if it's gonna work, but it might be
> worth giving a thought.
> Regards
> Steen
> Jim Wile wrote:
>
|||I'm not an expert on Collation, but as I understand it SQL takes the
collation into consideration when it does the comparison and then it ends up
trying to compare two different things - which it can't.
It might be worth looking up "collate" in Books On Line and see what you can
get out of that.
Sorry that I can't be to any more help...:-(.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Hi Steen
> Thanks for replying. What I don't understand is I can select (read)
> the vendor_number field from the linked server, but as soon as I try
> to compare it to the SQL vendor_number field (both are defined as
> character data)the error occurs.
> Thanks
> Jim Wile
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
|||On Fri, 18 Mar 2005 09:04:10 -0500, Jim Wile wrote:

> What I don't understand is I can select (read) the
>vendor_number field from the linked server, but as soon as I try to compare
>it to the SQL vendor_number field (both are defined as character data)the
>error occurs.
Hi Jim,
They are both character, but they are not defined using the same
collation. That's why SQL Server doesn't know how to comapre them (there
is no builtin priority setting in case of conflicting collations).
You'll have to specify which collation to use. Run the following example
in Query Analyzer to see a quick demo:
CREATE TABLE test (col1 varchar(10) COLLATE Arabic_CI_AI,
col2 varchar(10) COLLATE Finnish_Swedish_CS_AS)
go
INSERT test (col1, col2)
SELECT 'test', 'test'
go
SELECT *
FROM test
WHERE col1 = col2
go
SELECT *
FROM test
WHERE col1 = col2 COLLATE German_PhoneBook_CI_AI_KS_WS
go
DROP TABLE test
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help needed

Hello
I am trying to insert records into a lined server (Oracle RDB database) from
a sql database using Tsql. I cannot get past the error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
The Tsql script follows, any help will be greatly appreciated!
Thanks
Jim Wile
--SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
INSERT INTO CASPR_VENDORS...VENDORS
(VENDOR_NUMBER,
VENDOR_TYPE,
VENDOR_NAME,
ADDRESS_1,
ADDRESS_2,
CITY,
STATE,
ZIP_CODE,
PHONE_NUMBER,
TAGS_ON_BILLS,
ADDRESS_3)
SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
'XX',
M.VENDOR_NAME,
M.SYS_ADDRESS_1,
M.SYS_ADDRESS_2,
M.SYS_CITY,
M.SYS_STATE,
M.SYS_POSTAL_CODE,
M.PHONE,
'N',
SYS_ADDRESS_3
FROM Magpi.dbo.VENDORS AS M
WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
C.VENDOR_NUMBER)Is there no one who has had a simular situation or has indepth knowledge of
linked servers, I have been struggling with this for days.
Thanks
Jim Wile
"Jim Wile" <jimwile@.mopac.com> wrote in message
news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am trying to insert records into a lined server (Oracle RDB database)
from
> a sql database using Tsql. I cannot get past the error:
> Server: Msg 446, Level 16, State 9, Line 4
> Cannot resolve collation conflict for equal to operation.
> The Tsql script follows, any help will be greatly appreciated!
> Thanks
> Jim Wile
> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> INSERT INTO CASPR_VENDORS...VENDORS
> (VENDOR_NUMBER,
> VENDOR_TYPE,
> VENDOR_NAME,
> ADDRESS_1,
> ADDRESS_2,
> CITY,
> STATE,
> ZIP_CODE,
> PHONE_NUMBER,
> TAGS_ON_BILLS,
> ADDRESS_3)
> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> 'XX',
> M.VENDOR_NAME,
> M.SYS_ADDRESS_1,
> M.SYS_ADDRESS_2,
> M.SYS_CITY,
> M.SYS_STATE,
> M.SYS_POSTAL_CODE,
> M.PHONE,
> 'N',
> SYS_ADDRESS_3
> FROM Magpi.dbo.VENDORS AS M
> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =
> C.VENDOR_NUMBER)
>
>|||The easy answer would to that you need to change the collation for the
fields you are using in your comparison. I don't know if it's possible to
find out which collation the ORacle server is using - and also I don't know
if that will solve the problem.
If not, then you might be able to select the values you want to compare from
your Oracle server into a temp table on your SQL server and then use that
for mathing the values in your other SQL server database. It's maybe not a
very nice solution and I don't know if it's gonna work, but it might be
worth giving a thought.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Is there no one who has had a simular situation or has indepth
> knowledge of linked servers, I have been struggling with this for
> days.
> Thanks
> Jim Wile
>
> "Jim Wile" <jimwile@.mopac.com> wrote in message
> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...|||Hi Steen
Thanks for replying. What I don't understand is I can select (read) the
vendor_number field from the linked server, but as soon as I try to compare
it to the SQL vendor_number field (both are defined as character data)the
error occurs.
Thanks
Jim Wile
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
> The easy answer would to that you need to change the collation for the
> fields you are using in your comparison. I don't know if it's possible to
> find out which collation the ORacle server is using - and also I don't
know
> if that will solve the problem.
> If not, then you might be able to select the values you want to compare
from
> your Oracle server into a temp table on your SQL server and then use that
> for mathing the values in your other SQL server database. It's maybe not a
> very nice solution and I don't know if it's gonna work, but it might be
> worth giving a thought.
> Regards
> Steen
> Jim Wile wrote:
>|||I'm not an expert on Collation, but as I understand it SQL takes the
collation into consideration when it does the comparison and then it ends up
trying to compare two different things - which it can't.
It might be worth looking up "collate" in Books On Line and see what you can
get out of that.
Sorry that I can't be to any more help...:-(.
Regards
Steen
Jim Wile wrote:[vbcol=seagreen]
> Hi Steen
> Thanks for replying. What I don't understand is I can select (read)
> the vendor_number field from the linked server, but as soon as I try
> to compare it to the SQL vendor_number field (both are defined as
> character data)the error occurs.
> Thanks
> Jim Wile
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...|||On Fri, 18 Mar 2005 09:04:10 -0500, Jim Wile wrote:

> What I don't understand is I can select (read) the
>vendor_number field from the linked server, but as soon as I try to compare
>it to the SQL vendor_number field (both are defined as character data)the
>error occurs.
Hi Jim,
They are both character, but they are not defined using the same
collation. That's why SQL Server doesn't know how to comapre them (there
is no builtin priority setting in case of conflicting collations).
You'll have to specify which collation to use. Run the following example
in Query Analyzer to see a quick demo:
CREATE TABLE test (col1 varchar(10) COLLATE Arabic_CI_AI,
col2 varchar(10) COLLATE Finnish_Swedish_CS_AS)
go
INSERT test (col1, col2)
SELECT 'test', 'test'
go
SELECT *
FROM test
WHERE col1 = col2
go
SELECT *
FROM test
WHERE col1 = col2 COLLATE German_PhoneBook_CI_AI_KS_WS
go
DROP TABLE test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help needed

Hello
I am trying to insert records into a lined server (Oracle RDB database) from
a sql database using Tsql. I cannot get past the error:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for equal to operation.
The Tsql script follows, any help will be greatly appreciated!
Thanks
Jim Wile
--SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
INSERT INTO CASPR_VENDORS...VENDORS
(VENDOR_NUMBER,
VENDOR_TYPE,
VENDOR_NAME,
ADDRESS_1,
ADDRESS_2,
CITY,
STATE,
ZIP_CODE,
PHONE_NUMBER,
TAGS_ON_BILLS,
ADDRESS_3)
SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
'XX',
M.VENDOR_NAME,
M.SYS_ADDRESS_1,
M.SYS_ADDRESS_2,
M.SYS_CITY,
M.SYS_STATE,
M.SYS_POSTAL_CODE,
M.PHONE,
'N',
SYS_ADDRESS_3
FROM Magpi.dbo.VENDORS AS M
WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) = C.VENDOR_NUMBER)Is there no one who has had a simular situation or has indepth knowledge of
linked servers, I have been struggling with this for days.
Thanks
Jim Wile
"Jim Wile" <jimwile@.mopac.com> wrote in message
news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am trying to insert records into a lined server (Oracle RDB database)
from
> a sql database using Tsql. I cannot get past the error:
> Server: Msg 446, Level 16, State 9, Line 4
> Cannot resolve collation conflict for equal to operation.
> The Tsql script follows, any help will be greatly appreciated!
> Thanks
> Jim Wile
> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> INSERT INTO CASPR_VENDORS...VENDORS
> (VENDOR_NUMBER,
> VENDOR_TYPE,
> VENDOR_NAME,
> ADDRESS_1,
> ADDRESS_2,
> CITY,
> STATE,
> ZIP_CODE,
> PHONE_NUMBER,
> TAGS_ON_BILLS,
> ADDRESS_3)
> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> 'XX',
> M.VENDOR_NAME,
> M.SYS_ADDRESS_1,
> M.SYS_ADDRESS_2,
> M.SYS_CITY,
> M.SYS_STATE,
> M.SYS_POSTAL_CODE,
> M.PHONE,
> 'N',
> SYS_ADDRESS_3
> FROM Magpi.dbo.VENDORS AS M
> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) => C.VENDOR_NUMBER)
>
>|||The easy answer would to that you need to change the collation for the
fields you are using in your comparison. I don't know if it's possible to
find out which collation the ORacle server is using - and also I don't know
if that will solve the problem.
If not, then you might be able to select the values you want to compare from
your Oracle server into a temp table on your SQL server and then use that
for mathing the values in your other SQL server database. It's maybe not a
very nice solution and I don't know if it's gonna work, but it might be
worth giving a thought.
Regards
Steen
Jim Wile wrote:
> Is there no one who has had a simular situation or has indepth
> knowledge of linked servers, I have been struggling with this for
> days.
> Thanks
> Jim Wile
>
> "Jim Wile" <jimwile@.mopac.com> wrote in message
> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
>> Hello
>> I am trying to insert records into a lined server (Oracle RDB
>> database) from a sql database using Tsql. I cannot get past the
>> error:
>> Server: Msg 446, Level 16, State 9, Line 4
>> Cannot resolve collation conflict for equal to operation.
>> The Tsql script follows, any help will be greatly appreciated!
>> Thanks
>> Jim Wile
>> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
>> INSERT INTO CASPR_VENDORS...VENDORS
>> (VENDOR_NUMBER,
>> VENDOR_TYPE,
>> VENDOR_NAME,
>> ADDRESS_1,
>> ADDRESS_2,
>> CITY,
>> STATE,
>> ZIP_CODE,
>> PHONE_NUMBER,
>> TAGS_ON_BILLS,
>> ADDRESS_3)
>> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
>> 'XX',
>> M.VENDOR_NAME,
>> M.SYS_ADDRESS_1,
>> M.SYS_ADDRESS_2,
>> M.SYS_CITY,
>> M.SYS_STATE,
>> M.SYS_POSTAL_CODE,
>> M.PHONE,
>> 'N',
>> SYS_ADDRESS_3
>> FROM Magpi.dbo.VENDORS AS M
>> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
>> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =>> C.VENDOR_NUMBER)|||Hi Steen
Thanks for replying. What I don't understand is I can select (read) the
vendor_number field from the linked server, but as soon as I try to compare
it to the SQL vendor_number field (both are defined as character data)the
error occurs.
Thanks
Jim Wile
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
> The easy answer would to that you need to change the collation for the
> fields you are using in your comparison. I don't know if it's possible to
> find out which collation the ORacle server is using - and also I don't
know
> if that will solve the problem.
> If not, then you might be able to select the values you want to compare
from
> your Oracle server into a temp table on your SQL server and then use that
> for mathing the values in your other SQL server database. It's maybe not a
> very nice solution and I don't know if it's gonna work, but it might be
> worth giving a thought.
> Regards
> Steen
> Jim Wile wrote:
> > Is there no one who has had a simular situation or has indepth
> > knowledge of linked servers, I have been struggling with this for
> > days.
> >
> > Thanks
> > Jim Wile
> >
> >
> > "Jim Wile" <jimwile@.mopac.com> wrote in message
> > news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> >> Hello
> >>
> >> I am trying to insert records into a lined server (Oracle RDB
> >> database) from a sql database using Tsql. I cannot get past the
> >> error:
> >> Server: Msg 446, Level 16, State 9, Line 4
> >> Cannot resolve collation conflict for equal to operation.
> >>
> >> The Tsql script follows, any help will be greatly appreciated!
> >>
> >> Thanks
> >> Jim Wile
> >>
> >> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
> >>
> >> INSERT INTO CASPR_VENDORS...VENDORS
> >> (VENDOR_NUMBER,
> >> VENDOR_TYPE,
> >> VENDOR_NAME,
> >> ADDRESS_1,
> >> ADDRESS_2,
> >> CITY,
> >> STATE,
> >> ZIP_CODE,
> >> PHONE_NUMBER,
> >> TAGS_ON_BILLS,
> >> ADDRESS_3)
> >> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
> >> 'XX',
> >> M.VENDOR_NAME,
> >> M.SYS_ADDRESS_1,
> >> M.SYS_ADDRESS_2,
> >> M.SYS_CITY,
> >> M.SYS_STATE,
> >> M.SYS_POSTAL_CODE,
> >> M.PHONE,
> >> 'N',
> >> SYS_ADDRESS_3
> >> FROM Magpi.dbo.VENDORS AS M
> >> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
> >> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) => >> C.VENDOR_NUMBER)
>|||On Fri, 18 Mar 2005 09:04:10 -0500, Jim Wile wrote:
> What I don't understand is I can select (read) the
>vendor_number field from the linked server, but as soon as I try to compare
>it to the SQL vendor_number field (both are defined as character data)the
>error occurs.
Hi Jim,
They are both character, but they are not defined using the same
collation. That's why SQL Server doesn't know how to comapre them (there
is no builtin priority setting in case of conflicting collations).
You'll have to specify which collation to use. Run the following example
in Query Analyzer to see a quick demo:
CREATE TABLE test (col1 varchar(10) COLLATE Arabic_CI_AI,
col2 varchar(10) COLLATE Finnish_Swedish_CS_AS)
go
INSERT test (col1, col2)
SELECT 'test', 'test'
go
SELECT *
FROM test
WHERE col1 = col2
go
SELECT *
FROM test
WHERE col1 = col2 COLLATE German_PhoneBook_CI_AI_KS_WS
go
DROP TABLE test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I'm not an expert on Collation, but as I understand it SQL takes the
collation into consideration when it does the comparison and then it ends up
trying to compare two different things - which it can't.
It might be worth looking up "collate" in Books On Line and see what you can
get out of that.
Sorry that I can't be to any more help...:-(.
Regards
Steen
Jim Wile wrote:
> Hi Steen
> Thanks for replying. What I don't understand is I can select (read)
> the vendor_number field from the linked server, but as soon as I try
> to compare it to the SQL vendor_number field (both are defined as
> character data)the error occurs.
> Thanks
> Jim Wile
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uH1VJE8KFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> The easy answer would to that you need to change the collation for
>> the fields you are using in your comparison. I don't know if it's
>> possible to find out which collation the ORacle server is using -
>> and also I don't know if that will solve the problem.
>> If not, then you might be able to select the values you want to
>> compare from your Oracle server into a temp table on your SQL server
>> and then use that for mathing the values in your other SQL server
>> database. It's maybe not a very nice solution and I don't know if
>> it's gonna work, but it might be worth giving a thought.
>> Regards
>> Steen
>> Jim Wile wrote:
>> Is there no one who has had a simular situation or has indepth
>> knowledge of linked servers, I have been struggling with this for
>> days.
>> Thanks
>> Jim Wile
>>
>> "Jim Wile" <jimwile@.mopac.com> wrote in message
>> news:urtKS5vKFHA.3616@.TK2MSFTNGP09.phx.gbl...
>> Hello
>> I am trying to insert records into a lined server (Oracle RDB
>> database) from a sql database using Tsql. I cannot get past the
>> error:
>> Server: Msg 446, Level 16, State 9, Line 4
>> Cannot resolve collation conflict for equal to operation.
>> The Tsql script follows, any help will be greatly appreciated!
>> Thanks
>> Jim Wile
>> --SELECT VENDOR_NUMBER, VENDOR_NAME FROM CASPR_VENDORS...VENDORS
>> INSERT INTO CASPR_VENDORS...VENDORS
>> (VENDOR_NUMBER,
>> VENDOR_TYPE,
>> VENDOR_NAME,
>> ADDRESS_1,
>> ADDRESS_2,
>> CITY,
>> STATE,
>> ZIP_CODE,
>> PHONE_NUMBER,
>> TAGS_ON_BILLS,
>> ADDRESS_3)
>> SELECT SUBSTRING(M.VENDOR_NUMBER,1,6),
>> 'XX',
>> M.VENDOR_NAME,
>> M.SYS_ADDRESS_1,
>> M.SYS_ADDRESS_2,
>> M.SYS_CITY,
>> M.SYS_STATE,
>> M.SYS_POSTAL_CODE,
>> M.PHONE,
>> 'N',
>> SYS_ADDRESS_3
>> FROM Magpi.dbo.VENDORS AS M
>> WHERE NOT EXISTS (SELECT * FROM CASPR_VENDORS...VENDORS AS C
>> WHERE SUBSTRING(M.VENDOR_NUMBER,1,6) =>> C.VENDOR_NUMBER)