June 2, 2004 at 12:15 am
Hi
I am trying to use the 'Opendatabase' command from a stored procedure (in database A) to connect to a different database ( Database B)
and retreive a certain currency label from a table (Table1 1).
When I fire s direct select statement on DatabaseB.Table1 I get the correct output for the SYMUNIT column which displays the symbol.
But when I use the 'Opendatabase' command and execute it through sp_executesql I get all trash charaters for the column SYMUNIT.
Below is the code that I used and the results
SQL Command
-----------------
select CODE , SYMUNIT from Table1
CODE SYMUNIT
------------ -------
DGBP £
EUR €
GBP £
USD US$
(4 row(s) affected)
=========================================
declare @v_S_CURRENCY nvarchar(2000)
set @v_S_CURRENCY = 'SELECT CODE , SYMUNIT FROM OPENDATASOURCE( ''SQLOLEDB'' , ''Data Source=server;User ID= user;Password=user'').DatabaseB.dbo.Table1'
exec sp_executesql @v_s_CURRENCY
CODE SYMUNIT
------------ -------
DGBP ·
EUR Ã
GBP ·
USD US$
(4 row(s) affected)
How do I retreive the correct values from the Database? Any help is appreciated.
June 2, 2004 at 1:51 am
Are your databases on different servers? If they are then check the collations and character sets.
If they are on the same server then why not use a straight select?
If database B is on a different server then you could add that server as a linked server and do the full blown
SELECT <fields> from Server2.DatabaseB.dbo.Table1
June 2, 2004 at 2:02 am
Thanks David for ur response.
Yes the 2 databases have different collations. But i tried to define a #table with the same collation as DatabaseB and inserting the data into it, but that gave the same result.
How do I find out about the character sets?
Moreover if I use [SELECT <fields> from Server2.DatabaseB.dbo.Table1] , I think we need to have the other database on a linked server. We cannot have linked servers , that is the reason why we have to use opendatasource.
Is there any better method without using linked servers to connect to another database in a different server?
June 2, 2004 at 2:39 am
I had something similar and had to define a table as follows:-
DECLARE @tbl TABLE (SerialNumber VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS PRIMARY KEY CLUSTERED)
I also had a problem when the "default language" on each server was different.
I tend to use "British English" and unfortunately the default install is "US English".
I'm afraid its one of those problems that you solve once and never have to look at again and so I can't add any more to this.
If the table is exactly as you describe it then it is going to be a fairly static and very small table. Why not simply create the equivalent on your DatabaseA server?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply