Collation Error

Hi All,
Iam facing a problem with sql collations.
Problem:
I have a sproc1 residing on DB1 Database which makes a call to sproc2
on DB2 database.
The out params from sproc2 are processed inside sproc1. And the sproc1
is erroring out.
DB1 - SQL_Latin1_General_CP1_CI_AS Collation
DB2 - Japanese_CI_AS_KS_WS Collation
sproc1 Code:
declare @.name varchar(50)
SELECT @.Cmd = @.Server+'.'+@.Database+'.dbo.SPROC2 '
EXEC @.cmd @.name OUT
Error:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
With Regards,
Anandyou ought change that collation in DB2 and put on as in DB1.
"Anand" wrote:

> Hi All,
> Iam facing a problem with sql collations.
> Problem:
> I have a sproc1 residing on DB1 Database which makes a call to sproc2
> on DB2 database.
> The out params from sproc2 are processed inside sproc1. And the sproc1
> is erroring out.
> DB1 - SQL_Latin1_General_CP1_CI_AS Collation
> DB2 - Japanese_CI_AS_KS_WS Collation
> sproc1 Code:
> declare @.name varchar(50)
> SELECT @.Cmd = @.Server+'.'+@.Database+'.dbo.SPROC2 '
> EXEC @.cmd @.name OUT
> Error:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> With Regards,
> Anand|||Can you post sproc2's code?
See "collate" clause in BOL.
AMB
"Anand" wrote:

> Hi All,
> Iam facing a problem with sql collations.
> Problem:
> I have a sproc1 residing on DB1 Database which makes a call to sproc2
> on DB2 database.
> The out params from sproc2 are processed inside sproc1. And the sproc1
> is erroring out.
> DB1 - SQL_Latin1_General_CP1_CI_AS Collation
> DB2 - Japanese_CI_AS_KS_WS Collation
> sproc1 Code:
> declare @.name varchar(50)
> SELECT @.Cmd = @.Server+'.'+@.Database+'.dbo.SPROC2 '
> EXEC @.cmd @.name OUT
> Error:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> With Regards,
> Anand|||Both the databases resides in different servers.
As the collation is mandatory in both the databases we cannot change the
collation on both DB1 or DB2.
Is there any options without changing the collations?
"Enric" wrote:
> you ought change that collation in DB2 and put on as in DB1.
> "Anand" wrote:
>|||To change the collation at table/field level it could work, i don't know but
in any case, we would need see that sp.
Thanks and regards,
"Anand" wrote:
> Both the databases resides in different servers.
> As the collation is mandatory in both the databases we cannot change the
> collation on both DB1 or DB2.
> Is there any options without changing the collations?
> "Enric" wrote:
>|||sproc2:
create procedure sproc2
@.name varchar(50) output
As
Begin
Select @.name = name from table1
End
Note:
The sprocs are working fine with same collation as
SQL_Latin1_General_CP1_CI_AS
"Alejandro Mesa" wrote:
> Can you post sproc2's code?
> See "collate" clause in BOL.
>
> AMB
> "Anand" wrote:
>

Code page conversion shortcut?

Is there a simple way to avoid having to use a data conversion task to convert Unicode data (from DB2) to ANSI format (for insert to Oracle)?

I'm hoping that there is a way to set the code page property on the OLEDB driver so that it implicitly converts to the format I want instead of having to explicitly force the conversion in the package.

Thoughts? suggestions?

-Clayton

In the intervening month, the best method I've come up with is to use the SSIS wizard to build/rebuild the Data Flow. By default the wizard creates the Data Flow with the data conversion task in place, which saves a lot of time over performing the task manually.

To access the wizard, right click on the "SSIS Packages" node of the Solution Explorer and choose "SSIS Import and Export Wizard..." This will create a new package, but the data flow object can be cut from the new package and pasted into an existing one.

I did not find any way to tweak the code page settings on the source driver to cast to an ANSI code page.

|||

Try this: http://blogs.conchango.com/jamiethomson/archive/2005/10/25/2303.aspx

Code page conversion shortcut?

Is there a simple way to avoid having to use a data conversion task to convert Unicode data (from DB2) to ANSI format (for insert to Oracle)?

I'm hoping that there is a way to set the code page property on the OLEDB driver so that it implicitly converts to the format I want instead of having to explicitly force the conversion in the package.

Thoughts? suggestions?

-Clayton

In the intervening month, the best method I've come up with is to use the SSIS wizard to build/rebuild the Data Flow. By default the wizard creates the Data Flow with the data conversion task in place, which saves a lot of time over performing the task manually.

To access the wizard, right click on the "SSIS Packages" node of the Solution Explorer and choose "SSIS Import and Export Wizard..." This will create a new package, but the data flow object can be cut from the new package and pasted into an existing one.

I did not find any way to tweak the code page settings on the source driver to cast to an ANSI code page.

|||

Try this: http://blogs.conchango.com/jamiethomson/archive/2005/10/25/2303.aspx

Cnnecting to AS400 DB2 database in SQL SERVER 2000

Hi,

Im finding the way of connecting to DB 2 database which is exixst in the AS400

machine to do some data manupulation.Please attach the SQL cording.

Thanks

Pubudu

You have to use the linked server...

Here the sample query..

Code Snippet

EXEC sp_addlinkedserver

@.server = 'MyDB2Server',

@.provider = 'DB2OLEDB',

@.srvproduct = '',

@.provstr = 'Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=XXX.XXX.XXX.XXX;Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema;User ID=myUsername;Password=myPassword;'

Exec sp_addlinkedsrvlogin

@.rmtsrvname = 'MyDB2Server',

@.useself = true,

@.locallogin = null,

@.rmtuser = 'DB2UserName',

@.rmtpassword = 'DB2Password'

Go

Select * from MyDB2Server...Tablename