FR根据登陆用户连接各自的数据库服务器动态查询

背景：公司有很多客户，每个客户有不同的系统数据库服务器，数据库名也可能不一样，只有数据库结构是一样的，现在要做一个决策系统供所有客户的用户登陆，然后根据各自的用户指向各自公司的服务器和数据库返回数据
数据库版本：SQLSERVER2014以上

目前我做了一个简单的方案来实现，希望能有更好的解决方式。
1.指定一个数据库服务器作为主数据服务器，用于FR创建数据集
2.主数据服务器上创建表存储所有用户的用户名、公司，以及各自公司对应的数据库服务器信息，包括IP，数据库用户名，密码和对应的数据库名
3.在主数据服务器上按各客户的数据库信息创建链接服务器linkserver
4.在主服务器上创建存储过程，存储过程按FR传入的用户参数查找该用户所属公司，以及链接服务器信息和数据库名，在存储过程中用动态拼接SQL的方式来从各自公司的数据库中获取数据

1. --exec p_rep_pos_salemaster 'shinger@faya','','2018-04-01','2018-04-01'
   
2. create PROC p_rep_pos_salemaster  
   
3.   @userName NVARCHAR(200),      --传入的用户名参数
   
4.   @shoplist VARCHAR(max),      --店铺列表
   
5.   @begindate VARCHAR(10),       --开始日期
   
6.   @enddate VARCHAR(10) AS       --截止日期

复制代码

--如果传入的用户没有记录，直接返回空结果集

1. IF NOT EXISTS(SELECT 1 FROM fr_user WHERE fruserid=@username)
   
2. BEGIN
   
3.   SELECT '' AS shopid,NULL billdate,NULL aount
   
4.   return
   
5. end
   
6. ELSE
   
7. begin

复制代码

--根据传入的用户参数找到该用户的公司id

1. SELECT @companyid=companyid FROM fr_user WHERE fruserid=@username

复制代码

--根据公司id找到对应的链接服务器

1. SELECT @serveralias=serveralias,@ipadress=ipadress,@serveruser=serveruser,@serverpsd=serverpsd,@databasename=databasename
   
2.    FROM fr_companyserver
   
3.      WHERE companyid=@companyid
   

复制代码

--判断链接服务器是否存在，没有则创建

1. IF not exists(select 1 from master.dbo.sysservers where srvname=@serveralias)
   
2. begin
   
3.     EXEC sp_addlinkedserver @serveralias,'','SQLOLEDB',@ipadress
   
4.     EXEC sp_addlinkedsrvlogin @serveralias,'false',NULL,@serveruser,@serverpsd
   
5. end

复制代码

--定义拼接SQL用于输出门店列表

1. DECLARE @sql NVARCHAR(MAX)
   
2. 
   
3. SET @sql=N'select shopid,cast(billdate as date) billdate,sum(payamount) amount
   
4.                from @serveralias.@databasename.dbo.sd_pos_salemaster where companyid=''faya'' '+CASE WHEN ISNULL(@shoplist,'')='' THEN '' ELSE ' and shopid in ('''+@shoplist+''')' END+
   
5.                                   ' and billdate>='''+@begindate+''' and billdate<'''+@enddate+'''
   
6.                             group by shopid,cast(billdate as date)
   
7.                   union all
   
8.                     select shopid,cast(billdate as date) billdate,sum(payamount) amount
   
9.                from @serveralias.@databasename.dbo.sd_pos_salemaster2 where companyid=''faya'' '+CASE WHEN ISNULL(@shoplist,'')='' THEN '' ELSE ' and shopid in ('''+@shoplist+''')' END+
   
10.                                   ' and billdate>='''+@begindate+''' and billdate<'''+@enddate+'''
    
11.                             group by shopid,cast(billdate as date)'
    
12. SET @sql=REPLACE( REPLACE(@sql,'@serveralias',@serveralias),'@databasename',@databasename)
    
13. 
    
14. PRINT @sql

复制代码

--执行动态拼接脚本输出结果

1. EXEC sp_executesql @sql
   
2. END
   
3. SET NOCOUNT OFF

复制代码

有没有大神能提供更好的可行方案的？

难！用户名相同呢？结合密码判断？密码也相同呢？加前掇？
给你一个参考思路：
建立两个数据源，JDBC1和JDBC2，JDBC1是主数据源，用于登录，用户表里包含你所有客户数据库中的用户信息，其中用户表中设一个字段作为JDBC2的URL连接信息，如：jdbc:sqlserver://IP:1433;databaseName=***JDBC2的URL连接信息用参数表达，如：${UserDB}，也就是说，JDBC2的数据库连接信息是动态的，是根据不同用户名来实现不同的数据库连接信息。
前提是SQL后台登录名和口令要全部一样。
那么每一个报表里的数据集都连接到JDBC2中。也就是所有报表都是以JDBC2为数据库。
这只是我的思路，没实现过。但动态产生是连接信息是可行的，官方帮助文档中有提到。
官方绝对不希望每个人都可以买一套实现所有客户共享，因为这是他们的生财之本。所以官方做了许多限制，比如无法把SQL后台登录名和口令包括在连接信息中，而是独立出来。连接信息可以用参数动态传递的初衷应该连接多个年度的数据库，我们只是变通利用了这一点。