首页 > 代码库 > OPENDATASOURCE读取远程数据库数据中文乱码问题-sqlserver R2
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
OPENDATASOURCE读取远程数据库数据中文乱码问题-sqlserver R2
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。