Sql server distributed query question - Urgent solution required.

microsoft.public.dotnet.framework.adonet

15 Nov 2005 3:10 PM

DonP.

Hi All:

    I am currently using sql server 2000 with Oracle 9i. We have some of the
lagacy data kept in oracle and some data kept in sql server. I am writing an
application that supports multiple cultures including chinese/taiwan. I am
using a distributed query in sql server stored procedure like below:

set @table1 = 'OPENDATASOURCE (''MSDAORA'', ''Data Source=' + @pdatasrc +
';User ID=' + @puserid + ';Password=' + @ppassword + ''' )..' + @puserid +
'.IP_EQ e'
    set @table2 = 'OPENDATASOURCE (''MSDAORA'', ''Data Source=' + @pdatasrc +
';User ID=' + @puserid + ';Password=' + @ppassword + ''' )..' + @puserid +
'.OM_EQUIP_LINKS'

    set @subsql = 'SELECT DISTINCT EQ_NAME FROM ' + @table2 + ' WHERE
PARENT_EQ_NAME=''' + @pEqName + ''' OR EQ_NAME=''' + @pEqName + ''''

    if (@pIsShowChild = 0)
        set @sql = 'select c.SC_ID, ss.SS_ID, ss.Status, ss.SC_Name,
ss.SC_OwnerID, ss.CreatedBy, ss.CreateDate, ss.SubmittedBy, ss.SubmitDate,
ss.ApprovedBy, ss.ApproveDate, ss.MinTillLocked, e.EM_Name from
BC_ShiftSummary ss, BC_Container c, ' + @table1 + ' where ss.EqName = ''' +
@pEqName + ''' and ss.EqName = e.EQ_Name and ss.ShiftStartDate = '''+
CAST(@pShiftStartDate AS nvarchar(100)) + ''' and ss.ShiftEndDate = ''' +
CAST(@pShiftEndDate AS nvarchar(100)) + ''' and c.Name = ss.SC_Name and
c.OwnerID = ss.SC_OwnerID order by ss.CreatedBy, ss.Status, ss.EqName,
ss.SC_Name, ss.SC_OwnerID'
    else
        set @sql = 'select c.SC_ID, ss.SS_ID, ss.Status, ss.SC_Name,
ss.SC_OwnerID, ss.CreatedBy, ss.CreateDate, ss.SubmittedBy, ss.SubmitDate,
ss.ApprovedBy, ss.ApproveDate, ss.MinTillLocked, e.EM_Name from
BC_ShiftSummary ss, BC_Container c, ' + @table1 + ' where ss.EqName in (' +
@subsql + ') and ss.EqName = e.EQ_Name and ss.ShiftStartDate = '''+
CAST(@pShiftStartDate AS nvarchar(100)) + ''' and ss.ShiftEndDate = ''' +
CAST(@pShiftEndDate AS nvarchar(100)) + ''' and c.Name = ss.SC_Name and
c.OwnerID = ss.SC_OwnerID order by ss.CreatedBy, ss.Status, ss.EqName,
ss.SC_Name, ss.SC_OwnerID'

    EXEC (@sql)

Since I am using msdaora data provider I am not getting any unicode support
and hence returns me empty dataset because the join is improper. My oracle
database has unicode turned on and I am sure it's not problem from oracle
site because a part of the application that is completely driven from oracle
is working perfectly. The query works fine when using us-en culture or
culture that just has us fonts.

Please let me know any ideas to solve this problem or send me mail to
dod***@gmail.com. Your help will be greatly appreciated.

Thanks and regards,

Don P.