• OPENDATASOURCE读取远程数据库数据中文乱码问题-sqlserver R2


    insert into kraft_sync_Store(StoreName,StoreCode,Province,PrefectureCity,CountyCity,Region,Area,Unit,HQKA,KAName,StoreType,IsValid,ContactPerson,MobileTelephone,Telephone,Address,Latitude,Longitude,regiongroup,sfastoremanagername)
    	Select CAST(storename as nvarchar(500)),StoreCode,Province,PrefectureCity,CountyCity,Region,Area,Unit,HQKA,KAName,StoreType,IsValid,ContactPerson,MobileTelephone,Telephone,Address,Latitude,Longitude,regiongroup,sfastoremanagername From OPENDATASOURCE(
           'SQLOLEDB','Data Source=10.100.6.114;User ID=ISMEA;Password=IE1q2w3e4r'
        ).WARIS.dbo.I_V_Store
    

     对于多语言版本数据库之间的读取,有可能出现中文乱码的可能。 所以以上代码中 OPENDATASOURCE读取远程数据库乱码的情况。 应该换成下面linkserver的方式: 

    
    
    create  procedure [dbo].[sp_GetSyncStore_Insert]
    @storecode nvarchar(500)
     as
    if not exists(select name from sys.servers where name = 'sso')
    begin
     exec sp_addlinkedserver   'sso', ' ', 'SQLOLEDB ', '10.100.6.114' 
     exec sp_addlinkedsrvlogin 'sso', 'false ',null, 'ISMEA', 'IE1q2w3e4r' 
    end
    delete kraft_sync_Store where StoreCode=@storecode
    exec('insert into kraft_sync_Store(StoreName,StoreCode,Province,PrefectureCity,CountyCity,Region,Area,Unit,HQKA,KAName,StoreType,IsValid,ContactPerson,MobileTelephone,Telephone,Address,Latitude,Longitude,regiongroup,sfastoremanagername,storeTypeGroup,regionGroupCH,createtime)
    select StoreName,StoreCode,Province,PrefectureCity,CountyCity,Region,Area,Unit,HQKA,KAName,StoreType,IsValid,ContactPerson,MobileTelephone,Telephone,Address,Latitude,Longitude,regiongroup,sfastoremanagername,storeTypeGroup,'''',GETDATE() createtime from sso.WARIS.dbo.I_V_Store  where storecode='''+@storecode+'''')
    
    ;update a set a.regionGroupCH=b.regioncodeCN from kraft_sync_Store a
    left join KRAFT_REGIONCODES b on a.regionGroup=b.regioncodeEN
    where a.StoreCode=@storecode
    
    exec sp_dropserver  'sso', 'droplogins'
    
    GO
    

      

  • 相关阅读:
    db2 load命令装载数据时定位错误出现的位置
    DB2 SQL error: SQLCODE: -668, SQLSTATE: 57016, SQLERRMC: 3
    db2重组所有表和更新表统计信息
    DB2消息
    db2 SQL查询路径
    db2备份与恢复
    博弈-题表
    【POJ1082】Calendar Game (博弈)
    【POJ1067】取石子游戏 (威佐夫博弈)
    【POJ3710】Christmas Game (博弈-树上的删边问题)
  • 原文地址:https://www.cnblogs.com/Wilson6/p/6245553.html
Copyright © 2020-2023  润新知