sql server 在作业中 远程连接 oracle mysql sqlserver 数据库

 

在作业中执行远程连接时，需要对本次作业执行的步骤指定特定用户

并且该用户必须拥有所需操作数据库的db_owner角色，和服务器sysadmin角色

 

在作业中执行远程连接时，需要做登录映射

 

下面是我在作业中执行的sql脚本

/* 获取大容量访问权限，在执行作业时需先执行这句话
ALTER DATABASE TEMP SET TRUSTWORTHY ON 
GO
*/
--这句话在执行作业时必须加
SET QUOTED_IDENTIFIER ON
GO
/*       每月一号一点获取数据       */
insert into [dbo].[T_EntryLog] values(CONVERT(varchar(30), GETDATE(), 121),
    '发生时间：' + CONVERT(varchar(30), GETDATE(), 121) + char(10) +
    '自定义信息：' + '开始执行远程调取，读取文件【C:\inter.xml】。'  );

DECLARE @doc XML, --xml文件名称
        @sqls nvarchar(max), --要执行的sql语句
        @mtablelink varchar(500), --本地数据库表全名
        @thisdate varchar(500) = CONVERT(varchar(6),dateadd(MM,-1,getdate()), 112), --上一月份
        @tablelink varchar(500) --接口表全名
SELECT @doc=BulkColumn FROM OPENROWSET(BULK 'C:\inter.xml' ,SINGLE_BLOB ) AS x;

select @mtablelink = t.c.value('(text())[1]','VARCHAR(500)')
from @doc.nodes('/db/mlink') as t(c);
select @tablelink = t.c.value('(text())[1]','VARCHAR(500)')
from @doc.nodes('/db/link') as t(c);
declare @column_str varchar(500) = '', --拼接列sql语句
        @mixcolumn_str varchar(500) = '', --拼接混合列sql语句
        @cuttime_str varchar(500) = '', --拼接his数据时间筛选sql语句
        @servername varchar(100), --连接名称
        @tablename varchar(100) --表名称
select @servername = SUBSTRING(@tablelink,0,CHARINDEX('.', @tablelink, 1))
, @tablename = SUBSTRING(@tablelink,CHARINDEX('.', @tablelink, 1)+1,LEN(@tablelink));
select @column_str += stuff((
select (case 
        when  t.c.value('(@yName)[1]','VARCHAR(500)') = ''
        then ''
        when t.c.value('(@yName)[1]','VARCHAR(500)') !=''
        then ',' + t.c.value('(@yName)[1]','VARCHAR(500)')
        end)
from @doc.nodes('/db/columns/column') as t(c)  for xml path('')  ),1,1,'');
--print @column_str
select @mixcolumn_str += stuff((  
select ',' + (case 
                when  t.c.value('(@yName)[1]','VARCHAR(500)') = ''
                then 'CONVERT(varchar(30), GETDATE(), 121)'
                when t.c.value('(@yName)[1]','VARCHAR(500)') !=''
                then t.c.value('(@yName)[1]','VARCHAR(500)')
                end) + ' as ' + t.c.value('(@mName)[1]','VARCHAR(500)')
from @doc.nodes('/db/columns/column') as t(c)  for xml path('')  ),1,1,'');
--print @mixcolumn_str

select @cuttime_str = t.c.value('(@yName)[1]','VARCHAR(500)')
from @doc.nodes('/db/columns/cuttime[1]') as t(c);

/*-------删除临时表--------*/
begin try 
    exec('drop table ##temptable;')
end try
begin catch
end catch

begin try 
    begin tran
    
    if @doc.exist('/db[@type="MSSQL"]') = 1
    begin
        /*-----------------MSSQL--------------------*/
        --print 'MSSQL';
        
        exec( 
            /*-------将远程数据导入临时表--------*/
            ' select ' + @mixcolumn_str + ' into ##temptable FROM OPENQUERY( ' + @servername + ',''SELECT ' + @column_str + ' from ' + @tablename + 
            ' where CONVERT(varchar(6),  CONVERT(datetime,' + @cuttime_str + ',101), 112) = ''''' + @thisdate + ''''' ' + @clean_str + ' '');' +
            /*-------将临时表数据导入本地表-------- */
            ' insert into ' + @mtablelink + ' select * from ##temptable;')

    end
    else if @doc.exist('/db[@type="ORACLE"]') = 1
    begin
        /*-----------------ORACLE--------------------*/
        --print 'ORACLE';

        exec( 
            /*-------将远程数据导入临时表--------*/
            ' select ' + @mixcolumn_str + ' into ##temptable FROM OPENQUERY( ' + @servername + ',''SELECT ' + @column_str + ' from ' + @tablename + 
            ' where to_char(' + @cuttime_str + ',''''yyyyMM'''') = ''''' + @thisdate + ''''' ' + @clean_str + ' '');' +
            /*-------将临时表数据导入本地表-------- */
            ' insert into ' + @mtablelink + ' select * from ##temptable;')

    end
    else if @doc.exist('/db[@type="MYSQL"]') = 1
    begin
        /*-----------------MYSQL--------------------*/
        --print 'MYSQL';
        
        exec( 
            /*-------将远程数据导入临时表--------*/
            ' select ' + @mixcolumn_str + ' into ##temptable FROM OPENQUERY( ' + @servername + ',''SELECT ' + @column_str + ' from ' + @tablename + 
            ' where DATE_FORMAT(' + @cuttime_str + ',''''%Y%m'''') = ''''' + @thisdate + ''''' ' + @clean_str + ' '');' +
            /*-------将临时表数据导入本地表-------- */
            ' insert into ' + @mtablelink + ' select * from ##temptable;')

    end
    
    commit tran
end try 
begin catch
    rollback tran
    insert into [dbo].[T_EntryLog] values(CONVERT(varchar(30), GETDATE(), 121),
        '发生时间：' + CONVERT(varchar(30), GETDATE(), 121) + char(10) +
        '错误状态号：' + cast(ERROR_STATE() as varchar(20)) + char(10) +
        '严重性：' + cast(ERROR_SEVERITY() as varchar(20)) + char(10) +
        '错误号：' + cast(ERROR_NUMBER() as varchar(20)) + char(10) +
        '错误行号：' + cast(ERROR_LINE() as varchar(20)) + char(10) +
        '系统错误信息：' + ERROR_MESSAGE() );
end catch 

insert into [dbo].[T_EntryLog] values(CONVERT(varchar(30), GETDATE(), 121),
    '发生时间：' + CONVERT(varchar(30), GETDATE(), 121) + char(10) +
    '自定义信息：' + '结束执行远程调取。'  );


/*-------删除临时表--------*/
begin try 
    exec('drop table ##temptable;')
end try
begin catch
end catch

 以及我配置的xml文件

<?xml version="1.0" encoding="utf-8" ?>
<db Explain="数据库类型【MSSQL、ORACLE、MYSQL】" type="MSSQL" >
    <mlink Explain="我方字表链接全称">TEMP.[dbo].[MYTABLE]</mlink>
    <link Explain="对接系统数据库表链接全称">INTER_LINK.[INTERFACEDB].[dbo].[TABLE]</link>
    <columns Explain="表字段对照，mName：【我方字段名称(不需要操作)】，yName：【对接系统字段名称】" >
        <identity Explain="对接系统数据表过滤主键" yName="" />
        <cuttime Explain="对接系统数据截取时间字段，根据该字段过滤对接系统数据当月时间" yName="" />
        
        <column Explain="表字段对照" mName="" yName="" />
        <column Explain="创建时间" mName="" yName="" />
        <column Explain="导入时间【yName：不需要填写】" mName="" yName="" />
    </columns>
</db>