mysql odbc
드라이버 32, 64 둘 다 깔고
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'BOA', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'BOA', @provstr=N'BOA', @catalog=N'dbname'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'BOA',@useself=N'False',@locallogin=NULL,@rmtuser=N'',@rmtpassword=''
GO
EXEC master.dbo.sp_addlinkedserver @server = N'wmman', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'wmman', @provstr=N'wmman', @catalog=N'wmman'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'wmman',@useself=N'False',@locallogin=NULL,@rmtuser=N'id',@rmtpassword='pass'
select * from openquery(wmman,'select * from tb_ticket')
alter proc usp_loader
(
@sourceFolderName nvarchar(4000)
, @filename nvarchar(4000)
)
as
if object_id ('tempdb..##tMdsApiResultsTemp') is not null
drop table ##tMdsApiResultsTemp
create table ##tMdsApiResultsTemp
(cIp varchar(100)
,cApi varchar(100)
,cVersion varchar(100)
,cLastAccess varchar(100)
)
declare @sql nvarchar(4000)
=
'
BULK INSERT ##tMdsApiResultsTemp
FROM '''+@sourceFolderName+@fileName+'''
WITH
(
FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = ''0x0a'',
TABLOCK
)
'
print (@sql)
exec (@sql)
insert into tMdsApiResults (cFilename, cIp, cApiComponent, cApiPlatform, cVersion, cLastAccess)
select
@filename
, cIp
, substring(cApi, 1, CHARINDEX(':', cApi)-1)
, substring(cApi, CHARINDEX(':', cApi)+1, len(cApi))
, cVersion
, cLastAccess
from ##tMdsApiResultsTemp
go